This management pack is designed to monitor SQL Server. SQL Server runs the back-end components of many of Microsoft's technologies. In terms of the contents of this book, SQL Server can be found running the databases for SMS and MOM. This management pack will help you maintain the availability of SQL Server as it watches for problematic events and measures useful counters.
The SQL Server 2000 management pack consists of the following files:
MicrosoftSQLServer2000.akm
MicrosoftSQLServer2000Reports.xml
SQL Server MP Guide.doc
As of this writing, the latest version number is 05.0.3100.0000. After downloading the management pack, extract the contents to a common location and follow the steps in Chapter 8 to import the management pack. After importing the management pack, check the version number against the version number stated in the Management Pack and Product Connector Catalog.
You can get additional information on the SQL Server 2000 management pack at http://www.microsoft.com/technet/prodtechnol/mom/MOM2005/Library/.mspx or in the SQL Server MP Guide.doc included with the management pack.
Although the SQL Server 2000 management pack monitors your environment out of the box, some additional configuration changes can help fine tune monitoring to match your SQL Server environment. All of the configuration changes are made with native components of MOM. The "Scripts" section later in this chapter has all the scripts of this management pack with definable parameters. These parameters can be adjusted as required.
Remote Connectivity Monitoring verifies connectivity and responsiveness of SQL instances by issuing simple select statements from a remote client. This ensures that all the components necessary for responding to these requests are working properly on the SQL Server. In order to implement this, a few things need to be done.
Add the remote client to the Included Computers tab of the Microsoft SQL Server 2000 Client Side Monitoring Computer Group.
Modify the rule SQL Server Remote Connectivity. Change the CheckInstances parameter and enter a list of instance names (comma delimited) to check. Default instances require only the server name, whereas named instances require the server name and instance name.
Note | Provide instance names in the format of servername\instancename. |
The remote client must match the following requirements:
SQL Server, SQL Server Client Tools, or a SQL-DMO package must be installed on the remote client. (Refer to http://www.support.microsoft.com/default.aspx?scid=kb;en-us;326613 for more information on SQL-DMO packaging.)
The MOM service account must have permissions to connect to the SQL Server computers listed in the CheckInstances parameter.
This management pack consists of the following:
Attributes | Microsoft SQL Server 2000 |
Microsoft Windows Current Version | |
Computer Groups | Microsoft SQL Server 2000 |
Microsoft SQL Server 2000 Agentless Server | |
Microsoft SQL Server 2000 Client-Side Monitoring | |
Notification Groups | Database Administrators |
The Microsoft SQL Server 2000 Computer Group is formula-based and will populate the computer membership based on an attribute match on Microsoft SQL Server 2000. This should make up the majority of SQL Servers in an organization. Computers can be added manually to Microsoft SQL Server 2000 Agentless Server or Microsoft SQL Server 2000 Client-Side Monitoring statically where required.
This section lists all of the SQL Server 2000 management pack scripts that contain definable parameters. Many of these scripts contain the parameter InformationEvent. As with other management packs, this parameter is valuable only as a method for troubleshooting. Enable it if you're not certain that scripts are running. Reviewing the following scripts will help you better understand how the scripts support the management of the SQL Server 2000 infrastructure in your environment.
This script watches system process IDs for any potential blocking.
Name | Description | Value |
---|---|---|
InformationEvent | Logs an event on successful run when set to True | False |
WaitInMinutes | Threshold (in minutes) before alert is generated that blocking is occurring | 1 |
This script examines all the databases on a SQL Server and determines if the configuration is aligned with best practices.
Other than the InformationEvent parameter, the True/False values in this table do not enable or disable the script option. Instead, it defines whether or not an option is turned on. In other words, all values listed are examined. For example, if AutoClose is set to True, the script checks to see if the option is enabled. If it's set to False, the script checks to see if the option is disabled.
Name | Description | Value |
---|---|---|
AutoClose | Checks Auto close configuration | False |
AutoCreateStatistics | Checks Auto create statistics configuration | True |
AutoShrink | Checks Auto shrink configuration | False |
AutoUpdateStatistics | Checks Auto update statistics configuration | False |
CrossDBChaining | Checks Cross-database ownership chaining configuration | False |
InformationEvent | Logs an event on successful run when set to True | False |
TornPageDetection | Checks Torn page detection configuration | True |
This script checks databases to determine if they're healthy. If the database is in Emergency Mode, Recovering, or Suspect state, the script generates an event.
Name | Description | Value |
---|---|---|
HighSevDatabases | List of databases that require extra health checks (comma delimited) | master, tempdb, model, msdb, distribution, onepoint |
InformationEvent | Logs an event on successful run when set to True | False |
This script watches all SQL agent jobs running on the SQL Server. If any jobs exceed the specified value in the ThresholdInMinutes parameter, an alert is raised.
Name | Description | Value |
---|---|---|
ThresholdlnMinutes | Threshold (in minutes) that determines a long-running job | 60 |
InformationEvent | Logs an event on successful run when set to True | False |
The Remote Connectivity script performs Transact-SQL queries from a remote machine against the SQL Server. If queries run too long or do not succeed, an alert is raised.
Name | Description | Value |
---|---|---|
CheckInstances | List of instances to connect to (comma delimited) | |
DatabaseName | Database name to run query against | master |
ExecutionTimeAlert | Threshold for amount of time (in seconds) that query should take before raising alert | 5 |
InformationEvent | Logs an event on successful when set to True | False |
Query | Transact-SQL query to issue to server (empty value runs "SELECT GETDATE") |
This script looks for replication failures.
Name | Description | Value |
---|---|---|
InformationEvent | Logs an event on successful run when set to True | False |
This script monitors the following services: SQL Server, SQL Agent, and Full Text Search.
Name | Description | Value |
---|---|---|
CheckSearch | Monitors for availability of Full Text Search | False |
InformationEvent | Logs an event on successful run when set to True | False |
This is the discovery script for the SQL Server 2000 management pack. It discovers the installed instances of SQL Server.
Name | Description | Value |
---|---|---|
InformationEvent | Logs an event on successful run when set to True | False |
This script checks each instance of SQL Server to determine if the version of SQL is compliant. The version of SQL it checks for is based on the parameter VersionString.
Name | Description | Value |
---|---|---|
AlertOnAll | Generates an event if versions are compliant | False |
InformationEvent | Logs an event on successful run when set to True | False |
VersionString | Earliest version of SQL that is considered compliant | 8.00.194 |
This script checks for database free space. It raises alerts at error or warning based on the parameters in the script. If a database is set to autogrow, the script will account for this and generate an event specifying that an alert would have been raised if one or more of the database files had not been set to autogrow.
Note | The parameters DBErrorLevel and DBWarningLevel refer to all databases other than system databases and TempDB. Respectively, LogErrorLevel and LogWarningLevel refer to all database transaction logs other than system transaction logs and TempDB transaction logs. System databases include Master, MSDB, and Distribution. |
Name | Description | Value |
---|---|---|
DBErrorLevel | All databases: error threshold | 100 |
DBWarningLevel | All databases: warning threshold | 200 |
InformationEvent | Logs an event on successful run when set to True | False |
LogErrorLevel | All trans logs: error threshold | 75 |
LogWarningLevel | All trans logs: warning threshold | 150 |
SysDBErrorLevel | All system databases: error threshold | 15 |
SysDBWarningLevel | All system databases: warning threshold | 30 |
SysLogErrorLevel | All system logs: error threshold | 15 |
SysLogWarningLevel | All system logs: warning threshold | 30 |
TempDBErrorLevel | TempDB: error threshold | 75 |
TempDBWarningLevel | TempDB: warning threshold | 150 |
TempLogErrorLevel | TempLog: error threshold | 100 |
TempLogWarningLevel | TempLog: warning threshold | 200 |
The following table lists the available tasks in this management pack. It also states the context that the task runs in.
Task | Context |
---|---|
Display global configuration settings | Agent |
Run SQL Server Profiler (default instance) | Console |
Run SQL Server Query Analyzer (default instance) | Console |
Start SQL Agent | Agent |
Start SQL Server Mail | Agent |
Start SQL Service | Agent |
Stop SQL Agent | Agent |
Stop SQL Server Mail | Agent |
Stop SQL Service | Agent |
Note | If the tasks execute in the context of the agent, LocalSystem provides all necessary permissions. However, if the agent runs in least privilege configurations, it may require additional permissions to execute some of the tasks. All console tasks are executed under the permission of the user using the Operator Console. If the user doesn't ordinarily have the rights to execute the task, using the task will fail. |