SQL Server系统数据库详解

介绍
这里我们介绍SQL Sever内部的系统数据库的作用和用户数据库之间联系，关于SQL Sever如何管理用户数据库的原理，对于 每个数据库 开发人员和DBA 都是必须掌握的。

SQL Sever系统数据库是用来存储系统级的数据和元数据。

SQL Sever包含了6个系统数据库:Master 、Model 、MSDB 、TempDB 、Resource 、Distribution 。

不知道你有没有疑问？在SQL Sever Manage里不是只有4前面个数据？

其实系统数据包含了Master, Model, MSDB, TempDB, 和一个隐藏的Resource 数据库。如果你的服务是一个分布式的据库服务，那就还有个系统的distribution数据库。接下来我们会对这六个系统数据库分别做出 解释，说明其用途。

Master数据库

Master数据库记录了所有的SQL Server数据库系统的系统级信息。包括实例范围内的元数据，如登录帐号，终端，连接服务器和系统配置设置元数据。另外，Master是数据库，记录了所有其他数据库的基本信息，以及这些数据库文件的位置，并记录为SQL Server的初始化信息。因此，如果Master数据库不可用的话，SQL Server是无法启动的。但是在SQL Server 2005或者更高版本里并不是把系统信息存储在master数据库中，而是在Resource数据库。但是，系统的信息在逻辑上表现还是在Master数据库中。而且我们知道，这些系统数据库是存放在C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA。

对于Master数据库来说，数据文件和日子文件分别是在该路径下的master.mdf和mastlog.ldf。

但是操作Master数据库会有些限制，下面列举一些case（为了说明原意还是写了英文），

Adding files or filegroups.
Changing collation. The default collation is the server collation.
Changing the database owner. Master is owned by dbo.
Creating a full-text catalog or full-text index.
Creating triggers on system tables in the database.
Dropping the database.
Dropping the guest user from the database.
Enabling change data capture.
Participating in database mirroring.
Removing the primary filegroup, primary data file, or log file.
Renaming the database or primary filegroup.
Setting the database to OFFLINE.
Setting the database or primary filegroup to READ_ONLY. 

值得注意的是，我们操作Master数据库时，会对当前Master数据库做一个备份，而且他的备份工作一般在以下一些操作时执行：
Creating, modifying, or dropping any database
Changing server or database configuration values
Modifying or adding logon accounts
因此不要在Master数据里创建对象，否则Master数据库会经常被做备份。而且也不要对Master数据库的'TRUSTWORTHY'选项设置为'ON'。

Model数据库

Model数据库是一个模板数据库。每当创建一个新的数据库（包括系统数据库的TempDB），会创建一个以Model数据库为副本数据库，并更改成你创建数据库时所用的名称。这样的的优点是可以通过模板数据库先前创建好基本对象，然后创建新数据库，这样新建立的数据库就有了该基本对象。
举个例子，下面是怎么样用Model数据库来创建一个自己的数据实例。

USE Model
GO
CREATE FUNCTION dbo.Msg (@String VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
SET @String='Running this from Model for '+@String+' Database'
RETURN @String
END

CREATE DATABASE MyDB;
USE MyDB
GO
SELECT dbo.Msg('MyDB');
-----------------------------------------
Running this from Model for MyDB Database

(1 row(s) affected)

CREATE DATABASE DEMO;
USE DEMO
GO
SELECT dbo.Msg('DEMO');
-----------------------------------------
Running this from Model for DEMO Database

(1 row(s) affected)

MSDB数据库
MSDB数据库是SQL Server代理的数据库。这是因为SQL Server代理是通过使用MSDB数据库来做存储自动化作业定义，作业调度，操作定义，触发提醒定义。代理是负责几乎所有自动化操作和调度操作。MSDB还包含了所有的工作准备，比如对于开始任何工作，得到了状态或停止作业命令，这些都是运行在MSDB数据库中。
Use msdb
GO
sp_help_job
sp_start_job
sp_stop_job
MSDB数据库不仅仅是SQL Server代理来大量使用的唯一服务，比如Broker服务，数据库邮件服务和Reporting服务也是使用MSDB数据库来保存调度信息。除了之外，SQL Server集成服务（SSIS）也利用了MSDB数据库为SSIS包存储。

TempDB数据库
TempDB数据库是由SQL Server用于暂时存储数据的。TempDB数据库被经常使用的在SQL Server操作中，所以认真的计划和评价它的大小和位置是至关重要的，以确保有效的SQL Server数据库操作。TempDB数据库被数据库引擎用来保存和存储临时对象（如临时表，视图，游标和表值变量），这些当然也可以由由数据库程序员创建使用。

此外，TempDB数据库被SQL Server数据库引擎用来保存中间查询结果，用于排序操作前或操作其他数据。例如，如果你写一个查询，返回100行，你想通过一个结果的日期值排序时，SQL Server可能首先把未排序的结果发送到一个临时工作表中，然后将执行排序操作，然后返回排序你的结果。如果联机索引操作执行中，tempdb数据库会保存这个索引。

另一个需要牢记的一点是，对于TempDB数据库，是所有用户可以访问的，可以创建和创造和修改临时对象。这种访问有可能会带来死锁和大小限制的问题，因此就像任何其他SQL Server数据库，对tempdb数据库的监测是很重要的。

Resource数据库

接着介绍下Resource系统数据库。Resource数据库是一个只读数据库，包含所有的SQL Server实例使用的系统对象。Resource数据库是无法访问作为一个正常的数据库，也不能像正常数据库那样操作。它在逻辑上被看作是系统模式上的。它不包含任何用户数据或元数据。然而，它包含了所有的系统对象的结构和说明。
这种设计使得能够快速建立一个新的服务包应用，只要更换一个新的现有Resource数据库的。而且，假如要恢复一个服务包，你也只要把旧的Resource数据库更换成新的Resource数据库。这样极大的方面了数据库服务要求删除或增加了新的系统对象后简单安全运行。Resource数据库的物理文件名是mssqlsystemresource.mdf和mssqlsystemresource.ldf。默认情况下，这些文件位于 C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\下。每个SQL Server实例都只有一个关联的mssqlsystemresource.mdf文件，和其它实例不共享此文件。资源数据库依赖于master数据库的位置。注意，如果你移动master数据库，则必须同时移动资源数据库到相同的位置。

下面的代码可以来确定Resource数据库的版本号:

SELECT SERVERPROPERTY('ResourceVersion');
GO
----------
10.00.1600To determine when the Resource database was last updated, use:

 Collapse | Copy Code
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');
GO
-----------------------

Distribution数据库
最后讲的是Distribution系统数据库。一旦服务器实例配置成一个复制的分布数据库，Distribution数据库被创建在目录中。分发数据库存储元数据和历史数据的所有类型的复制和事务复制的事务。

结束语

对于在MS SQL Server数据库开发的初学者，我觉得有必要得到很好的了解系统数据库的基础知识，每一个是什么用途。希望这篇文章能够给你们带来帮助。

参考：http://www.codeproject.com/KB/database/Understanding_SysDatabase.aspx