Create Alerts for SQL Server Mirroring Failovers

You might have SQL Mirroring enabled in your Enterprise but might not have alerting enabled or you don’t know how to enable it.  If you want to enable that functionality just follow this 2 easy steps.

1. Creating SQL Alerts in SQL Server Agent

Go to your SQL Server Agent and on the Alert section, right-click on it and choose New Alert.

Now you are presented with the alert properties and just fill it up with the proper information
Name :  Choose a name for this Alert
Type :  There are 3 different types and since were interested in Database Mirroring State Change we need to query WMI so choose WMI event alert
Namespace : The namespace it auto populated to the namespace you needed so don’t change it
Query : Now write your WMI Query.  We are looking for the DATABASE_MIRRORING_STATE_CHANGE class so your query goes like this.

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE

Now there 19 Properties for that class and you are mainly interested on DatabaseName or DatabaseID and State.  DatabaseName or DatabaseID will be the database you are monitoring and State would be Mirroring State of the Database and here are the different values.

• 0 = Null Notification
• 1 = Synchronized Principal with Witness
• 2 = Synchronized Principal without Witness
• 3 = Synchronized Mirror with Witness
• 4 = Synchronized Mirror without Witness
• 5 = Connection with Principal Lost
• 6 = Connection with Mirror Lost
• 7 = Manual Failover
• 8 = Automatic Failover
• 9 = Mirroring Suspended
• 10 = No Quorum
• 11 = Synchronizing Mirror
• 12 = Principal Running Exposed

Having that in mind you can now filter out alerts by those two properties, so for example I want to have an alert for “SAMPLE” Database where the mirroring Failed Over my query would look like this

SELECT * FROM DATABASE_MIRRORING_STATE_CHANGE WHERE DatabaseName = 'SAMPLE' AND State = 8

Now you have an alert you need now to define notifications for operators, you can do that by going to the Response tab and choose who do you want to notify and what notifications they want to receive.

Now you define additional options such as message you want to send to those operator

At this stage you have a working alert.  But it might not work as by default the SQL Server Agent Alert System Mail Profile is disabled

2. Activating SQL Server Agent Alert System Mail Profile.

Go to SQL Server Agents properties

Then go to Alert System Tab then tick Enable mail profile and choose the Mail system installed on your server as well as its profile

Now your done, all you have to do is test, You will get a message similar to this once it’s activated.