SQL Server 2008 (1) (2)

---restore content starts---

SQL Server 2008 (1)

System Database

Master

Tempdb

Module

Body

A basic skill of the 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 database that comes with SQL Server. 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 the databases placed on the SQL Server entity, and it is the glue that holds the engine together. If you do not use the primary database, SQL Server will not start, so you have to carefully manage the database. It is necessary to make regular backups of this database. It is recommended that you back up the master database when a database change occurs.

This database includes information such as system logins, configuration settings, connected servers, and general information about other systems and user databases for that entity. The primary database also has an extended stored procedure that provides access to 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 unfortunately the system crashes and you have to restore the master database, you can refer to MCSE/MCDBA Steven Warren's post on TechRepublic. This article is very thorough and 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. So the new database should be as large as the model database. When we create the database, we specify the size of the database, which is usually larger than the size of the model database, because the empty page is filled in.

Tempdb

As the name suggests, tempdb has temporary objects, such as global and local temporary tables and stored procedures. This database is recreated every time that SQL Server restarts, and contains objects that are created from the objects defined in the model database. In addition to these objects, tempdb 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 the new snapshot isolation layer and the online index 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 store metadata for historical and snapshot, merge, 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.

SQL Server 2008 (2)

Database creation

General-Primary and secondary database files (MDF) and log files (LDF)

Options

Filegroup-PRIMARY filegroup and secondary filegroup

Database object creation

---restore content ends---