If you have database mirroring setup and running, you’ll notice that it has a lack of built in monitoring. And if you have a lot of databases having monitoring for each potential state that each database can be in can flood you with email if the network between sites goes down and comes back up. Especially if it happens a few times in a minute (the record that I’ve gotten using this sort of configuration is about 700 emails in one night of network maintenance.
So I’ve put together a slightly more simplified database mirroring monitor. It’ll still tell you if the network goes down and comes back up, but it’ll do so if a slightly nicer way. You’ll want to change the @operator_name value to a valid operator on your system before you run this, otherwise you’ll get an error as the operator doesn’t exist. I’ve set the delay between retries at 5 minutes, so even if the network is going nuts you’ll get at most 12 emails from each alert per hour. Depending on how stable your network is you may want to adjust this as needed.
Never fear, this works on SQL Server 2005 – SQL Server 2016 (CTP 2 at least).
USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirror Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 6',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO
USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM Perf: Oldest Unsent Transaction Threshold'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=32040,
@severity=0,
@enabled=1,
@delay_between_responses=1800,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO
USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Mirroring Suspended'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 9',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO
USE [msdb]
GO
DECLARE @name sysname
SET @name = @@SERVERNAME + N' - DBM State: Principal Connection Lost'
EXEC msdb.dbo.sp_add_alert @name=@name,
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=0,
@category_name=N'Database Mirroring',
@wmi_namespace=N'\.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE
WHERE State = 5',
@job_id=N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name=@name, @operator_name=N'SQLAdmins', @notification_method = 1
GO
Happy monitoring,
Denny
