SQL Server 2012 notes Sharing-38: Understanding System Databases

master database

Records all system-level information for an instance of SQL Server.

The master database records all system-level information for the SQL Server system. This includes instance-scoped metadata (such as login accounts), endpoints, linked servers, and system configuration settings. In addition, the master database records the presence of all other databases, the location of the database files, and the initialization information for SQL Server. Therefore, if the master database is unavailable, SQL Server cannot start. in SQL Server, System objects are no longer stored in the master database, but are stored in the Resource database.

The following table lists the initial configuration values for the master data and log files. For different versions of SQL Server, these files may be slightly different in size.

650) this.width=650; "title=" clipboard "style=" Border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard "Src=" http://img1.51cto.com/attachment/201406/24/639838_1403574822gPBb.png "height="/>

When using the master database, consider the following recommendations:

1. always have a current backup of the master database available.

2. After performing the following actions , back up the master database as soon as possible:

3. Create, modify, or delete any database

4. change the configuration value of the server or database

5. Modify or add a login account

6. Do not create user objects in master. Otherwise, you must back up master more frequently.

7. do not target The master database sets the trustworthy option to on.

You cannot perform the following actions in the master database:

2. Add a file or filegroup.

4. Change the collation. The default collation is the server collation.

6. Change the database owner. Master is owned by the dbo.

8. Create a full-text catalog or full-text index.

10. Create a trigger on the system table of the database.

12. Delete the database.

14. Remove the Guest user from the database.

16. Enable change data capture.

18. Participate in database mirroring.

20. Delete the primary filegroup, master data file, or log file.

22. Renames a database or primary filegroup.

24. Set the database to OFFLINE.

26. Set the database or primary filegroup to READ_ONLY.

===============================================================

msdb database

Used for SQL Server agent scheduled alerts and jobs.

The msdb database is used by SQL Server Agent to schedule alerts and jobs, or it can be used by other features, such as Service Broker and Database Mail.

650) this.width=650; "title=" clipboard[1] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [1] "src=" http://img1.51cto.com/attachment/201406/24/639838_1403574822TCHY.png "height=" "/>

You cannot perform the following actions in the msdb database:

2. Change the collation. The default collation is the server collation.

4. Delete the database.

6. Remove the Guest user from the database.

8. Enable change data capture.

10. Participate in database mirroring.

12. Delete the primary filegroup, master data file, or log file.

14. Renames a database or primary filegroup.

16. Set the database to OFFLINE.

18. Set the primary filegroup to READ_ONLY.

===============================================================

Model database

Serves as a template for all databases created on the instance of SQL Server. Modifications to the model database, such as database size, collation, recovery model, and other database options, are applied to all databases that are created later.

The model database is used as a template for all databases created on the instance of SQL Server. because Tempdb is created every time SQL Server is started, the model database must always exist in the SQL Server system.

Use of model

When the CREATE DATABASE statement is issued, the first part of the database is created by copying the contents of the model database, and then the remainder of the new database is populated with empty pages.

If you modify the model database, all of the databases that you create later inherit those modifications . For example, you can set permissions or database options or add objects, such as tables, functions, or stored procedures.

650) this.width=650; "title=" clipboard[2] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [2] "src=" http://img1.51cto.com/attachment/201406/24/639838_1403574822t4Qb.png "height="/>

=====================================================================

Resource Database

A read-only database that contains the system objects that SQL Server includes. system objects are physically persisted in the Resource database, but are logically displayed in the SYS schema of each database.

The Resource database depends on the location of the master database. If you move the master database, you must move the Resource database to the same location.

The Resource database can be easily and quickly upgraded to a new version of SQL Server. In earlier versions of SQL Server, upgrading requires the removal and creation of system objects. Because the Resource database file contains all system objects, you can now complete the upgrade only by copying a single Resource database file to the local server.

The physical file name for the Resource database is Mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in the < drive >:\program Files\Microsoft SQL Server\mssql10_50.<instance_name>\mssql\binn\ . Each instance of SQL Server has one (and only one) associated Mssqlsystemresource.mdf file, and the file is not shared between instances.

SQL Server cannot back up the Resource database. by using the Mssqlsystemresource.mdf file as a binary (. EXE) file instead of as a database file, you can perform your own file-based backup or disk-based backup , but you cannot use SQL Server to restore the database. You can only manually restore a backup copy of Mssqlsystemresource.mdf, and you must be careful not to overwrite the current Resource database with an outdated version or a potentially unsafe version.

How to access the resource database, you can refer to the online documentation for SQL 2012.

=====================================================================

tempdb database

A workspace that holds temporary objects or intermediate result sets.

The tempdb system database is a global resource that can be used by all users who connect to an instance of SQL Server and can be used to save the following:

2. sql The Server database engine creates an internal object, for example, a worksheet that stores the intermediate results for spooling or sorting.

3. by using Read Committed (using row Version control quarantine or snapshot isolation transaction the row version generated by the data modification transaction in the database.

4. data modification transaction for implementation A row version of a machine index operation, multiple active result sets (MARS), and features such as after triggers.

Operations in tempdb are minimally logged operations . This causes the transaction to be rolled back. tempdb is recreated every time SQL Server is started, thus always maintaining a clean database copy when the system starts. Temporary tables and stored procedures are automatically deleted when the join is disconnected, and there is no active connection after the system shuts down. So there is nothing in tempdb to save from one SQL Server session to another session. Backup and restore operations to tempdb are not allowed.

650) this.width=650; "title=" clipboard[3] "style=" border-top:0px;border-right:0px;background-image:none; border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;padding-right:0px, "border=" 0 "alt=" clipboard [3] "src=" http://img1.51cto.com/attachment/201406/24/639838_1403574822O4K4.png "height="/>

=====================================================================

modifying system data

SQL Server does not allow users to directly update information in system objects, such as system tables, system stored procedures, and catalog views . In fact, SQL Server provides a complete set of administrative tools that users can use to fully manage their systems and all users and objects in the database. These include:

1. Management utilities, such as SQL Server Management Studio.

2. Sql-smo API. This tool enables programmers to get the full functionality of managing SQL Server in their applications.

3. Transact-SQL scripts and stored procedures. They can use system stored procedures and Transact-SQL DDL statements.

These tools protect applications from changes to system objects. For example, SQL Server sometimes needs to change the system tables in a new version of SQL Server to support new features added to that version. But when an application issues a SELECT statement that directly references a system table, it usually relies on the old system table format. The site may be able to upgrade to a new version of SQL Server after overriding the application that was selected from the system tables. SQL Server considers the interfaces of system stored procedures, DDL, and Sql-smo Publishing to maintain backward compatibility with these interfaces.

SQL Server does not support defining triggers on system tables, because triggers can change the operation of the system.

======================================================================

This article from "Zeng Hung Xin Technical column" blog, declined to reprint!