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:
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,
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:
- In SSMS, connect to the principal or mirror server instance for which you want to define an alert.
- Expand the SQL Server Agent folder in Object Explorer.
- Right-click Alerts and select New Alert.
- In the New Alert dialog box, type a name you can use to identify the alert, such as "DB Mirroring: Unsent log warning."
- Select Error number and type the number from Table below.
- 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.
- 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
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.
(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 masalah when you
choose "All Databases".
want to define the alerts per database, then you will have a masalah 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.
cannot be backed up until they have been applied on the mirror.
For more info:
http://technet.microsoft.com/en-us/library/cc966392.aspx