Instant Always on Availability Group Alert- part 1

Hi Mates,

Today we are going to see how to setup an alert for availability groups if it fails over.
Before digging in to the alert let me explain the setup what we have in our environment.

We have 2 Availability groups & as per the requirement they want each group to reside on each one of the servers.

Environment: Windows server 2012 R2/SQL Server 2012 Enterprise Edition/Multi Subnet Cluster.

2 Servers acting as HA are in one subnet & the other server is the DR sitting on other subnet.

Issue: As said we want each of the Availability groups to run on dedicated nodes however the groups are getting failed over to other nodes due to some issues at server level.

We don't have any monitoring tools to alert us if the group gets failed over to other nodes.

In first availability group we have nearly 15 databases & in the second we have 4 databases.

if you search internet you will mostly find 1480 alert as the mechanism however the problem is it would get trigger multiple times for each database & flood up your mailbox.

 so I will show you 2 different ways of doing this.

one through SQL Server agent being scheduled as job & the other through task scheduler.

First Method: Alert through SQL Server agent job

create the below function inside master/or DBA related database.

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE  FUNCTION [dbo].[fn_hadr_group_is_primary] (@AGName sysname)
RETURNS bit
AS
    BEGIN
       
        DECLARE @PrimaryReplica sysname;

        SELECT @PrimaryReplica = hags.primary_replica
        FROM
            sys.dm_hadr_availability_group_states hags
            INNER JOIN sys.availability_groups ag ON ag.group_id = hags.group_id
        WHERE
            ag.name = @AGName;

        IF UPPER(@PrimaryReplica) =  UPPER(@@SERVERNAME)
            RETURN 1; -- primary

        RETURN 0; -- not primary
       
    END;

 Once it is done then we need to create a TSQL job with the below code inside it.
 Schedule job as " start Automatically when SQL Server Agent starts"

DECLARE @rc int;
declare @m as varchar(500)
set @m = 'Availability Group Check on Production server : ' + CONVERT(varchar(23), getdate(), 121) + char(10)
EXEC @rc = master.dbo.fn_hadr_group_is_primary N'Availability group name';
if @rc = 0
set @m  = @m + char(10) + 'WRONG : The Availability Group <XXXXX> is on SERVER B.
Failover it to SERVER A' + char(10)
else
set @m  = @m + char(10) + 'CORRECT : The Availability Group <XXXX> is on Server A.' + Char(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='Default Public Profile',
@recipients='vamsy.gc@gmail.com',
@subject='Availability Group Check',
@body=@m

 The funny part is even here I will make use of 1480 but applied below logic.

create an alert 1480 & tweak the delay between responses  as 1min.

That's it we are done with our setup. The caveat here is if my SQL Agent is down then no way we would come to know about this. Hence we rely on event viewer which I will show in the next post.