Datasheet

System Databases
The system databases in SQL Server are crucial, and you should leave them alone most of the time. The
only exception to that rule is the
model database, which allows you to deploy a change like a stored pro-
cedure to any new database created. If a system database is tampered with or corrupted, you risk your
SQL Server not starting. They contain all the stored procedures and tables needed for SQL Server to
remain online.
The Resource Database
New to SQL Server 2005 is the Resource database. This database contains all the read-only critical sys-
tem tables, metadata, and stored procedures that SQL Server needs to run. It does not contain any infor-
mation about your instance or your databases, because it is only written to during an installation of a
new service pack. The
Resource database contains all the physical tables and stored procedures refer-
enced logically by other databases. The database can be found by default in
C:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource.mdf
, and there is only one
Resource database per instance.
In SQL Server 2000, when you upgraded to a new service pack, you would need to run many long
scripts to drop and recreate system scripts. This process took a long time to run and created an environ-
ment that couldn’t be rolled back to the previous release after the service pack. In SQL Server 2005, when
you upgrade to a new service pack or quick fix, a copy of the
Resource database overwrites the old
database. This allows you to quickly upgrade your SQL Server catalog and allows you to roll back a
release.
The
Resource database cannot be seen through Management Studio and should never be altered unless
you’re under instruction to do so by Microsoft Product Support Services (PSS). You can connect to the
database under certain single-user mode conditions by typing the command
USE
MSSQLSystemResource
. The majority of what a DBA does is run simple queries against it while con-
nected to any database. For example, if you were to run this query while connected to any database, it
would return your
Resource database’s version and the last time it was upgraded:
SELECT serverproperty(‘resourceversion’) ResourceDBVersion,
serverproperty(‘resourcelastupdatedatetime’) LastUpdateDate
Do not place the Resource database on an encrypted or compressed drive. Doing this may cause
upgrade or performance issues.
The Master Database
The master database contains the metadata about your databases (database configuration and file loca-
tion), logins, and configuration information about the instance. If this important database is lost, your
SQL Server may not be able to start. For example, by running the following query, you will see what
databases are installed on the server:
SELECT * FROM sys.databases
The master database’s role has been slightly diminished in SQL Server 2005 with the addition of the
Resource database, but it is no less important. The main difference between the Resource and master
databases is that the master database holds data specific to your instance, while the Resource database
6
Chapter 1
04_055200 ch01.qxp 10/31/06 12:37 PM Page 6