SQLINFO – We play with data and technology

Sql  server 2008 uses 8 system databases for its efficient functionality. The Sql server 2008 system databases are

1. Master

2. Model

3.Msdb

4.tempdb

5.Resource Database

6.ReportServer

7.ReportServerTempDB

8.Distribution

Master Database :

It is the heart of the Sql server.It contain instance level configuration.It includes linked server configurations, SQL Server Logins, Service Broker Endpoints, System Level Stored Procedures, and System level Functions etc.There are two physical files for master database ie, master.mdf (data file) and master.log(log file). In Earlier versions of sql server, system objects are stored in Master database;after the release of sql server 2005 and 2008 the databases are stored in Resource Database.If master database is corrupted, the sql server will not work.

Model Database:

The Model database is used as a template when creating databases in SQL Server.SQL Server takes a copy of Model database whenever a user tries to create a new database.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.There are two physical files for master database ie, model.mdf (data file) and model.log(log file).Model database is created in full recovery model.That is why every user created database is in the full recovery model.

MSDB database

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 etc.It also contain 2 physical files , log file and data file.MSDB is created in simple recovery model.

Prominent Functionality

• SQL Server Agent Jobs, Operators and Alerts
• DTS Package storage in SQL Server 7.0 and 2000
• SSIS Package storage in SQL Server 2005

Tempdb Database:

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 etc.It is also used in DBCC CHECKDB work tables and Large object (varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.It is created in simple 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.

ReportServer
The ReportServer database is created when we installs SQL Server Reporting Service. This database basically stores all the metadata and object related information which is used by reporting services. This database is the heart for reporting services as it stores entire information with respect to SQL Server 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 schedule.

ReportServerTempDB

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

Distribution

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.