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:

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.

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!