SQL Server Availability Group

Automatic Failover of Availability Group to the Preferred Node

Objective:

This procedure created for the above subject (heading). If the procedure is executed against master database on the instance using any scheduler like SQL Server Agent jobs and others, then this procedure execution will automatically failover the AG (Availability Group) to the preferred node which is set through configuration table as explained in the procedure

CREATE PROCEDURE P_FailOver_Config_Execute (@TIME INT)
as
Begin
SET NOCOUNT ON

/*

This procedure created to automatically failover of AG to the preferred node. We can schedule this to failover based on our configured time.

Owner : Siddique

E.g. Script 

EXECUTE P_FailOver_Config_Execute 16

*/


/*
This is the temporary table to store the AG and the server information along with the status. The status 1 is for Preferred Node
This temporary object configuration needs to be created as a permanent object
*/
IF OBJECT_ID('tempdb..#AG') IS NOT NULL
    DROP TABLE #AG


Create Table #AG 
(
ID smallint identity(1,1),
Name sysname, 
replica_server_name nvarchar(256),
--role_desc nvarchar(60),
[status] bit,
LastProcessed datetime default getdate(),
LastFailover datetime NULL
)

/*

--This script is to take the AG and the server information for the configuration table
insert into #AG (name,replica_server_name,status)
SELECT C.name, CS.replica_server_name,0 as Status
    FROM sys.availability_groups_cluster AS C
        INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
            ON CS.group_id = C.group_id
        INNER JOIN sys.dm_hadr_availability_replica_states AS RS
            ON RS.replica_id = CS.replica_id;

SELECT NAME AS AVAILABILITY_GROUPNAME FROM sys.availability_groups  -- Script to find out the AG group name

*/

insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-MEDICST','COL-SH-SQL-008\MEDICST','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-MEDICST','COL-SH-SQL-010\MEDICST','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-MEDICST','COL-SH-SQL-009\MEDICST','1'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAG-ENGGP','COL-SH-SQL-008\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAG-ENGGP','COL-SH-SQL-009\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAG-ENGGP','COL-SH-SQL-010\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-AGS-ENGGP','COL-SH-SQL-008\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-AGS-ENGGP','COL-SH-SQL-009\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-AGS-ENGGP','COL-SH-SQL-010\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-ENGGP','COL-SH-SQL-008\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-ENGGP','COL-SH-SQL-009\ENGGP','0'
insert into #AG (NAME,replica_server_name,status)
select 'SQL-NAGS-ENGGP','COL-SH-SQL-010\ENGGP','0'

insert into #AG (NAME,replica_server_name,status)
select 'SQL-AG-ENGGT','COL-SH-SQL-008\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-NAG-ENGGT','COL-SH-SQL-008\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-NAG-ENGGT','COL-SH-SQL-009\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-NAG-ENGGT','COL-SH-SQL-010\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-AGS-ENGGT','COL-SH-SQL-008\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-AGS-ENGGT','COL-SH-SQL-009\ENGGT','0'
insert into #AG (NAME,replica_server_name,status) 
select 'SQL-AGS-ENGGT','COL-SH-SQL-010\ENGGT','0'

--For e.g. we can keep COL-SH-SQL-009 servers as a preferred Node

UPDATE #AG SET status=1 WHERE replica_server_name like 'COL-SH-SQL-009%'


--First Check

DECLARE @MAXOID int
SELECT @MAXOID = MAX(ID) FROM #AG

--WHILE loop
WHILE @MAXOID > 0
BEGIN

--SELECT @MAXOID  -- To find out
	DECLARE @STATUS BIT
	DECLARE @CONFIGURED_Replica_server_name nvarchar(256) 
	Declare @CONFIGURED_AG_Name sysname
	
	SELECT @STATUS = STATUS,@CONFIGURED_Replica_server_name = replica_server_name, 
	@CONFIGURED_AG_Name = name FROM #AG WHERE ID = @MAXOID 

IF @STATUS = 0
	BEGIN
		PRINT 'NO ACTION ON THIS'
		UPDATE #AG SET LastProcessed =GETDATE() WHERE ID =@MAXOID
	END
ELSE
	BEGIN

	DECLARE @SERVERNAME nvarchar(256)

	SELECT @SERVERNAME = @@SERVERNAME
	--SELECT @SERVERNAME

	DECLARE @PREFERRED_REPLICA nvarchar(256)
	DECLARE @ag_name sysname 
	DECLARE @AG_NAME_CONFIGURATION SYSNAME
	
	SELECT @PREFERRED_REPLICA =X.replica_server_name,@ag_name= x.name
	FROM 
	(
	SELECT C.name, CS.replica_server_name, CS.join_state_desc, RS.role_desc, RS.operational_state_desc, RS.connected_state_desc, 
	RS.synchronization_health_desc
    FROM sys.availability_groups_cluster AS C
        INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
            ON CS.group_id = C.group_id
        INNER JOIN sys.dm_hadr_availability_replica_states AS RS
            ON RS.replica_id = CS.replica_id
	)X

	INNER JOIN (SELECT * FROM #AG  WHERE ID =@MAXOID)  as AG on AG.Name =x.name and AG.replica_server_name =X.replica_server_name and AG.status =1 
	AND X.role_desc ='SECONDARY' -- This condition is to connect only to the Secondary server
	AND X.operational_state_desc ='ONLINE' -- This condition only if the Secondary Server status is ONLINE. If the script is executed in Primary, then this field will be NULL
	AND X.connected_state_desc ='CONNECTED'
	AND synchronization_health_desc = 'HEALTHY'

	SELECT @PREFERRED_REPLICA AS PREPLICA,@ag_name AS AGNAME,@CONFIGURED_Replica_server_name AS CONFREPLICA,@SERVERNAME AS SERVERNAME,@CONFIGURED_AG_Name AS CONFAGNAME

	IF @CONFIGURED_Replica_server_name  = @SERVERNAME and @CONFIGURED_AG_Name = @ag_name  AND @STATUS = 1 AND  LEFT(cast(GETDATE() as time),2) = @TIME -- we set the timing here
			BEGIN
			
			PRINT 'AG Start to Change'
			DECLARE @sSQL nvarchar(500);
			set @sSQL = 'ALTER AVAILABILITY GROUP [' + @CONFIGURED_AG_Name  +'] FAILOVER;'  
			print @sSQL -- This is only print. If needs to be executed you have to use below
			--EXEC (@sSQL) or EXEC sp_executesql @sSQL
			UPDATE #AG SET LastProcessed =GETDATE(),LastFailover = GETDATE() WHERE ID =@MAXOID -- This is to update the time when failover happened.
			
			END
	ELSE

	BEGIN

	PRINT 'NO ACTION ON THIS'
	UPDATE #AG SET LastProcessed =GETDATE() WHERE ID = @MAXOID
	END 

END

-- Decrement @MAXOID
	SET @MAXOID = @MAXOID - 1
END

SET NOCOUNT OFF
END

 

Leave a Reply

Your email address will not be published. Required fields are marked *