SQL Server系统数据库–主数据库

介绍 (Introduction)

There are at least 4 system databases in any SQL Server instance as shown by the following SQL Server Management Studio (SSMS) screen capture:

如以下SQL Server Management Studio（SSMS）屏幕截图所示，任何SQL Server实例中至少有4个系统数据库：

master
主
model
模型
msdb
数据库
tempdb
临时数据库

This is my second article about SQL Server system databases.

这是我的第二篇有关SQL Server系统数据库的文章。

The first one was about the tempdb database. In this article I will focus on the master database.

第一个是关于tempdb数据库的。在本文中，我将重点介绍master数据库。

SQL Server中的主数据库使用 (Master database usage in SQL Server)

SQL Server uses the master database to record all information about the SQL Server instance system, like login accounts, endpoints, linked servers and configuration settings.

SQL Server使用master数据库记录有关SQL Server实例系统的所有信息，例如登录帐户，端点，链接的服务器和配置设置。

The information that a SQL Server instance needs is stored in the master database, like the information about all existing databases and the location of their data and transaction log files. If the master database does not exist or cannot be read then the SQL Server instance cannot start.

SQL Server实例所需的信息存储在master数据库中，例如有关所有现有数据库及其数据和事务日志文件的位置的信息。如果主数据库不存在或无法读取，则SQL Server实例无法启动。

Even it is possible to create user objects in master database, it is not recommended to do so. The master database should stay as static as possible. For example, in the case that master database being rebuilt, all user objects will be lost.

即使可以在master数据库中创建用户对象，也不建议这样做。主数据库应尽可能保持静态。例如，在重建主数据库的情况下，所有用户对象将丢失。

运作方式 (Operations)

权限 (Permissions)

By default all users that have access to the SQL Server instance are granted to perform SELECT operations in the master database in the behalf of the public database role. The SELECT permission can be denied for any user as it is for a regular database or even, the public database role can be revoked to control which users may query the metadata from the master database.

默认情况下，所有有权访问SQL Server实例的用户均被授予代表公共数据库角色在master数据库中执行SELECT操作的权限。可以拒绝任何用户的SELECT权限，就像常规数据库一样，甚至可以取消公共数据库角色，以控制哪些用户可以从主数据库查询元数据。

后备 (Backups)

Usually changes in the master database only occurs when there are changes in system objects like add/changing/deleting logins, endpoints or linked servers. A change in the master database can also be caused by changes in the SQL Server instance configuration or a SQL Server patch has been applied. When any of these changes occurs, it is also recommended to perform a backup of the master database.

通常，仅当系统对象（例如添加/更改/删除登录名，端点或链接的服务器）发生更改时，才会发生master数据库中的更改。主数据库中的更改也可能是由于SQL Server实例配置的更改或已应用SQL Server修补程序引起的。当发生这些更改中的任何一个时，还建议对master数据库执行备份。

It is recommended to have regular backups of the master database since it will be very useful when the master database becomes unusable.

建议定期对master数据库进行备份，因为当master数据库不可用时，这将非常有用。

移动主数据库文件位置 (Moving the master database file locations)

As any regular database, master data and log files can be moved to another location if needed. But as opposed to the regular databases, to move master database file locations you will need to use the SQL Server Configuration Manager (SSCM).

与任何常规数据库一样，如果需要，主数据和日志文件可以移动到另一个位置。但是与常规数据库相反，要移动主数据库文件位置，您将需要使用SQL Server配置管理器（SSCM）。

To do this, open SSCM, go to SQL Server Services in the left panel. The existence services will appear listed in the right panel. Right click in the SQL Server service from the instance that you want to move the files and choose Properties. The correspondent Properties window will pop-up, then go to Startup Parameters tab where you will see the existing parameters for the actual path for the master database files.

为此，请打开SSCM，然后转到左侧面板中的SQL Server服务 。存在服务将显示在右侧面板中。右键单击要移动文件的实例中的SQL Server服务，然后选择“ 属性” 。对应的“属性”窗口将弹出，然后转到“ 启动参数”选项卡，您将在其中看到主数据库文件的实际路径的现有参数 。

-d is the parameter for the data file location
-d是数据文件位置的参数
-l is the parameter for the log file location
-l是日志文件位置的参数

Select the one that you want to change and type the new file location and click in the Update button to save the changes made. Repeat the process for all files that you want to move. When done click on the OK button to return to the SQL Server Services pane.

选择要更改的位置，然后键入新文件的位置，然后单击“ 更新”按钮以保存所做的更改。对要移动的所有文件重复该过程。完成后，单击“确定”按钮以返回到“ SQL Server服务”窗格。

Stop the respective SQL Server instance service and copy the master database files to the new location. Now when starting the SQL Server instance, the master database files will run from the new location.

停止相应SQL Server实例服务，然后将主数据库文件复制到新位置。现在，当启动SQL Server实例时，主数据库文件将从新位置运行。

Assure that the SQL Server service account has full control permissions in the new location path, otherwise the following error will occur when attempting to start the SQL Server service:

确保SQL Server服务帐户在新的位置路径中具有完全控制权限，否则在尝试启动SQL Server服务时将发生以下错误：

It can be confirmed by checking in the SQL Server log for the reason. A similar access denied error should been logged:

可以通过检查SQL Server日志中的原因来确认。应该记录类似的访问被拒绝错误：

