Follow these simple steps along with the script to set up and receive a simple and single email whenever a failover happens in your AlwaysOn Availability Group.
AlwaysOn Availability Group in SQL Server
AlwaysOn Availability Group was released with SQL Server 2012 and is the successor of database mirroring. You can set it up in your SQL Server environment for high availability or disaster recovery. For High Availability the group has to be set in synchronous mode and for disaster recovery the group has to be set in asynchronous mode.
Problem with Built-in Alert System
As a DBA, once you set the Always On Availability Group in your SQL Server environment you would be interested in setting alerts so that you are aware when something disrupts your Always On setup. It might be for the suspension of data movement or when the replica changes role. Though you can use SQL Server’s inbuilt SQL agent and alert to send you notifications, you will end up receiving multiple emails that might fill your inbox. Imagine that you have been on a holiday and when you are back your mailbox would be flooded with emails. In such a scenario there is a very high chance that you might miss the alert email.
So if you don’t want your inbox to be filled with alert mails and at the same time don’t want to miss a failover that happened in your Always On group, then follow these simple steps and scripts. This will send you just one email indicating which server hosts the Primary group and which is hosting the secondary group.
SQL Server Script
This script is less than 15 lines. It will send a simple yet effective email as shown below
Image : Snapshot showing a sample email that will be triggered by this Alert.
From a Central Server before deploying this script via a SQL Server job, you should first create a linked server to the Primary server. Now you can schedule the SQL Server job with this script to run at specific time. To get an instant alert, you can create a SQL Server alert for Error ID 1480 and as a response to that Alert you can point this SQL Server Job.
In this script, we are passing the Availability group name as parameter to the function fn_hadr_group_is_primary using openquery and linkedserver. Based on the result, a mail is triggered
Script :
declare @f as int
declare @m as varchar(500)
set @m = 'Availability Group Check : ' + CONVERT(varchar(23), getdate(), 121) + char(10)
-- <PRIMARY SERVER NAME> , <SECONDARY SERVER NAME> , <ALWAYS ON AVAILABILITY GROUP NAME> are place holders and you should update the script with Proper values before deploying it
set @f = (select * from openquery([<PRIMARY SERVER NAME>],'Select dbo.fn_hadr_group_is_primary(''<ALWAYS ON AVAILABILITY GROUP NAME>'')'))
if @f = 0
set @m = @m + char(10) + 'WRONG : The Availability Group <ALWAYS ON AVAILABILITY GROUP NAME> is on <SECONDARY SERVER NAME>. Failover it to <PRIMARY SERVER NAME>' + char(10)
else
set @m = @m + char(10) + 'CORRECT : The Availability Group <ALWAYS ON AVAILABILITY GROUP NAME> is on <PRIMARY SERVER NAME>.' + Char(10)
print @m
EXEC msdb.dbo.sp_send_dbmail @profile_name='<SQL DBMAIL PROFILE NAME>',
@recipients=',RECIPIENT EMAIL ADDRESS. SEPARATE MULTIPLE EMAIL ADDRESS BY ;',
@subject='Availability Group Check',
@body=@m
Recover Data on Disaster
When data disaster occurs, an automatic failover and disaster recovery may not enough for all the times. Sometimes, when you get the alert email from the script, you need to perform a manual SQL data recovery and recover your important data from the failed SQL Server instance.
Author Introduction:
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook and excel recovery software products. For more information visit www.datanumen.com
1