In-depth understanding of the operating principles of sqlserver System databases)

A basic skill of the database administrator (DBA) is a deep understanding of the system database of the SQL database engine. It is also useful for database developers to understand the built-in system databases of sqlserver. Some System databases are listed below. (Note: If you decide to study these system databases, you need a development database .) J: 2y0dg
'R5w + V
Master Xzyj1a rk
-"P nqzi
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. Q> bkww/+
Drfc y
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 systems like C ++. Programming Language . Fp3 ^ p1u $
BDN ^ 'C *
If the primary database must be recovered due to a system crash, you can refer to Article . This article is very thorough and explains some special steps required to restore this important database. > 2 = nt + * w
[Hkga2g
Model > 4O \ Kd =
] Xyooygj
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. M $ 5%
A8rs! /B | @
Tempdb T-Egs8x 'z
; Jitd \ _ WR
As prompted by its name, tempdb has temporary objects such as global and local temporary tables and stored procedures. {$7 'I ^ Q! I +
] * W/8ke7 /"
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. ) B: i0xam
\ 5! JZ + N *
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. 7uh | UJD
A (G; b9mk
Distribution Rz | Z + 1O
Z'5zco2
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. YL "~ Dyjl
\? Tw [%
MSDB Wgrit3zq
Wszb3e "G
MSDB databases are stored in database backup, SQL Agent information, and data transmission Program Package, sqlserver task and other information, as well as replication information such as log transfer. V1j]-84> S
F ~ X & IE
Conclusion J zrrc_1
^ # Is: G
Over the past few years, I have found that the best way to understand sqlserver is to study the operating principles of System databases. As a general rule, I do not recommend that you query tables directly in sqlserver. However, by studying the tables in these system databases, you can learn a lot about the working principles of sqlserver.