Blog Introduction: SQL Server is a powerful database system that provides an array of features and capabilities. One of these features is the system databases, which are essential for managing and maintaining the system. In this blog post, we will take a look at the different system databases in SQL Server and how they are used.
Master Database
The master database is the main repository for all data related to the entire SQL Server instance. It stores information about logins, configuration settings, and other system-level information such as linked servers, linked server logins, and server certificates. The master database should never be modified directly; instead, any changes should be done via stored procedures or built-in commands.
The master database also contains metadata about all the other databases on the SQL Server instance, including their file locations and configuration settings. If the master database becomes corrupt or is lost, the entire SQL Server instance may become unusable.
To prevent this, it is important to back up the master database regularly and to keep a copy of the backup in a safe location. Additionally, any changes made to the master database should be carefully planned and tested before implementation to avoid any unintended consequences.
The following are some important Dynamic Management Views (DMVs) that are available in the system databases of SQL Server:
sys.dm_tran_active_transactions - This DMV provides information about all currently active transactions in the SQL Server instance.
sys.dm_db_index_usage_stats - This DMV provides information about the usage of indexes in a database, such as when an index was last used, and how many times it has been used.
sys.dm_exec_query_stats - This DMV provides information about the performance of queries that have been executed on the SQL Server instance.
sys.dm_os_wait_stats - This DMV provides information about the wait statistics of the SQL Server instance, such as how long tasks have been waiting for a particular resource.
sys.dm_exec_connections - This DMV provides information about the active connections to the SQL Server instance, including the login name, client address, and connection time.
sys.dm_db_file_space_usage - This DMV provides information about the space usage of database files, such as the size of data and log files, and how much space is used.
sys.dm_db_partition_stats - This DMV provides information about the space usage of individual partitions in a database, including the number of rows and pages.
These DMVs can be used to monitor and optimize the performance of SQL Server, as well as troubleshoot issues with the system.
The following operations cannot be performed on the master database:
Adding files or filegroups.
Backups, only a full database backup can be performed on the master database.
Changing collation. The default collation is the server collation.
Changing the database owner. master is owned by sa.
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.
Recommendations
When you work with the master database, consider the following recommendations:
Always have a current backup of the master database available.
Back up the master database as soon as possible after the following operations:
Creating, modifying, or dropping any databas
Changing server or database configuration values
Modifying or adding logon accounts
Do not create user objects in master. If you do, master must be backed up more frequently.
Do not set the TRUSTWORTHY option to ON for the master database.
What to Do If master Becomes Unusable
If the master database becomes unusable in SQL Server, it can be a critical situation because the master database stores information about all other databases and server-level objects, such as logins and endpoints. Here are the steps to follow in case the master database becomes unusable:
Try to restore a backup: If you have a recent backup of the master database, you can restore it to a different instance and extract the necessary information. In this case, you might lose any changes that were made after the last backup.
Rebuild the master database: If you don't have a recent backup, you can rebuild the master database by running the setup program for SQL Server and choosing the "Rebuild the system databases" option. This will create a new master database with the default settings.
Reattach user databases: After rebuilding the master database, you need to reattach any user databases that were detached before the rebuild.
Restore server-level objects: If you have a script or backup of the server-level objects, you can restore them using that script or backup. This will help to restore the server to its original state.
Recreate logins: If you don't have a script or backup of the server-level objects, you will need to recreate any logins, endpoints, and other server-level objects manually.
It is important to note that rebuilding the master database should be done only as a last resort, as it can cause significant downtime and might result in data loss. Therefore, it is recommended to have regular backups of the master database and to test the backup and restore process to ensure that it works correctly.
Model Database
The model database acts as a template for all new databases created on the SQL Server instance. When a new database is created, it inherits all of its settings from the model database. This includes default filegroups and default file sizes as well as other options such as page checksums and auto-growth settings. If you need to apply certain configurations to all new databases, then it is best to modify the model database instead of manually setting them up with each new database creation.
MSDB Database
The MSDB database contains information about backups, replication activities, SQL Agent jobs, alert notifications and operators, SSIS packages, and other maintenance activities. This information can be accessed by using views or stored procedures that are provided by Microsoft.
Recommendations
When you work with the msdb database, consider the following recommendations:
Always have a current backup of the msdb database available.
Back up the msdb database as soon as possible after the following operations:
Creating, modifying, or deleting any jobs, alerts, proxies or maintenance plans
Adding, changing or deleting database mail profiles
Adding, modifying or deleting Policy based management policies
Do not create user objects in msdb. If you do, msdb must be backed up more frequently.
Treat the msdb database as highly sensitive and do not grant access to anyone without a proper need. Especially keep in mind, that SQL Server Agent jobs are often owned by members of the sysadmin-role and therefore make sure that code that is executed cannot be tampered with.
Audit any changes to objects in msdb
The following operations cannot be performed on the msdb database:
Changing collation. The default collation is the server collation.
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 primary filegroup to READ_ONLY
Here are some common queries to manage the MSDB database in SQL Server:
To view a list of all the jobs in the MSDB database:
USE MSDB
SELECT * FROM sysjobs
To view the job history for a specific job:
USE MSDB
SELECT * FROM sysjobhistory WHERE job_id = 'job_id_here'To view the job schedules for a specific job:
USE MSDB
SELECT * FROM sysjobschedules WHERE job_id = 'job_id_here'To view the backup history for a specific database:
USE MSDB
SELECT * FROM backupset WHERE database_name = 'database_name_here'To view the SQL Server Agent properties:
USE MSDB
EXEC sp_helpserver 'server_name'To view the SQL Server Agent operators:
USE MSDB
SELECT * FROM dbo.sysoperators
Some of the important tables in the MSDB database are:
sysjobs - This table contains information about the SQL Server Agent jobs that have been defined on the instance, such as the job name, owner, and job category.
sysjobsteps - This table contains information about the steps that are defined for a SQL Server Agent job, such as the step name, command, and output file.
sysjobschedules - This table contains information about the schedules that are associated with SQL Server Agent jobs, such as the start and end times, and the frequency of the job.
sysjobservers - This table contains information about the SQL Server instances that are associated with SQL Server Agent jobs, such as the server name and version.
sysalerts - This table contains information about the alerts that have been defined on the instance, such as the alert name, condition, and response.
sysschedules - This table contains information about the schedules that are defined on the instance, such as the start and end times, and the frequency of the schedule.
sysoperators - This table contains information about the operators who are defined on the instance, such as the operator name, email address, and pager number.
These tables and others in the MSDB database can be queried to retrieve information about SQL Server Agent jobs, alerts, and schedules, as well as to monitor and troubleshoot issues with the SQL Server Agent service.
Resource Database
The Resource database is a read-only database in SQL Server that contains all the system objects that are included with the installation of SQL Server. These objects include system stored procedures, system functions, views, and other system-defined database objects. The purpose of the Resource database is to provide a dedicated location for these system objects so that they can be easily accessed and maintained by the SQL Server instance.
The Resource database is created during the installation of SQL Server and is located in the SQL Server installation directory. It is not meant to be modified directly by users or administrators, and attempts to modify its contents may result in system instability or errors. The Resource database is a vital component of SQL Server and should not be deleted or modified without the guidance of Microsoft support.
When a user or application references a system object in SQL Server, the SQL Server engine checks the Resource database first to see if the object exists. If the object is not found in the Resource database, the SQL Server engine then searches the user-defined databases for the object. This allows SQL Server to efficiently manage and organize its system objects and provide a consistent and reliable experience for users and applications.
Other DB's
In addition to the Resource, Master, Model, MSDB, and TempDB databases, there are a few other system databases that may be present in a SQL Server instance.
Distribution database: This database is used in SQL Server replication to store and manage replication data. It is created when replication is configured and is used to store the snapshot files, transaction logs, and other metadata that is necessary for replication to function properly.
ReportServer and ReportServerTempDB databases: These databases are used by SQL Server Reporting Services (SSRS) to store and manage report data. The ReportServer database contains metadata about reports and their execution history, while the ReportServerTempDB database is used to store temporary data that is generated during report processing.
SSISDB: This database is used by SQL Server Integration Services (SSIS) to store and manage SSIS packages, project files, and other artifacts. It provides a centralized location for SSIS administrators to manage and deploy packages across the enterprise.
FileStream Filegroup: This is not a database, but rather a filegroup that can be added to a database. It is used to store large binary data such as images, audio, and video files. FileStream data is stored as files on the file system rather than in the database itself, which can improve performance and reduce storage costs.
Overall, these system databases and features play an important role in the functionality and performance of SQL Server and should be carefully managed and maintained by database administrators.