將資料庫新增至 Always On 可用性群組Add a Database to an Always On availability group

適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions) 適用範圍:Applies to: 是SQL ServerSQL Server (所有支援的版本) yesSQL ServerSQL Server (all supported versions)

本主題描述如何使用 SQL Server Management StudioSQL Server Management StudioTransact-SQLTransact-SQLSQL ServerSQL Server中的 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 ServerSQL Server.

必要條件和限制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.

    例如,下列命令會將次要資料庫 MyDd 加入 MyAG 可用性群組中,而其主要複本是由 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)