XML FeedsThis morning I came into work and went through my usual 100 or so emails. One of the emails was from MSSQLTips.com, it was on how to monitor SQL Server Database mirroring with email alerts. By Alan Cranfield. While agree with Alan that every DBA should monitor their database mirroring with email alerts I disagreed with his method. He had the DBA create a job that was scheduled to run at some interval throughout the day. His job would query the sys.database_mirroring view. As DBAs we need to know immediately when something fails or changes. 5 minutes could be the difference between a quick fix and restoring a 500 GB db mirror.
So what would be a better way to monitor and alert a DBA when there is a change in the state of Database mirroring? I prefer to use Alerts for events. Event notifications can be created directly in the SQL Server Database Engine or by using the WMI Provider for Server Events. A DBA can specify which db mirroring event they wish to moitor. Here is a table of events to monitor for:
| State | Name | Description |
|---|---|---|
| 0 | Null Notification | This state occurs briefly when a mirroring session is started. |
| 1 | Synchronized Principal with Witness | 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. |
| 2 | Synchronized Principal without Witness | 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. |
| 3 | Synchronized Mirror with Witness | 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. |
| 4 | Synchronized Mirror without Witness | 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. |
| 5 | Connection with Principal Lost | This state occurs on the mirror server instance when it cannot connect to the principal. |
| 6 | Connection with Mirror Lost | This state occurs on the principal server instance when it cannot connect to the mirror. |
| 7 | Manual Failover | 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. |
| 8 | Automatic Failover | 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. |
| 9 | Mirroring Suspended | 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. |
| 10 | No Quorum | 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. |
| 11 | Synchronizing Mirror | This state occurs on the mirror server instance when there is a backlog of unsent log. The status of the session is Synchronizing. |
| 12 | Principal Running Exposed | 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. |
| 13 | Synchronizing Principal | This state occurs on the principal server instance when there is a backlog of unsent log. The status of the |
Now that we know the Event and the State here is how to add an Alert to notify you that the state of DB mirroring has changed.
- USE [msdb]
- GO
- /****** Object: Alert [DBM State Change] Script Date: 10/15/2009 08:03:20 ******/
- EXEC msdb.dbo.sp_add_alert @name=N'DBM State Change',
- @message_id=0,
- @severity=0,
- @enabled=1,
- @delay_between_responses=0,
- @include_event_description_in=1,
- @category_name=N'[Uncategorized]',
- @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
This is an alert that I created on the principal server. I also have created a similar alert on the mirror server where I look for state = 5. These two alerts will notify me if the connection between the Principal and Mirror is lost due to network or some other failure.
To receive notification when this event happens it is simple to just create an operator and have the event email the operator if and when the event conditions are met.
What other Mirror Events should every DBA monitor? I find the unsent and unrestored log to be two very import events to receive notifications for. For those events just simply create a new event for the event ID in the table below and set you monitor threshold.
| Database Mirroring Monitor warning | Event name | Event ID |
|---|---|---|
| Warn if the unsent log exceeds the threshold | Unsent log | 32042 |
| Warn if the unrestored log exceeds the threshold | Unrestored log | 32043 |
| Warn if the age of the oldest unsent transaction exceeds the threshold | Oldest unsent transaction | 32044 |
| Warn if the mirror commit overhead exceeds the threshold | Mirror commit overhead | 32045 |
You can also script this by using sp_add_alert as follows:
- USE [msdb]
- GO
- /****** Object: Alert [DB Mirroring Unsent Log Warning] Script Date: 10/15/2009 08:14:29 ******/
- EXEC msdb.dbo.sp_add_alert @name=N'DB Mirroring Unsent Log Warning',
- @message_id=32042,
- @severity=0,
- @enabled=0,
- @delay_between_responses=0,
- @include_event_description_in=1,
- @category_name=N'[Uncategorized]',
- @job_id=N'00000000-0000-0000-0000-000000000000'
- GO
Good Luck and Happy monitoring!
Excellent tip! 
I know I'm doing things inline with the Sr DBAs now :P
That's the first, they are always the most difficult one. 
back in the day (on sql 2000) I also used net send because every now and then email would be delayed or could be stuck and then you would get it 3 hours later :-)
You did a great job on this post. I implemented your solution to configuring alerts and I have a question. How do you get useful data in your email? 
In order to get more useful data you would have to query the WMI Event data. You can also insert the data in to a history table in the msdb database if you want to keep a records of the events. If you check out this link from msdn it goes into great detail and even provides scripts to set it all up.
I appreciate the mention of my mssqltip. Following on I have now decided to implement more granular alerting by setting performance thresholds. I have been hitting what appears to be bugs with SQL in that the warning threshold event will hit the error log but the alert will not fire OR the alert will fire (if you omit the database name - !bug!) but an email will not he sent to the operator.
pay attention: according to Microsoft docs third and forth Event IDs are incorrect!
What I did is used the alerts to fire up the job that is created by Alan Cranfield.. better that i do not have to run the job and can get appropriate emails as well 
Nik D, Can you please share the job script? 
Hi,
Great Info
Thanks for an insightful post! 
The alerts are firing properly but I have not received any mails yet. Hope you've a solution for this.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.