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

This topic explains how to join a secondary database to an AlwaysOn availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server 2014. After you prepare a secondary database for a secondary replica, you need to join the database to the availability group as soon as possible. This will start data movement from the corresponding primary database to the secondary database.

• Before you begin:

  Prerequisites

  Security

• To prepare a secondary database, using:

  SQL Server Management Studio

  Transact-SQL

  PowerShell

Note

For information about what happens after a secondary database joins the group, see Overview of AlwaysOn Availability Groups (SQL Server).

Before You Begin

Prerequisites

• You must be connected to the server instance that hosts the secondary replica.

• The secondary replica must already be joined to the availability group. For more information, see Join a Secondary Replica to an Availability Group (SQL Server).

• The secondary database must have been prepared recently. For more information, see Manually Prepare a Secondary Database for 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 a secondary database to an availability group

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

2. Expand the AlwaysOn High Availability node and the Availability Groups node.

3. Expand the availability group that you want to change, and expand the Availability Databases node.

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

5. This opens the Join Databases to Availability Group dialog box. Verify the availability group name, which is displayed on the title bar, and database name or names displayed in the grid, and click OK, or click Cancel.

Using Transact-SQL

To join a secondary database to an availability group

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

2. Use the SET HADR clause of the ALTER DATABASE statement, as follows:

   ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name

   where database_name is the name of a database to be joined and group_name is the name of the availability group.

   The following example joins the secondary database, Db1, to the local secondary replica of the MyAG availability group.

   ALTER DATABASE Db1 SET HADR AVAILABILITY GROUP = MyAG;  
   

   Note

   To see this Transact-SQL statement used in context, see Create an Availability Group (Transact-SQL).

Using PowerShell

To join a secondary database to an availability group

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

2. Use the Add-SqlAvailabilityDatabase cmdlet to join one or more secondary databases to the availability group.

   For example, the following command joins a secondary database, Db1, to the availability group MyAG on one of the server instances that hosts a secondary replica.

   Add-SqlAvailabilityDatabase -Path SQLSERVER:\SQL\SecondaryServer\InstanceName\AvailabilityGroups\MyAG -Database "Db1"  
   

   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

• SQL Server PowerShell Provider

Related Tasks

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

• Manually Prepare a Secondary Database for an Availability Group (SQL Server)

See Also

ALTER AVAILABILITY GROUP (Transact-SQL)
Overview of AlwaysOn Availability Groups (SQL Server)
Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)deleted