SqlServer series: Database composition and System Database

1. The storage structure of databases is divided into logical and physical storage structures. Logical Storage Structure: describes the nature of the database information. SQL Server databases not only store data, but all information related to data processing operations is stored in the database. Physical storage structure: the database is stored on a disk as a file

1. The storage structure of databases is divided into logical and physical storage structures. Logical Storage Structure: describes the nature of the database information. SQL Server databases not only store data, but all information related to data processing operations is stored in the database. Physical storage structure: the database is stored on a disk as a file

1. Database Composition

The storage structure of a database is divided into logical and physical storage structures.

Logical Storage Structure: describes the nature of the database information. SQL Server databases not only store data, but all information related to data processing operations is stored in the database.

◊ Physical storage structure: the database is stored in files on disks and consists of database files and transaction log files. A database should contain at least one database file and one transaction log file.

Database files in the SQL Server database system are composed of data files and log files. data files are stored in storage units in the disk area.

1.1> data files

A database file is a file used to store database data and database objects. A database can have one or more database files. A database file can belong to only one database. When multiple database files exist, one file is specified as the primary data file to store the database startup information and part or all of the data. A database can only have one primary database file. Data files are divided into different pages and regions. pages are the basic unit for SQL Server to store data.

The primary data file is the starting point of the database and points to other parts of the database file. Each database has a primary data file with the extension. mdf.

　　The secondary data file contains all the data files except the primary database file. A single database can have no secondary data files or multiple secondary data files with the extension. ndf.

1.2> log files

SQL Server logs are composed of a series of log records, which record transaction log information such as database updates, all operations performed on the database, such as insertion, deletion, and update, are recorded in the log file. When the database is damaged, you can analyze the cause of the Error Based on the log file, or use the transaction log to restore the database when data is lost. Each database must have at least one transaction log file and multiple log files are allowed.

SQL Server2012 does not force the use of. mdf,. ndf, or. ldf as the file extension. However, we recommend that you use these extensions to help use standard files. The locations of all files in the database are recorded in the master database and the database in the master data file.

2. System Database

2.1> master database

Master is the most important database in SQL Server and the core of the entire database Server. You cannot directly modify the master database. If the master database is damaged, the entire SQL Server cannot work.

The master database contains the following content:

Login information of all users

Region User Group

◊ Configuration options for all systems

Name and information of the local database in the hosts Server

◊ SQL Server Initialization Method

As a database administrator, you should regularly back up the master database.

2.2> model database

The model database is the template used to create a database in SQL Server.

When you want to create a database with the same initialization file size, you can save the file size information in the model database;

Users wants all databases to have the same one or more tables. They can save the data table in the model database.

The newly created database uses the data in the model database as the template. Therefore, before modifying the model database, any modification to the data in the model database must affect all databases created using the template.

2.3> msdb Database

Msdb provides information about running the SQL Server Agent. The SQL Server Agent is a Windows Service in SQL Server. It is used to run scheduled tasks.

2.4> tempdb Database

Tempdb is a temporary database in SQL Server used to store temporary objects or intermediate results. After SQL Server is disabled, the content in the database is cleared. After the Server is restarted, the tempdb database will be rebuilt.