2017-06-27 08:31:33.64 spid8s Starting up database ‘master’.
2017-06-27 08:31:33.64 spid8s Error: 17204, Severity: 16, State: 1.
2017-06-27 08:31:33.64 spid8s FCB::Open failed: Could not open file C:\temp\master\master.mdf for file number 1. OS error: 5(Access is denied.).
2017-06-27 08:31:33.64 spid8s Error: 5120, Severity: 16, State: 101.
2017-06-27 08:31:33.64 spid8s Unable to open the physical file “C:\temp\master\master.mdf”. Operating system error 5: “5(Access is denied.)”.
2017-06-27 08:31:33.64 spid8s Error: 17204, Severity: 16, State: 1.
2017-06-27 08:31:33.64 spid8s FCB::Open failed: Could not open file C:\temp\master\mastlog.ldf for file number 2. OS error: 5(Access is denied.).
2017-06-27 08:31:33.64 spid8s Error: 5120, Severity: 16, State: 101.
2017-06-27 08:31:33.64 spid8s Unable to open the physical file “C:\temp\master\mastlog.ldf”. Operating system error 5: “5(Access is denied.)”.
2017-06-27 08:31:33.64 spid8s SQL Server shutdown has been initiated

2017-06-27 08：31：33.64 spid8s正在启动数据库'master'。
2017-06-27 08：31：33.64 spid8s错误：17204，严重性：16，状态：1。
2017-06-27 08：31：33.64 spid8s FCB :: Open失败：无法为文件号1打开文件C：\ temp \ master \ master.mdf。操作系统错误：5（访问被拒绝。）
2017-06-27 08：31：33.64 spid8s错误：5120，严重性：16，状态：101。
2017-06-27 08：31：33.64 spid8s无法打开物理文件“ C：\ temp \ master \ master.mdf”。操作系统错误5：“ 5（拒绝访问。）”。
2017-06-27 08：31：33.64 spid8s错误：17204，严重性：16，状态：1。
2017-06-27 08：31：33.64 spid8s FCB :: Open失败：无法为文件号2打开文件C：\ temp \ master \ mastlog.ldf。操作系统错误：5（访问被拒绝。）
2017-06-27 08：31：33.64 spid8s错误：5120，严重性：16，状态：101。
2017-06-27 08：31：33.64 spid8s无法打开物理文件“ C：\ temp \ master \ mastlog.ldf”。操作系统错误5：“ 5（拒绝访问。）”。
2017-06-27 08：31：33.64 spid8s SQL Server关闭已启动

After confirming that SQL Server instance is up and running properly you may delete the old master data and log files from the old location.

在确认SQL Server实例已启动并正常运行后，您可以从旧位置删除旧的主数据和日志文件。

储存程序 (Stored procedures)

As a regular database, the master database has its own system stored procedures and allows user stored procedures to be created and stored as well.

作为常规数据库，主数据库具有其自己的系统存储过程，并允许创建和存储用户存储过程。

It has the particular ability feature of a special stored procedure that can execute any other stored procedure whenever the SQL Server instance starts. This automation can be configured with the SP_PROCOPTION, available only for members of the sysadmin server role.

它具有特殊存储过程的特殊功能功能，该特殊存储过程可以在SQL Server实例启动时执行任何其他存储过程。可以使用SP_PROCOPTION来配置此自动化功能，该功能仅对sysadmin服务器角色的成员可用。

Another particularity is that Extended Stored Procedures can only be defined in the master database.

另一个特殊之处是扩展存储过程只能在master数据库中定义。

限制条件 (Restrictions)

The master database has many restrictions and some are explained below.

主数据库有许多限制，下面将对一些限制进行说明。

删除数据库 (Drop database)

The master database cannot be deleted. If you try to delete it you will receive an error

主数据库无法删除。如果您尝试将其删除，则会收到错误消息

saying that a system database cannot be dropped:

说不能删除系统数据库：

设为离线 (Set offline)

The master database cannot be set to offline. If you try to do it you will receive the respective error:

主数据库无法设置为脱机。如果尝试这样做，则会收到相应的错误：

数据库重命名 (Database rename)

It is not possible to rename the master database. If you try to do it you will receive the respective error:

无法重命名master数据库。如果尝试这样做，则会收到相应的错误：

变更资料库拥有者 (Change database owner)

It is not possible to change the owner of the master database. If you try to do it you will receive the respective error:

无法更改master数据库的所有者。如果尝试这样做，则会收到相应的错误：

更改数据捕获（CDC） (Change Data Capture (CDC))

It is not possible to enable the Change Data Capture feature on the master database. If you try to do it you will receive the error stating that system databases do not support CDC:

无法在主数据库上启用“更改数据捕获”功能。如果尝试执行此操作，则会收到错误消息，指出系统数据库不支持CDC：

其他限制 (Other restrictions)

There are some more restrictions that are good to be known:

还有其他一些众所周知的限制：

The master database and its primary filegroup cannot be set to READ_ONLY status;
无法将master数据库及其主文件组设置为READ_ONLY状态。
The master database does not allow for adding more filegroups for the database nor rename the primary filegroup;
主数据库不允许为该数据库添加更多文件组，也不能重命名主文件组。
The primary filegroup, primary data file, primary log file and the guest user of master cannot be deleted;
无法删除主文件组，主数据文件，主日志文件和master的guest用户。
The default collation for the master database is the SQL Server instance collation and cannot be changed without rebuilding the SQL Server instance itself;
主数据库的默认排序规则是SQL Server实例排序规则，如果不重建SQL Server实例本身，则无法更改。
The master database cannot be part of a database mirroring solution;
主数据库不能成为数据库镜像解决方案的一部分。
Triggers cannot be created on the master database system tables;
无法在主数据库系统表上创建触发器；
A full-text catalog and full-text index cannot be created on the master database.
不能在master数据库上创建全文目录和全文索引。

参考资料 (References)

翻译自: https://www.sqlshack.com/sql-server-system-databases-the-master-database/