I’ve developed a stored procedure to run on SQL Server Agent Job, to monitor the synchronism status from AlwaysOn Availability Groups.
In case of “NO HEALTHY” status, the SP will send you an email providing details of the database.
– DatabaseMail Instaled
So, to set up the alert properly, I execute the stored procedure above in a job task (every 5 or 10 minutes) on the primary instance, passing ‘Primary’ as parameter.
EXEC [pr_monitor_alwayson] 'PRIMARY'
On the “expected” secondary replica, I pass ‘SECONDARY’ as parameter using the same stored procedure
EXEC [pr_monitor_alwayson] 'SECONDARY'
Once you need to force a failover, you should just change the parameters in both instances.
In the next post, I’m going to provide scripts of alerts that I usually configure to complement with more details about each event that occur in AlwaysOn Availability Groups.
Please, let me know if you have any questions.