The role of SQL Server system database in depth understanding _mssql

Source: Internet
Author: User
Tags configuration settings
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.
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.