Add a Database to an Availability Group (SQL Server)

  • Makale

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

Before You Begin

Prerequisites and Restrictions

Security

Permissions

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

Using SQL Server Management Studio

To add a database to an availability group

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

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

  3. 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 AlwaysOn Secondary Database (SQL Server).

Using Transact-SQL

To add a database to an availability group

  1. Connect to the server instance that hosts the server instance that hosts the primary replica.

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

  3. 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 AlwaysOn Secondary Database (SQL Server).

Using PowerShell

To add a database to an availability group

  1. Change directory (cd) to the server instance that hosts the primary replica.

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

  3. 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 AlwaysOn Secondary Database (SQL Server).

To set up and use the SQL Server PowerShell provider, see SQL Server PowerShell Provider.

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"

See Also

Overview of AlwaysOn Availability Groups (SQL Server)
Creation and Configuration of Availability Groups (SQL Server)
Use the AlwaysOn Dashboard (SQL Server Management Studio)
Monitor Availability Groups (Transact-SQL)