What is the function of the SQL Server System database?

Master

The Master database stores all the databases on the SQLSERVER entity, and it is also a binder that holds the engine. If you do not use the primary database, SQLSERVER cannot be started. Therefore, you must carefully manage the database. Therefore, it is necessary to perform regular backup for this database.

This database includes information such as system logon, configuration settings, Connected SERVER, and general information of other systems and user databases used for this entity. The primary database also has an extended stored procedure that can access external processes, allowing you to interact with features such as disk subsystems and system API calls. These processes generally use modern programming languages like C ++.

If the primary database must be recovered due to a system crash, you can refer to the articles published by MCSE/mcba Steven Warren on TechRepublic. This article is very thorough and explains some special steps required to restore this important database.

Model

Model is a template database used to create a user database on an object. You can place any stored procedures, views, users, and so on in the model database. When creating a new database, the new database will contain all the objects you put in the model database.

Tempdb

As prompted by its name, tempdb has temporary objects such as global and local temporary tables and stored procedures.

This database is re-created every time SQLSERVER restarts, and the objects contained in the database are created based on the objects defined in the model database. In addition to these objects, tempdb also contains other objects, such as table variables, result sets from Table value functions, and temporary table variables. Since tempdb retains these object types of all databases on SQLSERVER, it is very important to optimize the database configuration.

In SQL Server 2005, The tempdb database also has an additional task. It is also used as a version library for some features, such as the new Snapshot isolation layer and online index operations. For a brief description of the new isolation layer, please refer to my article on SQL Server 2005 advanced features.

Distribution

When your SQLSERVER entity is configured as a replication distribution SERVER, this database will be added to your system. By default, the database name is distribution, but you can change its name. This database is used to save the metadata of history and snapshots, merge and transaction replication.

Msdb

The Msdb database is used to store information such as database backup, SQL Agent information, DTS Packages, and SQLSERVER tasks, and replication information such as log transfer.