Monitoring and Visualizing SQL Server using System Center Operations Manager

There have been significant enhancements which have been made in the area of visualization of SQL server and in custom dashboard creation as a result.

Let’s start with a teaser of what is available as of the end of this blog post… How about a custom dashboard showcasing IIS information using the new SQL dashboard type!

We’ll get there, but let’s start with looking at this from a SQL perspective. This is the first in a four blog post series focusing on how to monitor and visualize SQL servers in your environment using Microsoft technologies. The blog post topics in this series include:

Part 1: Monitoring & Visualizing SQL Server using System Center Operations Manager 2012 R2 [This post]
Part 2: Monitoring & Visualizing SQL Server using 3^rd party dashboard solutions
Part 3: Monitoring & Visualizing SQL server using Operational Insights (OMS)
Part 4: Monitoring & Visualizing other variations of SQL Server

This blog post will focus on:

How Operations Manager works with Management Packs
Where to find the management packs
What is included in the SQL management pack family?
Customization of the SQL dashboards
Do we gain these new dashboard types in OpsMgr

Monitoring & Visualizing SQL Server using System Center Operations Manager 2012 R2

If you want to see what can be done to visualize information with Operations Manager (and 3^rd party extensions) you do not need to go any further than seeing what can be done with SQL server. As a starting point it needs to be clear how Operations Manager works with management packs and where to find them. Next we will explore the various SQL server focused management packs and how they can be used to visualize your SQL servers.

How Operations Manager works with Management Packs

When you install Operations Manager there are a series of built-in management packs which are used by the product. These management packs are key to having a functional environment, but when looking at monitoring from a SQL perspective they effectively only provide one key piece of functionality – heartbeat alerting. The built-in management packs will provide you with alerts if an agent that it is monitoring is not responding both via a heartbeat alert (Health Service Heartbeat) and via a ping alert (Failed to Connect to Computer).

To provide monitoring for a SQL server we need to look at the various other components addition to SQL server that need to be monitored. The list below shows an example of management packs which should also be in the Operations Manager environment so that SQL can be effectively monitored:

Base management packs
Operating System management packs
IIS management packs (optional)
SQL management packs

Where to find the management packs

The focus areas include showcasing the new SQL management pack for Operations Manager and what you get from that, how you use other management packs to monitor SQL server, etc. I am also thinking about including dashboarding solutions such as Squared Up and Savision Live Maps (both of which I believe have pre-built dashboards for SQL). Additionally, I want to showcase OMS and its features for SQL best practices, and to also show how we can monitor SQL Azure within OpsMgr. Overall it’s looking at SQL server as a tier-1 workload and trying to showcase how we can use all of the Microsoft solutions together to form a comprehensive picture.

Where to find the management packs?

The Wiki page: http://tinyurl.com/OMMPWIKI (this is the most up to date resource and therefore the preferred approach)
Community management packs: http://tinyurl.com/OMCommunityMP
You can also directly download management packs through the Operations Manager console in the administration pane

What is included in the SQL management pack family?

There are currently 15 matches for “SQL Server” on the wiki page reference above (and shown below):

If you want to see what components you should add in your environment a great place to start is the new Microsoft SQL Server Summary dashboard shown below. Note that this shows that the SQL Server Instances are monitored (because the SQL Server 2005/2008/2012 management pack is loaded) but other components are not being monitored (shown with the blue tiles below). These include SQL Server Replication Database Health, SQL Server Mirroring, SQL Server AlwaysOn, SQL Server Reporting Services and SQL Server Analysis Services. I do not recommend loading all of the above management packs, but if you have these functions in your environment it is definitely worth checking them out.

When additional management packs for SQL are loaded then additional details are available (see the SQL Server Reporting Services and SQL Server Analysis Services sections of the dashboard below for an example of what they look like after their management packs have been loaded).

Each of the top level tiles can be expanded to provide more detail as shown below (SQL Server Instances):

SQL Server Reporting Services:

SQL Server Analysis Services:

You can interact with these dashboards as shown below for both the bar graphs and the circular graphs:

Individual SQL Server instances can be drilled down even further to provide an extremely useful and detailed view of the instance.

These graphs can be interacted with as well by highlighting sections of the graph to display the values at that period in time.

The SQL Reporting Services and SQL Server Analysis Services dashboards also drill in to provide further details.

SQL Reporting Services:

SQL Server Analysis Services (note the orange colored Memory Usage on the Server indicator below!)

I would expect that the other dashboards which are not activated in my environment (SQL Server Replication Database Health, SQL Server Mirroring, SQL Server AlwaysOn) also provide this type of drill-down capability.

Customization of the SQL dashboards:

These dashboards also provide the ability to customize them further for your environment (right-click on the three lines shown below).

I renamed my SQL Service Instances to “My SQL Server Instances” as an example.

You can also add aggregated tiles for performance or monitors. A performance example is shown below:

Here’s the new “Performance Tile”:

A new aggregated monitor tile is shown below:

Here’s the new “Aggregated Monitor Tile”:

[If you are asking yourself – “Where does it store these new tiles?” I am asking myself the same question. I had assumed that these were stored in a non-sealed management pack but I’m not seeing a management pack other than the default one which is changing and that doesn’t appear to have any information on “Aggregated Monitor Tile” as an example. I’m also not seeing an option to remove the custom tiles that I added so that’s another question to think about another day…]

Do we gain these new dashboard types in OpsMgr?

There is a new type of dashboards available now!

After creating the dashboard, you add groups:

For an example I’ll see if we can use these same dashboards for IIS related groups:

This populates like a champ!

And we can add our own performance counters and monitors as shown below.

Summary: The SQL management packs have come a long way in their visualization capabilities as shown by the dashboard examples above. The ability to create your own dashboards using these new technologies adds value throughout Operations Manager and should not be missed! If you haven’t installed the new SQL management pack (or haven’t tried the dashboards shown above) I highly recommend taking a look!

In the next part of this blog series we will investigate monitoring & visualizing SQL Server using 3^rd party dashboard solutions.

Reference URL’s:

Monitoring a SQL Server with more than 1000 databases: https://www.catapultsystems.com/cfuller/archive/2014/06/18/using-operations-manager-to-monitor-a-sql-server-with-more-than-1000-databases/
Primary SQL Management pack: http://www.microsoft.com/en-us/download/details.aspx?id=10631 (2005, 2008, 2008 R2, 2012)
Primary SQL Management pack: https://www.microsoft.com/en-us/download/details.aspx?id=42573 (2014)
The Wiki page: http://tinyurl.com/OMMPWIKI (this is the most up to date resource and therefore the preferred approach)
Community management packs: http://tinyurl.com/OMCommunityMP

UPDATE: I totally missed that Stefan had covered a lot of this already. His blog post is available at: http://stefanroth.net/2015/07/09/scom-new-sql-server-management-pack-6-6-0-0-dashboard-capabilities/