SMS Database Components

[Previous] [Next]

The SMS database contains data objects and their attributes arranged in an organized fashion. Each database consists of five main elements, as follows:

• Tables
• Indexes
• Views
• Event triggers
• Stored procedures

A table is a database object that contains all the 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 2.0 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 different locations in the book. The index does not 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 are 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 2.0 contains more than 250 predefined indexes.

A view represents a specific, predefined query that can be used to generate reports. This type of database object was used extensively in SMS 1.2 to support Crystal Reports (known in SMS 2.0 as Crystal Info) and other report access tools. Although SMS 2.0 supports views and maintains them if you are upgrading from SMS 1.2, SMS 2.0 itself does not include views. Rather, data access must be secured through Open Database Connectivity (ODBC) and Windows Management Instrumentation (WMI) for management reporting purposes.

An event trigger is a Transact-SQL statement that is executed whenever a specific event occurs in a given table. The Transact-SQL language is used for communication between applications and SQL Server. It is an enhancement to structured query language (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 (see Chapter 3). SMS 2.0 uses over 200 different 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 you might execute are SP_SPACEDUSED, 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.