Current status of mirroring

Mirroring systems can go offline. This metric returns the current state of the mirroring system so you’ll know from a single value what the current status of your mirroring environment is.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

This metric is described in Grant Fritchey’s Custom Metrics for monitoring mirroring article on Simple Talk.

With this metric installed, you’ll know from a single value what the current status of your mirroring system is. It can be used to check for changes in the environment, for example, if a mirror goes from synchronized to pending failover, or if the there’s an error situation that causes the server to disconnect.

Important setup information

Before you can start using this metric, you need to:

  1. Run the following Microsoft procedure on the msdb database you’ll use in the custom metric:
    [sql]USE msdb;
    GO
    EXEC sp_dbmmonitoraddmonitoring;
    GO
    [/sql]This sets up a SQL Agent job that periodically updates information about the mirrored databases on your server. For more information, see http://msdn.microsoft.com/en-us/library/ms403582.aspx.
  2. Make sure the SQL Agent is configured and running on the server. Then you can retrieve information using the sp_dbmmonitorresults procedure.

Metric definition

Name

Status of mirroring on 'InsertDatabaseNameHere'

Description

This metric returns the current state of the mirroring system on the named database. States are represented by the following values:

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

The T-SQL query that will collect data

Instances to collect from

Select all

Databases to collect from

msdb

Collection frequency

60

Note: The frequency depends on the status you’re attempting to monitor. If you’re looking for errors, it may be best to run this once a minute.

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Mirror state disconnected or suspended on 'InsertDatabaseNameHere'

Description

This alert is raised if the status of mirroring changes from synchronizing to either disconnected or suspended.

Raise an alert when the metric value goes

Below the defined threshholds

Default threshold values

High:2
Medium:Leave unchecked
Low:Leave unchecked

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled