Add a Database to an Always On availability group

Article
03/03/2023

Applies to: SQL Server

This topic describes how to add a database to an Always On availability group by using SQL Server Management Studio, Transact-SQL, or PowerShell in SQL Server.

Prerequisites and Restrictions

You must be connected to the server instance that hosts the primary replica.
The database must reside on the server instance that hosts the primary replica and comply with the prerequisites and restrictions for availability databases. For more information, see Prerequisites, Restrictions, and Recommendations for Always On Availability Groups (SQL Server).

Permissions

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

Use SQL Server Management Studio

In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.
Expand the Always On High Availability node and the Availability Groups node.
Right-click the availability group, and select one of the following commands:
- To launch the Add Database to Availability Group Wizard, select the Add Database command. For more information, see Use the Add Database to Availability Group Wizard (SQL Server Management Studio).
- To add one or more databases by specifying them in the Availability Group Properties dialog box, select the Properties command. The steps for adding a database are as follows:
  1. In the Availability Databases pane, click the Add button. This creates and selects a blank database field.
  2. Enter the name of a database that meets the availability-databases prerequisites.
  To add another database, repeat the preceding steps. When you are done specifying databases, click OK to complete the operation.
  
  After you use the Availability Group Properties dialog box to add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica. For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

Use Transact-SQL

Connect to the server instance that hosts the server instance that hosts the primary replica.
Use the ALTER AVAILABILITY GROUP statement, as follows:

ALTER AVAILABILITY GROUP group_name ADD DATABASE database_name [,...n]

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

The following example adds the MyDb3 database to the MyAG availability group.
```
-- Connect to the server instance that hosts the primary replica.  
-- Add an existing database to the availability group.  
ALTER AVAILABILITY GROUP MyAG ADD DATABASE MyDb3;  
GO  
```
After you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica. For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

Use PowerShell

Change directory (cd) to the server instance that hosts the primary replica.
Use the Add-SqlAvailabilityDatabase cmdlet.

For example, the following command adds the secondary database MyDd to the MyAG availability group, whose primary replica is hosted by PrimaryServer\InstanceName.
```
Add-SqlAvailabilityDatabase `   
-Path SQLSERVER:\SQL\PrimaryServer\InstanceName\AvailabilityGroups\MyAG `   
-Database "MyDb"  
```
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.
After you add a database to an availability group, you need to configure the corresponding secondary database on each server instance that hosts a secondary replica. For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

To set up and use the SQL Server PowerShell provider

SQL Server PowerShell Provider

For a complete example, see Example (PowerShell), below.

Example (PowerShell)

The following example shows the full process for preparing a secondary database from a database on the server instance that hosts the primary replica of an availability group, adding the database to an availability group (as a primary database), and then joining the secondary database to the availability group. First, the example backs up the database and its transaction log. Then the example restores the database and log backups to the server instances that host a secondary replica.

The example calls Add-SqlAvailabilityDatabase twice: first on the primary replica to add the database to the availability group, and then on the secondary replica to join the secondary database on that replica to the availability group. If you have more than one secondary replica, restore and join the secondary database on each of them.

$DatabaseBackupFile = "\\share\backups\MyDatabase.bak"  
$LogBackupFile = "\\share\backups\MyDatabase.trn"  
$MyAgPrimaryPath = "SQLSERVER:\SQL\PrimaryServer\InstanceName\AvailabilityGroups\MyAg"  
$MyAgSecondaryPath = "SQLSERVER:\SQL\SecondaryServer\InstanceName\AvailabilityGroups\MyAg"  
  
Backup-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance "PrimaryServer\InstanceName"  
Backup-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance "PrimaryServer\InstanceName" -BackupAction 'Log'  
  
Restore-SqlDatabase -Database "MyDatabase" -BackupFile $DatabaseBackupFile -ServerInstance "SecondaryServer\InstanceName" -NoRecovery  
Restore-SqlDatabase -Database "MyDatabase" -BackupFile $LogBackupFile -ServerInstance "SecondaryServer\InstanceName" -RestoreAction 'Log' -NoRecovery  
  
Add-SqlAvailabilityDatabase -Path $MyAgPrimaryPath -Database "MyDatabase"  
Add-SqlAvailabilityDatabase -Path $MyAgSecondaryPath -Database "MyDatabase"