Alerting on Database Mirroring

At the company where I work for the moment as SQL Server DBA consultant, they had configured database mirroring alerts per possible event.
This worked perfectly because there are not a lot of databases that are mirrored. Until a few weeks ago where I had to mirror 23 databases of Sharepoint 2010 😉
23 databases with 9 alerts for each database, too much… and can be done better!

To fix this issue, I did the following

I created one alert for the complete server.  The alert is a WMI event alert that captures all database mirroring state changes.

To create the alert check the script below

USE [msdb]
GO
IF  EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Database Mirroring Change State')
EXEC msdb.dbo.sp_delete_alert @name=N'Database Mirroring Change State'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Database Mirroring Change State',
@message_id=0,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'[Uncategorized]',
@wmi_namespace=N'.rootMicrosoftSqlServerServerEventsMSSQLSERVER',
@wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE',
@job_name=N'_Server: Database Mirroring Alert'
GO

As you may notice, when the alert is received a job “_Server: Database Mirroring Alert” is executed.  You can also simply configure Operators that will be notified when a mirroring alert occurs.

The problem is that you only receive an alert, for example by E-mail, that “some” database mirroring has changed status.  But you don’t know which one and to which state… That’s not mentioned in the alert.

This is the reason why I created an extra job that will be executed when a mirror change occurs.When the job is executed, SQL Server Agent replaces the tokens with the values of the WMI event properties

The job is going to collect all the WMI DATABASE_MIRRORING_STATE_CHANGE events, put them in a nice readable format and send them to a specific E-mail address.

To create the job, execute the script below

USE [msdb]
GO
IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'_Server: Database Mirroring Alert')
EXEC msdb.dbo.sp_delete_job @job_name=N'_Server: Database Mirroring Alert', @delete_unused_schedule=1
GO
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'_Server: Database Mirroring Alert',
 @enabled=1,
 @notify_level_eventlog=0,
 @notify_level_email=0,
 @notify_level_netsend=0,
 @notify_level_page=0,
 @delete_level=0,
 @description=N'Send an E-mail when a database mirroring alert occurs',
 @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'Send Alert',
 @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'declare @Msg varchar(8000)
declare @sub varchar(128)
declare @State smallint
declare @Statename varchar(128)
declare @StartTime varchar(30)
declare @Description varchar(512)
set @sub=''SQL Database Mirroring Alert from '' + @@SERVERNAME
set @State=''$(ESCAPE_NONE(WMI(State)))''
set @StartTime=SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',0,5) + ''-'' + SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',5,2)
+ ''-'' + SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',7,2)
+ '' '' + SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',9,2)
+ '':'' + SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',11,2)
+ '':'' + SUBSTRING(''$(ESCAPE_NONE(WMI(StartTime)))'',13,2)
select @Statename=case @State
 WHEN  0 THEN ''Null Notification''
 WHEN  1 THEN ''Synchronized Principal with Witness''
 WHEN  2 THEN ''Synchronized Principal without Witness''
 WHEN  3 THEN ''Synchronized Mirror with Witness''
 WHEN  4 THEN ''Synchronized Mirror without Witness''
 WHEN  5 THEN ''Connection with Principal Lost''
 WHEN  6 THEN ''Connection with Mirror Lost''
 WHEN  7 THEN ''Manual Failover''
 WHEN  8 THEN ''Automatic Failover''
 WHEN  9 THEN ''Mirroring Suspended''
 WHEN 10 THEN ''No Quorum''
 WHEN 11 THEN ''Synchronizing Mirror''
 WHEN 12 THEN ''Principal Running Exposed''
 WHEN 13 THEN ''Synchronizing Principal'' END
