Click here to monitor SSC
  • Av rating:
  • Total votes: 8
  • Total comments: 2
Grant Fritchey

Custom Metrics for Monitoring Database Mirroring

16 April 2013

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:

USE msdb;

GO

EXEC sp_dbmmonitoraddmonitoring;

GO

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:

EXEC sys.sp_dbmmonitorresults

       @database_name = 'InsertDatabaseNameHere', -- sysname

       @mode = 0, -- int

       @update_table = 0 -- int

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:

DECLARE       @MonitorResults AS TABLE (

       database_name VARCHAR(255),

       role INT,

       mirror_state TINYINT,

       witness_status TINYINT,

       log_generat_rate INT,

       unsent_log INT,

       sent_rate INT,

       unrestored_log INT,

       recovery_rate INT,

       transaction_delay INT,

       transaction_per_sec INT,

       average_delay INT,

       time_recorded DATETIME,

       time_behind DATETIME,

       local_time DATETIME);

 

INSERT INTO @MonitorResults

              EXEC sp_dbmmonitorresults

                     @database_name = 'InsertDatabaseNameHere',

                     @mode = 0,

                     @update_table = 0;

 

SELECT mirror_state

FROM   @MonitorResults;

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:

DECLARE       @MonitorResults AS TABLE (

       database_name VARCHAR(255),

       role INT,

       mirror_state TINYINT,

       witness_status TINYINT,

       log_generat_rate INT,

       unsent_log INT,

       sent_rate INT,

       unrestored_log INT,

       recovery_rate INT,

       transaction_delay INT,

       transaction_per_sec INT,

       average_delay INT,

       time_recorded DATETIME,

       time_behind DATETIME,

       local_time DATETIME);

 

INSERT INTO @MonitorResults

              EXEC sp_dbmmonitorresults

                     @database_name = 'InsertDatabaseNameHere',

                     @mode = 0,

                     @update_table = 0;

 

SELECT role

FROM   @MonitorResults;

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:

DECLARE       @MonitorResults AS TABLE (

       database_name VARCHAR(255),

       role INT,

       mirror_state TINYINT,

       witness_status TINYINT,

       log_generat_rate INT,

       unsent_log INT,

       sent_rate INT,

       unrestored_log INT,

       recovery_rate INT,

       transaction_delay INT,

       transaction_per_sec INT,

       average_delay INT,

       time_recorded DATETIME,

       time_behind DATETIME,

       local_time DATETIME);

 

INSERT INTO @MonitorResults

              EXEC sp_dbmmonitorresults

                     @database_name = 'InsertDatabaseNameHere',

                     @mode = 0,

                     @update_table = 0;

 

SELECT witness_status

FROM   @MonitorResults;

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:

DECLARE       @MonitorResults AS TABLE (

       database_name VARCHAR(255),

       role INT,

       mirror_state TINYINT,

       witness_status TINYINT,

       log_generat_rate INT,

       unsent_log INT,

       sent_rate INT,

       unrestored_log INT,

       recovery_rate INT,

       transaction_delay INT,

       transaction_per_sec INT,

       average_delay INT,

       time_recorded DATETIME,

       time_behind DATETIME,

       local_time DATETIME);

 

INSERT INTO @MonitorResults

              EXEC sp_dbmmonitorresults

                     @database_name = 'InsertDatabaseNameHere',

                     @mode = 0,

                     @update_table = 0;

 

SELECT unsent_log

FROM   @MonitorResults;

 

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.

Grant Fritchey

Author profile:

Grant Fritchey, SQL Server MVP, works for Red Gate Software as Product Evangelist. In his time as a DBA and developer, he has worked at three failed dot–coms, a major consulting company, a global bank and an international insurance & engineering company. Grant volunteers for the Professional Association of SQL Server Users (PASS). He is the author of the books SQL Server Execution Plans (Simple-Talk) and SQL Server 2008 Query Performance Tuning Distilled (Apress). He is one of the founding officers of the Southern New England SQL Server Users Group (SNESSUG) and it’s current president. He earned the nickname “The Scary DBA.” He even has an official name plate, and displays it proudly.

Search for other articles by Grant Fritchey

Rate this article:   Avg rating: from a total of 8 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Good Work
Posted by: slimrichard (view profile)
Posted on: Wednesday, April 24, 2013 at 2:25 AM
Message: 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 :)

Subject: Nice
Posted by: Grant Fritchey (view profile)
Posted on: Thursday, May 16, 2013 at 5:20 AM
Message: Excellent catch. Well done.

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

SQL Server XML Questions You Were Too Shy To Ask
 Sometimes, XML seems a bewildering convention that offers solutions to problems that the average... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.