Tip

Monitoring SQL Server 2008 R2 using SCOM 2007 R2

Shirmattie Seenarine, Contributor
Monitoring SQL Server 2008 R2 using SCOM 2007 R2 allows solutions providers to have greater control over customers' infrastructure and applications. System Center Operations Manager (SCOM) 2007 R2 can also help you can gain insight into the health and performance of customers' SQL Server infrastructure through the use of reports and log files.

    Requires Free Membership to View

More resources on monitoring SQL Server 2008 R2:

Monitoring SQL Server 2008 R2 with SQL Server Management Packs

Resource Governor in SQL Server 2008 R2 manages customers' workloads

New SQL Server 2008 R2 editions, features and licensing

Optimizing SQL Server 2008 performance

Monitoring SQL Server 2008 R2 health using SCOM 2007 R2

Solutions providers can use a dedicated SQL Server node in the SCOM 2007 R2 console to monitor and obtain health states and performance information on the SQL Server 2008 R2 environment. Database views present information, such as the health state for each database and the amount of free space available within database and transaction log files. Monitoring views, which present the health state on all of the SQL Server components managed, are also very useful. Finally, information is presented based on events triggered through SQL Server Agent jobs.

Monitoring SQL Server 2008 R2 health with SCOM 2007 R2 reports

SCOM 2007 R2 also allows solutions providers to run detailed reports on SQL Server performance, availability and other monitored elements. These reports are automatically installed when the SQL Server MP is installed on SCOM.

Some of the reports include information on SQL Server component availability and performance, configuration changes, backup history, block analysis and Agent job history. Solutions providers can also use capacity information reports to view pertinent SQL Server counters, such as buffer cache hit ratio, lock timeouts per second, number of deadlocks per second, SQL recompiles per second and transactions per second.

You can also view day-to-day operations information reports that include items such as SQL Server configuration, lock analysis, SQL Server service packs, SQL Server user activity, user connections per day, user connections by peak hours and top five deadlocked databases. Finally, trending charts are available. Trending charts communicate items such as the amount of free space available on databases.

Monitoring SQL Server 2008 R2 audit compliance with SCOM 2007 R2

Many organizations are also using the Audit Collection Services (ACS) component in Operations Manager 2007 R2 for security and compliance. ACS collects records found in each SQL Server security log, consolidates them and stores them in a centralized database. Solutions providers can then run reports and analytics to determine if there are any security violations that need investigating or remediation.

SCOM 2007 R2 is a product that solutions providers should welcome for its monitoring capabilities and detailed reports. SCOM also gives better control of customers' SQL Server infrastructure and database environment while freeing up time to focus on other critical events. Likewise, customers will most likely believe they went with the right product when they witness other benefits, such as a reduction in total cost of ownership (as a result of monitoring), an increase in availability and applications running at peak performance.

About the expert
Shirmattie Seenarine is an independent technical writer with more than 10 years of experience. She has contributed to many books, including Windows Server 2008 Unleashed, Exchange Server 2007 Unleashed, SharePoint Server 2007 Unleashed and SQL Server 2008 Management and Administration. As a technical writer, Shirmattie has written many articles, white papers, design documents and operational procedures for Fortune 500 organizations, including Microsoft, NetApp Inc. and Gilead Sciences. Shirmattie frequently writes articles for TechTarget on various subject matters.


This was first published in March 2010

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.