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:
|
1 2 3 4 |
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:
|
1 2 3 4 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
- 0 = Suspended
- 1 = Disconnected
- 2 = Synchronizing
- 3 = Pending Failover
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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;
- 0 = Unknown
- 1 = Connected
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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.
Load comments