مشاركة عبر

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

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

Arrow icon used with Back to Top link[Top]

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

Arrow icon used with Back to Top link[Top]

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

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

See Also

Reference

ALTER AVAILABILITY GROUP (Transact-SQL)

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Troubleshoot AlwaysOn Availability Groups Configuration (SQL Server)