Introduction to SQL Server Four "system database" role

The master, model, msdb, and tempdb are referred to as "3m1t", and other system-level databases, such as reportserver, distribution, etc., are introduced in the following versions after choosing to install different components, including the 3M1T database.

"Master database":
The master database is the most important database in SQL Server and records all system information in the SQL Server system, including login accounts, system configuration and settings, names of databases in the server, location of these database files, system processes, linked server, and SQL Server initialization information, and so on. Once the database file is lost or damaged, it will have a significant impact on the operation of the entire SQL Server system, or even the entire system, so backup the master database frequently to recover the database in the event of a problem.

"Model Database":
Model system database is a template database, is to create a new database of the board. It contains the basic objects that are required to create a new database, such as system tables, view tables, login information, and so on. When the system performs a new database operation, it replicates the contents of the template database to the new database. All newly created databases are inherited from this model database, and if you change the contents of the model database, such as adding table/views/store procedures, any database object, the later database will also contain that change. Because the tempdb database is recreated every time SQL Server is started, the model database is also required in SQL Server databases and cannot be missing.

"MSDB database":
The msdb system database is used to provide a "SQL Server Agent Service" for dispatching alerts, jobs, and logging operators. If you do not use these SQL Server Agent services, the system database will not be used.
The SQL Server Agent service is a Windows service in SQL Server that is used to run any scheduled jobs that have been created. A job is a series of operations defined in SQL Server that can be run automatically. For example, if you want to perform a task at a certain time, you can configure the job to let the task execute as required at the specified time. If you need to perform the job properly, make sure that the SQL Server Agent service processes the running state, in addition to the job's normal setup and its code error. In addition, the database can be used to see what job the system has, and the operation record of the job.

"Tempdb database":
The tempdb database is a temporary database that exists during a SQL Server session. Once SQL Server shuts down or restarts, the contents of the tempdb database will automatically disappear. When you restart SQL Server, the system will re-create a new,
An empty tempdb database. How to configure tempdb has an important impact on the performance of production server. The settings for the parameters of tempdb are based on the actual business scenario, and the different workloads have different settings. In a subsequent article
will tell you how to set the item parameters for tempdb.

The main content and role of tempdb preservation:
Displays the creation of temporary objects, such as tables, stored procedures, table variables, or cursors.
Internal worksheets created by SQL Server.
The result of a temporary sort when the index is created or rebuilt.

Introduction to SQL Server Four "system database" role