Quick overview of SQL Server 2008 Databases

Microsoft SQL server 2008, just as other releases, has two type of databases – system and user databases. System databases contain system information and metadata and the user databases are manually created and configured users (as a part of an installation of an application or for other specific needs).

System Databases

SQL Server has by default a master, model, msdb, tempdb, resource databases and if replication is enabled and the designated server is a distributor, then there will be and a distribution database which is named while configuring the replication.

The master database

The master database is vital for SQL Server engine to start. You cannot start the engine without having an uncorrupted master database. This database contains information on all server-level objects such as logins, endpoints and linked servers. The data, however, is not stored in the master database itself. It is stored in the hidden resource database and properly presented in the SYS schema of the all databases.

The model database

The model database is used as a template for any newly created user databases i.e. if you want in any new database on your server to has a certain object (such as and UDF for example) you can create that UDF in the model database and it will be created in any new database created from this point in time on. The process is the following: when a new database is created, the model database is copied and renamed with the name of the new database. This includes and tempdb when it is recreated.

The msdb database

The msdb database is also known and considered as the SQL Server Agent database. In this database information on jobs, configuration, SSIS packages, maintenance plans, etc. is stored. Besides SQL Server Agent service, this database is used by SQL Server Reporting Services, database mail, Service Broker, etc.

The tempdb database

The tempdb database is used to store objects and data temporarily. It is extensively used in sorting operations (the unsorted set is temporarily stored in tempdb abd then sorted). Also tempdb plays important role in any online index rebuild operations as this database holds the index while it is being rebuild in its source database.

Also all user must have access to this database. Besides that its storage should be carefully planned, especially when it comes to physical storage, I/O optimizations and number of db files of the tempdb (for this subject I will post another post where I will try to summarize the best practice for storage configuration of tempdb database).

The resource database

The resource database is read-only database that is used by SQL server instance and holds information on objects. It is not accessible during normal operation of the SQL server instance. It also contains object descriptions and when a service pack is applied it just updates the resource database. This means that if you have to rollback a service pack, you just have to restore the resource database to the point in time prior to the update.

User Databases

Well, user databases are created by users and they usually hold information from different systems, applications, etc. Microsoft has sample databases ready for download on the Codeplex web page (www.codeplex.com). There are plenty of descriptions on the web for those several sample database, so you can always “google” them.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.