Skip to content

join-a-secondary-replica-to-an-availability-group-sql-server.md

Latest commit

 

History

History
131 lines (85 loc) · 7.67 KB

join-a-secondary-replica-to-an-availability-group-sql-server.md

File metadata and controls

131 lines (85 loc) · 7.67 KB
title Join a Secondary Replica to an Availability Group (SQL Server) | Microsoft Docs
ms.custom
ms.date 05/17/2016
ms.prod sql
ms.prod_service high-availability
ms.reviewer
ms.suite sql
ms.technology high-availability
ms.tgt_pltfrm
ms.topic conceptual
f1_keywords
sql13.swb.availabilitygroup.joinreplica.f1
helpviewer_keywords
Availability Groups [SQL Server], availability replicas
Availability Groups [SQL Server], joining
Availability Groups [SQL Server], configuring
ms.assetid e5bd2489-097a-490e-8ea1-34fe48378ad1
caps.latest.revision 41
author MikeRayMSFT
ms.author mikeray
manager craigg

Join a Secondary Replica to an Availability Group (SQL Server)

[!INCLUDEappliesto-ss-xxxx-xxxx-xxx-md] This topic describes how to join a secondary replica to an Always On availability group by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell in [!INCLUDEssCurrent]. After a secondary replica is added to an Always On availability group, the secondary replica must be joined to the availability group. The join-replica operation must be performed on the instance of [!INCLUDEssNoVersion] that is hosting the secondary replica.

Before You Begin

Prerequisites

  • The primary replica of the availability group must currently be online.

  • You must be connected to the server instance that hosts a secondary replica that has not yet have been joined to the availability group.

  • The local server instance must be able to connect to the database mirroring endpoint of the server instance that is hosting the primary replica.

Important

If any prerequisite is not met, the join operation fails. After a failed join attempt, you might need to connect to the server instance that hosts the primary replica to remove and re-add the secondary replica before you can join it to the availability group. For more information, see Remove a Secondary Replica from an Availability Group (SQL Server) and Add a Secondary Replica to an Availability Group (SQL Server).

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 join an availability replica to an availability group

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

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

  3. Select the availability group of the secondary replica to which you are connected.

  4. Right-click the secondary replica, and click Join to Availability Group.

  5. This opens the Join Replica to Availability Group dialog box.

  6. To join the secondary replica to the availability group, click OK.

Using Transact-SQL

To join an availability replica to an availability group

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

  2. Use the ALTER AVAILABILITY GROUP statement, as follows:

    ALTER AVAILABILITY GROUP group_name JOIN

    where group_name is the name of the availability group.

    The following example, joins the secondary replica to the MyAG availability group.

    ALTER AVAILABILITY GROUP MyAG JOIN;

    [!NOTE]
    To see this [!INCLUDEtsql] statement used in context, see Create an Availability Group (Transact-SQL).

Using PowerShell

To join an availability replica to an availability group

In the [!INCLUDEssNoVersion] PowerShell provider:

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

  2. Join the secondary replica to the availability group by executing the Join-SqlAvailabilityGroup cmdlet with the name of the availability group.

    For example, the following command joins a secondary replica hosted by the server instance located at the specified path to the availability group named MyAg. This server instance must host a secondary replica in this availability group.

    Join-SqlAvailabilityGroup -Path SQLSERVER:\SQL\SecondaryServer\InstanceName -Name 'MyAg'

    [!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

Follow Up: Configure Secondary Databases

For every database in the availability group, you need a secondary database on the server instance that is hosting the secondary replica. You can configure secondary databases either before or after you join a secondary replica to an availability group, as follows:

  1. Restore recent database and log backups of each primary database onto the server instance that hosts the secondary replica, using RESTORE WITH NORECOVERY for every restore operation. For more information, see Manually Prepare a Secondary Database for an Availability Group (SQL Server).

  2. Join each secondary database to the availability group. For more information, see Join a Secondary Database to an Availability Group (SQL Server).

See Also

Creation and Configuration of Availability Groups (SQL Server)
Overview of Always On Availability Groups (SQL Server)
Troubleshoot Always On Availability Groups Configuration (SQL Server)