Monitor SQL Server from System Center Operations Manager

I’ll start part 1 of the Microsoft System Center series, focused on what a SQL Server DBA should know about System Center, with System Center Operations Manager, or SCOM, with the SQL Server Management Pack. When you have licensed the System Center suite of products, you can use these different pieces or put them together in your data centers for what we are now calling a SQL Server “private cloud” that has elastic scale, maximum server consolidation capabilities, charge back capabilities, self-service provisioning, the greatest data center economies of scale and (my favorite) load-balanced SQL Server with zero downtime configurations. The SQL Server Monitoring Pack for SCOM is downloadable here and adds a very distinctive monitoring capability to your SQL Server infrastructure. Out of the box, the SCOM SQL monitoring pack can monitor all areas of your SQL Server instances and databases by using the SCOM agent on that server. This also then allows you to create SLAs and root-cause analysis that can drill down from an application or server alert that you’ve configured down to the database level all from the System Center GUI. As you can see from this health monitor roll-up diagram from SCOM below, you will be able to monitor all aspects of SQL Server including database performance and health areas including database files, long-running jobs, blocking sessions, compliance and many others:

This is similar to what you will find from 3rd party monitoring tools on the market today. I just want you to become familiar with what you can use to monitor SQL Server from System Center. As I take you through the rest of the System Center suite and its applicability to SQL Server management, you may find that the combination of monitoring, management, auditing, virtualization, etc. that the System Center Suite brings to the table may be a more compelling value proposition. I’ve also put a few screen shots below that show how SCOM can bring together your SQL Server health with the rest of the environment that System Center is monitoring from server agents (including Unix, Linux, Oracle and more) and gives you a much wider lens from which to view your overall system health. Now, having said all of that, and since I pretty much live exclusively in DBA land these days, as a SQL Server DBA, you may not want to have to look into ANY tools that are outside of your daily bread-and-butter tools like SSMS. In that case, you can set-up SQL Server with a central management and monitoring server instance using SQL Server Enterprise Edition to get visibility into just your SQL Server investments using MDW for data collection and trending, UCP for dashboards, PBM for management and SQL Audit for auditing.


Configure a SQL Server Standalone Performance Monitoring Instance

SQL Server 2008 R2 Enterprise Edition has enough combined features that you can now centralize monitoring your SQL Server instances from a single point. What this allows you to do is to enable pro-active performance monitoring of your SQL Server infrastructure so that you can identify trends and alerts in common areas that require monitoring such as query performance, server capacity and resource utilization.

