System Center for the SQL Server DBA, cont’d SCOM Pack for SQL

My original posting on System Center Operations Manager for SQL Server DBAs start in part 1 of the series here. Today, I want to take just a minute to give you some pointers in getting started using the System Center Monitoring Pack for SQL Server. Notice in that download link, that the pack has been updated to allow you to monitor up to SQL Server 2012, which now also includes monitoring your Always On Availability Groups from SCOM, a very important new feature.

One thing that I always mention to DBAs or other IT professionals who are new to using the SCOM SQL monitoring pack is to start by downloading and taking a look at the online guide for the MP: http://download.microsoft.com/download/0/7/7/07714012-3B7C-4691-9F2B-7ADE4188E552/SQLServerMPGuide.doc.

There is a LOT in there. This is good. As a DBA and someone responsible for SQL Server database health, you want a lot of monitoring tools and capabilities at your fingertips. But I recommend starting off with a small subset of counters that you will monitor.

As a common best practice when using the SCOM MP for SQL Server, start by picking a list of critical areas in your SQL Server environment that you most need visibility to provide the highest level of service, such as performance, uptime, high availability, replication, etc. This kind of approach will force you to think about which counters and reports to light-up in SCOM instead of overloading a dashboard or NOC with too much noise & chatter.

This is one of the best top-10 lists that you can use to get started is by Gregory A. Larsen from Database Journal: http://www.databasejournal.com/features/mssql/article.php/3932406/Top-10-SQL-Server-Counters-for-Monitoring-SQL-Server-Performance.htm.

I generally agree with this list and monitor these counters through normal tools like perfmon, DMVs or MDW. It’s a good starting point for you for database performance. From there, you can start to configure the infrastructure monitoring pieces in the monitoring pack through SCOM for clustering, replication, Always On, etc.