SQL Server Availability Group

Automatic Failover of Availability Group to the Preferred Node – SQL Agent Job

Objective:

This post information is created to automate the Failover of Availability Group to the Preferred Node via SQL Agent Job.

There is a variable of TIME assigned to the automation SQL Server stored procedure to failover the Availability Group to the preferred Replica. When the procedure runs at the specified time, for eg. With a value of 3, this will automate at the specified time of 3 AM. The time will be from 0 to 23.

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. The job script is to run the AG failover automation.

STEP I:

This step will create the T_AG table to hold the configuration settings.

USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_AG](
	[ID] [smallint] IDENTITY(1,1) NOT NULL,
	[Name] [sysname] NOT NULL,
	[replica_server_name] [nvarchar](256) NULL,
	[status] [bit] NULL,
	[LastProcessed] [datetime] NULL DEFAULT (getdate()),
	[LastFailover] [datetime] NULL
) ON [PRIMARY]

GO

 

STEP II:

This step will populate the configuration data in to the table T_AG.

PRINT 'CONFIGURATION DATA CREATION STARTING'

/*

The below temporary table and execution is to configure the automation. It needs to be run against the current Primary Availability Group Instance

The below execution will create the data on T_AG table.
*/

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
)

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;

			SET IDENTITY_INSERT [dbo].[T_AG] ON 

			INSERT [dbo].[T_AG] ([ID], [Name], [replica_server_name], [status], [LastProcessed], [LastFailover])
			SELECT * FROM #AG

			SET IDENTITY_INSERT [dbo].[T_AG] OFF
			GO

	
			UPDATE T_AG SET STATUS =1 WHERE replica_server_name ='COM-SH-SQL-009\SHAREDP5'  -- This is to keep the preferred AG as Primary. Therefore, we are updating the status = 1 for the preferred AG
			GO
			
			SELECT * FROM MASTER.DBO.T_AG

PRINT 'CONFIGURATION DATA CREATION COMPLETED'
GO

STEP III:

This step will create the procedure and that will be responsible for failover the instance to the preferred node.

USE [master]
GO


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[P_FailOver_Config_Execute] (@TIME INT)
as
Begin
SET NOCOUNT ON

--First Check

DECLARE @MAXOID int
SELECT @MAXOID = MAX(ID) FROM T_AG

-- 5 - WHILE loop
WHILE @MAXOID > 0
BEGIN

SELECT @MAXOID
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 T_AG WHERE ID = @MAXOID 
IF @STATUS = 0
BEGIN
--RETURN
PRINT 'NO ACTION ON THIS'
UPDATE T_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--,
--@AG_NAME_CONFIGURATION = 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
--WHERE X.name = @CONFIGURED_AG_Name AND X.replica_server_name = @CONFIGURED_Replica_server_name
inner join (SELECT * FROM T_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'
AND X.operational_state_desc ='ONLINE' 
AND X.connected_state_desc ='CONNECTED'
AND synchronization_health_desc = 'HEALTHY'



Declare @NOTSYNCHRONIZED INT
select  @NOTSYNCHRONIZED = COUNT(1) FROM
(

  select DB_NAME(database_id) [Database], ST.synchronization_state_desc [SyncState],
  is_primary_replica,synchronization_health_desc FROM sys.dm_hadr_database_replica_states ST 
  INNER JOIN sys.availability_groups AG ON AG.group_id = ST.group_id 
  INNER JOIN sys.availability_replicas AR ON AR.replica_id = ST.replica_id 
  WHERE AG.name = @CONFIGURED_AG_Name  AND AR.replica_server_name = @CONFIGURED_Replica_server_name
  )X
  WHERE X.SyncState<> 'SYNCHRONIZED' AND X.synchronization_health_desc <> 'HEALTHY'

  select @NOTSYNCHRONIZED as notsynchronised  -- The value zero refers all are in synchronized and healthy state


SELECT @PREFERRED_REPLICA AS PREPLICA,@ag_name AS AGNAME,@CONFIGURED_Replica_server_name AS CONFREPLICA,@SERVERNAME AS SERVERNAME,@CONFIGURED_AG_Name AS CONFAGNAME
--if @@ROWCOUNT > 0

--BEGIN
--PRINT 'ACTION ON THIS'
		IF @CONFIGURED_Replica_server_name  = @SERVERNAME and @CONFIGURED_AG_Name = @ag_name  AND @STATUS = 1 AND  LEFT(cast(GETDATE() as time),2) = @TIME and @NOTSYNCHRONIZED = 0
			BEGIN

			PRINT 'HELLO'
			DECLARE @sSQL nvarchar(500);
			set @sSQL = 'ALTER AVAILABILITY GROUP [' + @CONFIGURED_AG_Name  +'] FAILOVER;'
			print @sSQL
			execute (@sSQL)
			UPDATE T_AG SET LastProcessed =GETDATE(),LastFailover = GETDATE() WHERE ID =@MAXOID
			
			END
ELSE

BEGIN
--RETURN
PRINT 'NO ACTION ON THIS'
UPDATE T_AG SET LastProcessed =GETDATE() WHERE ID =@MAXOID
END 

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

SET NOCOUNT OFF
END

STEP IV:

This step for the below SQL Code to create the SQL Server Agent Job for the purpose of automating the AG failover to the Preferred Node. The job T-SQL should act against msdb database.

Print 'SQL Server Agent Job Creation Started'

USE [msdb]
GO


BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'AG-AutoFailover-To-PreferredNode', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'AG-Failover-To-PreferredNode', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'Execute [dbo].[P_FailOver_Config_Execute] 3', 
		@database_name=N'master', 
		@output_file_name=N'C:\Temp\AutoFailoverToPreferredNode.txt', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily', 
		@enabled=1, 
		@freq_type=4, 
		@freq_interval=1, 
		@freq_subday_type=4, 
		@freq_subday_interval=10, 
		@freq_relative_interval=0, 
		@freq_recurrence_factor=0, 
		@active_start_date=20170922, 
		@active_end_date=99991231, 
		@active_start_time=30000, 
		@active_end_time=35959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

Print 'SQL Server Agent Job Creation Completed'

 

Leave a Reply

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