The site database and software metering database are supported in SQL Server version 6.5 or version 7.0. SQL Server terminology beyond the database terminology explained in Chapter 2 and SMS-related SQL Server functions are explored in this lesson.
NOTE
See Chapter 2 for information on databases, transaction logs, and devices.
Tables, indexes, views, stored procedures, and triggers are objects within the SQL Server database devices. Tables store data, and indexes provide a mechanism to rapidly retrieve data contained in the tables. Queries and other procedures may be run against the database using SQL commands written in the Transact-SQL language. A view is a virtual table whose contents are the result of a query of existing database tables. Stored procedures are pre-defined sequences of Transact-SQL commands compiled and run against the database. Triggers are stored procedures that run when requested by a database event.
A database can contain multiple tables. A table is a collection of rows (records) that have associated columns (fields). The SMS 2.0 site database contains over 200 tables.
An index is a feature that enables users to gain fast access to data in the tables. If no index is available, the entire table is searched to locate the requested information. An entire table search is significantly slower than an indexed search. An index in SQL Server is similar in function to an index in a book: It provides a fast way to locate information. SMS 2.0 contains more than 250 indexes. There are two types of indexes: clustered and nonclustered.
Data is stored in sorted order on a specific column in the database table. Only one clustered index may be created for a table because the clustered index imposes a specific sort order upon the database table.
Data in the table is stored in non-sorted order. The nonclustered index is sorted, but the data it points to can be found in a random order within the table. Single tables may have multiple nonclustered indexes.
Views enable users to store predefined queries as objects in the database for later use. SMS supports views created in SMS 1.2. However, for more complete access to the site database, use the Web-Based Enterprise Management (WBEM) SDK and SMS Toolkit to access data through the SMS Provider.
A stored procedure is a named collection of Transact-SQL statements stored on the server and compiled into a single execution plan. Transact-SQL is the language of SQL Server. Stored procedures are similar to batch (.BAT) or command (.CMD) files in that they can carry out multiple SQL procedures.
A trigger contains Transact-SQL statements that are run when data in a specific table is inserted, updated, or deleted. For example, when a row is inserted into the orders table, a trigger checks to see if there is a corresponding row in the customers table.
Through the Trigger Manager thread of the SMS Executive and the SMS SQL Monitor process, SMS uses triggers extensively as a means of communicating to the different components when a related change has occurred in the site database. There are approximately 200 different triggers designed for the site database. These triggers are the means for activating processes and minimizing the number of polling cycles that might otherwise have to be put into effect. Most components "wake up" on a file or directory change notification when a SQL trigger fires, and a corresponding stored procedure places a wake-up file in the appropriate component's inbox.
NOTE
For more information on tables, indexes, views, stored procedures, and triggers, see SQL Server Books Online.
Most database maintenance tasks for the site database and software metering database should be performed from the SMS Administrator console. At times, it may be necessary to use SQL Server tools to perform database maintenance tasks. However, before accessing the database using SQL Server tools, make sure you are well versed in SQL Server database management and SMS.
A number of administrative tools are provided with SQL Server. The tool you will use most often for SQL Server administration is SQL Server Enterprise Manager. This tool provides easy, enterprise-wide management from a server or a workstation. It enables you to perform system administration tasks using a graphical interface. For example, you can configure servers, manage databases, schedule events, and configure replication.
In SQL Server version 7.0, SQL Server Enterprise Manager is a Microsoft Management Console (MMC) snap-in. All of the systems management data that SMS works with, except for the CIMOM repository and software inventory collected files, are stored in the site database or the software metering database. Therefore, it is essential that the databases are properly maintained and backed up.
NOTE
A proper backup of a site includes more than just a backup of the database. See Lesson 3, "Restoring an SMS Site," for more details on backup strategies.
Two other important tools in SQL Server are the SQL Query Window (iSQL/w) in SQL Server version 6.5 and the SQL Server Query Analyzer in SQL Server version 7.0. These tools provide a command window interface where you can execute Transact-SQL programming language commands, such as database queries and maintenance commands. Transact-SQL commands can also be executed from the Windows NT/2000 command prompt using iSQL.EXE.
Because the SMS databases can grow quite large, it is important to make sure you do not run out of free space in the database.
SQL Server Version 7.0 Provides Automatic Maintenance
If you are using SQL Server version 7.0, then you can set the database and transaction log to grow automatically, by a specified amount of space, each time the current file fills up. During an SMS setup that automatically creates the database and device files, the database is not configured to grow automatically. You must configure the database to grow automatically after the SMS installation is complete.
To find out how much space is available in SQL Server version 7.0, select the database in the SQL console tree. Database information, including space available, appears in the right pane, as shown in Figure 13-1.
Figure 13-1. Viewing the space allocated to the site database devices.
Notice in the left pane of Figure 13-1 that there is a database named SMS_S01LicDB; this is the software metering database. You can also view the space allocated in this database by selecting it and clicking the Space Allocated link.
SQL Server Version 6.5 Requires Attention to Free Space
In SQL Server version 6.5, if the site database, software metering database, tempdb database, or any of their transaction logs becomes full, SMS processing stops. Because database space is pre-allocated, it is important to monitor its usage to make sure there is always room for transactions. The database and logs should be checked weekly to ensure there is adequate free space.
To find out how much space is available in a SQL Server version 6.5 database, and to expand it if necessary, follow these steps:
If a database must be expanded beyond the limits of the device, either increase the device size or add a new device. This may entail adding a new hard disk. See the SQL Server version 6.5 documentation for more details on how to expand the device.
tempdb Database
The tables in tempdb are the temporary working tables whose size varies widely based on database activity. Free space should be checked during peak usage; that is, when several copies of the SMS Administrator console are querying the database. If the database is more than 60 percent full, it should be expanded. In Microsoft SQL Server version 6.5, use the SQL Performance Monitor to monitor database space use. Select SQLServer:Max Tempdb Space Used.
View the space used by tempdb in SQL Server version 7.0 by clicking the Space Allocated link for the tempdb database.
Transaction Logs
In SQL Server version 6.5 you can monitor the transaction logs using a counter (SQLServer-Log:Log Size) in the Windows NT Performance Monitor utility. Assign a Performance Monitor alert to start Sqlalrtr.exe, which records SQL Server errors in the Windows NT Event Viewer Application log.
In SQL Server version 7.0, the counter is SQL Server: Databases - Percent Log Used. SQL Server version 7.0 also allows you to monitor the logs directly from the SQL Server Enterprise Manager.
Once a month, plan to run an integrity check on the SMS database and log. If any of these checks produces errors, consider restoring the last backup that ran before the failed integrity check.
DBCC Commands
The Database Consistency Checker (DBCC) Transact-SQL language command is the SQL Server database consistency checker. DBCC helps ensure the physical and logical consistency of a database. It is recommended that you run periodic checks to ensure the logical and physical consistency of your data. DBCC commands are run from the command line using iSQL or through a Windows graphical interface. In SQL Server version 7.0, the graphical interface is SQL Server Query Analyzer; in SQL Server version 6.5, the graphical interface is iSQL/w.
Before running DBCC commands, make sure that all copies of the SMS Administrator console are closed, and shut down all SMS services. Leave the Microsoft SQL Server, MSSQL Service running in order to execute DBCC commands.
The commands listed below do not include the full syntax for the DBCC commands. Only simple syntax is provided here. For more details on the parameters that can be used with DBCC, refer to SQL Server Books Online, which is included with SQL Server.
To run this command against a database named SMS_S01, for example, type DBCC CHECKALLOC (`SMS_S01').
This command checks the specified database to make sure that all pages are correctly allocated and used. If no database name is given, DBCC CHECKALLOC checks the current database. It reports the amount of space allocated and used. The final result message generated from running this test in SQL Server version 7.0's SQL Server Query Analyzer application is:
Total number of extents = 368, used pages = 2646, referenced pages = 2332 in this database. (number of mixed extents = 129, mixed pages = 1012) in this database. CHECKALLOC found 0 allocation errors and 0 consistency errors in database `SMS_S01'. |
As a precaution, shut down and restart the server after DBCC has completed. This prevents the possibility of a manual checkpoint attempting to write the allocation pages.
This command performs the same function as DBCC CHECKALLOC for SQL Server version 6.5. In SQL Server version 6.5, use the iSQL/w query interface.
This command checks for consistency within and between system tables. DBCC CHECKCATALOG also reports on any segments that have been defined.
This command checks each table in the database to see that index and data pages are correctly linked; that indexes are sorted in proper order; that all pointers are consistent; and that the data information on each page and page offsets is correct.