UNDERCOVER TOOLBOX: sp_AGReconfigure – Manage Always on Sync & Failover settings from a single stored procedure.

2017-12-07 16_32_27

From time to time we need to make changes to Always on Availability group Synchronization mode settings , this also includes changes to the automatic failover partner.

Most of the time the goto is to use the GUI which is a nice graphical way of doing it which can make configuring the settings somewhat easier , but this comes with a cost – the cost being time! I have lost count of the times where I have sat waiting for the operation of changing a Synchronization mode from Asynchronous to Synchronous commit, even scripting it out from the GUI can be slow. This will not cause you a problem if you only manage one availability group , but if you are managing a few then the time spent is multiplied and in an emergency situation every second can be critical.

An alternative to using the GUI for such changes is to script the changes out, well this is fine too but adhoc queries need to be stored somewhere then they need opening on demand , I have these queries myself and they sit in a folder of lots of other Availability group related queries such as those that I posted some time ago 7 Ways to Query Always On Availability groups but what is missing is a one size fits all for when it comes to changing Sync and failover mode settings , So I decided to write sp_AGReconfigure.

Imagine managing 6+ availability groups and you get a call out at 2am, there is evidence of slowness being caused by secondary replica not committing transactions fast enough leaving you with high commit waits on the primary, you know that switching the Secondary replica to async for all 6 of your availability groups is going to remedy the issue in the short term , this is where the GUI can be a right pain – having to wait for each change to be made per AG.

When I need to get it done and I need it done fast – that’s when I goto sp_AGReconfigure, in fact I use it for planned maintenance too for switching nodes from sync to async commit across all AGs its just so much faster doing it this way.

The idea behind the stored procedure is that it will produce statement/s based on what you need to do, this may be setting a single Replica to Synchronous commit or this could be setting ever secondary replica to Asynchronous commit, the good part about it is that it will validate the selection you make e.g if you are trying to set a replica to Asynchronous Commit and it is already set that way then you will not get a statement produced.

There is also an option to simply view the current configuration which can be a handy first step , below is an explanation of the Procedure parameters and some common use scenarios.

Parameter List:
@ServerName –  Default: NULL , This will include ALL Secondary Servers where the server connected to is acting as a Primary ,  Single Servers can be specified e.g ‘SQL01’
@AGName –  Default: NULL,  This will include ALL Availability groups where the current server is acting as a Primary, for single Availability groups specify the Group name instead e.g  ‘AG1’
@SyncCommit –  Default: 0, Produce statements to set the Servername/s for the specified Availability group/s to Asynchronous commit ,  set to a 1 for Synchronous statements.*
@AutoFailover –  Default: 0,  Produce statements to set the Failover mode to Manual Failover for the specified servername/s and Availability group/s, set to a 1 to set to Automatic Failover.*
@CheckOnly –  Default: 0, When set to a 1 this will override ALL of the above parameters except for @Servername  , if @Servername is NULL then this will show information for all replicas for all availability groups – If @Servername is set to a Servername e.g ‘SQL02’ then this will show information for just this replica for all availability groups.

*When @SyncCommit = 0 and @AutoFailover = 1 and error message will be generated as this is an invalid combination.

 

Example Executions:

Check the current Syhnchronisation and Failover mode settings for ALL Availability groups where the server you are connected to is a primary or secondary replica.

EXEC sp_AGreconfigure
@CheckOnly = 1

2017-12-07 21_51_29-DC01A [Running] - Oracle VM VirtualBox

 

The following examples will all relate to the configuration in the above screenshot.

 

Produce statements to Set ALL Secondary replicas for ALL Availability groups (where the connected server is a Primary) to Async commit and Manual Failover

EXEC sp_AGreconfigure
@ServerName = NULL,
@AGName = NULL,
@SyncCommit = 0,
@AutoFailover = 0,
@CheckOnly = 0

2017-12-07 23_28_26-DC01A [Running] - Oracle VM VirtualBox

 

Statements produced:


ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

ALTER AVAILABILITY GROUP [AG3] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG3] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

 

 

Produce statements for a single Secondary replica ‘SQL02’ for ALL Availability groups (where the connected server is a Primary) to Async commit and Manual Failover

