Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.
Monitor SQL Server 2008 with OpsMgr
SQL Server management pack lets DBAs monitor space utilization, backup jobs, and more
Kerrie Meyler
September 14, 2009
9 Min Read
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.databasesWHERE name = ‘OperationsManager’ If result=1, the broker is enabled. If result=0, enable the broker as follows:
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.)
Execute the following statement from SSMS:
ALTER DATABASE OperationsManagerSET ENABLE_BROKERRestart the services.
If the alert reoccurs, disable the rule using an override. To do so, perform the following steps:
Right-click the alert in the Monitoring node of the OpsMgr console, and select Overrides, Override the rule, For the object: MSSQLSERVER.
On the Override Properties screen, select the checkbox enabling the override.
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:
In the OpsMgr console, navigate to the Authoring node. In the left pane, select Management Pack Objects, Object Discoveries.
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.)
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.
Select the component you want to enable; in this case, I’ve selected the Discover SQL 2008 Agent Jobs object.
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.
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.
Long-Running Jobs
You might want to tune the Long Running Jobs monitor. Default thresholds are specified in minutes, with 60.00 the low threshold and 120.00 the high threshold.
When a job runs between 60 and 120 minutes, the health state changes to Warning; for jobs over 120 minutes, the health state becomes Critical. OpsMgr also generates an alert.
To change these values, perform the following steps:
Open the OpsMgr console and select the Monitoring node in the left pane. In the Look for box, type long running jobs for the applicable list of monitors.
Find the Long Running Jobs monitor for the SQL Server 2008 management pack.
To override the value for a particular agent, right-click Long Running Jobs, Overrides. Click Override the Monitor, For a specific object of type: SQL Server 2008 Agent.
In the Select Object screen, select the agent of interest and click OK.
On the Override Properties screen, override the values for Lower Threshold and Upper Threshold as necessary. Be sure to specify a management pack other than Default as the place to save your changes.
Using the Long Running Jobs monitor incurs a performance hit. It also behaves differently depending on the SQL Server version:
For SQL Server 2000, long-running-job monitoring is available only on a per-job basis. You must enable the discovery.
However, every job for every SQL Server installation you are monitoring is separately monitored—with a performance overhead. After enabling this discovery, each job has an associated Job Duration monitor. Enter thresholds for this monitor in HHMMSS format. The defaults in the current release of the SQL Server management pack are now one hour (010000) and two hours (020000), with updated knowledge regarding the thresholds.
SQL Server 2008 and 2005 include this per-job functionality, disabled by default, with thresholds in minutes.
SQL Server 2008 and 2005 also have per-SQL Server agent monitoring for SQL Server jobs. If you enable this discovery, each instance of the SQL Server agent is discovered, with the Long Running Jobs monitor targeted at the SQL Server agent class and monitoring duration of all jobs on that agent. Without overrides, if any job exceeds the defined threshold for that agent, state changes with the monitor generating an alert.
Reports
The SQL Server management pack comes with predefined reports. These include the following:
SQL Broker Performance
SQL Database space report
SQL Server Configuration
SQL Server Database Engine Counters
SQL Server Lock Analysis
SQL Server Service Pack Report
SQL User Activity
Top 5 Deadlocked Databases
User connections by day
User connections by peak hours
Selecting one of these reports in the Reporting node of the OpsMgr console brings up a Details pane that tells how the report works, how to use it, and its parameters. Parameters include a Date/Time selection, which lets you choose a relative or fixed date and time range and time zone.
Figure 4 displays part of the database counters report, showing performance over a week. Reports can be scheduled or run on-demand and saved in XML, CSV, TIF, PDF, MHT (Web archive), and Microsoft Excel workbook format.
The DBA's Monitoring Tool
OpsMgr monitors pretty much the same things that DBAs monitor for SQL Server performance: memory usage, disk I/O, CPU utilization, latency (network saturation), backups, and key indicators. And OpsMgr puts it all, along with reports, in one convenient tool.
About the Author
You May Also Like
.png?width=700&auto=webp&quality=80&disable=upscale)