Share via

Use the Add Database to Availability Group Wizard (SQL Server Management Studio)

  • Article

Use the Add Database to Availability Group Wizard to help you add one or more databases to an existing AlwaysOn availability group.

Note

For information about using Transact-SQL or PowerShell to add a database, see Add a Database to an Availability Group (SQL Server).

In This Topic:

  • Before you begin:

    Prerequisites and Restrictions

    Security

  • To add a database, using: Add Database to Availability Group Wizard (SQL Server Management Studio)

Before You Begin

If you have never added a database to an availability group, see the "Availability Databases" section in Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server).

Prerequisites, Restrictions, and Recommendations

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

  • If a database is encrypted or even contains a Database Encryption Key (DEK), you cannot use the New Availability Group Wizard or Add Database to Availability Group Wizard to add the database to an availability group. Even if an encrypted database has been decrypted, its log backups might contain encrypted data. In this case, full initial data synchronization could fail on the database. This is because the restore log operation might require the certificate that was used by the database encryption keys (DEKs), and that certificate might be unavailable.

    To make a decrypted database eligible to add to an availability group using the wizard:

    1. Create a log backup of the primary database.

    2. Create a full database backup of the primary database.

    3. Restore the database backup on the server instance that hosts the secondary replica.

    4. Create a new log backup from primary database.

    5. Restore this log backup on the secondary database.

  • Prerequisites for using full initial data synchronization

    • All the database-file paths must be identical on every server instance that hosts a replica for the availability group.

    • No primary database name can exist on any server instance that hosts a secondary replica. This means that none of the new secondary databases can exist yet.

    • You will need to specify a network share in order for the wizard to create and access backups. For the primary replica, the account used to start the Database Engine must have read and write file-system permissions on a network share. For secondary replicas, the account must have read permission on the network share.

    If you are unable to use the wizard to perform full initial data synchronization, you need to prepare your secondary databases manually. You can do this before or after running the wizard. 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.

Arrow icon used with Back to Top link[Top]

Using the Add Database to Availability Group Wizard (SQL Server Management Studio)

To Use the Add Database to Availability Group Wizard

  1. In Object Explorer, connect to the server instance that hosts the primary replica of the availability group, and expand the server tree.

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

  3. Right-click the availability group to which you are adding a database, and select the Add Database command. This command launches the Add Database to Availability Group Wizard.

  4. On the Select Databases page, select one or more databases. For more information, see Select Databases Page (New Availability Group Wizard/Add Database Wizard).

  5. On the Select Initial Data Synchronization page, choose how you want your new secondary databases to be created and joined to the availability group. Choose one of the following options:

    • Full

      Select this option if your environment meets the requirements for automatically starting initial data synchronization (for more information, see Prerequisites, Restrictions, and Recommendations , earlier in this topic).

      If you select Full, after creating the availability group, the wizard will attempt to back up every primary database and its transaction log to a network share and restore the backups on every server instance that hosts an secondary replica. The wizard will then join every secondary database to the availability group.

      In the Specify a shared network location accessible by all replicas: field, specify a backup share to which all of the server instance that host replicas have read-write access. The log backups will be part of your log backup chain. Store the log backup files appropriately.

      Security noteSecurity Note

      For information about the required file-system permissions, see Prerequisites, earlier in this topic.

    • Join only

      If you have manually prepared secondary databases on the server instances that will host the secondary replicas, you can select this option. The wizard will join the existing secondary databases to the availability group.

    • Skip initial data synchronization

      Select this option if you want to use your own database and log backups of your primary databases. For more information, see Start Data Movement on an AlwaysOn Secondary Database (SQL Server).

    For more information, see Select Initial Data Synchronization Page (AlwaysOn Availability Group Wizards).

  6. On the Connect to Existing Secondary Replicas page, if the instances of SQL Server that host the availability replicas for this availability group are all running as a service in the same user account, click Connect all. If any of the server instances are running as a service under different accounts, click the individual Connect button to the right of each server instance name.

    For more information, see Connect to Existing Secondary Replicas Page (Add Replica Wizard/Add Databases Wizard).

  7. The Validation page verifies whether the values you specified in this Wizard meet the requirements of the New Availability Group Wizard. To make a change, you can click Previous to return to an earlier wizard page to change one or more values. The click Next to return to the Validation page, and click Re-run Validation.

    For more information, see Validation Page (AlwaysOn Availability Group Wizards).

  8. On the Summary page, review your choices for the new availability group. To make a change, click Previous to return to the relevant page. After making the change, click Next to return to the Summary page.

    For more information, see Summary Page (AlwaysOn Availability Group Wizards).

    If you are satisfied with your selections, optionally click Script to create a script of the steps the wizard will execute. Then, to create and configure the new availability group, click Finish.

  9. The Progress page displays the progress of the steps for creating the availability group (configuring endpoints, creating the availability group, and joining the secondary replica to the group).

    For more information, see Progress Page (AlwaysOn Availability Group Wizards).

  10. When these steps complete, the Results page displays the result of each step. If all these steps succeed, the new availability group is completely configured. If any of the steps result in an error, you might need to manually complete the configuration. For information about the cause of a given error, click the associated "Error" link in the Result column.

    When the wizard completes, click Close to exit.

    For more information, see Results Page (AlwaysOn Availability Group Wizards).

  11. If initial data synchronization was not automatically started on all of you secondary database, you need to configure any not-yet-joined secondary databases. For more information, see Start Data Movement on an AlwaysOn Secondary Database (SQL Server).

Arrow icon used with Back to Top link[Top]

Arrow icon used with Back to Top link[Top]

See Also

Concepts

Overview of AlwaysOn Availability Groups (SQL Server)

Prerequisites, Restrictions, and Recommendations for AlwaysOn Availability Groups (SQL Server)

Add a Database to an Availability Group (SQL Server)

Start Data Movement on an AlwaysOn Secondary Database (SQL Server)

Add a Database to an Availability Group (SQL Server)