16 April 2013

Custom Metrics for Monitoring Database Mirroring

SQL Monitor comes with plenty of metrics, but there are always going to be things that you want to measure that can't be done 'out of the box'. Taking database mirroring as an example, Grant shows that custom metrics can be used to monitor and alert whatever aspect of SQL Server or database that you need.

You won’t find metrics for database mirroring set up for you in SQL Monitor v3.0 since most people just aren’t going to need those types of metrics. But, what if you do want to monitor what’s happening with the databases you have mirrored on your server? That’s when you can put the custom metrics to work. Once they are installed, they can be used just like the built-in metrics. With custom metrics you can create your own monitoring metric within SQL Monitor and use it to capture the performance data that you need or look for situations where you need to be alerted.

It’s actually really simple to set up some metrics for monitoring what happens within your mirrored databases. This article describes four custom metrics that users of SQL Monitor v3.2 and later can install automatically from Red Gate’s online resource for custom metrics, sqlmonitormetrics.com. Let’s look at them in more detail.

Setup

Before we can even begin monitoring your mirroring setup, you need to do a little bit of work on the servers. You must go to the msdb database and run the procedure sp_dbmmonitoraddmonitoring. This is a Microsoft-provided procedure that sets up a special job within SQL Agent that periodically updates information about the mirrored databases on your server. The full documentation for this procedure is available on MSDN. To execute the procedure, use the following script on each of your mirroring servers:

This will create a SQL Agent job with a schedule. In order for it to function though, you must have SQL Agent configured and running on the server as well. Once that’s done, you can very quickly pull back information from your servers using a procedure, sp_dbmmonitorresults. The information is gathered, by default, once a minute. You can adjust that when you create the monitor by passing a parameter as outlined on MSDN. Or, you can adjust the schedule on the Agent after the fact. When you call sp_dbmmonitorresults, you have to determine how many rows you want to return. Since the custom metrics in SQL Monitor deal with a single value, we’re going to return one row. I have a database set up for mirroring, so I can check the monitor values this way:

This returns all sorts of great information about the status of monitoring. There are a couple of considerations you should apply within your SQL Monitor setup in order to make all this work. First, the frequency of your monitoring should be equal to or greater than the frequency with which you are updating the mirroring information. If you retrieve the data more frequently, it just won’t make sense. Each of these queries is oriented towards a particular database, so you’ll need to be sure that you specify that database in the custom metric. You also need to make sure that your process that updates the monitoring data is running regularly because we can’t update the data from this call to sys.sp_dbmmonitorresults. That’s about it for special considerations. Let’s look at the metrics.

Current status of mirroring

To get the current status of mirroring on a database you can pull the mirror_state value. This would allow you to set up an alert if the status went into something other than operational. The query is pretty straight forward:

The values that can be returned are:

  1. 0 = Suspended
  2. 1 = Disconnected
  3. 2 = Synchronizing
  4. 3 = Pending Failover
  5. 4 = Synchronized

If you then set up an alert to check for values less than 2, you can know when your mirroring system is offline.

Install this metric from sqlmonitormetrics.red-gate.com

Current role of a database

You can also keep track of what the role of a given database is within your mirroring set up. Maybe you always want one server to act as the Principal. You can check the role by modifying the query like this:

The values returned from this query appear as follows:

  • 1 = Principal
  • 2 = Mirror

Depending on what you’re planning to monitor, you can track when a database changes from the Principal.

Install this metric from sqlmonitormetrics.red-gate.com

Status of the witness

If you’ve got a witness set up to manage connections to your mirrored databases so that you maintain uptime, knowing if the witness is offline can be as important as knowing if your mirrored databases are available. You can modify the query again to capture this information:

The results returned are here;

  1. 0 = Unknown
  2. 1 = Connected
  3. 2 = Disconnected

In my case, I currently don’t have a witness set up, so the value is 0. But if a witness were defined you could track the status and set up an alert to know when it changed to any value other than 1 = Connected.

Install this metric from sqlmonitormetrics.red-gate.com

Data not yet sent to the mirror

You can track the amount of data (in KBs) waiting to go to the server.  This is sometimes referred to as the send queue. It just requires a small modification to the query:

This is not a metric that you would probably set up an alert on unless you had already been tracking the values for a considerable period of time so that you know exactly how your system normally behaves.

Install this metric from sqlmonitormetrics.red-gate.com

Conclusion

As you can see, setting up the database mirroring metrics in SQL Monitor is pretty simple and a great many others are available. You can see the average delay on getting the transactions moved from the principal to the mirror, the rate at which information is recovered on the mirror, and the redo queue on the mirror, all sorts of stuff and it just requires a minor modification to the same query.

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

This post has been viewed 14120 times – thanks for reading.

Tags: , , , , ,

  • Rate
    [Total: 11    Average: 4.5/5]
  • Share

Grant Fritchey

View all articles by Grant Fritchey

  • slimrichard

    Good Work
    I made a very similar custom metric to this one here http://sqlmonitormetrics.red-gate.com/mirroring-latency/ I found if you add the DB_NAME() to a variable and use that in the execution of the stored proc you can add the metric to an instance rather than a specific db. This is handy if you have many mirrored db’s in an instance and cuts down on the number of separate custom metrics for each one 🙂

  • Grant Fritchey

    Nice
    Excellent catch. Well done.