将数据库添加到 AlwaysOn 可用性组Add a Database to an Always On availability group

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题介绍如何通过在 SQL Server Management StudioSQL Server Management Studio中使用 Transact-SQLTransact-SQLSQL Server 2017SQL Server 2017或 PowerShell 将数据库添加到 AlwaysOn 可用性组中。This topic describes how to add a database to an Always On availability group by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell in SQL Server 2017SQL Server 2017.

先决条件和限制Prerequisites and Restrictions

权限Permissions

对可用性组要求 ALTER AVAILABILITY GROUP 权限、CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

使用 SQL Server Management StudioUse SQL Server Management Studio

  1. 在对象资源管理器中,连接到承载主副本的服务器实例,然后展开服务器树。In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

  2. 依次展开“Always On 高可用性” 节点和“可用性组” 节点。Expand the Always On 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. 有关详细信息,请参阅使用“将数据库添加到可用性组向导”(SQL Server Management Studio)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. 有关详细信息,请参阅本主题后面的 启动 AlwaysOn 辅助数据库的数据移动 (SQL Server)For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

使用 Transact-SQLUse Transact-SQL

  1. 连接到承载主副本的服务器实例。Connect to the server instance that hosts the server instance that hosts the primary replica.

  2. 按如下所示使用 ALTER AVAILABILITY GROUP 语句:Use the ALTER AVAILABILITY GROUP statement, as follows:

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

    其中 group_name 是可用性组的名称, database_name 是要添加到该组的数据库的名称。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.

    以下示例添加 MyDb3 数据库到 MyAG 可用性组。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. 有关详细信息,请参阅本主题后面的 启动 AlwaysOn 辅助数据库的数据移动 (SQL Server)For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

使用 PowerShellUse PowerShell

  1. 将目录 (cd) 更改为托管主副本的服务器实例。Change directory (cd) to the server instance that hosts the primary replica.

  2. 使用 Add-SqlAvailabilityDatabase cmdlet。Use the Add-SqlAvailabilityDatabase cmdlet.

    例如,以下命令将添加辅助数据库 MyDdMyAG 可用性组中,其主副本由 PrimaryServer\InstanceName承载。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"  
    

    备注

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL ServerSQL Server cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor 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. 有关详细信息,请参阅 启动 AlwaysOn 辅助数据库的数据移动 (SQL Server)For more information, see Start Data Movement on an Always On Secondary Database (SQL Server).

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

有关完整示例,请参阅下面的 示例 (PowerShell)For a complete example, see Example (PowerShell), below.

示例 (PowerShell)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.

此示例调用两次 Add-SqlAvailabilityDatabase :第一次是针对主要副本调用,以便将数据库添加到可用性组;第二次是针对次要副本调用,以便将该副本上的辅助数据库加入到可用性组。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

AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
创建和配置可用性组 (SQL Server) Creation and Configuration of Availability Groups (SQL Server)
使用 AlwaysOn 仪表板 (SQL Server Management Studio) Use the Always On Dashboard (SQL Server Management Studio)
监视可用性组 (Transact-SQL)Monitor Availability Groups (Transact-SQL)