System Administrator Responsibilities
A system administrator is
responsible for the integrity and availability of the data in a
database. This is a simple concept, but it is a huge responsibility.
Some large corporations place a valuation on their data as high as $1
million per 100MB. The investment in dollars is not the only issue; many
companies that lose mission-critical data simply never recover.
Job descriptions
for system administrators vary widely. In small shops, the administrator
might lay out the physical design, install SQL Server, implement the
logical design, tune the installation, and then manage ongoing tasks,
such as backups. At larger sites, tasks might be broken out into
separate job functions. Managing users and backing up data are common
examples. However, a lead administrator should still be in place to
define policy and coordinate efforts.
Whether performed by an individual or as a team, the core administration tasks are as follows:
Install and configure SQL Server.
Plan and create databases.
Manage data storage.
Control security.
Tune the database.
Perform backup and recovery.
Another task
sometimes handled by administrators is managing stored procedures.
Because stored procedures for user applications often contain complex
Transact-SQL (T-SQL) code, they tend to fall into the realm of the
application developer. However, because stored procedures are stored as
objects in the database, they are also the responsibility of the
administrator. If an application calls custom stored procedures, the
system administrator must be aware of this and coordinate with the
application developers.
The system administration job
can be stressful, frustrating, and demanding, but it is a highly
rewarding, interesting, and respected position. As a system
administrator, you are expected to know all, see all, and predict all,
but you should be well compensated for your efforts.
System Databases
SQL Server uses system
databases to support different parts of the database management system
(DBMS). Each database plays a specific role and stores information that
SQL Server needs to do its job. The system databases are much like the
user databases created in SQL Server. They store data in tables and
contain the views, stored procedures, and other database objects that you also see in user databases. They also have associated database files (that is, .mdf and .ldf files) that are physically located on the SQL Server machine. Table 1 lists system databases and their related database filenames.
Table 1. System Databases and Their Associated Database Files
| Database | .mdf Filename | .ldf Filename |
|---|
| master | master.mdf | mastlog.ldf |
| resource | mssqlsystemresource.mdf | mssqlsystemresource.ldf |
| model | model.mdf | modellog.ldf |
| msdb | msdbdata.mdf | msdblog.ldf |
| distribution | distmdl.ldf | distmdl.mdf |
| tempdb | tempdb.mdf | templog.ldf |
Tip
You can use the sys.master_files
catalog view to list the physical locations of the system database
files as well as the user database files. This catalog view contains a
myriad of information, including the logical name, current state, and
size of each database file.
The folder where each of
these database files is located depends on the SQL Server installation.
By default, the installation process places these files in a folder
named <drive>:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\.
You can move these files after the installation by using special
procedures that are documented in the SQL Server Books Online topic
“Moving System Databases.”
The following sections describe the function of each system database.
The master Database
The master
database contains server-wide information about the SQL Server system.
This server-wide information includes logins, linked server information,
configuration information for the server, and information about user
databases created in the SQL Server instance. The actual locations of
the database files and key properties that relate to each user database
are stored in the master database.
SQL Server cannot start without a master database. This is not surprising, given the type of information that it contains. Without the master database, SQL Server does not know the location of the databases that it services and does not know how the server is configured to run.
The resource Database
The resource
database contains all the system objects deployed with SQL Server 2008.
These system objects include the system stored procedures and system
views that logically appear in each database but are physically stored
in the resource database. Microsoft moved all the system objects to the resource
database to simplify the upgrade process. When a new release of the
software is made available, upgrading the system objects is accomplished
by simply copying the single resource database file to the local server. Similarly, rolling back an upgrade only requires overwriting the current version of the resource database with the older version.
You do not see the resource database in the list of system databases shown in SQL Server Management Studio (SSMS). You also cannot add user objects to the resource database. For the most part, you should not be aware of the existence of the resource database. It has database files named mssqlsystemresources.mdf and mssqlsystemresources.ldf that are located in the Binn
folder, but you cannot access the database directly. In addition, you
do not see the database listed when selecting databases using system
views or with system procedures, such as sp_helpdb.
The model Database
The model database is
a template on which all user-created databases are based. All databases
must contain a base set of objects known as the database catalog.
When a new database is created, the model is copied to create the
requisite objects. Conveniently, objects can be added to the model database. For example, if you want a certain table created in all your databases, you can create the table in the model database, and it is then propagated to all subsequently created databases.
The msdb Database
The msdb
database is used to store information for the SQL Server Agent, the
Service Broker, Database Mail, log shipping, and more. When you create
and schedule a SQL Server Agent job, the job’s parameters and execution
history are stored in msdb. Backups and maintenance plan information are stored in msdb
as well. If log shipping is implemented, critical information about the
servers and tables involved in this process is stored in msdb.
The distribution Database
The distribution
database, utilized during replication, stores metadata and history
information for all types of replication. It is also used to store
transactions when transactional replication is utilized. By default,
replication is not set up, and you do not see the distribution database listed in SSMS. However, the actual data files for the distribution database are installed by default.
The tempdb Database
The tempdb database
stores temporary data and data objects. The temporary data objects
include temporary tables, temporary stored procedures, and any other
objects you want to create temporarily. The longevity of data objects in
the temporary database depends on the type of object created.
Ultimately, all temporary database objects are removed when the SQL
Server service is restarted. The tempdb database is re-created, and all objects and data added since the last restart of SQL Server are lost.
tempdb can also be used for some of SQL Server’s internal operations. Large sort operations are performed in tempdb before the result set is returned to the client. Certain index operations can be performed in tempdb to offload some of the space requirements or to spread I/O. SQL Server also uses tempdb
to store row versions that are generated from database modifications in
databases that use row versioning or snapshot isolation transactions.
Maintaining System Databases
You should give system
databases the same attention that you give your user databases. These
databases should be backed up on a regular basis and secured in the
event that one of them needs to be restored. All the system databases,
with the exception of tempdb and resource, can be backed up. These same databases can also be restored to bring them back to a previous state.
Note
Although you cannot back up the resource database using SQL Server’s BACKUP and RESTORE commands, you can make a backup copy of it by performing a file-based or disk-based backup of the mssqlsystemresource.mdf file (SQL Server must not be running at the time). Likewise, you can manually restore a backup copy of the mssqlsystemresource.mdf file only when SQL Server is not running. You must be careful not to overwrite the current resource database with a version for a different release level of SQL Server.
It’s important that you
monitor the size of your system databases. The amount of data that
accumulates in these databases can be significant. This is particularly
true for the tempdb, msdb, and distribution databases. Large sort or index operations can increase the size of your tempdb database in a short period of time. The msdbdistribution
databases contain a great deal of historical information. Consider, for
example, a server with hundreds of databases that have log backups
occurring every 15 minutes. The information captured for each individual
backup is not significant, but the total number of databases and frequency of the backups cause many rows to be stored in the msdb database. Cleanup tasks and similar activities that remove older historical data can help keep the database size manageable. and
