Change the Availability Mode of an Availability Replica (SQL Server)

Article
06/13/2017

This topic describes how to change the availability mode of an availability replica in an AlwaysOn availability group in SQL Server 2014 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.

Before You Begin

Prerequisites

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

Security

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

In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
Expand the AlwaysOn High Availability node and the Availability Groups node.
Click the availability group whose replica you want to change.
Right-click the replica, and click Properties.
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

Connect to the server instance that hosts the primary replica.
Use the ALTER AVAILABILITY GROUP statement, as follows:

ALTER AVAILABILITY GROUP group_name MODIFY REPLICA ON 'server_name'

WITH ( {

AVAILABILITY_MODE = { SYNCHRONOUS_COMMIT | ASYNCHRONOUS_COMMIT }

| FAILOVER_MODE = { AUTOMATIC | 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

Change directory (cd) to the server instance that hosts the primary replica.
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, see SQL Server PowerShell Provider.

Share via