Create Mirroring Alerts

/*

This script has 3 main parts:

Delete any old DBM alerts

Create a Stored Proc to create alerts for a given database (adapted from an msdn article)

Loop through all mirrored databases and call the SP

Drop the SP

This script will create alerts for each mirrored database, the alerts created will cover:

Commit Overhead

Oldest Unsent Transaction Threshold

Unrestored Log Threshold

Unsent Log Threshold

Automatic Failover

Manual Failover

Mirror Connection Lost (my favourite)

Mirroring Suspended

No Quorum

Principal Connection Lost (my favourite)

Principal Running Exposed

Synchronizing Mirror

Synchronizing Principal

This script also assumes you have an email enabled operator on your server, (assuming you have database mail configured), if not, simply create an Operator first: (change the email address)

EXEC msdb.dbo.sp_add_operator @name=N'sqladmins',

@enabled=1,

@weekday_pager_start_time=0,

@weekday_pager_end_time=235959,

@saturday_pager_start_time=0,

@saturday_pager_end_time=235959,

@sunday_pager_start_time=0,

@sunday_pager_end_time=235959,

@pager_days=127,

@email_address=N'sqladmins@whatever.co.uk',

@category_name=N'[Uncategorized]'

GO

*/

--##############################################################

--## Creates WMI Mirroring alerts for all Mirrored databases

--##############################################################

--#### Delete old Mirroring Alerts (based on DMB prefix)

DECLARE @Cursor_MirrorAlerts CURSOR

DECLARE @AlertName NVARCHAR(128)

SET @Cursor_MirrorAlerts = CURSOR FAST_FORWARD

FOR

SELECT [name] FROM msdb.dbo.sysalerts WHERE [name] LIKE 'DBM%'

OPEN @Cursor_MirrorAlerts

FETCH NEXT FROM @Cursor_MirrorAlerts

INTO @AlertName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC msdb.dbo.sp_delete_alert @name = @AlertName

FETCH NEXT FROM @Cursor_MirrorAlerts

INTO @AlertName

END

CLOSE @Cursor_MirrorAlerts

DEALLOCATE @Cursor_MirrorAlerts

--#### Create the Main Stored Procedure that creates alerts

USE [msdb]

GO

IF EXISTS ( SELECT *

FROM sys.objects

WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_CreateMirroringAlerts]')

AND TYPE IN ( N'P', N'PC' ) )

DROP PROCEDURE [dbo].[usp_CreateMirroringAlerts]

GO

USE [msdb] ;

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Jordon Pilling | www.heavencore.co.uk>

-- Create date: <23/11/2010>

-- Description: Creates Mirroring Alerts for all the different state changes etc

-- Created based on Article: http://technet.microsoft.com/en-us/library/cc966392.aspx

-- Note, Alerts regarding witness server are commented out, simply uncomment to include

-- =============================================

CREATE PROCEDURE usp_CreateMirroringAlerts

@DatabaseName NVARCHAR(255)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @ReturnCode INT ;

DECLARE @namespace NVARCHAR(200) ;

DECLARE @wquery NVARCHAR(200) ;

DECLARE @alertName NVARCHAR(200) ;

DECLARE @dbName NVARCHAR(128) ;

DECLARE @instanceName NVARCHAR(128) ;

SELECT @ReturnCode = 0 ;

SELECT @dbName = @DatabaseName ;

IF ( SERVERPROPERTY('InstanceName') IS NOT NULL )

SELECT @instanceName = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName')) ;

ELSE

SELECT @instanceName = N'MSSQLSERVER' ;

IF NOT EXISTS ( SELECT name FROM msdb.dbo.syscategories WHERE name = N'Database Mirroring' AND category_class = 2 )

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'ALERT', @type = N'NONE', @name = N'Database Mirroring' ;

IF ( @@ERROR <> 0 OR @ReturnCode <> 0 )

GOTO Quit_Alert ;

END ;

--#### Create [DBM Perf: Unsent Log Threshold (<dbname>]

SELECT @alertName = N'DBM Perf: Unsent Log Thresholdd (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = @dbName, @message_id = 32042, @severity = 0, @delay_between_responses = 1800, @include_event_description_in = 0, @enabled = 1 ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM Perf: Oldest Unsent Transaction Threshold (<dbname>]

