OpsMgr R2 by Example: the SQL Server Management Pack

The SQL Server management pack is available as a single download which contains different libraries to monitor SQL 2000, 2005 and 2008 database servers.

How to Install the SQL Server MP

  1. Download the SQL Server Management Pack from the Management Pack Catalog (http://technet.microsoft.com/en-us/opsmgr/cc539535.aspx). The SQL Server Management Pack Guide is included in the download and labeled “OM2007_MP_SQLSrvr.doc.”
  2. Read the Management Pack guide – cover to cover. This document spells out in detail some important pieces of information you will need to know.
  3. Import the SQL Server Management Pack (using either the Operations console or PowerShell). It is recommended that you also import the appropriate version of the Windows Server management pack (Windows 2000, 2003, or 2008). The Windows Server management packs monitor various aspects of the OS that can influence the performance of those computers running SQL Server! This includes disk capacity, disk performance, memory utilization, network adapter utilization, and processor performance.
  4. Running the SQL Server Studio and SQL Profiler tasks from the OpsMgr console requires you have installed that software on all OpsMgr computers where these tasks will execute, or you will receive an error message “the system cannot find the file specified.” Installing the Management Studio and Profiler are not required unless you want to run those tasks.
  5. If your environment includes clustered SQL Servers, enable Agent Proxy configuration on all members of the SQL cluster. This is in the Administration space, under Administration -> Device Management -> Agent Managed. Right-click each SQL Server in the cluster, select Properties, click the Security tab, and then check the box labeled Allow this agent to act as a proxy and discover managed objects on other computers.
  6. Create a SQLServer_Overrides management pack to contain any overrides required for the MP.

The SQL Server MP supports agentless monitoring with the exception of tasks that start and stop SQL Server services and SQL Server mail. The management pack installs two Run As Profiles: the SQL Server Discovery account and the SQL Server Monitoring account. By default, the management pack uses the Default Action account.

SQL MP Optional Configuration

The SQL Server MP does not automatically discover all object types. Go to the Authoring Pane of the Operations console to enable discovering additional components. Components not discovered include:

  • SQL Server 2008 Distributor
  • SQL Server 2005 Distributor
  • SQL Server 2008 Publisher
  • SQL Server 2005 Publisher
  • SQL Server 2008 Subscriber
  • SQL Server 2005 Subscriber
  • SQL Server 2008 Subscription
  • SQL Server 2005 Subscription
  • SQL Server 2008 Agent Job
  • SQL Server 2005 Agent Job
  • SQL Server 2000 Agent Job
  • SQL Server 2008 DB File Group
  • SQL Server 2005 DB File Group
  • SQL Server 2008 DB File
  • SQL Server 2005 DB File

This means you will not receive alerts for these objects failing since they are not even discovered objects! For example, if you have scheduled SQL backups using the SQL Agent and the job fails, OpsMgr won’t tell you about it. If an agent job failed in MOM 2005, the SQL MP generated an alert. So these behaviors are not necessarily the same between MOM 2005 and OpsMgr 2007.

You can use overrides to change the settings for automatic discovery to enable these object types. Be sure to change your settings in an unsealed MP other than the Default management pack.

SQL MP Tuning / Alerts to look for

The following alerts were encountered and resolved the following alerts while tuning the various SQL Server management packs (these are listed in alphabetical order by Alert name):

Alert: A SQL job failed to complete successfully.

Issue: A variety of scripts were failing on the system but the scripts were on a development server.

Resolution: Created an override to disable this alert on the development server experiencing the issues as there was no action required to address these on the development environment. Closed the alerts.

Alert: A SQL job failed to complete successfully.

Issue: A variety of scripts were failing on the system but the scripts were on a server which had a database that had been decommissioned.

Resolution: These jobs were not required, accessed the SQL server and disabled each of the jobs which were failing. Closed the alerts.

Alert: A SQL job failed to complete successfully.

Issue: There are close to 100 of the systems in an environment with only about 5 servers creating the alerts.

Resolution: Created an override to set these to low priority informational instead of warnings as no action was being taken when they occurred.

Alert: Auto Close Flag

Issue: The auto close flag for database MSCUPTDB in SQL instance MSSQL SERVER on computer 123.abc.com is not set according to best practice.

Resolution: As this is a standard Microsoft application (patch Management for SMS and Configuration Manager) and a default configuration, created an override to exclude this database.

Alert: Auto Close Flag

Issue: The auto close flag was set on a database that was used for anti-virus. This was an MSDE database that had been upgraded to a full SQL server. Changed the setting on the database to auto close false. The auto close setting is discussed at http://msdn.microsoft.com/en-us/library/ms190249.aspx. Per this article:

“True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.”

Resolution: Changed the setting on the database to auto close equals false.

Alert: Auto Shrink Flag

Issue: The auto shrink flag for database (DBNAME) in SQL instance MSSQL SERVER on computer 123.abc.com is not set according to best practice.

Resolution: This was found on a series of standard Microsoft applications including SUSDB, WSUS and MSCUPTDB. Additional databases found with the Auto Shrink Flag: Backup Exec, ItAssist, SOE, DSPre, XRXDBDiscovery (Xerox), XRXDBCWW (Xerox). Options available include contacting the vendor to determine if this flag can be changed (and changing the flag if it can) or to create an override to exclude this database from monitoring this configuration.

Alert: Cannot start SQL Server Service Broker on Database

Issue: This occurred when a large number of management packs and reports were being imported into the management environment. A total of three events were created the application log (9697) each occurring about two minutes after the prior one.

Resolution: Validated that it was not still occurring and tracked back to a period of time when the OpsMgr environment was under significant strain.

Alert: Could not allocate space for object in database because the filegroup is full

Issue: The database could not extend because the filegroup was full. Logged into the server and verified that there was free disk space and that the filegroup was set to auto-grow. Attempted to manually extend the database but it failed because it was running in MSDE and was restricted in size to 4096 MB (see http://databases.aspfaq.com/database/what-are-the-limitations-of-msde.html for limits on MSDE).

Resolution: Moved the database from MSDE to a full version of SQL server and then expanded the database.

Alert: Percentage Change in DB % Used Space

Issue: This was on the ReportServerTempDB database.

Resolution: This was on the reportingtemp database which is very small to begin with (6MB). Major percentage changes occur as part of this being a temp table. Threshold ranges are between 25% and 45% (low value of threshold = 25, high value of threshold = 45). Created an override for this for this specific database due to its size (6MB in size). Threshold1 = 45 (this is the growth size, increased from 25), Threshold2 = 55 (this is the shrink size, ended up leaving it this way). Increased due to the size of this database as the percentage figures become out of whack with a database of this size.

Alert: Service Check Data Source Module Failed Execution

Issue: Error getting state of service, error 0x8007007b. Documented in the SQL management pack guide:

“If the SQL Full Text Search service is not installed on computers running SQL Server 2005 that are being monitored, disable the monitor”

Resolution:

For two systems that were running MSDE, configured an override to disable the alert.

For another system, the service had been set to manual. Configured the service to run automatically and started the service.

For another system, the primary instance had this service but the additional instance installed on it did not. Configured an override to disable the alert.

Finally one system had the service running for a second instance but not for the first instance. Configured an override to disable the alert.

Alert: Service Check Probe Module Failed Execution

Issue: Error getting state of service, error 0x8007007b for workflow name Microsoft.SQLServer.2008.DBEngine.FullTextServiceMonitor. Documented in the SQL management pack guide:

“If the SQL Full Text Search service is not installed on computers running SQL Server 2005 that are being monitored, disable the monitor”

Resolution:

For two systems that were running MSDE, configured an override to disable the alert.

For another system, the service had been set to manual. Configured the service to run automatically and started the service.

For another system, the primary instance had this service but the additional instance installed on it did not. Configured an override to disable the alert.

Finally one system had the service running for a second instance but not for the first instance. Configured an override to disable the alert.

Alert: Service Pack Compliance – MSSQLSERVER (SQL 2005 DB Engine) Warning

Issue: The database server was running SQL 2005 service pack (SP) 2, which is acceptable for the ACS database server (SQL 2005 SP 2 has been approved for all OpsMgr database components per threads on the newsgroups).

Resolution: Created an override (for specific object of type SQL Engine DB) to allow this configuration for this server/set the enabled parameter to False for this server. Reset the health for this health monitor on this server, and refreshed and the state updated to green from yellow.

Alert: Service Pack Compliance

Issue: The SQL server in question was a SQL Server 2005 that was running SP 2. OpsMgr identified this as non-compliant because the rule was checking for service pack 1. Verified the version of SQL through the query: SELECT @@Version which returned:

Microsoft SQL Server 2005 – 9.00.1399.06 (Intel X86)

Oct 14 2005 00:33:37

Copyright (c) 1988-2005 Microsoft Corporation

Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

This issue occurred using version 6.0.6278.8 of the SQL management pack. There was a new version of the management pack available (6.0.6460.0). Downloaded and installed the new version of the management pack, closed the alert. This did not resolve the issue.

Created an override to set the “Good Value” from 1 to 2 within the Service Pack Compliance monitor and stored it in the MicrosoftSQLServer_Overrides management pack created for the SQL MP. Closed the alert but this did not resolve the issue as the MP was actually identifying the error condition but the OpsMgr administrator was incorrectly interpreting it.

http://support.microsoft.com/kb/321185 provided clarification as to what was seen here. The SQL install itself was RTM which did NOT have SP 1 installed.

Resolution: Installed SQL 2005 SP 2 on the system and closed the alert.

Alert: The SQL Server Service Broker or Database Mirroring transport is disabled or not configured

Issue: On the alert context, you will see that the description for eventid 9666 says “The Database Mirroring protocol transport is disabled or not configured.” What is interesting on this is that the same eventide (9666) means two different things: The Service Broker protocol transport is disabled or not configured or The Database Mirroring protocol transport is disabled or not configured. The only way to tell which of the two situations is occurring is on the alert context tab for the alert.

Resolution: The database mirroring protocol error reported is only relevant if database mirroring will be used on the server. If mirroring is not going to be used on the server, the alert should be disabled. To do so create an override to disable the alert for the specific server reporting the alert (again assuming that it doesn’t require database mirroring) and store it in a custom management pack (MicrosoftSQLServer_Overrides). Manually closed the alert.

SQL Server Management Pack Evolution

Microsoft wrote the SQL Server 2008 management pack to have functional parity with the SQL Server 2005 management pack. Other than SQL Job monitoring, the management pack focuses primarily on the condition of SQL Server and its services, rather than what is built on top of those installed objects and how they are configured and running.

It would be nice if future versions could incorporate monitoring SQL Server 2008 policy management and the database tuning advisor. SQL 2008 also incorporates a resource governor, which puts constraint on the various SQL components. An updated management pack should be aware of the constraints the governor puts on the components such that thresholds become relative to what has been set, rather than what the overall system is otherwise capable of doing.

SQL Server 2008’s Performance Studio and Performance Data Collection components have functionality at a depth beyond the current SQL MP. Incorporating this requires a transition from the high-level monitoring provided by the MP to the lower level tracing and reporting provided by SQL Server itself. Optimally, a new SQL MP would defer to the performance data collector, with tasks, diagnostics, and such to make that happen. There also would probably need to be some discovery and diagramming work added to the MP to show relationships between those components running collections and the warehouse where the results are stored.

Advertisements
This entry was posted in Tuning and Configuration. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s