SQLGeeksPro

Hey there! this is the 3rd article in the category “SQL Server DBA Tutorial: Beginners to Pro”. Here we are going to discuss about SQL Server System databases. The prior article in this series is Understanding SQL Server Versions and Editions

Introduction

To begin with, when you install SQL Server, it creates the system databases automatically. Understanding the role of system databases is crucial. Here we are going to explore everything you need to know about SQL Server system databases,
including their purpose, components, and best practices for managing them.

Table of Contents

• What are System Databases?
• Importance of System Databases
• Understanding the Core System Databases
  • Master
  • Model
  • Msdb
  • Tempdb
  • Resource Database
• Distribution Database
• Best Practices to maintain the System Databases

What are System Databases?

System databases are the backbone of a SQL Instance. Each of them play a critical role in the functioning of the database management system. These databases provide critical services to the server and user databases. Each system database has a specific purpose, and they work in harmony.

Importance of System Databases

System databases contain crucial information about the SQL Server instance, including server-level configurations, system objects, and metadata. Understanding the purpose and components of these databases is essential for efficient database management, backup and recovery, and overall system stability.

Understanding the Core System Databases

Now let’s take a deep dive into the core System Databases. The below image shows the system databases when you connect to a SQL Instance.

Master Database:

Contains all the system-level information for an instance of SQL Server. The master database is the most important as it is necessary for starting the SQL Server.

This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database:

The model database is used as a template for other databases created in SQL Server, especially when creating user-defined databases.

The entire configuration of the model database, including database options, are copied to the newly created database.

Every time SQL Server is started, it creates tempdb from model hence if model is corrupted, SQL will not come up normally.

Msdb Database: 

The msdb database is used mainly by the SQL Server Agent, and also by other features like SQL Server Management Studio, Service Broker, and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.

Tempdb Database:

TempDB is a global resource that is accessed by all the users in the SQL Server Instance

This stores temporary user objects that you create like temporary tables (Global and Local), temporary variables, temporary stored procedures that hold intermediate results during the sorting or query processing, and cursors.

Internal objects like Work Tables, Work files. Intermediate sort results for operations such as creating or rebuilding indexes.

Version stores, which are collections of data pages that hold the data rows that support features for row versioning.

Resource database:

The Resource Database is a hidden, read-only database that contains system objects, system catalogs, and metadata.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.

Microsoft created this database to allow very fast and safe upgrades. You can’t see the database by selecting from sys.databases or
executing sp_helpdb. It also won’t show up in the system databases tree in the Object Explorer pane
of SQL Server Management Studio, and it doesn’t appear in the drop-down list of databases from your query windows.

However, this database still needs disk space.
You can see the files in your default binn directory by using Microsoft Windows Explorer. For Example: C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn

mssqlsystemresource.mdf,  and mssqlsystemresource.ldf files.

To explain refer the below screen shot:

Distribution Database:

SQL Server creates this database when you configure Replication on SQL Server and you choose the SQL Instance to act as a distributor. It stores replication metadata, history, and the transactions to replicate to other servers.

Best practices to Maintain System Databases:

Create a robust backup strategy for Regular backups of system databases.

Make proper SOP for restoring the system databases so that it is easier in the event of a disaster. Not to mention knowing how to restore system databases is essential for recovering your SQL Server instance.

Managing the size of system databases, especially TempDB, can significantly impact server performance. Hence proper Sizing and Growth Planning is very essential.

Equally important is monitoring solutions to keep an eye on the health and performance of your system databases.

In addition Consider placing system databases on separate disks to prevent contention and optimize performance.