A fundamental skill for DBAs is to
have a firm understanding of the SQL Server database
engine’s system databases. It’s also useful for database developers to be up
on system databases packaged with SQL Server. Here’s a rundown of the system
databases. (Note: If you decide to
explore these system databases, make sure you do so on
a development server.)

Master

The Master database holds information for all databases located
on the SQL Server instance and is the glue that holds the engine together. Because
SQL Server cannot start without a functioning master database, you must
administer this database with care. For this reason, it is vital to make
regular backups of this database.

This database includes information such as system logins, configuration
settings, linked servers, and general information regarding the other system
and user databases for the instance. The master database also holds extended
stored procedures, which access external processes, allowing you to interact
with features such as the disk subsystem and system API calls. These procedures
are typically written in a modern programming language such as C++.

If you run into a situation where you encounter system
failure and must recover your master database, review
this TechRepublic article by Steven Warren MCSE,
MCDBA
. It is very thorough, and explains some of the special steps needed
to restore this crucial database.

Model

Model is essentially a template database used
in the creation of any new user database created in the instance. You can place
any stored procedures, views, users, etc. in the model database so that when a
new database is created, the database will contain the objects you have placed
in the model database.

Weekly SQL tips in your inbox

TechRepublic’s free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

Tempdb

As its name implies, tempdb holds
temporary objects such as global and local temporary tables and stored
procedures.

This database is recreated every time SQL Server starts, and
the objects contained in it will be based upon the objects defined in the model
database. In addition to these objects, tempdb also
houses other objects such as table variables, results sets from table-valued
functions, and temporary table indexes. Because tempdb
will hold these types of objects for all of the databases on the SQL Server
instance, it is important that the database is configured for optimal
performance.

In SQL Server 2005, the tempdb
database has taken on an additional workload; it is used as the version store
for features such as the new snapshot isolation levels and online indexing
operations. For a brief overview about the new isolation levels, refer to
my article about SQL Server 2005’s advanced features
.

Distribution

When your SQL Server instance is configured as a distributor
for replication, this database is added to your system. By default, the name of
the database is distribution, but you can rename it. This database holds history
and metadata for snapshot, merge, and transactional replication.

Msdb

The msdb database stores
information regarding database backups, SQL Agent information, DTS packages,
SQL Server jobs, and some replication information such as for log shipping.

Conclusion

Over the years, I have found that the best way to learn the
underpinnings of SQL Server is to explore how things work in the system
databases. As a general rule, it is not recommended to directly query the
system tables in SQL Server; however, you can learn a lot about how SQL Server
works by exploring the tables in these system databases.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays

Subscribe to the Data Insider Newsletter

Learn the latest news and best practices about data science, big data analytics, artificial intelligence, data security, and more. Delivered Mondays and Thursdays