SMS Database Components

 < Day Day Up > 



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 (see Chapter 3, “Configuring Site Server Properties and Site Systems,” for more information). 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.



 < Day Day Up > 



Microsoft Systems Management Server 2003 Administrator's Companion
Microsoft Systems Management Server 2003 Administrators Companion (Pro-Administrators Companion)
ISBN: 0735618887
EAN: 2147483647
Year: 2006
Pages: 178

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net