System Databases in SQL Server

When SQL server is installed four system databases are created. We cannot drop system databases. Below are the system databases in SQL 2005 and 2008:

1. Master
2. Model
3. Msdb
4. Tempdb
5. ReportServer
6. ReportServerTempDB
7. Resource Database
8. Distribution Database

Master Database

  • The Master database is the heart and soul of SQL Server. It basically records all the system level information.
  • Every instance of SQL Server will have an independent Master database; as it captures instance level configuration information.

 

  • The information which is captured in the Master database includes SQL Server instance level configurations, linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc.

 

  • The system and user databases related information such as name and location for user and system database are captured in Master database.

 

  • The Master database basically consists of two physical files, namely master.mdf (data file) and mastlog.ldf (log file).
  • Default location for SQL Server 2008 when installed is Drive:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\.
  • If master database is corrupted or if it is not available then the SQL Server Service will not start.
  • In SQL Server 2005 and later versions the system objects are stored in Resource Database rather than in Master Database.
  • The Master database is created using Simple Recovery Model.
  • SELECT DATABASEPROPERTYEX(‘Master’,’RECOVERY’) AS [RECOVERY MODEL]

 

Model Database

  • The Model database is basically used as a template when creating databases in SQL Server. Basically SQL Server takes a copy of Model database whenever a user tries to create a new database in SQL Server.
  • This also means that if a user creates any tables, stored procedures, user defined data types or user defined functions within a Model database; then those objects will be available in every newly created database on that particular instance of SQL Server.

 

  • In SQL Server, TempDB database is recreated every time SQL Server Service is restarted. This also means that the some of the settings of Model database are also used when TempDB is created.

 

  • The Model database basically consists of two physical files namely Model.mdf (data file) and ModelLog.ldf (log file). By Default these were created in the default installed directory of the SQL Server

 

  • If the Model database is damaged or corrupted then SQL Server Service will not start up as it will not be able to create the tempdb database.
  • The Model database is created using Full Recovery Model. As the Model database is in Full recovery model, all the newly created user databases will be created with Full recovery model. If you want to change the recovery model to Simple or Bulk Logged for all the newly created databases then you can set the recovery model for Model database accordingly.

 

  • SELECT DATABASEPROPERTYEX(‘Model’,’RECOVERY’) AS [RECOVERY MODEL]

 

MSDB Database:

  • SQL Server Agent uses MSDB database to store information related to the configuration of SQL Server Agent Jobs, Job schedules, Alerts, Operators etc.
  • MSDB also stores information related to configuration of Service Broker, Log Shipping, database backups and restore information, Maintenance Plan Configuration, Configuration of Database Mail, Policy Bases Information of SQL Server 2008 etc.

 

  • This database also stores SSIS packages created in SQL Server 2005 & 2008. The Data Transformation Services (DTS) Packages in SQL Server 2000 are also stored in MSDB Database.

 

  • The MSDB database basically consists of two physical files namely MSDBData.mdf (data file) and MSDBLog.ldf (log file). These are created in the default installed folder of SQL Server

 

  • If the MSDB database is corrupted or damaged then scheduling information used by SQL Server Agent will be lost. This will result in the failure of all scheduled activities.

 

  • The MSDB database is created using Simple Recovery Model.

 

  • SELECT DATABASEPROPERTYEX(‘Msdb’,’RECOVERY’) AS [RECOVERY MODEL]

 

TEMPDB Database

  • The TempDB is the only database in SQL Server which is recreated every time when SQL Server restarts.
  • It stores all the temporary objects such as temporary tables, global temporary tables, temporary stored procedures, cursors, table variables, it is also used when indexes are create or rebuild using SORT_IN_TEMPDB option

 

  • It is not advisable to store any permanent objects within TempDB database as this database will be recreated whenever SQL Server is restarted.

 

  • It doesn’t support database backup and recovery operations. If your application uses TempDB a lot then you need to plan the growth of TempDB database to avoid any performance issues.

 

  • The TempDB database basically consists of two physical files namely tempdb.mdf (data file) and templog.ldf (log file). By default these were recreated at the installed directory for the SQL Server.

 

  • The TempDB database is created using Simple Recovery Model and it is not possible to alter the database recovery model.

 

  • SELECT DATABASEPROPERTYEX(‘TempDB’,’RECOVERY’) AS [RECOVERY MODEL]

 

ReportServer Database

  • The ReportServer database is created when the user installs SQL Server Reporting Services.
  • This database basically stores all the metadata and object related information which is used by reporting services.

 

  • It includes information related to all the reports, linked reports, data sources, report models, folders, permissions, the properties and security related settings used by all the objects, report execution schedules, report execution logs etc.

 

  • This database is the heart for reporting services as it stores entire information with respect to SQL Server Reporting Services.

 

  • The ReportServer database basically consists of two physical files namely ReportServer.mdf (data file) and ReportServer_log.ldf (log file). These are created in the default directory where SQL Server is installed.

 

  • The ReportServer database is created using Full Recovery Model.
  • SELECT DATABASEPROPERTYEX(‘ReportServer’,’RECOVERY’) AS [RECOVERY MODEL]

 

ReportServerTempDB Database

  • The ReportServerTempDB database is created when a user installs SQL Server Reporting Service.
  • This database is basically used by ReportServer database to stores session information, cached reports, working tables used by reporting services etc.

 

  • The ReportServerTempDB database basically consists of two physical files namely ReportServerTempDB.mdf (data file) and ReportServerTempDB_log.ldf (log file). These are created at default directory where SQL Server is installed.

 

  • The ReportServer database is created using Simple Recovery Model.
  • SELECT DATABASEPROPERTYEX(‘ReportServerTempDB’,’RECOVERY’) AS [RECOVERY MODEL]

 

Resource Database

  • The Resource database is a read only, hidden system database that contains all the SQL Server system objects such as sys.objects which are physically available only in the Resource database, even though they logically appear in the SYS schema of every database.
  • Resource Database does not contain any user data or any user metadata.

 

  • Resource database is not visible under Databases Node in SQL Server Management Studio.

 

  • The physical file names of Resource database is mssqlsystemresource.mdf and mssqlsystemresource.ldf.

 

  • The important thing to be noted by the DBA is that each instance of SQL Server has one and only one associated mssqlsystemresource.mdf & mssqlsystemresource.ldf file and instances do not share this file.

 

  • In a cluster environment, the Resource database basically exists in the \Data folder on a shared disk drive.

 

  • The ID for the Resource Database will be always 32767.

 

  • The DBA shouldn’t be renaming or moving the Resource Database file. If the files are renamed or moved from their respective locations then the SQL Server will not start.

 

  • Another important thing to be considered is not to put the Resource Database files in compressed or encrypted NTFS file system folders as it will hinder the performance and will also prevent upgrades.

 

Distribution Database

  • The Distribution database is created on the Distribution SQL Server when replication is configured.
  • This database basically stores all the metadata information related to the configuration of replications and it also stores replicated transaction from the publisher database which needs to be delivered to the subscribers in case of transactional replications.

 

  • However for Merge replication it basically stores synchronization history.

 

  • The distribution database basically consists of two physical files namely distribution.mdf (data file) and distribution_log.ldf (log file). These are created in Data folder where SQL Server is installed.

 

  • The Distribution database is basically created using Simple Recovery Model.
  • SELECT DATABASEPROPERTYEX(‘Distribution’,’RECOVERY’) AS [RECOVERY MODEL]
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: