Skip to content

Latest commit

 

History

History
102 lines (68 loc) · 7.08 KB

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

File metadata and controls

102 lines (68 loc) · 7.08 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords
Join a secondary replica to an availability group
Steps to join a secondary replica to 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
sql13.swb.availabilitygroup.joinreplica.f1
Availability Groups [SQL Server], availability replicas
Availability Groups [SQL Server], joining
Availability Groups [SQL Server], configuring

Join a secondary replica to an Always On availability group

[!INCLUDE SQL Server] This topic describes how to join a secondary replica to an Always On availability group by using [!INCLUDEssManStudioFull], [!INCLUDEtsql], or PowerShell in [!INCLUDEssnoversion]. 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.

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

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)