4 ways to efficiently monitor DB Mirroring with/without email/SMS Alerts

A setup of DB Mirroring (See my post here) cannot be full without
a setup of DB Mirroring monitoring.
I want to share with you 4 ways to monitor the DB Mirroring:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts.
2) Using SQL Server Events Notifications, State-change (WMI) events.
3) Setting up Performance Threshold Events in the "Database Mirroring Monitor".
4) Setting up SQL Performance counters related to DB Mirroring.

Now more in details:

1) Querying the "sys.database_mirroring" catalog view and sending Email Alerts

We can query the sys.database_mirroring catalog view for checking the status
of the mirrored databases, and in case the DB status is not in valid state, like
SYNCHRONIZED or SYNCHRONIZING, we will send an email and/or SMS.

DECLARE @DBMirroringState VARCHAR(30),@DB_ID INT,@ErrorMessageToSend VARCHAR(100)
DECLARE @MirroredDatabases TABLE (DatabaseID INT, mirroring_state_desc VARCHAR(30))

-- get status for mirrored databases
INSERTINTO @MirroredDatabases(DatabaseID,mirroring_state_desc)
SELECT database_id, mirroring_state_desc
FROM    [sys].[database_mirroring]
WHERE  mirroring_role_desc IN ('PRINCIPAL','MIRROR')
             AND mirroring_state_desc NOT IN ('SYNCHRONIZED','SYNCHRONIZING')

WHILE EXISTS (SELECT TOP 1 DatabaseID FROM @MirroredDatabases WHERE mirroring_state_desc IS NOT NULL)
BEGIN
     SELECT TOP 1
                   @DB_ID = DatabaseID,
                   @DBMirroringState = mirroring_state_desc
     FROM    @MirroredDatabases

    SET @ErrorMessageToSend = 'DBMirroring Error on DB:'+CAST(DB_NAME(@DB_ID) AS VARCHAR)+
                                           ',DBState='+@DBMirroringState

   -- Send Email
   EXEC msdb.dbo.sp_send_dbmail @profile_name='XXXXX',@recipients='DBA@Company.com',
                                              @body = @ErrorMessageToSend,@subject = @ErrorMessageToSend
  -- Send SMS
  -- put here code to send SMS

   DELETE FROM @MirroredDatabases WHERE DatabaseID = @DB_ID
END

Source: http://www.mssqltips.com/tip.asp?tip=1859

--------------------------------------------------------------------------------------------------------------------------------------------------------------
The second way is:
2) Using SQL Server Events Notifications, State-change (WMI) events
--------------------------------------------------------------------------------------------------------------------------------------------------------------
There is a way in the SQL Server to create an Alert for various inner events.
We will create an alert for specific DB mirroring events.

When a mirroring session changes from one state to another, SQL Server generates
a WMI event of type DATABASE_MIRRORING_STATE_CHANGE.
We will create WMI-based alert by querying the DATABASE_MIRRORING_STATE_CHANGE.

For example:
SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6

In the Principal Server we will check for following events:

   - Connection with Mirror Lost
   - Mirroring Suspended

In the Mirror Server for following alerts:

  -  Connection with Principal Lost
  -  Mirroring Suspended

You can setup such alerts via the SSMS GUI or by executing a script.
In SSMS, go to "SQL Server Agent", right click on Alerts and choose "New Alert"
and define it as follow, for example:

And do not forget to specify to send the alert email:

or you can run a script, for example:

USE [msdb]

GO
EXEC msdb.dbo.sp_update_alert
         @name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @message_id=0,
         @severity=0,
         @enabled=1,
         @delay_between_responses=0,
         @include_event_description_in=1,
         @database_name=N'',
         @notification_message=N'',
         @event_description_keyword=N'',
         @performance_condition=N'',
         @wmi_namespace=N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
         @wmi_query=N'SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE State = 6',
         @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_update_notification
         @alert_name=N'DB Mirroring State Change: Connection with Mirror Lost',
         @operator_name=N'DBA', @notification_method = 1
GO

Source: http://blogs.lessthandot.com/index.php/DataMgmt/DBAdmin/how-to-monitor-database-mirroring

3) Setting up Performance Threshold Events in the "Database Mirroring Monitor"

With these alerts, most of us should be familiar.
We can setup the thresholds in the "Database Mirroring Monitor":

As the result, SQL server will create "Database Mirroring Monitor Job" on
Principal and Mirror servers. This job will run every minute (by default) and update
undocumented database mirroring status table in the msdb database.

Now, after setting the thresholds, you need to create alert(s) using SQL Server Agent:

1. In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
2. Expand the SQL Server Agent folder in Object Explorer.
3. Right-click Alerts and select New Alert.
4. In the New Alert dialog box, type a name you can use to identify the alert, such as "DB Mirroring: Unsent log warning."
5. Select Error number and type the number from Table below.
6. In Select a page, click Response to specify the actions to take when the event occurs, such as executing a job or notifying an operator.
7. In Select a page, click Options to specify how often to send a notification on this event, the format of the message sent to operators, and other options.

 Event IDs for Performance Warnings :

The first two warnings in table above should be defined in the Principal Server,
the rest in the Mirror Server.

Now very important note that can save you time,

When you define such alerts, do not specify the database, choose "All Databases",

Otherwise the alert will not occur (not fire) and the email will not be sent.
The SQL Server will write the alert to the Windows Event log and to
SQL Server Error Log, but SQL Server will not be able to recognize it
and will not raise the alert, because it has some bug recognizing such alerts

where database name was specified.
(The event logged as a server-level event and not a database-level event)

I have this bug in SQL Server 2008 R2 ENT with CU6 and Microsoft does not
provided a workaround yet.

http://connect.microsoft.com/SQLServer/feedback/details/657230/alerting-on-database-mirroring-events

If you have more that one mirrored database in one SQL Server instance and you
want to define the alerts per database, then you will have a problem when you
choose "All Databases".

Also when the alert defined with "All Databases", an email is sent without

specifying the database that exceeded its threshold.

4) Setting up SQL Performance counters related to DB Mirroring.

We can fetch SQL performance counters related to DB Mirroring from DMV called

"sys.dm_os_performance_counters".

I recommend to fetch following counters:

- Redo Queue KB :         The number of bytes of transaction log that remains to be

                                          applied to the mirror to roll it forwards.

                                              

- Log Send Queue KB:   The number of bytes in the log that has not been sent to

                                         the mirror

SELECT   [counter_name] as CounterName,[cntr_value] as CounterValueFROM      sys.dm_os_performance_counters

WHERE   [object_name] LIKE ('%Database Mirroring%')

                 AND [counter_name] IN ('Log Send Queue KB','Redo Queue KB')

                 AND [instance_name]='TestDB'

 

For example, you can query this DMV every minute and save the values in CACTI.

In the Cacti you will see nice graphs:

This method is for long run monitoring, to recognize trends and etc.

If you have other way, please share.

One more thing that good to know and understand:

In asynchronous mirroring the transaction log can fill up as transactions
cannot be backed up until they have been applied on the mirror.

For more info:

http://msdn.microsoft.com/en-us/library/ms365781.aspx

http://msdn.microsoft.com/en-us/library/ms408393.aspx

http://technet.microsoft.com/en-us/library/cc966392.aspx