Technical Article

Create Mirror Alerts for All Mirrored Databases

,

Ever come across a 64bit SQL Server with more cores than you can shake a stick at, with a but load of Mirrored databases with no mirroring alerts what so ever?

This script has 6 main parts:

  1. Delete any old DBM alerts
  2. Create a Stored Proc to create alerts for a given database, both Threshold Based alerts (which it created Default THreashold values for you) and WMI based alerts for those awkward state changes.
  3. Create a Stored Proc to Email current Mirror Status for all databases
  4. Create a Job to Run the SP in point 3 (to be used by certain alerts)
  5. Loop through all mirrored databases and call the SP in point 2
  6. Drop the Temporary Components etc

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

To use the script, simply paste it into a new query window on your principle, do a find replace on "XXXXX@YYYY.COM" and replace with your DBA address, then execute. Repeat on the Mirror.

This is version 8 of the script, based on feedback in the discussion thead.

--##############################################################
--## Creates WMI Mirroring alerts for all Mirrored databases
--## ===========================================================
--## The following script assumes your mirroring is working and 
--## SQL Job "Database Mirroring Monitor Job" is running every 
--## minute or so.
--## 0.1 - BETA Release
--## 0.2 - Published to SSC
--## 0.3 - Removed private email address from Operator
--## 0.4 - Changes @include_event_description_in to 1 for Threshold based alerts
--## 0.5 - Added Threshold Creation via sp_dbmmonitorchangealert
--## 0.6 - Changed Threshold alerts to <all databases> as DB level do not seem to work
--##     See More: http://www.sqlservercentral.com/Forums/Topic1029139-2854-1.aspx#bm1084290
--## 0.7 - Added usp_dbmmonitorresults_EmailMode to the installer (see below)
--## 0.8 - Added auto-job creation and linkage on the threshold tasks
--## USAGE: find replace XXXXX@YYYY.COM with your email address then run on msdb
--##############################################################

--#### Set Scope, ALWAYS run this on the MSDB database
USE [msdb]
GO

--#### Create your Mirroring Operator (Replacing any that exist with same name)
IF EXISTS (SELECT name FROM msdb.dbo.sysoperators WHERE name = N'Admins_MirrorAlerts')
EXEC msdb.dbo.sp_delete_operator @name=N'Admins_MirrorAlerts'
GO
EXEC msdb.dbo.sp_add_operator @name=N'Admins_MirrorAlerts', 
        @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'XXXXX@YYYY.COM', 
        @category_name=N'[Uncategorized]'
GO

--#### 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
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) ;
    DECLARE @Threshold_AlertsResponseDelay INT ;
    DECLARE @Threshold_OldestUnsentTransaction INT ;
    DECLARE @Threshold_UnsentLogSize INT ;
    DECLARE @Threshold_UnrestoredLogSize INT ;
    DECLARE @Threshold_MirrorCommitOverhead INT ;
    DECLARE @Threshold_RetentionPeriod INT ;
    DECLARE @Mirroring_StatusEmailJob UNIQUEIDENTIFIER ;
    
    --#### Settings, feel free to change these
    SET @Threshold_AlertsResponseDelay        = 600;    --#### In Seconds
    SET @Threshold_OldestUnsentTransaction    = 30;    --#### In Minutes
    SET @Threshold_UnsentLogSize            = 2048;    --#### In KB
    SET @Threshold_UnrestoredLogSize        = 2048;    --#### In KB
    SET @Threshold_MirrorCommitOverhead        = 1000;    --#### In Milliseconds
    SET @Threshold_RetentionPeriod            = 12;    --#### In Hours ( i.e: How long to keep the alerts in the database mirroring status table)
    
    --#### Grab the JOB ID of JOB 'DBM: Email Mirror Status'
    SELECT @Mirroring_StatusEmailJob = job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBM: Email Mirror Status'
    IF @Mirroring_StatusEmailJob IS NULL
        BEGIN
            RAISERROR('Unable to build alerts, Job [DBM: Email Mirror Status] could not be found', 16, 1)
            RETURN
        END

    --#### Determine Instance Name
    IF ( SERVERPROPERTY('InstanceName') IS NOT NULL ) 
        SELECT @instanceName = CONVERT(NVARCHAR(128), SERVERPROPERTY('InstanceName')) ;
    ELSE 
        SELECT @instanceName = N'MSSQLSERVER' ;

    --#### Check the Alert 'type' of 'Database Mirroring' exists, create if not
    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 ;

    --#### Paramater Sniffing Precaution - not really applicable, but old habbits die hard and all that
    SET @dbName = @DatabaseName ; 

