Types of Databases in SQL Server
1. System Databases
2. User Databases
Following are SQL Server System Databases:
master - Records all the system-level information for an instance of SQL Server.
msdb - Is used by SQL Server Agent for scheduling alerts and jobs.
model - 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 - 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 - Is a workspace for holding temporary objects or intermediate result sets.
System Database Detailing:
master - 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. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database. 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.
Restrictions
The following operations cannot be performed on the master database:
- Adding files or filegroups.
- Backups, only a full database backup can be performed on the master database.
- Changing collation. The default collation is the server collation.
- Changing the database owner. master is owned by sa.
- 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.
Recommendations
When you work with the master database, consider the following recommendations:
1. Always have a current backup of the master database available.
2. 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. If you do, master must be backed up more frequently.
3. Do not set the TRUSTWORTHY option to ON for the master database.
msdb - The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail. By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.
Restrictions
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 - 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. The entire contents of the model database, including database options, are copied to the new database. Some of the settings of model are also used for creating a new tempdb during start up, so the model database must always exist on a SQL Server system.
Newly created user databases use the same recovery model as the model database. The default is user configurable.
Restrictions
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 sa.
- 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.
- 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.
Physical Properties of Resource
The physical file names of the Resource database are mssqlsystemresource.mdf and mssqlsystemresource.ldf. These files are located in <drive>:\Program Files\Microsoft SQL Server\MSSQL<version>.<instance_name>\MSSQL\Binn\ and should not be moved. Each instance of SQL Server has one and only one associated mssqlsystemresource.mdf file, and instances do not share this file.
tempdb - 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.
tempdb never has anything to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.
The tempdb system database is a global resource that's available to all users connected to the instance of SQL Server or connected to Azure SQL Database. tempdb holds:
1. Temporary user objects that are explicitly created. They include global or local temporary tables and indexes, temporary stored procedures, table variables, tables returned in table-valued functions, and cursors.
2. Internal objects that the database engine creates. They include:
- Work tables to store intermediate results for spools, cursors, sorts, and temporary large object (LOB) storage.
- Work files for hash join or hash aggregate operations.
- Intermediate sort results for operations such as creating or rebuilding indexes (if SORT_IN_TEMPDB is specified), or certain GROUP BY, ORDER BY, or UNION queries.
Permissions
Any user can create temporary objects in tempdb. Users can access only their own objects, unless they receive additional permissions. It's possible to revoke the connect permission to tempdb to prevent a user from using tempdb. We don't recommend it because some routine operations require the use of tempdb.
Restrictions
The following operations can't 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 sa.
- 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.
