SQL Server 2000 Management Pack

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.

Installation

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.

Configuration

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

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.

Components

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.

Scripts

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.

SQL Server 2000 Block Analysis

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

SQL Server 2000 Database Configuration Monitoring

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

SQL Server 2000 Database Health

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

SQL Server 2000 Long Running Agent Jobs

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

SQL Server 2000 Remote Connectivity

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`")

SQL Server 2000 Replication Monitoring

This script looks for replication failures.

Name	Description	Value
`InformationEvent`	Logs an event on successful run when set to True	False

SQL Server 2000 Service Availability

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

SQL Server 2000 Service Discovery

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

SQL Server 2000 Service Pack Compliance

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

SQL Server 2000 Space Analysis

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

Tasks

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.