This is not meant to replace more feature-rich off-the-shelf products that monitoring the health of SQL Server with specific designs that allow DBAs to take SQL Server performance monitoring to the next level. But that being said, here is a common set of steps that you can take to enable centralized monitoring with SQL Server 2008 R2:

  1. Determine how many instances you are going to monitor centrally from this monitoring SQL Server instance. If the number of instances is greater than 25, then you may want to buy the SQL Server license for this monitoring server as Data Center because the UCP feature which I’ll touch on in this posting, requires Data Center to monitor more than 25 SQL Server instances centrally with UCP. At minimum, you will require Enterprise Edition so that you can use MDW (also explained below) which is required at each monitored instance, too.
  2. The hardware for the monitoring server does not need to be the beefiest of your iron in terms of RAM or CPU. This is a monitoring server, not a database server for OLTP or DW workloads. That being said, you will centralize data collection of performance stats across your instances here, so this server will need disk space.
  3. The out-of-the-box (with SQL Server EE) features that we’ll use to make this work are Utility Control Point (UCP) , Management Data Warehouse (MDW) and Central Management Server (CMS). CMS is available in ALL editions of SQL Server 2008. I’m not going to go any further in this post about multi-server management and central management of your servers. I’ll stay focused on monitoring SQL Server. I’ll leave you with a promise to follow-up with a similar entry on how to MANAGE SQL Server centrally with CMS, DAC & PBM. Let me just say this: centralizing your monitoring on a single instance like this helps to set you up for centralizing management, too. So this becomes another added benefit to this technique and approach.
  4. Let’s start with MDW on your central monitoring instance of SQL Server (2008 R2, of course). In SSMS on the Management folder, right-click on “Data Collection” and select “Configure Management Data Warehouse”. You will run through the wizard here to set up the MDW collection database on your central instance.
  5. Next, configure MDW on each instance that you want to monitor. When you run through the wizard on the monitored instance, you want to set-up the data collectors to collect stats on that instance and send the data to the centralized MDW database. Do not just default to setting up each & every instance of SQL Server to collect and send to MDW. The reasons for this include that not every instance that you have in your environment will be Enterprise Edition and you also need to understand that based on the # of instances & stats you collect and the collection interval will effect the amount of data being stored and retained in the MDW centralized database. This can result in very large MDW databases. Be sure to automate the purge history job for the data warehouse.
  6. Now configure UCP on the central monitoring instance. To get to the configuration tool for UCP, choose View | Utility Explorer in SSMS. You will see the set-up option now in SSMS for UCP. Once you’ve walked through the wizard to set up the local centralized UCP instance, you can now enroll instances into the UCP. This is done from the central server in the Utility Explorer. Select the Managed Instance node, right-click and select Enroll Instance. Be aware that these instances must be SQL Server 2008 R2. In the NOTES section below, I’ll point you to some ways that you can use MDW for SQL Server instances other than 2008 R2.
  7. Lastly, Central Management Server (CMS) will allow you to create groups in the central monitoring server’s SSMS where you can run queries and access the remote instances from a single SSMS instance. Why this is important to centralized performance monitoring is because with CMS, one thing that you can do is to run DMV queries across all of your monitored instances from that single central instance with one single query execution. This will allow you to get results back from a number of instances around missing indexes, long-running queries, buffer size, etc. by running 1 query.


Here are my examples of how to configure MDW to collect through a pull mechanism instead of the classic push data collector mechanism so that you can collect stats from SQL Server instances that are not SQL Server 2008 instances.

Here is where I talk a little bit about actually using these tools on your monitoring instance once you have it up & running.

CLOSED: Free SQL Server Performance Monitoring Seminar in Malvern MTC

**** We had to close registration for this 20-seat seminar as the response was so quick and we filled it up!

**** If you sent me an email and received a response back that you are registered, then we will see you here on June 22 @ 2 PM.

If not, don’t worry … I promise to work to schedule another SQL Server free seminar in the Microsoft Malvern office again very soon.

Thanks! Mark




Are you going to be in the Philly are on June 22 from 2-5 PM? We are hosting Sebastian Meine for a 3-hour complimentary seminar in our Microsoft Technology Center in Malvern, PA. This free training will focus on SQL Server 2008 R2 performance Monitoring and will include snacks & beverages.

If you can join us, that would be great! Just send me an email here. You must register with me in order to attend. Below is the flier for the seminar. Many thanks! Mark

SQL Server Performance Monitoring Basics — Will my SQL Server keep up with the workload of tomorrow?

This class will introduce the concepts and tools to recognize performance trends to identify and address small problems before they become big ones.

To accomplish this we will:

-­‐ introduce the ACIDproperties, focusing on Isolation including locks and latches

-­‐ explore the query lifecycle with query compilation and the three query execution phases

-­‐ investigate blocking, showing ways to monitor for blocking occurrences

-­‐ examine performance-­‐related DMVs

The class  closes out by introducing the Management Data Warehouse, a tool to use on all your servers to monitoring the items covered in this class.

After  attending the class, you will be able to:

-­‐ describe ACID and its components and explain its  impact on concurrency

-­‐ differentiate between locks  and latches

-­‐ explain the query lifecycle

-­‐ identify blocked and blocking sessions and their SQL statements

-­‐ use DMVs to get a picture of your server health

-­‐ monitor  important SQL Server performance metrics over time using the Management Data Warehouse

-­‐ see  important trends in your performance metrics