Vitor Montalvão

SQL Server system databases – the master database

July 6, 2017 by

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:

  • master
  • model
  • msdb
  • tempdb

This is my second article about SQL Server system databases.

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

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.

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.

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.

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.

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.

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

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).

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.

  • -d is the parameter for the data file location
  • -l is the parameter for the log file location

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.

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.

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:

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

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

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.

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.

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

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:

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:

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:

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;
  • 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;
  • The default collation for the master database is the SQL Server instance collation and cannot be changed without rebuilding the SQL Server instance itself;
  • 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.

Other articles in this series:

References


Vitor Montalvão
Latest posts by Vitor Montalvão (see all)

Related posts:

  1. Restoring Transparent Data Encryption (TDE) enabled databases on a different server
  2. SQL Server system databases – the msdb database
  3. SQL Server system databases – the model database
  4. Understanding benefits of Graph Databases over Relational Databases through Self-Joins in SQL Server
  5. How to replace hardcoded lookups using SQL Server Master Data Services
168 Views