Cool Tips‎ > ‎Database Mirroring‎ > ‎

Database Mirroring - 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:
  1. Delete any old DBM alerts 
  2. Create a Stored Proc to create alerts for a given database (adapted from an msdn article) 
  3. Loop through all mirrored databases and call the SP 
  4. 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


ċ
Add mirroring alerts for all mirrored dbs on a server.sql
(16k)
Andy Hughes,
Jul 19, 2012, 10:00 AM
Comments