This metric is described in Grant Fritchey’s Custom Metrics for monitoring mirroring article on Simple Talk.
It’s best practice to set up a witness that manages connections to your mirrored databases. The witness is a SQL Server instance that ensures you maintain uptime, so knowing if it is offline can be as important as knowing if your mirrored databases are available. This metric returns a single value that tells you whether the witness is connected, disconnected or unknown. For more details about witness servers, see Database Mirroring Witness (Microsoft TechNet article).
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 the witness 'InsertWitnessNameHere'
This metric returns the state of the SQL Server instance configured as the mirroring witness. States are represented by the following values:
- 0 = Unknown
- 1 = Connected
- 2 = Disconnected
The T-SQL query that will collect data
Instances to collect from
Databases to collect from
Use collected or calculated values
Leave the Use a calculated rate of change between collections check box unchecked
Witness 'InsertWitnessNameHere' disconnected
Raise an alert when the metric value goes
Above the defined threshholds
Default threshold values
Raise an alert when the threshold is passed for