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

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
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