Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Configure SQL Server Alerts and Notifications for AlwaysOn Availability Groups


By:   |   Read Comments (2)   |   Related Tips: More > Availability Groups

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

The next step after you have deployed and successfully configured an AlwaysOn Availability Group is to maintain it. As a DBA you would want to know immediately if something critical happens. But what is the most important alert that you would like be notified first and foremost? You would probably want to be alerted if a failover happens to your AlwaysOn production setup right?

Solution

There are a lot of monitoring alerts that you can setup for AlwaysOn Availability Groups, but the most critical alerts that you need to setup are for these 3 scenarios below:

  1. When the replica changes role or a failover to secondary replica happens
  2. When data movement is suspended
  3. When data movement resumed

So lets proceed to create the alerts for each of the scenarios above. By now creating alerts should be a breeze for you. But if this the first time you are creating one, don't fret. You have an option of doing it via the SQL Server Agent GUI or by scripting.

The easiest way to do this is via scripting, but you may choose to do this using SQL Server Management Studio (SSMS). In SSMS, open Object Explorer, go to SQL Server Agent > Alerts > New Alerts. This should open up a new alert dialog window for you.

But in this tip, I am going to show how to do this via scripting.

Create SQL Server Alert for AlwaysOn Role Change

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Role Change',
@message_id=1480,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]', 
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

The message_id is the error id for the role change or failover event, for this event the value is 1480.

For the job_id value, you have an option to create a SQL Server Agent Job that will run when this alert is fired.  The job_id would be the job_id for the SQL Server Agent Job.  You can run sp_help_job to get a list of SQL Agent jobs and the job_id value.

Create SQL Server Alert for AlwaysOn Data Movement Suspended

To setup an alert for the data movement suspended, you can run this code, but note we are looking for message_id 35264.

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Suspended',
@message_id=35264,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0, 
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Create SQL Server Alert for AlwaysOn Data Movement Resumed

To setup an alert for the data movement resumed, you can run this code, but note we are looking for message_id 35265.

EXEC msdb.dbo.sp_add_alert
@name=N'AlwaysOn - Data Movement Resumed',
@message_id=35265,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=0,
@category_name=N'[Uncategorized]',
@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Setup SQL Server Alert Notifications

After you have created the alerts, the next thing to do is to add notifications to your alerts, so you get alerted if the scenario described above happens. You may again do this via the SSMS or alternatively update and execute the script below based on the values in your environment. This would need to be done for each alert that you create. Also, you will need to setup Database Mail and Operators.

EXEC msdb.dbo.sp_add_notification
@alert_name = N'AlwaysOn - Role Change',
@operator_name = N'myemail@email.com',
@notification_method = <Notification,INT,1>;
GO

When doing this via the SSMS, open your newly created Alerts, go to Response on the left menu and check the Notify Operators checkbox. On the name of your Operator, check the correct email checkbox.

Summary

As I have mentioned, it is best to script and deploy these alerts for your other servers where AlwaysOn is setup and configured, I advise you to create and save a script for the alert creation and adding of notifications. To easily create the scripts for existing alerts, from within SSMS just right click on the Alert and select the Script option.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Carla Abanes Carla Abanes works for a private bank in Singapore as a SQL Server DBA.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Monday, February 09, 2015 - 10:25:24 PM - Carla Abanes Back To Top

Thanks for sharing this too, Danilo :)


Friday, January 23, 2015 - 3:03:16 PM - Danilo Braga Back To Top

Carla Abanes, 

Thank you for sharing your experience.

I've been reading all of your posts and everyone are very good, mainly because they are focused on AlwaysOn Availability Group.

I think you can also include more two alerts to know if occur timeout between primary and secondary replicas. It's useful if your environment has multiple subnets in different datacenters where you secondary replica is configured for asynchronous mode.

 

-- 35206 - AG Timeout to Secondary Replica

EXEC msdb .dbo . sp_add_alert

        @name = N'AG Timeout to Secondary Replica',

        @message_id = 35206,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1;

GO

-- 35202 - AG Timeout to Secondary Replica

EXEC msdb .dbo . sp_add_alert

        @name = N'AG Connection has been successfully established',

        @message_id = 35202,

    @severity = 0,

    @enabled = 1,

    @delay_between_responses = 0,

    @include_event_description_in = 1;

 

 

Danilo Cunha Braga

https://twitter.com/danilocbraga

 

https://br.linkedin.com/pub/danilo-cunha-braga/21/324/805


Learn more about SQL Server tools