--#### Set Alert Thresholds for the given database, feel free to change these
    EXEC sp_dbmmonitorchangealert @dbName, 1, @Threshold_OldestUnsentTransaction, 1 ;    --#### Oldest unsent transaction: Enable and set to @Threshold_OldestUnsentTransaction Minutes
    EXEC sp_dbmmonitorchangealert @dbName, 2, @Threshold_UnsentLogSize, 1 ;                --#### Unsent log: Enable and set to @Threshold_UnsentLogSize KB
    EXEC sp_dbmmonitorchangealert @dbName, 3, @Threshold_UnrestoredLogSize, 1 ;            --#### Unrestored log: Enable and set to @Threshold_UnrestoredLogSize KB
    EXEC sp_dbmmonitorchangealert @dbName, 4, @Threshold_MirrorCommitOverhead, 1 ;        --#### Mirror commit overhead: Enable and set to @Threshold_MirrorCommitOverhead seconds
    EXEC sp_dbmmonitorchangealert @dbName, 5, @Threshold_RetentionPeriod, 1 ;            --#### Retention period: Enable and set to @Threshold_RetentionPeriod hours

--#### Create Threshold Based Alerts if they dont already exist

    --#### Create [DBM Perf: Unsent Log Threshold]
        SELECT @alertName = N'DBM Perf: Unsent Log Threshold (All Databases)' ;
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @alertName)
            BEGIN
                EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = N'', @message_id = 32042, @severity = 0, @delay_between_responses = @Threshold_AlertsResponseDelay, @include_event_description_in = 1, @notification_message=N'You will receive another email shortly detailing the problematic databases.', @enabled = 1, @job_id = @Mirroring_StatusEmailJob ;
                EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'Admins_MirrorAlerts', @notification_method = 1
            END
    --#### Create [DBM Perf: Oldest Unsent Transaction Threshold]
        SELECT @alertName = N'DBM Perf: Oldest Unsent Transaction Threshold (All Databases)' ;
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @alertName)
            BEGIN
                EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = N'', @message_id = 32040, @severity = 0, @delay_between_responses = @Threshold_AlertsResponseDelay, @include_event_description_in = 1, @notification_message=N'You will receive another email shortly detailing the problematic databases.', @enabled = 1, @job_id = @Mirroring_StatusEmailJob ;
                EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'Admins_MirrorAlerts', @notification_method = 1
            END
    --#### Create [DBM Perf: Unrestored Log Threshold]
        SELECT @alertName = N'DBM Perf: Unrestored Log Threshold (All Databases)' ;
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @alertName)
            BEGIN
                EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = N'', @message_id = 32043, @severity = 0, @delay_between_responses = @Threshold_AlertsResponseDelay, @include_event_description_in = 1, @notification_message=N'You will receive another email shortly detailing the problematic databases.', @enabled = 1, @job_id = @Mirroring_StatusEmailJob ;
                EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'Admins_MirrorAlerts', @notification_method = 1
            END
    --#### Create [DBM Perf: Mirror Commit Overhead Threshold]
        SELECT @alertName = N'DBM Perf: Mirror Commit Overhead Threshold (All Databases)' ;
        IF NOT EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = @alertName)
            BEGIN
                EXEC msdb.dbo.sp_add_alert @name = @alertName, @category_name = N'Database Mirroring', @database_name = N'', @message_id = 32044, @severity = 0, @delay_between_responses = @Threshold_AlertsResponseDelay, @include_event_description_in = 1, @notification_message=N'You will receive another email shortly detailing the problematic databases.', @enabled = 1, @job_id = @Mirroring_StatusEmailJob ;
                EXEC msdb.dbo.sp_add_notification @alert_name = @alertName, @operator_name = N'Admins_MirrorAlerts', @notification_method = 1
            END
            
--#### Create WMI based alerts (State Changes)
    
    --#### 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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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'Admins_MirrorAlerts', @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

