Change availability mode of a replica within an Always On availability group

Applies to: SQL Server

This topic describes how to change the availability mode of an availability replica in an Always On availability group in SQL Server by using SQL Server Management Studio, Transact-SQL, or PowerShell. The availability mode is a replica property that controls the whether the replica commits asynchronously or synchronously. Asynchronous-commit mode maximizes performance at the expense of high availability and supports only forced manual failover (with possible data loss), typically called forced failover. Synchronous-commit mode emphasizes high availability over performance and, once the secondary replica is synchronized, supports manual failover and, optionally, automatic failover.

Prerequisites

You must be connected to the server instance that hosts the primary replica.

Permissions

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Using SQL Server Management Studio

To change the availability mode of an availability group

1. In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

2. Expand the Always On High Availability node and the Availability Groups node.

3. Click the availability group whose replica you want to change.

4. Right-click the replica, and click Properties.

5. In the Availability Replica Properties dialog box, use the Availability mode drop list to change the availability mode of this replica.

Using Transact-SQL

To change the availability mode of an availability group

1. Connect to the server instance that hosts the primary replica.

2. Use the ALTER AVAILABILITY GROUP statement, as the following example:

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

   Where group_name is the name of the availability group and server_name is the name of the server instance that hosts the replica to be modified.

   Note

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

   The following example, entered on the primary replica of the AccountsAG availability group, changes the availability and failover modes to synchronous commit and automatic failover, respectively, for the replica hosted by the INSTANCE09 server instance.

   ALTER AVAILABILITY GROUP AccountsAG MODIFY REPLICA ON 'INSTANCE09'  
      WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);  
   ALTER AVAILABILITY GROUP AccountsAG MODIFY REPLICA ON 'INSTANCE09'  
      WITH (FAILOVER_MODE = AUTOMATIC);  
   

Using PowerShell

To change the availability mode of an availability group

1. Change directory (cd) to the server instance that hosts the primary replica.

2. Use the Set-SqlAvailabilityReplica cmdlet with the AvailabilityMode parameter and, optionally, the FailoverMode parameter.

   For example, the following command modifies the replica MyReplica in the availability group MyAg to use synchronous-commit availability mode and to support automatic failover.

   Set-SqlAvailabilityReplica -AvailabilityMode "SynchronousCommit" -FailoverMode "Automatic" `   
   -Path SQLSERVER:\Sql\PrimaryServer\InstanceName\AvailabilityGroups\MyAg\AvailabilityReplicas\MyReplica  
   

   Note

   To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider

• SQL Server PowerShell Provider

See Also

Overview of Always On Availability Groups (SQL Server)
Availability Modes (Always On Availability Groups)
Failover and Failover Modes (Always On Availability Groups)