SELECT @alertName = N'DBM Perf: Oldest Unsent Transaction Threshold (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = @dbName, @message_id = 32040, @severity = 0, @delay_between_responses = 1800, @include_event_description_in = 0, @enabled = 1 ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM Perf: Unrestored Log Threshold (<dbname>]

SELECT @alertName = N'DBM Perf: Unrestored Log Threshold (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = @dbName, @message_id = 32043, @severity = 0, @delay_between_responses = 1800, @include_event_description_in = 0, @enabled = 1 ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM Perf: Mirror Commit Overhead Threshold (<dbname>]

SELECT @alertName = N'DBM Perf: Mirror Commit Overhead Threshold (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = @dbName, @message_id = 32044, @severity = 0, @delay_between_responses = 1800, @include_event_description_in = 0, @enabled = 1 ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Principal Connection Lost (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 5 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Principal Connection Lost (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Mirror Connection Lost (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Mirror Connection Lost (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Manual Failover (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 7 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Manual Failover (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Automatic Failover (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 8 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Automatic Failover (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Mirroring Suspended (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 9 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Mirroring Suspended (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: No Quorum (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 10 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: No Quorum (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Synchronizing Mirror (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 11 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Synchronizing Mirror (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Principal Running Exposed (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 12 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Principal Running Exposed (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

--#### Create [DBM State: Synchronizing Principal (<dbname>)]

SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 13 AND DatabaseName = ''' + @dbName + '''' ;

SELECT @alertName = N'DBM State: Synchronizing Principal (' + @dbName + ')' ;

EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 1, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'sqladmins', @notification_method = 1

----#### Create [DBM State: Synchronized Principal with Witness (<dbname>)]

-- SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

-- SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 1 AND DatabaseName = ''' + @dbName + '''' ;

-- SELECT @alertName = N'DBM State: Synchronized Principal with Witness (' + @dbName + ')' ;

-- EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

----#### Create [DBM State: Synchronized Principal without Witness (<dbname>)]

-- SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

-- SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 2 AND DatabaseName = ''' + @dbName + '''' ;

-- SELECT @alertName = N'DBM State: Synchronized Principal without Witness (' + @dbName + ')' ;

-- EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

----#### Create [DBM State: Synchronized Mirror with Witness (<dbname>)]

-- SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName ;

-- SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 3 AND DatabaseName = ''' + @dbName + '''' ;

-- SELECT @alertName = N'DBM State: Synchronized Mirror with Witness (' + @dbName + ')' ;

-- EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

----#### Create [DBM State: Synchronized Mirror without Witness (<dbname>)]

-- SELECT @namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + @instanceName

-- SELECT @wquery = N'SELECT * from DATABASE_MIRRORING_STATE_CHANGE WHERE State = 4 AND DatabaseName = ''' + @dbName + ''''

-- SELECT @alertName = N'DBM State: Synchronized Mirror without Witness (' + @dbName + ')'

-- EXEC msdb.dbo.sp_add_alert @name = @alertName, @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 0, @include_event_description_in = 0, @category_name = N'Database Mirroring', @wmi_namespace = @namespace, @wmi_query = @wquery ;

Quit_Alert:

END

GO

--#### Loop through each mirrored database and call the above SP to create its alerts

DECLARE @Cursor_MirroredDatabases CURSOR

DECLARE @MirroredDatabaseName NVARCHAR(128)

SET @Cursor_MirroredDatabases = CURSOR FAST_FORWARD

FOR

SELECT DB_NAME(database_id) AS [DatabaseName] FROM sys.database_mirroring WHERE mirroring_guid IS NOT NULL

OPEN @Cursor_MirroredDatabases

FETCH NEXT FROM @Cursor_MirroredDatabases

INTO @MirroredDatabaseName

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC msdb.dbo.usp_CreateMirroringAlerts @DatabaseName = @MirroredDatabaseName

FETCH NEXT FROM @Cursor_MirroredDatabases

INTO @MirroredDatabaseName

END

CLOSE @Cursor_MirroredDatabases

DEALLOCATE @Cursor_MirroredDatabases

--#### Clean Up

USE [msdb]

GO

IF EXISTS ( SELECT *

FROM sys.objects

WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[usp_CreateMirroringAlerts]')

AND TYPE IN ( N'P', N'PC' ) )

DROP PROCEDURE [dbo].[usp_CreateMirroringAlerts]

GO