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:
- When the replica changes role or a failover to secondary replica happens
- When data movement is suspended
- 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
