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

08 Jan 2018 – V1.1 released –  Details here

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

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

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

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

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

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

IF OBJECT_ID('dbo.sp_AGreconfigure') IS NOT NULL
DROP PROCEDURE sp_AGreconfigure;
GO
/**********************************************
Procedure Name: sp_AGreconfigure
Author: Adrian Buckman
Revision date: 07/01/2018
Version: 1.1

URL: https://sqlundercover.com/2017/12/08/undercover-toolbox-sp_agreconfigure-manage-always-on-sync-failover-settings-from-a-single-stored-procedure/

Description: Produce statements to ALTER Synchronisation mode ,Auto Failover Mode and Readable Secondary modes

06 Jan 2018 - Added @Readable parameter to allow for Readable Secondary scripts to be produced
	NULL - Default: Ignore Readable secondary check (acts as though you are using V1)
	0 - Produce a statement to switch readable secondary off if switched on
	1 - Produce a statement to switch readable on if set to off or Read intent
	2 - Produce a statement to switch read intent on if set to off or Readable

© www.sqlundercover.com 

MIT License
------------

Copyright 2018 Sql Undercover

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files
(the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge,
publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE
FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 

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

CREATE PROCEDURE [dbo].[sp_AGreconfigure]
(
@ServerName NVARCHAR(128) = NULL,
@AGName NVARCHAR(128) = NULL,
@SyncCommit BIT = 0,
@AutoFailover BIT = 0,
@Readable TINYINT = NULL,
@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
	IF @Readable > 2
	BEGIN
		RAISERROR('Incompatible option set, @Readable must be one of the following values "NULL 0, 1, 2"',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 AND failover_mode_desc = 'AUTOMATIC'
					 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);
'
				 WHEN @AutoFailover = 0 AND [Failover_Mode_Desc] = 'AUTOMATIC'
				 THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (FAILOVER_MODE = MANUAL);
'				 ELSE ''
				 END
		ELSE ''
		END +
			CASE
			WHEN replica_server_Name IN (ISNULL(@ServerName,replica_server_Name))
			AND replica_server_name != primary_replica
			AND AGname IN (ISNULL(@AGName,AGname)) AND @Readable IN (NULL,0,1,2)
			THEN
				CASE
				--Readable Secondary statements
				WHEN AGname IN (ISNULL(@AGName,AGname)) AND @Readable = 0 AND [ReadableSecondary] IN ('READ_ONLY','ALL')
				THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
'
				WHEN AGname IN (ISNULL(@AGName,AGname)) AND @Readable = 1 AND [ReadableSecondary] != 'ALL'
				THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

'				WHEN AGname IN (ISNULL(@AGName,AGname)) AND @Readable = 2 AND [ReadableSecondary] != 'READ_ONLY'
				THEN 'ALTER AVAILABILITY GROUP ['+AGname+'] MODIFY REPLICA ON N'''+replica_server_Name+''' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

'				WHEN @Readable IS NULL
				THEN ''
				ELSE ''
				END
			ELSE ''
		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

 

Thanks for reading 🙂