This morning I came into work and went through my usual 100 or so emails. One of the emails was from MSSQLTips.com, it was on how to monitor SQL Server Database mirroring with email alerts. By Alan Cranfield. While agree with Alan that every DBA should monitor their database mirroring with email alerts I disagreed with his method. He had the DBA create a job that was scheduled to run at some interval throughout the day. His job would query the sys.database_mirroring view. As DBAs we need to know immediately when something fails or changes. 5 minutes could be the difference between a quick fix and restoring a 500 GB db mirror.

So what would be a better way to monitor and alert a DBA when there is a change in the state of Database mirroring? I prefer to use Alerts for events. Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events. A DBA can specify which db mirroring event they wish to moitor. Here is a table of events to monitor for:

State Name Description
Null Notification This state occurs briefly when a mirroring session is started.
1 Synchronized Principal with Witness This state occurs on the principal when the principal and mirror are connected and synchronized and the principal and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
2 Synchronized Principal without Witness This state occurs on the principal when the principal and mirror are connected and synchronized but the principal does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
3 Synchronized Mirror with Witness This state occurs on the mirror when the principal and mirror are connected and synchronized and the mirror and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.
4 Synchronized Mirror without Witness This state occurs on the mirror when the principal and mirror are connected and synchronized but the mirror does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.
5 Connection with Principal Lost This state occurs on the mirror server instance when it cannot connect to the principal.
6 Connection with Mirror Lost This state occurs on the principal server instance when it cannot connect to the mirror.
7 Manual Failover This state occurs on the principal server instance when the user fails over manually from the principal, or on the mirror server instance when a force service is executed at the mirror.
8 Automatic Failover This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.
9 Mirroring Suspended This state occurs on both partner instances when the user suspends (pauses) the mirroring session or when the mirror server instance encounters an error. It also occurs on the mirror server instance following a force service command. When the mirror comes online as the principal, mirroring is automatically suspended.
10 No Quorum If a witness is configured, this state occurs on the principal or mirror server instance when it cannot connect to its partner or to the witness server instance.
11 Synchronizing Mirror This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing.
12 Principal Running Exposed This state occurs on the principal server instance when the operating mode is high protection (synchronous) and the principal cannot connect to the mirror server instance.
13 Synchronizing Principal This state occurs on the principal server instance when there is a backlog of unsent log. The status of the

Now that we know the Event and the State here is how to add an Alert to notify you that the state of DB mirroring has changed.

USE [msdb]
GO

/****** Object:  Alert [DBM State Change]    Script Date: 10/15/2009 08:03:20 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBM State Change', 
		@message_id=0, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER', 
		@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6 ', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

This is an alert that I created on the principal server. I also have created a similar alert on the mirror server where I look for state = 5. These two alerts will notify me if the connection between the Principal and Mirror is lost due to network or some other failure.

To receive notification when this event happens it is simple to just create an operator and have the event email the operator if and when the event conditions are met.

What other Mirror Events should every DBA monitor? I find the unsent and unrestored log to be two very import events to receive notifications for. For those events just simply create a new event for the event ID in the table below and set you monitor threshold.

Database Mirroring Monitor warning Event name Event ID
Warn if the unsent log exceeds the threshold Unsent log 32042
Warn if the unrestored log exceeds the threshold Unrestored log 32043
Warn if the age of the oldest unsent transaction exceeds the threshold Oldest unsent transaction 32044
Warn if the mirror commit overhead exceeds the threshold Mirror commit overhead 32045

You can also script this by using sp_add_alert as follows:

USE [msdb]
GO

/****** Object:  Alert [DB Mirroring Unsent Log Warning]    Script Date: 10/15/2009 08:14:29 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DB Mirroring Unsent Log Warning', 
		@message_id=32042, 
		@severity=0, 
		@enabled=0, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Good Luck and Happy monitoring!