EXEC sp_AGreconfigure
@ServerName = 'SQL02',
@AGName = NULL,
@SyncCommit = 0,
@AutoFailover = 0,
@CheckOnly = 0

2017-12-07 23_33_12-DC01A [Running] - Oracle VM VirtualBox

 

Statements produced:


ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

ALTER AVAILABILITY GROUP [AG3] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG3] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

 

 

Produce statements for a single Secondary replica ‘SQL03’ for Availability group ‘AG1’ (where the connected server is a Primary) to Async commit and Manual Failover

EXEC sp_AGreconfigure
@ServerName = 'SQL03',
@AGName = 'AG1',
@SyncCommit = 0,
@AutoFailover = 0,
@CheckOnly = 0

2017-12-07 21_56_00-DC01A [Running] - Oracle VM VirtualBox

 

Statements produced:


ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03' WITH (FAILOVER_MODE = MANUAL);
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL03' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);

 

 

Inverse the above @SyncCommit and @AutoFailover to produce statements for setting Synchronous commit and AutoFailover respectively.
Setting @SyncCommit = 0 and @AutoFailover = 1 will simply return a message stating that this combination is invalid as you cannot have an Automatic failover to an Async Replica.

 

Here is a scenario where the Primary server (SQL01) is not set to Sync commit (I Switched this to Async to demonstrate) but you have set the proc to produce statements to set a SQL02 to Sync commit with Auto Failover.
You will notice that there is a statement to correct the Primary in this example because this was validated whilst the procedure ran, the procedure will validate the primary setting when switching to Sync commit and Automatic failover but will not produce statements for the primary to switch to Async and Manual Failover , this is by design.

EXEC sp_AGreconfigure
@ServerName = 'SQL02',
@AGName = 'AG1',
@SyncCommit = 1,
@AutoFailover = 1,
@CheckOnly = 0

2017-12-07 22_00_57-DC01A [Running] - Oracle VM VirtualBox

 

Statements produced:


ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL01' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL01' WITH (FAILOVER_MODE = AUTOMATIC);

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = AUTOMATIC);

 

Please be aware that running this procedure with the following parameters:

@Servername = NULL,

@AGname = NULL,

@SyncCommit = 1,

@AutoFailover = 1

Depending on how many secondary replicas you have in the availability group, the proc will produce the statements fine but you may encounter an error when you execute the statements stating that you have reached the maximum AutoFailover limit, the Procedure does a check at run time to ensure that you are not currently at the total Auto failover replica limit and raise an error but this will not take into account those which you are planning to switch to AutoFailover.

With the above in mind you may want to produce the statements but run them selectively.

 

Here is the code which can be found on our GitHub Repo

USE [master]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/**********************************************
--Procedure Name: sp_AGreconfigure
--Author: Adrian Buckman

--Description: Produce statements to ALTER Synchronisation mode and Auto Failover Mode
--www.SQLUndercover.com

*********************************************/

CREATE PROCEDURE [dbo].[sp_AGreconfigure]
(
@ServerName NVARCHAR(128) = NULL,
@AGName NVARCHAR(128) = NULL,
@SyncCommit BIT = 0,
@AutoFailover BIT = 0,
@CheckOnly BIT = 0
)
AS

BEGIN
SET NOCOUNT ON;

DECLARE @ErrorText NVARCHAR(256)

IF OBJECT_ID('TempDB.dbo.#AutoFailoverReplicaCount') IS NOT NULL
DROP TABLE #AutoFailoverReplicaCount;

CREATE TABLE #AutoFailoverReplicaCount
(
AGname NVARCHAR(128),
Total INT
);

IF @CheckOnly = 1
BEGIN
	WITH AGStatus AS(
	SELECT
	name as AGname,
	replica_server_name,
	CASE WHEN  (primary_replica  = replica_server_name) THEN  1
	ELSE  '' END AS IsPrimaryServer,
	secondary_role_allow_connections_desc AS ReadableSecondary,
	[availability_mode] AS [Synchronous],
	failover_mode_desc
	FROM master.sys.availability_groups Groups
	INNER JOIN master.sys.availability_replicas Replicas ON Groups.group_id = Replicas.group_id
	INNER JOIN master.sys.dm_hadr_availability_group_states States ON Groups.group_id = States.group_id
	)

	Select
	[AGname],
	[Replica_server_name],
	[IsPrimaryServer],
	[Synchronous],
	[ReadableSecondary],
	[Failover_mode_desc]
	FROM AGStatus
	WHERE replica_server_name = ISNULL(@ServerName,replica_server_name)
	ORDER BY
	AGname ASC,
	IsPrimaryServer DESC,
	Synchronous DESC;
