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

Artykuł
12/08/2014

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

Security
To change the availability mode of an availability replica, using:

SQL Server Management Studio

Transact-SQL

PowerShell

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.

Ikona strzałki używana z łączem Powrót na górę strony [Top]

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.

Ikona strzałki używana z łączem Powrót na górę strony [Top]

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.

[!UWAGA]

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

Ikona strzałki używana z łączem Powrót na górę strony [Top]

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
```
[!UWAGA]

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