--#### Install usp_dbmmonitorresults_EmailMode
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_dbmmonitorresults_EmailMode]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_dbmmonitorresults_EmailMode]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Jordon Pilling | www.heavencore.co.uk>
-- Create date: <29/03/2011>
-- Description:    Runs sys.sp_dbmmonitorresults on ALL mirrored databases
-- and emails the results AS a HTML table
-- =============================================
CREATE PROCEDURE [dbo].[usp_dbmmonitorresults_EmailMode]
AS 
 BEGIN
        --#### Update core stats (this is usally done by job "Database Mirroring Monitor Job")
        SET NOCOUNT ON;
        
 EXEC sys.sp_dbmmonitorupdate

 DECLARE @HTML_Body VARCHAR(MAX)
 DECLARE @HTML_Head VARCHAR(MAX)
 DECLARE @HTML_Tail VARCHAR(MAX)

 DECLARE @Cursor_MirroredDatabases CURSOR 
 DECLARE @command CHAR(256)
 DECLARE @MirroredDatabaseName NVARCHAR(128)    
 DECLARE @MirrorStats TABLE
 (
 database_name SYSNAME , -- Name of database 
 role TINYINT , -- 1 = Principal, 2 = Mirror 
 mirroring_state TINYINT , -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized 
 witness_status TINYINT , -- 1 = Connected, 2 = Disconnected 
 log_generation_rate INT NULL , -- in kb / sec 
 unsent_log INT , -- in kb 
 send_rate INT NULL , -- in kb / sec 
 unrestored_log INT , -- in kb 
 recovery_rate INT NULL , -- in kb / sec 
 transaction_delay INT NULL , -- in ms 
 transactions_per_sec INT NULL , -- in trans / sec 
 average_delay INT , -- in ms 
 time_recorded DATETIME ,
 time_behind DATETIME ,
 local_time DATETIME -- Added for UI 
 ) 
    
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
    --#### Run the monitor (and update the main table)
 SET @command = N'sys.sp_dbmmonitorresults ''' + REPLACE(@MirroredDatabaseName, N'''', N'''''') + N''',0,0' 
 INSERT INTO @MirrorStats
 EXEC ( @command ) 
 FETCH NEXT FROM @Cursor_MirroredDatabases 
     INTO @MirroredDatabaseName
 END 

 CLOSE @Cursor_MirroredDatabases 
 DEALLOCATE @Cursor_MirroredDatabases 

 SET @HTML_Head = '<html>'
 SET @HTML_Head = @HTML_Head + '<head>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <style>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' body{font-family: arial; font-size: 13px;}table{font-family: arial; font-size: 13px; border-collapse: collapse;width:100%} td {padding: 2px;height:15px;border:solid 1px black;} th {padding: 2px;background-color:black;color:white;border:solid 1px black;}' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' </style>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '</head>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<body><b>Below is a list of Mirrored Databases and their current mirror state.</b><hr />' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<b>Roles: </b>1 = Principal, 2 = Mirror<br />' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<b>Mirror State: </b>0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized<br />' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<b>Witness State: </b>0 = n/a, 1 = Connected, 2 = Disconnected<br /><br />' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + '<table>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <tr>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Database</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Role</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Mirror State</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Witness Status</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Log Generation Rate (KB/sec)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Unsent Log (KB)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Send Rate (KB/sec)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Unrestored Log (KB)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Recovery Rate (KB/sec)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Transaction Delay (ms)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Transactions per sec</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Avg Delay (ms)</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Time Recorded</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' <th>Time Behind</th>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Head = @HTML_Head + ' </tr>' + CHAR(13) + CHAR(10) ;
 SET @HTML_Tail = '</table></body></html>' ; 

 SET @HTML_Body = @HTML_Head + ( SELECT database_name AS [TD] ,
 role AS [TD] ,
 mirroring_state AS [TD] ,
 witness_status AS [TD] ,
 log_generation_rate AS [TD] ,
 unsent_log AS [TD] ,
 send_rate AS [TD] ,
 unrestored_log AS [TD] ,
 recovery_rate AS [TD] ,
 transaction_delay AS [TD] ,
 transactions_per_sec AS [TD] ,
 average_delay AS [TD] ,
 time_recorded AS [TD] ,
 time_behind AS [TD]
 FROM @MirrorStats
 ORDER BY database_name
 FOR
 XML RAW('tr') ,
 ELEMENTS
 ) + @HTML_Tail

    --#### Send the finished Email
 EXEC msdb.dbo.sp_send_dbmail 
        @recipients = 'XXXXX@YYYY.COM', 
        @subject = 'DMB: Current Mirror Status (All Databases)', 
        @body = @HTML_Body, 
        @body_format = 'HTML' ;
 END


GO

--#### Install a SQL job that will run usp_dbmmonitorresults_EmailMode
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'DBM: Email Mirror Status')
EXEC msdb.dbo.sp_delete_job @job_name=N'DBM: Email Mirror Status', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'DBM: Email Mirror Status', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'DBM: Email Mirror Status', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'EXEC usp_dbmmonitorresults_EmailMode', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'EXEC usp_dbmmonitorresults_EmailMode', 
        @database_name=N'msdb', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

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

Read 278 times
(2 in last 30 days)

Rate

4.82 (11)

You rated this post out of 5. Change rating

Share

Share

Rate

4.82 (11)

You rated this post out of 5. Change rating