Now that you have your SQL Server Availability Groups up and running, how do you know if there is a problem? You can always look at the AG Dashboard, or even better you can set up database alerts to send you an email or page you in case of certain problems.
Remember from our previous posting, you have the Availability Group dashboard available to see the status. This is great, but what if you are not in the office or cannot carry a computer around all day? It would be nice to be sent an email if there is a problem.
In this posting: Setting up SQL Server Database Mail I showed you how to configure database mail. This needs to be setup before SQL Server can send you emails.
How do you know what you want to create an alert for? You can query the database in the sys.messages table and see what is available. Below I did a query for any message with the word availability in it. You of course can create alerts for any number of things, I also have an alert that tells me if there are any unsuccessful logins.
Below I will show you how to create the three alerts I use.
In my environment, I want to know when the AG is changing roles, or if data movement is stopped or resumed.
Go into SSMS and right click alert Alerts and choose New Alert.
That brings up this screen. Name it what you want, but it is usually a good idea to call it what it is actually alerting on 🙂
Click the radio button Error Number, and put in the message_id from the query we ran above.
On the Options tab, pick email. Remember you need to have database mail already setup.
And that’s it. Below are the other two Alerts I always setup with Availability Groups.
Now if you have an issue with your Availability Groups you will be sent an email, and will know of any problems quickly.
Below is an email I received when the server was being rebooted during Windows Updates. Even though the description says it “is an informational message only”, I always check the servers just in case and make sure everything is healthy.
After you have these setup, you can now be confident you will know quickly when your Availability Groups are experiencing any problems
Jim Salasek's SQL Server Blog 