Skip to content

Latest commit

 

History

History
94 lines (65 loc) · 5.73 KB

change-the-availability-mode-of-an-availability-replica-sql-server.md

File metadata and controls

94 lines (65 loc) · 5.73 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic helpviewer_keywords
Change availability mode of a replica for an availability group
A description for how to change the availability mode of an availability replica within an Always On availability group using either Transact-SQL (T-SQL), PowerShell, or SQL Server Management Studio.
MashaMSFT
mathoma
05/17/2016
sql
availability-groups
how-to
Availability Groups [SQL Server], deploying
Availability Groups [SQL Server], configuring
Availability Groups [SQL Server], availability modes

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

[!INCLUDE SQL Server] This topic describes how to change the availability mode of an availability replica in an Always On availability group in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], 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 [!INCLUDEssNoVersion] PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the 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)