Any suggestions? Export (0) Print
Expand All

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


Applies To: SQL Server 2016 Preview

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


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

Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.   

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.

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:


    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.  


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

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"

    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

Community Additions

© 2016 Microsoft