SMS Database Components
The SMS database contains
data objects and their attributes arranged in an organized fashion. Each
database consists of four main elements, as follows:
Tables
Indexes
Event triggers
Stored procedures
A table
is a database object that contains data in the database organized as a
collection of rows and columns. Each row in the table represents a data
record, and each column represents an associated field for that record.
Generally, each table defines one or more columns (fields) as a key
entry that can be used to link tables for the purpose of sorting,
searching, and reporting on data in the database. SMS 2003 contains more
than 200 predefined tables.
An index
can be thought of as a companion object to a table. Separate from the
table, an index functions much like an index in a book, providing a
quick way to search and locate data. If an index is available for a
table, your queries will exhibit better performance. If no index is
available, the entire table must be searched. The two index types,
clustered index and nonclustered index, determine how the data records
are searched. The nonclustered index is similar to a book index—each
entry contains a bookmark that tells the database where to find the data
that corresponds to the key in the index. For example, when you look up
an entry such as “site-server” in a book index, you might be directed
to several locations in the book. The index doesn’t represent the order
in which the data is stored in the book. The clustered index is similar
to a telephone directory—it contains the data itself, not just the index
keys. The clustered indexes are usually based on a primary key defined
in each table. Each index entry corresponds to the order in which the
data is stored in the book. Like looking up a name in the phone book,
when you find the name, you also find the address and phone number.
When you execute a
query, you’re searching tables for a specific value based on the
criteria you enter, using indexes whenever possible. The query result
represents the records or data values obtained from records contained in
one or more tables. SMS 2003 contains more than 250 predefined indexes.
An event trigger
is a Transact-SQL statement that’s executed whenever a specific event
occurs in a given table. The Transact-SQL language is used for
communication between applications and SQL Server. It’s an enhancement
to SQL and provides a comprehensive language for defining tables,
maintaining tables, and controlling access to data in the tables. If
data is added, deleted, or modified within a specific table, an event
trigger will be executed. SMS uses event triggers to notify its
components that an event has occurred that a particular component needs
to attend to. Event triggers cause components to “wake up” in response
to an event rather than waiting for a specific polling cycle to occur.
Obviously, this translates to better performance for the site server.
For example, when you change a site setting, an event trigger causes SQL
Monitor to write a wake-up file in the Hierarchy Manager inbox. SMS 2003 uses over 200 event triggers.
A stored procedure
is a group of Transact-SQL statements that have been compiled into a
single executable routine. You could think of a stored procedure as a
kind of batch file for SQL Server. When a SQL Server event activates a
trigger, a corresponding stored procedure is executed that writes the
wake-up file into the appropriate SMS component’s inbox on the site
server. Two common stored procedures that you might execute are
SP_SPACEUSED, which displays the amount of reserved and actual disk
space used by a table in the database or by the entire database, and
SP_WHO, which identifies SQL Server connections (users and processes)
currently in use. Both are included with SQL’s master database.
SQL Server Management Tools
A quick scan of the SQL
Server program group reveals that many tools are installed to assist the
SQL Server administrator in maintaining the server running SQL. Unless
you’re the SQL Server administrator as well as the SMS administrator or
you get the necessary education to fully understand and appreciate the
product, you’ll probably use only two or three of these tools. The SQL
Server 2000 program tools are described in Table 1.
Table 1. SQL Server 2000 program tools
| Tool | Description |
|---|
| Analysis Services | A
folder that provides access to the Analysis Manager, another link to
Books Online and MDX Sample Application. The Analysis Manager is a
console application that provides an interface for accessing and
managing analysis servers. For more information about Analysis Services,
please refer to Books Online. |
| Books Online | Provides an online version of the documentation set SQL Server Books Online with full searching capabilities. |
| Client Network Utility | Used to set the custom server connection, DB-Library, and Net-Library configuration for clients. |
| Configure SQL XML Support In IIS | Used
to define and register a new virtual directory in Internet Information
Services (IIS) so that HTTP can be used to access a SQL Server 2000
database. |
| Enterprise Manager | MMC
snap-in that facilitates the configuration of the server running SQL
and the management of SQL Server databases, including devices and
databases, space usage, backups and restores, permissions, data import
and export, and so on, through object-level folders. |
| Import And Export Data | Starts
the Data Transformation Service Import/Export Wizard, designed to
facilitate the import, export, and conversion of data from various data
formats. |
| Profiler | Used
to monitor server events, procedure calls, and other real-time server
activity; Profiler can also filter events and direct output to the
screen, file, or table. |
| Query Analyzer | Graphical query interface used to execute maintenance tasks such as Transact-SQL statements or stored procedures. |
| Server Network Utility | Used
to configure SQL Server to use Net-Libraries and to specify the network
protocol stacks on which the server will listen for SQL Server clients’
requests. |
| Service Manager | Starts, stops, and pauses the SQL Server, SQL Server Agent, and MS DTC services. Also implemented as a taskbar program. |
Many SQL Server
maintenance tasks specific to the SMS database can be configured and
scheduled to run through the SMS Administrator Console, including a
complete site server backup . You can also effect
database backups by using a third-party backup program capable of
including SQL Server databases as part of its backup routine.
Consequently, as an SMS administrator, you’re most likely to use
Enterprise Manager to perform any additional or advanced maintenance
tasks. Through this interface you can create database devices, manage
space usage, configure the server, schedule events, back up and restore
the database, and so on.
As
with most Microsoft BackOffice applications, when you install SQL
Server several performance objects and counters are included to assist
you in evaluating the ongoing performance and resource use of your
server running SQL as well as to facilitate the troubleshooting of
specific performance-related problems. To view the available SQL Server
performance objects and counters, you can start the Windows System
Monitor utility, accessible through the Performance Console. For
example, the SQL Server:Database object has a counter called Data
File(s) Size (KB) that will help you monitor the cumulative size of your
databases, such as Tempdb.
Database Maintenance
As
we’ve seen, some database maintenance tasks should be carried out on a
regular basis—either daily, weekly, or monthly. For example, every day
you might execute a database backup and review status messages and
system performance. Once a week, you might monitor database size usage
and purge old data from the database. Once a month, you might verify the
integrity of the database backup by testing a restore of the database.
Once a month, you might also review security and make appropriate
adjustments such as resetting account passwords.
Most of these tasks can
be performed or configured and scheduled to run through the SMS
Administrator Console. However, you can perform many of these same
tasks, and database backup and restores, through SQL Server.
Commands Used for Performing Essential Maintenance Tasks
Some of the
database integrity checking and space monitoring commands you might
consider running on a weekly or monthly basis are listed below. These
database consistency checker (DBCC) commands are certainly not the only
ones available, but they’re among the commands that Microsoft most often
recommends.
DBCC CHECKALLOC
Checks the specified database to verify that all pages have been
correctly allocated and used; reports the space allocation and usage.
DBCC CHECKDB
Checks every database table and index to verify that they are linked
correctly, that their pointers are consistent, and that they are in the
proper sort order.
DBCC CHECKCATALOG Checks consistency between tables and reports on defined segments.
DBCC UPDATEUSAGE
Used with a recently reindexed database to reset space usage reporting
so that SP_SPACEUSED returns accurate data. You could schedule this
command to run with SP_SPACEUSED or to run separately under its own
schedule.
Tip
To
obtain a complete list and explanation of all Transact-SQL statements
and stored procedures, including the DBCC commands, query the online
help for SQL Server 2000. |
Before
you run any DBCC command, remember to set SQL Server to single-user
mode.
Executing a Maintenance Command Using SQL Server 2000
To execute a database
maintenance command in SQL Server 2000, launch the Query Analyzer tool
found in the Microsoft SQL Server programs group to display the Connect
To SQL Server dialog box, shown in Figure 1, and follow these steps:
1. | In
the Connect To SQL Server dialog box, click the Browse button to choose
from a list of servers running SQL located on the network. Select
[Local] to specify the use of the local server. If you need to start the
service, select the Start SQL Server If It Is Stopped check box.
|
2. | Select
either Windows Authentication or SQL Server Authentication, depending
on the security mode you enabled for your server running SQL. Supply a
login name and password if appropriate.
|
3. | Click OK to display the Query window, shown in Figure 2.
Select the database you want to query against from the list on the left
and enter the command that you want to execute—in this case, DBCC
CHECKDB in the Query window.
|
4. | Choose
Execute from the Query menu or click the Execute Query button (the
green arrow) on the toolbar. The results of the query are displayed on
the bottom half of the Query window, shown in Figure 3.
|
