OpsMgr by Example: The SQL Management Pack

This blog entry is another in a series of Operations Manager related items that review the steps that we performed to install, configure and tune management packs in real-world environments. This entry focuses on the SQL MP.

Installation:

  1. Download the SQL Management Pack (http://www.microsoft.com/downloads/details.aspx?FamilyID=8c0f970e-c653-4c15-9e51-6a6cadfca363&DisplayLang=en), and the SQL Server Management Pack Guide (http://download.microsoft.com/download/7/4/d/74deff5e-449f-4a6b-91dd-ffbc117869a2/OM2007_MP_SQLSrvr.doc).
  2. Read the Management Pack guide – cover to cover. There are important pieces to know that the document spells out in detail.
  3. Import the SQL Server Management Pack. The management pack for each monitored version of SQL Server (2000 and 2005) consists of two .mp files. These files provide logic for discovery and monitoring, meaning you can use a smaller management pack to discover the existence of SQL Server; deploying the monitoring MP to the agent after OpsMgr has discovered SQL Server there. There is also a SQL Server Library MP, which is a prerequisite for the other management packs.
  4. We recommend you also import the appropriate version of the Windows Server management pack (Windows 2000 or 2003). 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.
  5. Running the SQL Server Studio and SQL Profiler tasks from the OpsMgr console requires that 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.
  6. The SQL Server MP supports agentless monitoring with the exception of tasks that start and stop SQL Server services and SQL Server mail.
  7. 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.

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 2005 Publisher
  • SQL Server 2005 Subscriber
  • SQL Server 2005 Subscription
  • SQL Server 2005 Agent Job
  • SQL Server 2000 Agent Job
  • SQL Server 2005 DB File Group
  • SQL Server 2005 DB File

What 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.

Tuning/Alerts to Look for: The following are alerts found and resolved while tuning the SQL Server management pack.

Alert: The SQL Server Service Broker or Database Mirroring transport is disabled or not configured. (EventID 9666)

Issue: This alert may occur even if the broker IS enabled.

Resolution: Verify the broker is enabled by running the following query in Management Studio, connected to the Master database:

SELECT is_broker_enabled FROM sys.databases WHERE name = ‘OperationsManager’

If the result=1, the broker is enabled. If result=0 enable the broker as follows:

  1. Stop the SDK, Config, and Health Services on the RMS, and the Health Service on any secondary management servers
  2. Execute the following statement from SQL Management Studio
    ALTER DATABASE OperationsManager SET ENABLE_BROKER
  3. Restart the services

If the alert continues to reoccur, disable the rule using an override.

Issue: Clustered virtual servers are discovered and display as agentless managed, but the SQL Server database engine on the cluster does not appear to be monitored.

Resolution: Only the virtual SQL Servers are discovered (the cluster and not the individual cluster nodes). In the Monitoring tab under Windows Server, check that each Virtual Server shows up as a Windows Server with the property "Is Virtual Server" set to True. Restart the Health Service on the RMS and any other management servers after adding the cluster. You may need to restart the Health Service on the cluster as well, which will rerun the discovery.

It is also possible that you are having RPC issues. See KB article 306985 (http://support.microsoft.com/kb/306985) for additional information.

Alert: 8957 Monitor Name: DBCC executed found and repaired errors – but found 0 errors and repaired 0.

Issue: When DBCC runs it generates this event log message with the same event ID if any problems were found or not.

Resolution: Disable the rule and create your own. For the new rule, copy all of the settings the same from the original but set the description to not contain "found 0 errors." For all other events with this ID, it will generate an alert to indicate a problem was found.

Alert: Health Monitor Description: Service Pack Compliance – MSSQLSERVER (SQL 2005 DB Engine) Warning (against ACS database)

Issue: SQL Server 2005 Service Pack 2 is installed, which is acceptable for the ACS database server. SP2 has been approved for all OpsMgr database components.

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.

Issue: The Management Server Action account is used as the Default Data Warehouse Action Account, rather than the DW Action account you specified during setup.

Resolution: This will be fixed in SP1. In the interim, create a RunAs account, type Simple, and set the username and password to a single space. In the same-name profile associate this account to all management servers, including the RMS. Also, be sure that the Data Warehouse Action account profile is correctly associated with an account for all management servers to be used as the Window authentication account. This information was obtained from the newsgroups (nntp://msnews.microsoft.com/microsoft.public.opsmgr.setup/7363632A-A650-4367-9DCE-27CC2887B786@microsoft.com).

Issue: SQL Server 2000 database engine health is not monitored. This is an aggregate monitor that includes the SQL Service State terminated unexpected monitor and the SQL Service terminated unexpectedly monitor.

Resolution: The SQL DB Engine Service Health Rollup monitor is not enabled by default. Use the Authoring pane of the OpsMgr console to enable the aggregate rollup monitor (Under Management Pack objects, select Monitors, change the scope to SQL 2000 DB Engine, search, then expand the SQL 2000 DB Engine, expand Entity Health, expand Availability, select SQL DB Engine Serve Health Rollup, and create an override to Override the monitor for all objects of type SQL 2000 DB Engine. See KB article 938991 (http://support.microsoft.com/kb/938991) for additional information.

The following issues are related to specific applications you may have installed:

Issue: Alert Rule or Alert Monitor: Auto Shrink Flag Alert Description: The auto shrink flag for database SUSDB in SQL instance MSSQL SERVER on computer 123.abc.com is not set according to best practice.

Resolution: This is a standard Microsoft application (WSUS) and a default configuration. Created an override to exclude this database.

Issue: Alert Rule or Alert Monitor: Auto Shrink Flag Alert Description: The auto shrink flag for database BEDB in SQL instance MSSQL SERVER on computer 123.abc.com is not set according to best practice.

Resolution: This is the standard configuration for Backup Exec’s database.

Issue: Alert Rule or Alert Monitor: Auto Shrink Flag Alert Description: The auto shrink flag for database MSCUPTDB in SQL instance MSSQL SERVER on computer 123.abc.com is not set according to best practice.

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

Issue: Alert Rule or Alert Monitor: Auto Close Flag Alert Description: 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: This is a standard Microsoft application (patch Management for SMS and Configuration Manager) and a default configuration. Created an override to exclude this database.

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

4 Responses to OpsMgr by Example: The SQL Management Pack

  1. Steve says:

    RE: Alert: The SQL Server Service Broker or Database Mirroring transport is disabled or not configured. (EventID 9666) 
     
    The result on the query should be 1 for service enabled and 0 for disabled.

  2. Operations says:

    Small typo, big difference! Good catch – Thanks! 

  3. Frath says:

    RE: Alert: The SQL Server Service Broker or Database Mirroring transport is disabled or not configured. (EventID 9666)
    This occurs on ALL our SQL 2005 servers.  The Event\’s data bytes say it\’s coming from the Master DB, which does not have the broker enabled:
     
    0000: c2 25 00 00 0a 00 00 00   Â%……0008: 09 00 00 00 4c 00 41 00   ….L.A.0010: 2d 00 53 00 51 00 4c 00   -.S.Q.L.0018: 30 00 39 00 00 00 07 00   0.9…..0020: 00 00 6d 00 61 00 73 00   ..m.a.s.0028: 74 00 65 00 72 00 00 00   t.e.r…
     
    Our SQL 2005 servers are not mirrored, and most other DBs have the Service Broker turned on.  Should we disable the rule in this case as well?

  4. Operations says:

    We suggest overriding (rather than disabling) the rule on all databases or database servers where it is giving you an unnecessary alert.

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