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