Create Mirroring Alerts
Applicability:
SQL Server 2000: Not Supported
SQL Server 2005: Not Tested
SQL Server 2008: Not Tested
SQL Server 2008R2: Not Tested
SQL Server 2012: Not Tested
Credits:
Author: Jordon Philling
Date: 23 Nov 2010
Description
Another mirroring script that I haven't yet had chance to fully test.
This script has 4 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)
Code
/*
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