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:
- Run the following Microsoft procedure on the msdb database you’ll use in the custom metric:
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.
- Make sure the SQL Agent is configured and running on the server. Then you can retrieve information using the sp_dbmmonitorresults procedure.
Status of mirroring on 'InsertDatabaseNameHere'
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
Databases to collect from
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
Mirror state disconnected or suspended on 'InsertDatabaseNameHere'
Raise an alert when the metric value goes
Below the defined threshholds
Default threshold values
Raise an alert when the threshold is passed for