Exploring System Databases in SQL Server

	During interviews for databases , most frequently asked question is about system databases and often we confused about it. Some people remembers only three out of five databases like tempdb, sysdb and model and even many more remembers only tempdb.
If you expand the system database node in your sql server you will see only four databases as shown in the left hand image. So just clear you confusion and go through the below article which is extracted from MSDN.

System database	Description
master Database	Records all the system-level information for an instance of SQL Server.
msdb Database	Is used by SQL Server Agent for scheduling alerts and jobs.
model Database	Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
Resource Database	Is a read-only database that contains system objects that are included with SQL Server. System objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.
tempdb Database	Is a workspace for holding temporary objects or intermediate result sets.

Master Database
The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.
The following operations cannot be performed on the master database:

• Adding files or filegroups.
• Changing collation. The default collation is the server collation.
• Changing the database owner. master is owned by dbo.
• Creating a full-text catalog or full-text index.
• Creating triggers on system tables in the database.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

When you work with the master database, consider the following recommendations:

• Always have a current backup of the master database available.
• Back up the master database as soon as possible after the following operations:
• Creating, modifying, or dropping any database
• Changing server or database configuration values
• Modifying or adding logon accounts
• Do not create user objects in master. Otherwise, master must be backed up more frequently.
• Do not set the TRUSTWORTHY option to ON for the master database.

MSDB Database
The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.
The following operations cannot be performed on the msdb database:

• Changing collation. The default collation is the server collation.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the primary filegroup to READ_ONLY.

Model Database
The model database is used as the template for all databases created on an instance of SQL Server. Because tempdb is created every time SQL Server is started, the model database must always exist on a SQL Server system.
When a CREATE DATABASE statement is issued, the first part of the database is created by copying in the contents of the model database. The rest of the new database is then filled with empty pages.
If you modify the model database, all databases created afterward will inherit those changes. For example, you could set permissions or database options, or add objects such as tables, functions, or stored procedures.
The following operations cannot be performed on the model database:

• Adding files or filegroups.
• Changing collation. The default collation is the server collation.
• Changing the database owner. model is owned by dbo.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.
• Creating procedures, views, or triggers using the WITH ENCRYPTION option. The encryption key is tied to the database in which the object is created. Encrypted objects created in the model database can only be used in model.

Resource Database
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
The Resource database makes upgrading to a new version of SQL Server an easier and faster procedure. In earlier versions of SQL Server, upgrading required dropping and creating system objects. Because the Resource database file contains all system objects, an upgrade is now accomplished simply by copying the single Resource database file to the local server.

Temp db Database
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

• Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
• Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
• Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
• Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
In SQL Server, tempdb performance is improved in the following ways:

• Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention.
• Allocation page latching protocol is improved. This reduces the number of UP (update) latches that are used.
• Logging overhead for tempdb is reduced. This reduces disk I/O bandwidth consumption on the tempdb log file.
• The algorithm for allocating mixed pages in tempdb is improved. The following operations cannot be performed on the tempdb database:
• Adding filegroups.
• Backing up or restoring the database.
• Changing collation. The default collation is the server collation.
• Changing the database owner. tempdb is owned by dbo.
• Creating a database snapshot.
• Dropping the database.
• Dropping the guest user from the database.
• Enabling change data capture.
• Participating in database mirroring.
• Removing the primary filegroup, primary data file, or log file.
• Renaming the database or primary filegroup.
• Running DBCC CHECKALLOC.
• Running DBCC CHECKCATALOG.
• Setting the database to OFFLINE.
• Setting the database or primary filegroup to READ_ONLY.

References:
(1). http://msdn.microsoft.com/en-us/library/ms178028(v=sql.100).aspx

Download the whole article in doc format from the below image.

---