1. Database 101
Before we talk
about administering objects, let's briefly review the database itself. A
database is made up of two or more physical files (one data and one
log). Here are some key things to keep in mind about those files:
The data files contain the actual data that is stored in the database and the objects required to support the data.
The log files contain information needed to recover transactions.
The data files are stored in logical containers called filegroups.
When you create an object
in a database, you can specify the filegroup where the object will be
created. If you do not specify a filegroup when creating an object, it
will belong to the default filegroup. One filegroup can contain multiple
data files, which allows you to spread data processing over multiple
disks. For example, if you create a new filegroup and place two data
files in the filegroup that are located on separate disks, any object
created on that filegroup will be able to take advantage of using
multiple disks to service requests. Filegroups are also often used to
move indexes or large tables to a separate physical disk in order to
increase query performance.
2. Working with System Databases
The first thing you come across when opening the Databases folder in the Object Explorer (shown in Figure 1)
is a folder called System Databases. This folder is where you can find
the databases that SQL Server uses to store the internal system
information necessary for the operation of SQL Server itself. It is
important to have an understanding of each system database. I find
myself using them often to write queries to help with administration
tasks. Actually, one of the questions I always ask when interviewing
DBAs is to name all of the system databases and describe each of their
functions. Because there is a hidden system database, the interview
question even has built-in extra credit points.
|
You should not manually alter
the system databases, with the exception of adding custom configuration
items to the model database. Certain changes to the system databases
could cause major issues within SQL Server.
|
|
The following list provides a brief description of each of the system databases:
master: The master
database contains all of the system information that pertains to an
instance of SQL Server, such as logins, database names, and file
locations.
model: The model database is used as a template when creating a new database. Any changes applied to the model database will be reflected in any user database that you create after the changes have been made.
msdb: The msdb
database is used to store configuration and logging information by
features, such as SQL Server Agent, Service Broker, Database Mail, log
shipping, and backups.
tempdb: The tempdb database is used to store temporary objects created by SQL Server when generating result sets. The tempdb
is recreated every time SQL Server is restarted. If you ever want to
find out how long SQL Server has been running, you can just select the create_date for the tempdb from sys.databases, as shown in the following query:
Select create_date AS SystemUpSince From sys.databases Where name = 'tempdb'
Resource:
This is a read-only database containing system objects that appear in
the sys schema of every database. The Resource database is overwritten
during the upgrade process to update the system objects.
You cannot see the
Resource database from SQL Server Management Studio, but the data files
are called mssqlsystemresource.mdf and mssqlsystemresource.ldf and are
located in the C:\Program Files\Microsoft SQL
Server\MSSQL10.InstanceName\MSSQL\Binn\ folder for a default
installation. If you are one of those people who would like to see the
data that is stored in the Resource database, just make a copy of the
data files, rename them, and restore them with a name like ResourceTest.