END
ELSE

--Check that the current server is currently a Primary (ALTER Statements can only be made on the Primary)
IF EXISTS (
			SELECT TOP 1 primary_replica
			from sys.dm_hadr_availability_group_states States
			INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
			WHERE primary_replica = @@SERVERNAME
		  )
BEGIN

IF @SyncCommit = 0 AND @AutoFailover = 1
BEGIN
	RAISERROR('Incompatible options set, When @SyncCommit = 0 then @AutoFailover must be 0',0,0)
END
ELSE
	BEGIN
		--Count total AutoFailover replicas per AG or for @AGname if not null
		IF @AutoFailover = 1
			BEGIN
				INSERT INTO #AutoFailoverReplicaCount (AGname,Total)
				SELECT
				Groups.Name,
				COUNT(Groups.Name)
				FROM sys.dm_hadr_availability_group_states AGStates
				INNER JOIN master.sys.availability_groups Groups ON AGStates.group_id = Groups.group_id
				INNER JOIN master.sys.availability_replicas AGReplicas ON Groups.group_id = AGReplicas.group_id
				WHERE failover_mode_desc = 'AUTOMATIC'
				AND primary_replica = @@SERVERNAME
				AND Groups.Name IN (ISNULL(@AGName,Groups.Name))
				GROUP BY
				Groups.Name
			END

		IF OBJECT_ID('Tempdb..#AGReplicaInfo') IS NOT NULL
		DROP TABLE #AGReplicaInfo;

		CREATE TABLE #AGReplicaInfo
		(
		[AGName] NVARCHAR(128),
		[Primary_Replica] NVARCHAR(128),
		[Replica_Server_Name] NVARCHAR(128),
		[IsPrimary] BIT,
		[ReadableSecondary] NVARCHAR(128),
		[IsSynchronous] BIT,
		[Failover_Mode_Desc] NVARCHAR(128),
		[Availability_Mode] BIT
		);

		IF OBJECT_ID('TempDB..#Statements') IS NOT NULL
		DROP TABLE #Statements;

		CREATE TABLE #Statements
		(
		ID INT IDENTITY(1,1),
		AGName NVARCHAR(128),
		Replica_Server_Name NVARCHAR(128),
		IsPrimary BIT,
		ReadableSecondary NVARCHAR(128),
		IsSynchronous BIT,
		Failover_mode_desc NVARCHAR(128),
		AlterStatement NVARCHAR(400)
		);

		INSERT INTO #AGReplicaInfo ([AGName],[Primary_Replica],[Replica_Server_Name],[IsPrimary],[ReadableSecondary],[IsSynchronous],[Failover_Mode_Desc])
		SELECT
		[Groups].[Name],
		[primary_replica],
		[AGReplicas].[replica_server_Name],
		CASE
		WHEN [primary_replica] = [AGReplicas].[replica_server_Name] THEN 1 ELSE 0
		END AS IsPrimary,
		[secondary_role_allow_connections_desc] AS ReadableSecondary,
		[availability_mode] AS IsSynchronous,
		[Failover_Mode_Desc]
		FROM sys.dm_hadr_availability_group_states AGStates
		INNER JOIN master.sys.availability_groups Groups ON AGStates.group_id = Groups.group_id
		INNER JOIN master.sys.availability_replicas AGReplicas ON Groups.group_id = AGReplicas.group_id
		WHERE
		primary_replica = @@ServerName --Only Show AG's where this server is the Primary Server

		INSERT INTO #Statements ([AGName],[Replica_Server_Name],[IsPrimary],[ReadableSecondary],[IsSynchronous],[Failover_Mode_Desc],[AlterStatement])
		SELECT
		AGName,
		replica_server_Name,
		IsPrimary,
		ReadableSecondary,
		IsSynchronous,
		failover_mode_desc,
		NULLIF(CASE
			--Ensure that the Primary is set to Synchronous Commit if @SyncCommit = 1
			WHEN AGname IN (ISNULL(@AGName,AGname)) AND @SyncCommit = 1 AND IsSynchronous = 0
			THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+primary_replica+''' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
'
			ELSE ''
		END +
		CASE
			--Ensure that the Primary is set to Auto Failover if @AutoFailover = 1
			WHEN AGname IN (ISNULL(@AGName,AGname)) AND @AutoFailover = 1 AND [Failover_Mode_Desc] = 'MANUAL'
			THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+primary_replica+''' WITH (FAILOVER_MODE = AUTOMATIC);
'
			ELSE ''
		END,'')
		FROM #AGReplicaInfo
		WHERE primary_replica = replica_server_name;

		INSERT INTO #Statements ([AGName],[Replica_Server_Name],[IsPrimary],[ReadableSecondary],[IsSynchronous],[Failover_Mode_Desc],[AlterStatement])
		SELECT
		AGName,
		replica_server_Name,
		IsPrimary,
		ReadableSecondary,
		IsSynchronous,
		failover_mode_desc,
		--If the Secondary/s are set to Sync and you are setting to Async then produce a statement
		NULLIF(
		CASE
		WHEN replica_server_Name IN (ISNULL(@ServerName,replica_server_Name))
		AND replica_server_name != primary_replica
		AND AGname IN (ISNULL(@AGName,AGname)) AND IsSynchronous = 1 AND @SyncCommit = 0
		THEN	 CASE
					 WHEN @AutoFailover = 0
					 THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (FAILOVER_MODE = MANUAL);
'
					 ELSE ''
					 END +'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT);
'
		--If the Secondary/s are set to Async and you are setting to Sync then produce a statement
		WHEN replica_server_Name IN (ISNULL(@ServerName,replica_server_Name))
		AND replica_server_name != primary_replica
		AND AGname IN (ISNULL(@AGName,AGname)) AND @SyncCommit = 1
		THEN CASE
			 WHEN IsSynchronous = 0
			 THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
'			 ELSE ''
			 END +
				 CASE
				 WHEN @AutoFailover = 1 AND [Failover_Mode_Desc] = 'MANUAL'
				 THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (FAILOVER_MODE = AUTOMATIC);
'
				 ELSE ''
				 END		  

		END,'') AS AlterStatement
		FROM #AGReplicaInfo
		WHERE replica_server_Name IN (ISNULL(@ServerName,replica_server_Name))
		AND AGName IN (ISNULL(@AGName,AGName))
		ORDER BY
		AGName,
		replica_server_Name;

		SELECT
		AGName,
		Replica_Server_Name,
		IsPrimary,
		ReadableSecondary,
		IsSynchronous,
		Failover_mode_desc,
		AlterStatement
		FROM #Statements
		WHERE AlterStatement IS NOT NULL
		ORDER BY
		ID ASC

--If total Autofailover replicas for any AG or @AGname if it is not null is equal to 3 then RAISERROR
IF (SELECT ISNULL(MAX(Total),0) FROM #AutoFailoverReplicaCount WHERE AGname IN (ISNULL(@AGName,AGname))) = 3
		BEGIN
			IF @AGName IS NOT NULL
				BEGIN
					SELECT AGName, Total AS Total_AutoFailover_Replicas FROM #AutoFailoverReplicaCount WHERE AGName = @AGName ORDER BY AGname ASC
					SET @ErrorText = N'There are already 3 Automatic failover replicas for ['+@AGName+'] some statements may fail'
					RAISERROR(@ErrorText,11,0)
				END

			IF @AGName IS NULL
				BEGIN
					SELECT AGName, Total AS Total_AutoFailover_Replicas FROM #AutoFailoverReplicaCount ORDER BY AGname ASC
					SET @ErrorText = N'There are already 3 Automatic failover replicas for one or more Availability groups some statements may fail'
					RAISERROR(@ErrorText,11,0)
				END

		END

END

END
ELSE
	BEGIN
		RAISERROR('This server is not acting as a Primary server, Please connect to a Primary server and re-run the stored procedure',0,0)
	END
END

GO

 

Thanks for reading 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: