Create Alerts for Availability Groups

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.

agp10

 

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.

aga6

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.

 

aga1

 

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.

aga2

On the Options tab, pick email.  Remember you need to have database mail already setup.

aga7

And that’s it.  Below are the other two Alerts I always setup with Availability Groups.

aga3

 

aga4

 

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.

aga5

After you have these setup, you can now be confident you will know quickly when your Availability Groups are experiencing any problems

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s