Get an insight into how SQL Server system databases work

One of the basic skills of a database administrator (DBA) is a deep understanding of the SQL Database engine's system database. It is also useful for database developers to understand the system databases that SQL Server brings. Some of these system databases are listed below. (Note: If you decide to study these system databases, you need to have a development database.) ）

Master

The master database holds all databases that are placed on a SQL Server entity, and it is the glue that keeps the engine fixed. Because SQL Server does not start if you do not use the primary database, you must carefully manage the database. Therefore, regular backups of this database are necessary.

This database includes information such as system logins, configuration settings, connected servers, and other system and user databases for that entity. The master database also has extended stored procedures that can access external processes, allowing you to interact with features such as disk subsystems and system API calls. These processes are generally used in modern programming languages such as C + +.

If you have an unfortunate encounter with a system crash and have to restore the primary database, see MCSE/MCDBA Steven Warren's article on TechRepublic. This article is very thorough, and it explains some of the special steps needed to restore this important database.

Model

Model is a template database that is used to create a new user database on an entity. You can put any stored procedures, views, users, etc. in the model database, so that when you create a new database, the new database will contain all the objects you put in the model database.

Tempdb

As the name suggests, tempdb has temporary objects, such as global and local temporary tables and stored procedures.

The database is recreated every time that SQL Server restarts, and the objects contained are created from objects defined in the model database. In addition to these objects, tempdb also has other objects, such as table variables, result sets from table-valued functions, and temporary table variables. Because tempdb retains these object types for all databases on the SQL Server entity, it is important to optimize the configuration of the database.

In SQL Server 2005, the tempdb database has an additional task, and it is also used as a repository for some features, such as a new snapshot isolation layer and an online indexing operation. For a brief description of the new isolation layer, please refer to my article on SQL Server 2005 advanced features.

Distribution

When your SQL Server entity is configured to replicate the distribution server, the database is added to your system. By default, the name of the database is distribution, but you can change its name. This database is used to hold metadata for history and snapshots, mergers, and transactional replication.

Msdb

The msdb database is used to store information such as database backups, SQL Agent information, DTS packages, SQL Server tasks, and replication information such as log transfers.

Conclusion

Over the past few years, I've found that the best way to understand SQL Server is to study how the system database works. As a general rule, I don't recommend querying system tables directly in SQL Server, but you can learn a lot about the workings of SQL Server by studying the tables in these system databases.