Delen via

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

  • Artikel

This topic describes how to join a secondary replica to an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2012. After a secondary replica is added to an AlwaysOn availability group, the secondary replica must be joined to the availability group. The join-replica operation must be performed on the instance of SQL Server that is hosting the secondary replica.

  • Before you begin:  

    Prerequisites

    Security

  • To prepare a secondary database, using:  

    SQL Server Management Studio

    Transact-SQL

    PowerShell

  • Follow Up:  Configure Secondary Databases

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.

Arrow icon used with Back to Top link[Top]

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 AlwaysOn 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 Transact-SQL statement used in context, see Create an Availability Group (Transact-SQL).

Arrow icon used with Back to Top link[Top]

Using PowerShell

To join an availability replica to an availability group

In the SQL Server 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 SQL Server PowerShell environment. For more information, see Get Help SQL Server PowerShell.

To set up and use the SQL Server PowerShell provider

Arrow icon used with Back to Top link[Top]

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

Concepts

Creation and Configuration of Availability Groups (SQL Server)

Overview of AlwaysOn Availability Groups (SQL Server)

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)