Page 51 - DCAP508_DATABASE_ADMINISTRATION

Unit 4: SQL Server Storage Architecture

It will present some benefits and concerns about different storage designs so that you can more Notes
easily choose what suits your solution. We will finish the chapter with a presentation of SQL
Server-examining its architecture and how it fits into your storage policy.

4.1 Resource Database

The Resource database is a read-only database that contains all the system objects that are
included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted
in the Resource database, but they logically appear in the sys schema of every database. The
Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster
procedure. In earlier versions of SQL Server, upgrading required dropping and creating system
objects. Because the Resource database file contains all system objects, an upgrade is now
accomplished simply by copying the single Resource database file to the local server.

4.1.1 Physical Properties of Resource

The physical file names of the Resource database are mssqlsystemresource.mdf and
mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL
Server\MSSQL10_50.<instance_name>\MSSQL\Binn\. Each instance of SQL Server has one and
only one associated mssqlsystemresource.mdf file, and instances do not share this file.

4.1.2 Backing Up and Restoring the Resource Database

SQL Server cannot back up the Resource database. You can perform your own file-based or a
disk-based backup by treating the mssqlsystemresource.mdf file as if it were a binary (.EXE) file,
rather than a database file, but you cannot use SQL Server to restore your backups. Restoring a
backup copy of mssqlsystemresource.mdf can only be done manually, and you must be careful
not to overwrite the current Resource database with an out-of-date or potentially insecure
version.

Notes After restoring a backup of mssqlsystemresource.mdf, you must reapply any
subsequent updates.

4.1.3 Accessing the Resource Database

The Resource database should only be modified by or at the direction of a Microsoft Customer
Support Services (CSS) specialist. The ID of the Resource database is always 32767. Other important
values associated with the Resource database are the version number and the last time that the
database was updated.
To determine the version number of the Resource database, use:
SELECT SERVERPROPERTY(‘ResourceVersion’);

GO
To determine when the Resource database was last updated, use:
SELECT SERVERPROPERTY(‘ResourceLastUpdateDateTime’);
GO

LOVELY PROFESSIONAL UNIVERSITY 45