Microsoft System Center Operations Manager 2007 (OpsMgr) can help DBAs monitor SQL Server performance with its SQL Server management pack, which includes predefined monitors and rules to check the health and availability of SQL Server 2008, 2005, and 2000. The management pack is available for download from the management pack catalog at the Microsoft website. A new feature in the R2 version lets you select and download management packs from within the OpsMgr console.
Although OpsMgr won’t rewrite your application or tune your tables, its SQL Server management pack can help you with monitoring locks, space utilization, backup jobs, and some aspects of replication. Let’s see how to use OpsMgr to monitor critical aspects of your SQL Server 2008 environment.
What OpsMgr Does
OpsMgr facilitates monitoring and managing servers, clients, applications, OSs, network devices, and business services. New with OpsMgr is the concept of using monitors. Monitors make available the real-time state of a component at a very granular level.
OpsMgr uses a combination of monitors, rules, and alerts, which Table 1 shows, to notify you of issues and potential problems. Rules and monitors, together with everything else needed for monitoring, are assembled into management packs grouped by application or OS.
OpsMgr uses object-oriented concepts for monitoring and operates on a class-based structure. All components need to be working for the object (in this case, for SQL Server, which includes the services, databases, and disk drives) to identify and reflect overall health. The health model provided with the SQL Server management pack allows OpsMgr to monitor those objects, and defines whether a given object is in a healthy state or not (using monitors and rules) and how to keep it healthy.
What's Wrong with My Application?
Many issues that end up escalating have the same symptom: poor response time. Many things cause poor response time: networking issues, lock contention, online backups, page splits caused by insert or update activity, replication volume, improperly designed databases, or (imagine!) poorly written applications.
The SQL Server management pack helps monitor applications by monitoring database activity and yourSQL Server environment. It detects failures and lowers the time required to resolve problems and presents answers to questions that confront DBAs:
Is SQL Server available and is it accepting connections? If users can’t connect to SQL Server, OpsMgr alerts you of the situation.
Do all databases and logs have sufficient free space? Are transactions being blocked? Blocking occurs when a transaction locks resources that another transaction needs to read or modify. Blocking is temporary by nature but can negatively affect the user’s application experience.
Are SQL Server agent jobs (backup, optimize, and others) working? The SQL Server agent is used to schedule maintenance activities against your databases. If jobs aren’t running properly, database maintenance isn’t taking place as expected.
Is SQL Server replication working? By default, the functionality provided by OpsMgr for SQL Server replication monitoring isn’t fully enabled, because not all installations use replication. If you use SQL replication, you might want to enable this functionality.
OpsMgr monitors all instances and databases on your servers unless specifically overridden. It supports monitoring for replication, but each replicated server needs to be running the OpsMgr agent.
Basic support for mirroring using Windows Management Instrumentation (WMI) is included and is on the enhancements list for future releases. OpsMgr is also fully cluster-aware for Active/Passive and Active/Active configurations. Now let’s look at how you can use the SQL Server management pack.
Tuning the Management Pack
You might see an alert appear in the Monitoring node of the OpsMgr console that the SQL Server Service Broker or Database Monitoring transport is disabled or not configured. This alert is triggered by Event 9666 in the Application event log on the SQL Server system.
Unfortunately, this alert can occur even with the broker enabled. Here’s what you can do: Verify the broker is enabled. To do so, run the following query in SQL Server Management Studio (SSMS) connected to the master database on the database server receiving the alert:
SELECT is_broker_enabled FROM sys.databases
WHERE name = ‘OperationsManager’
If result=1, the broker is enabled. If result=0, enable the broker as follows:
1. Using the Services Microsoft Management Console (MMC), stop the SDK, Config, and Health Services on the Root Management Server and the Health Service on any secondary management servers. (In OpsMgr R2, these are renamed System Center Data Access, System Center Management Configuration, and System Center Management Services, respectively.)
2. Execute the following statement from SSMS:
ALTER DATABASE OperationsManager
SET ENABLE_BROKER
3. Restart the services.
If the alert reoccurs, disable the rule using an override. To do so, perform the following steps:
1. Right-click the alert in the Monitoring node of the OpsMgr console, and select Overrides, Override the rule, For the object: MSSQLSERVER.
2. On the Override Properties screen, select the checkbox enabling the override.
3. Before clicking OK at the bottom of this screen to save changes, select a destination pack other than the Default management pack. It’s best to maintain a custom management pack for overrides for each tuned management pack, giving granularity if you need to remove a management pack later.
This example uses a management pack previously created called SQL Server MP Overrides. If you don’t have a management pack for your changes, click the New button to create it on the spot. (Disabling a rule rather than overriding it saves changes to the Default management pack.) Figure 1 shows this step. To learn more about Microsoft’s best practices for configuring overrides, see the Microsoft support site.
Enabling Components
The SQL Server management pack doesn’t automatically discover all SQL Server object types. SQL Server 2008 components not discovered include the following:
• SQL 2008 Agent Job
• SQL 2008 DB File Group
• SQL 2008 DB File
• SQL 2008 Distributor
• SQL 2008 Publisher
• SQL 2008 Subscription
This list is similar to the SQL Server 2005 and 2000 components not discovered by default. Microsoft chose not to automatically discover these objects because of potential performance impact and added alerts.
You might decide to enable at least some of them if doing so benefits your environment. Just remember that if you don’t, you won’t receive alerts you might be expecting because OpsMgr hasn’t discovered the objects generating them.
For example, if you schedule SQL backups using the SQL Agent and then the job fails, OpsMgr won’t tell you about it without the Agent Job object type enabled.
To enable a component, perform the following steps:
1. In the OpsMgr console, navigate to the Authoring node. In the left pane, select Management Pack Objects, Object Discoveries.
2. In the Look for: box at the top of the right pane, type SQL Server 2008, and select Find Now. This returns all SQL Server 2008 management pack components. (These steps also apply to see and alter discoveries on SQL Server 2005 and 2000.)
3. Now look at the value in the Enabled by default column on the far right, which shows each component as either Yes or No. Figure 2 shows an example.
4. Select the component you want to enable; in this case, I’ve selected the Discover SQL 2008 Agent Jobs object.
5. Right-click and select Overrides, Override the Object Discovery, For a group … to open the Select Object screen. Choose SQL 2008 Computers to open the Override Properties screen.
6. Check the Override box for the Enabled parameter, verify the Override Setting is now True, select a destination management pack (not Default), then click OK to save. Figure 3 displays the Override Properties screen.
Running SQL Server Configuration Manager, SSMS, and SQL Server Profiler from the OpsMgr console requires installing that software on the computer or computers running the console. Otherwise, an error message occurs: The system cannot find the file specified.
Ensure the OpsMgr administrator imports the appropriate version of the Windows Server management pack (Windows Server 2008 or Windows Server 2003). This management pack monitors aspects of the OS that can influence SQL Server performance, including memory utilization, disk capacity, disk performance, processor performance, and network adapter utilization.