View Thread
 Print Thread
SQL Mirroring - Create SQL Agent Alerts for Mirroring Events
admin
As posted here: http://www.sqlservercentral.com/scrip...rts/71731/

Basically sets thresholds, alerts and notifications for your mirrored databases.

This assumes you have SQL Server Agent setup with a valid Database Mail profile, and that a valid email enabled operator has been setup on your server.

See inline SQL comments for more information and usage instructions.



--## 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 - Changed @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         = 4096;   --#### In KB
   SET @Threshold_UnrestoredLogSize      = 4096;   --#### 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

Jordon Pilling | Heavencore Administrator
 
Jump to Forum