16 April 2013

Status of the witness

by Grant Fritchey

15,213 0 Mirroring

This metric returns a single value that tells you whether the witness is connected, disconnected or unknown. Knowing if it is offline can be as important as knowing if your mirrored databases are available.

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.

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:
[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.
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 the witness 'InsertWitnessNameHere'

Description

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


		
			
			
Transact-SQL
			
			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;
			
				
					
				
					1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
				
						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;

Instances to collect from

Select all

Databases to collect from

msdb

Collection frequency

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

Witness 'InsertWitnessNameHere' disconnected

Description

This alert is raised when the state of the SQL Server instance configured as the witness changes to disconnected.

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:	1
Medium:	Leave unchecked
Low:	Leave unchecked

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled

15,213 Rate

Click to rate this post!

[Total: 0 Average: 0]

Status of the witness

Metric definition

Name

Description

The T-SQL query that will collect data

Instances to collect from

Databases to collect from

Collection frequency

Use collected or calculated values

Metric collection

Alert definition

Alert name

Description

Raise an alert when the metric value goes

Default threshold values

Raise an alert when the threshold is passed for

Alert is

What are custom metrics?

What is Redgate Monitor?

Featured custom metrics

Total database file size

Number of active backups and restores

Full backup to odd path

Find metrics