select @Description=case @State
 WHEN  0 THEN ''This state occurs briefly when a mirroring session is started.''
 WHEN  1 THEN ''This state occurs on the principal when the principal and mirror are connected and synchronized and the principal and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.''
 WHEN  2 THEN ''This state occurs on the principal when the principal and mirror are connected and synchronized but the principal does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.''
 WHEN  3 THEN ''This state occurs on the mirror when the principal and mirror are connected and synchronized and the mirror and witness are connected. For a mirroring configuration with a witness, this is the normal operating state.''
 WHEN  4 THEN ''This state occurs on the mirror when the principal and mirror are connected and synchronized but the mirror does not have a connection to the witness. For a mirroring configuration without a witness, this is the normal operating state.''
 WHEN  5 THEN ''This state occurs on the mirror server instance when it cannot connect to the principal.''
 WHEN  6 THEN ''This state occurs on the principal server instance when it cannot connect to the mirror.''
 WHEN  7 THEN ''This state occurs on the principal server instance when the user fails over manually from the principal, or on the mirror server instance when a force service is executed at the mirror.''
 WHEN  8 THEN ''This state occurs on the mirror server instance when the operating mode is high safety with automatic failover (synchronous) and the mirror and witness server instances cannot connect to the principal server instance.''
 WHEN  9 THEN ''This state occurs on both partner instances when the user suspends (pauses) the mirroring session or when the mirror server instance encounters an error. It also occurs on the mirror server instance following a force service command. When the mirror comes online as the principal, mirroring is automatically suspended.''
 WHEN 10 THEN ''If a witness is configured, this state occurs on the principal or mirror server instance when it cannot connect to its partner or to the witness server instance.''
 WHEN 11 THEN ''This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing.''
 WHEN 12 THEN ''This state occurs on the principal server instance when the operating mode is high protection (synchronous) and the principal cannot connect to the mirror server instance.''
 WHEN 13 THEN ''This state occurs on the principal server instance when there is a backlog of unsent log. The status of the session is Synchronizing.'' END
SELECT @Msg = ''Database Mirroring State of $(ESCAPE_NONE(WMI(DatabaseName))) database changed to ''
+ @Statename +  '' on '' + @StartTime
+ CHAR(13) + CHAR(13)
+ ''Description:''
+ CHAR(13)
+ @Description
+ CHAR(13) + CHAR(13)
+ ''$(ESCAPE_NONE(WMI(TextData)))''
EXEC msdb.dbo.sp_send_dbmail
 @profile_name = ''SQLServerDefault'',
 @recipients = <a href="mailto:''pieter.vanhove@kohera.be''">''pieter.vanhove@kohera.be''</a>,
 @body = @Msg,
 @subject = @sub
',
 @database_name=N'master',
 @flags=0
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR &lt;&gt; 0 OR @ReturnCode &lt;&gt; 0) GOTO QuitWithRollback
GOTO EndSave
QuitWithRollback:
 IF (@@TRANCOUNT &gt; 0) ROLLBACK TRANSACTION
EndSave:
GO

The alerts and the descriptions can be found here http://technet.microsoft.com/en-us/library/cc966392.aspx

Note that you must enable the use of tokens in SQL Server Agent jobs. To do so, do the following:
  1. Right-click the SQL Server Agent folder in Object Explorer and click Properties on the shortcut menu.
  2. In the SQL Server Agent Properties dialog box, in Select a page, click Alert System
  3. At the bottom of the page, select the Replace tokens for all job responses to alerts check box.
  4. Restart the SQL Server Agent service
Or execute the following script and restart your SQL Server Agent.
USE [msdb]
GO
EXEC <a href="mailto:msdb.dbo.sp_set_sqlagent_properties@alert_replace_runtime_tokens=1">msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1</a>
GO

 

Happy monitoring!!!

2 comments on “Alerting on Database Mirroring
  1. hello,
    I just stopped by to say thank you, thank you!.
    I know this is almost a three year old post, but I wanted to drop you a line to tell you how much this post help me.
    I had a client with a Citrix database that was being mirrored, and while I knew how to set up alerts and notify the operator, I knew there got to be a better way… and then Google found you!
    Thank Sir.

    Miguel Q.

Leave a Reply

Your email address will not be published. Required fields are marked *