Export (0) Print
Expand All

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.

Note Note

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

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]

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]

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;
    
    NoteNote

    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]

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"
    
    NoteNote

    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]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft