System Databases In Sql Server 2005

SQL Server System Databases
Master
Purpose – Core system database to manage the SQL Server instance. In SQL Server 2005, the Master database is the logical repository for the system objects residing in the sys schema. In SQL Server 2000 and previous editions of SQL Server, the Master database physically stored all of the system objects.
Prominent Functionality
Per instance configurations
Databases residing on the instance
Files for each database
Logins
Linked\Remote servers
Endpoints
Additional Information
The first database in the SQL Server startup process
In SQL Server 2005, needs to reside in the same directory as the Resource database
Resource
Purpose – The Resource database is responsible for physically storing all of the SQL Server 2005 system objects. This database has been created to improve the upgrade and rollback of SQL Server system objects with the ability to overwrite only this database.
Prominent Functionality
System object definition
Additional Information
Introduced in SQL Server 2005 to help manage the upgrade and rollback of system objects
Prior to SQL Server 2005 the system related data was stored in the master database
Read-only database that is not accessible via the SQL Server 2005 tool set
The database ID for the Resource database is 32767
The Resource database does not have an entry in master.sys.databases
TempDB
Purpose – Temporary database to store temporary tables (#temptable or ##temptale), table variables, cursors, work tables, row versioning, create or rebuild indexes sorted in TempDB, etc. Each time the SQL Server instance is restarted all objects in this database are destroyed, so permanent objects cannot be created in this database.
Prominent Functionality
Manage temporary objects listed in the purpose above
Additional Information
Each time a SQL Server instance is rebooted, the TempDB database is reset to its original state
Model
Purpose – Template database for all user defined databases
Prominent Functionality
Objects
Columns
Users
Additional Information
User defined tables, stored procedures, user defined data types, etc can be created in the Model database and will exist in all future user defined databases
The database configurations such as the recovery model for the Model database are applied to future user defined databases
MSDB
Purpose – Primary database to manage the SQL Server Agent configurations
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
Additional Information
Provides some of the configurations for the SQL Server Agent service
For the SQL Server 2005 Express edition installations, even though the SQL Server Agent service does not exist, the instance still has the MSDB database
Distribution
Purpose – Primary data to support SQL Server replication
Prominent Functionality
Database responsible for the replication meta data
Supports the data for transaction replication between the publisher and subscriber(s)
ReportServer
Purpose – Primary database for Reporting Services to store the meta data and object definitions
Prominent Functionality
Reports security
Job schedules and running jobs
Report notifications
Report execution history
ReportServerTempDB
Purpose – Temporary storage for Reporting Services
Prominent Functionality
Session information
Cache