How to Change Always On availability group mode

This topic describes how to change the availability mode of an availability replica in an Always On availability group in SQL Server by using Transact-SQL.


The available options are as below.


Asynchronous commit: It supports manual failover with possible data loss.

Synchronous commit: It supports manual\automatic failover.

Note: Execute the script on the Primary replica.

Change availability mode from Synchronous to Asynchronous

ALTER AVAILABILITY GROUP [<availability_group_name>] MODIFY REPLICA ON '*server_name*'  
WITH ( AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT)
ALTER AVAILABILITY GROUP [<availability_group_name>] MODIFY REPLICA ON '*server_name*'  
WITH ( FAILOVER_MODE=MANUAL );

Change availability mode from Synchronous to Asynchronous

ALTER AVAILABILITY GROUP [<availability_group_name>] MODIFY REPLICA ON '*server_name*
  WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT); 
ALTER AVAILABILITY GROUP [<availability_group_name>] MODIFY REPLICA ON '*server_name*
  WITH (FAILOVER_MODE = AUTOMATIC);

Note: FAILOVER_MODE = AUTOMATIC is supported only if you specify AVAILABILITY_MODE = SYNCHRONOUS_COMMIT.


106 views0 comments