建立可用性群組 (Transact-SQL)

此主題描述如何使用 Transact-SQL 在 SQL Server 2012 執行個體上建立和設定已啟用 AlwaysOn 可用性群組 功能的可用性群組。 「可用性群組」(Availability Group) 會定義當做單一單位容錯移轉的一組使用者資料庫，以及支援容錯移轉的一組容錯移轉夥伴 (也稱為「可用性複本」(Availability Replica))。

[!附註]

如需可用性群組的簡介，請參閱＜AlwaysOn 可用性群組概觀 (SQL Server)＞。

• 開始之前： 

  必要條件

  安全性

  工作和對應 Transact-SQL 陳述式的摘要

• 使用下列項目，建立和設定可用性群組：Transact-SQL

• 範例：設定使用 Windows 驗證的可用性群組

• 相關工作

• 相關內容

[!附註]

Transact-SQL 以外的替代方案是，使用 [建立可用性群組精靈] 或 SQL Server PowerShell 指令程式。 如需詳細資訊，請參閱＜使用新增可用性群組精靈 (SQL Server Management Studio)＞、＜使用新增可用性群組對話方塊 (SQL Server Management Studio)＞或＜建立可用性群組 (SQL Server PowerShell)＞。

開始之前

我們強烈建議您先閱讀本節內容，然後再嘗試建立您的第一個可用性群組。

必要條件、限制及建議

• 建立可用性群組之前，請確認裝載可用性複本的 SQL Server 執行個體位於相同 Windows Server 容錯移轉叢集 (WSFC) 容錯移轉叢集的不同 WSFC 節點上。 此外，請確認每個伺服器執行個體都符合所有其他 AlwaysOn 可用性群組 必要條件。 如需詳細資訊，我們強烈建議您閱讀＜AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)＞。

安全性

權限

需要系統管理員 (sysadmin) 固定伺服器角色的成員資格，以及 CREATE AVAILABILITY GROUP 伺服器權限、ALTER ANY AVAILABILITY GROUP 權限或 CONTROL SERVER 權限。

[回到頁首]

工作和對應 Transact-SQL 陳述式的摘要

下表列出與建立和設定可用性群組有關的基本工作，並指出哪些 Transact-SQL 陳述式要用於這些工作。 AlwaysOn 可用性群組 工作必須依照其出現在表格中的順序來執行。

工作	Transact-SQL 陳述式	在何處執行工作*
建立資料庫鏡像端點 (每個 SQL Server 執行個體一次)	CREATE ENDPOINT endpointName … FOR DATABASE_MIRRORING	在缺少資料庫鏡像端點的每一個伺服器執行個體上執行。
建立可用性群組	CREATE AVAILABILITY GROUP	於裝載初始主要複本的伺服器執行個體上執行。
將次要複本加入可用性群組	ALTER AVAILABILITY GROUP group_name JOIN	在裝載次要複本的每一個伺服器執行個體上執行。
準備次要資料庫	BACKUP 和 RESTORE。	在裝載主要複本的伺服器執行個體上建立備份。 使用 RESTORE WITH NORECOVERY，還原裝載次要複本之每個伺服器執行個體上的備份。
將每一個次要資料庫加入可用性群組來啟動資料同步處理。	ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = group_name	在裝載次要複本的每一個伺服器執行個體上執行。

* 若要執行指定的工作，請連接到指定的伺服器執行個體。

[回到頁首]

使用 Transact-SQL 建立和設定可用性群組

[!附註]

如需包含每一個這類 Transact-SQL 陳述式之程式碼範例的範例組態程序，請參閱範例：設定使用 Windows 驗證的可用性群組。

1. 連接到裝載主要複本的伺服器執行個體。

2. 使用 CREATE AVAILABILITY GROUP Transact-SQL 陳述式建立可用性群組。

3. 將新的次要複本加入可用性群組。 如需詳細資訊，請參閱＜將次要複本聯結至可用性群組 (SQL Server)＞。

4. 如果是可用性群組中的每個資料庫，請使用 RESTORE WITH NORECOVERY 還原主要資料庫的最近備份來建立次要資料庫。 如需詳細資訊，請參閱＜建立可用性群組 (Transact-SQL)＞，從還原資料庫備份的步驟開始。

5. 將每一個新的次要資料庫加入可用性群組。 如需詳細資訊，請參閱＜將次要複本聯結至可用性群組 (SQL Server)＞。

[回到頁首]

範例：設定使用 Windows 驗證的可用性群組

這個範例會建立範例 AlwaysOn 可用性群組 組態程序，此程序會使用 Transact-SQL 來設定使用 Windows 驗證的資料庫鏡像端點，並建立及設定可用性群組以及其次要資料庫。

此範例包含下列章節：

• 使用範例組態程序的必要條件

• 範例組態程序

• 範例組態程序的完整程式碼範例

使用範例組態程序的必要條件

此範例程序有下列需求：

• 伺服器執行個體必須支援 AlwaysOn 可用性群組。 如需詳細資訊，請參閱＜AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)＞。

• MyDb1 和 MyDb2 這兩個範例資料庫必須存在於將裝載主要複本的伺服器執行個體上。 下列程式碼範例會建立及設定這兩個資料庫，並建立每一個資料庫的完整備份。 在您打算建立範例可用性群組的伺服器執行個體上，執行這些程式碼範例。 此伺服器執行個體將會裝載範例可用性群組的初始主要複本。

  1. 下列 Transact-SQL 範例會建立這些資料庫，並加以更改為使用完整復原模式：

     -- Create sample databases:
     CREATE DATABASE MyDb1;
     GO
     ALTER DATABASE MyDb1 SET RECOVERY FULL;
     GO
     
     CREATE DATABASE MyDb2;
     GO
     ALTER DATABASE MyDb2 SET RECOVERY FULL;
     GO
     

  2. 下列程式碼範例會建立 MyDb1 和 MyDb2 的完整資料庫備份。 此程式碼範例會使用虛構的備份共用 \\FILESERVER\SQLbackups。

     -- Backup sample databases:
     BACKUP DATABASE MyDb1 
     TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
         WITH FORMAT
     GO
     
     BACKUP DATABASE MyDb2 
     TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
         WITH FORMAT
     GO
     

[回到範例]

範例組態程序

在此範例組態中，將會於不同但受信任網域 (DOMAIN1 和 DOMAIN2) 底下執行其服務帳戶的兩個獨立伺服器執行個體上建立可用性複本。

下表摘要說明此範例組態中所使用的值。

初始角色	系統	主機 SQL Server 執行個體
主要	COMPUTER01	AgHostInstance
次要	COMPUTER02	預設執行個體

1. 在您打算建立可用性群組的伺服器執行個體上 (這是 COMPUTER01 上名為 AgHostInstance 的執行個體)，建立名為 dbm_endpoint 的資料庫鏡像端點。 此端點使用通訊埠 7022。 請注意，可用性群組建立所在的伺服器執行個體將會裝載主要複本。

   -- Create endpoint on server instance that hosts the primary replica:
   CREATE ENDPOINT dbm_endpoint
       STATE=STARTED 
       AS TCP (LISTENER_PORT=7022) 
       FOR DATABASE_MIRRORING (ROLE=ALL)
   GO
   

2. 在要裝載次要複本的伺服器執行個體上 (這是 COMPUTER02 上的預設伺服器執行個體)，建立 dbm_endpoint 端點。 此端點使用通訊埠 5022。

   -- Create endpoint on server instance that hosts the secondary replica: 
   CREATE ENDPOINT dbm_endpoint
       STATE=STARTED 
       AS TCP (LISTENER_PORT=5022) 
       FOR DATABASE_MIRRORING (ROLE=ALL)
   GO
   

3. [!附註]

   如果要裝載可用性複本之伺服器執行個體的服務帳戶在相同的網域帳戶下執行，則不需要此步驟。 略過它，直接進入下一個步驟。

   如果伺服器執行個體的服務帳戶在不同的網域使用者之下執行，請在每一個伺服器執行個體上建立其他伺服器執行個體的登入，並授與此登入存取本機資料庫鏡像端點的權限。

   下列程式碼範例將示範用來建立登入，並在端點上授與其權限的 Transact-SQL 陳述式。 遠端伺服器執行個體的網域帳戶在這裡表示為 domain_name\user_name。

     -- If necessary, create a login for the service account, domain_name\user_name
     -- of the server instance that will host the other replica:
     USE master;
     GO
     CREATE LOGIN [domain_name\user_name] FROM WINDOWS;
     GO
     -- And Grant this login connect permissions on the endpoint:
     GRANT CONNECT ON ENDPOINT::dbm_endpoint 
        TO [domain_name\user_name];
     GO
   

4. 在使用者資料庫所在的伺服器執行個體上，建立可用性群組。

   下列程式碼範例會在建立 MyDb1 和 MyDb2 範例資料庫的伺服器執行個體上建立名為 MyAG 的可用性群組。 先指定本機伺服器執行個體，即 COMPUTER01 上的 AgHostInstance。 這個執行個體將會裝載初始主要複本。 接著指定遠端伺服器執行個體，即 COMPUTER02 上的預設伺服器執行個體，以裝載次要複本。 這兩個可用性複本都是設定為具有手動容錯移轉的非同步認可模式 (對於非同步認可複本，手動容錯移轉表示在可能遺失資料的情況下強制容錯移轉)。

   -- Create the availability group, MyAG: 
   CREATE AVAILABILITY GROUP MyAG 
      FOR 
         DATABASE MyDB1, MyDB2 
      REPLICA ON 
         'COMPUTER01\AgHostInstance' WITH 
            (
            ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022', 
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL
            ),
         'COMPUTER02' WITH 
            (
            ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',
            AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
            FAILOVER_MODE = MANUAL
            ); 
   GO
   

   如需建立可用性群組的其他 Transact-SQL 程式碼範例，請參閱＜CREATE AVAILABILITY GROUP (Transact-SQL)＞。

5. 在裝載次要複本的伺服器執行個體上，將次要複本加入至可用性群組。

   下列程式碼範例會將 COMPUTER02 上的第二個複本加入到 MyAG 可用性群組。

   -- On the server instance that hosts the secondary replica, 
   -- join the secondary replica to the availability group:
   ALTER AVAILABILITY GROUP MyAG JOIN;
   GO
   

6. 在裝載次要複本的伺服器執行個體上，建立次要資料庫。

   下列程式碼範例會使用 RESTORE WITH NORECOVERY 來還原資料庫備份，以建立 MyDb1 和 MyDb2 次要資料庫。

   -- On the server instance that hosts the secondary replica, 
   -- Restore database backups using the WITH NORECOVERY option:
   RESTORE DATABASE MyDb1 
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
       WITH NORECOVERY
   GO
   
   RESTORE DATABASE MyDb2 
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
       WITH NORECOVERY
   GO
   

7. 在裝載主要複本的伺服器執行個體上，於每一個主要資料庫上備份交易記錄。

   重要事項
   當您設定真正的可用性群組時，我們建議您在取得此記錄備份之前，先暫停主要資料庫的記錄備份工作，直到您將對應的次要資料庫加入可用性群組為止。

   下列程式碼範例會在 MyDb1 和 MyDb2 上建立交易記錄備份。

   -- On the server instance that hosts the primary replica, 
   -- Backup the transaction log on each primary database:
   BACKUP LOG MyDb1 
   TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
       WITH NOFORMAT
   GO
   
   BACKUP LOG MyDb2 
   TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
       WITHNOFORMAT
   GO
   

   提示
   一般而言，您必須在每一個主要資料庫上進行記錄備份，然後在對應的次要資料庫上還原 (使用 WITH NORECOVERY)。 不過，如果資料庫剛剛建立，而且尚未建立任何記錄備份，或是如果復原模式剛剛從 SIMPLE 變更為 FULL，可能就不需要此記錄備份。

8. 在裝載次要複本的伺服器執行個體上，將記錄備份套用到次要資料庫。

   下列程式碼範例會使用 RESTORE WITH NORECOVERY 還原資料庫備份，將備份套用到 MyDb1 和 MyDb2 次要資料庫。

   重要事項
   當您準備真正的次要資料庫時，您必須套用您從建立次要資料庫的資料庫備份開始所取得的每一個記錄備份 (從最早的開始)，而且一定要使用 RESTORE WITH NORECOVERY。 當您同時還原完整和差異資料庫備份時，您只需要套用差異備份之後所做的記錄備份。

   -- Restore the transaction log on each secondary database,
   -- using the WITH NORECOVERY option:
   RESTORE LOG MyDb1 
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
       WITH FILE=1, NORECOVERY
   GO
   RESTORE LOG MyDb2 
       FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
       WITH FILE=1, NORECOVERY
   GO
   

9. 在裝載次要複本的伺服器執行個體上，將新的次要資料庫加入至可用性群組。

   下列程式碼範例會依序將 MyDb1 次要資料庫和 MyDb2 次要資料庫加入至 MyAG 可用性群組。

   -- On the server instance that hosts the secondary replica, 
   -- join each secondary database to the availability group:
   ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
   GO
   
   ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
   GO
   

[TopOfExample]

範例組態程序的完整程式碼範例

下列範例會合併範例組態程序之所有步驟的程式碼範例。 下表摘要說明此程式碼範例中所使用的預留位置值。 如需有關此程式碼範例中步驟的詳細資訊，請參閱本主題稍早的使用範例組態程序的必要條件和範例組態程序。

預留位置	說明
\\FILESERVER\SQLbackups	虛構的備份共用。
\\FILESERVER\SQLbackups\MyDb1.bak	MyDb1 的備份檔案。
\\FILESERVER\SQLbackups\MyDb2.bak	MyDb2 的備份檔案。
7022	指派給每一個資料庫鏡像端點的通訊埠編號。
COMPUTER01\AgHostInstance	裝載初始主要複本的伺服器執行個體。
COMPUTER02	裝載初始次要複本的伺服器執行個體。 這是 COMPUTER02 上的預設伺服器執行個體。
dbm_endpoint	為每個資料庫鏡像端點指定的名稱。
MyAG	範例可用性群組的名稱。
MyDb1	第一個範例資料庫的名稱。
MyDb2	第二個範例資料庫的名稱。
DOMAIN1\user1	要裝載初始主要複本之伺服器執行個體的服務帳戶。
DOMAIN2\user2	要裝載初始次要複本之伺服器執行個體的服務帳戶。
TCP://COMPUTER01.Adventure-Works.com:7022	COMPUTER01 上 SQL Server 之 AgHostInstance 執行個體的端點 URL。
TCP://COMPUTER02.Adventure-Works.com:5022	COMPUTER02 上 SQL Server 之預設執行個體的端點 URL。

[!附註]

如需建立可用性群組的其他 Transact-SQL 程式碼範例，請參閱＜CREATE AVAILABILITY GROUP (Transact-SQL)＞。

-- on the server instance that will host the primary replica, 
-- create sample databases:
CREATE DATABASE MyDb1;
GO
ALTER DATABASE MyDb1 SET RECOVERY FULL;
GO

CREATE DATABASE MyDb2;
GO
ALTER DATABASE MyDb2 SET RECOVERY FULL;
GO

-- Backup sample databases:
BACKUP DATABASE MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FORMAT
GO

BACKUP DATABASE MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FORMAT
GO

-- Create the endpoint on the server instance that will host the primary replica:
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- Create the endpoint on the server instance that will host the secondary replica: 
CREATE ENDPOINT dbm_endpoint
    STATE=STARTED 
    AS TCP (LISTENER_PORT=7022) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the primary replica, 
-- create a login for the service account 
-- of the server instance that will host the secondary replica, DOMAIN2\user2, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN2\user2];
GO

-- If both service accounts run under the same domain account, skip this step. Otherwise, 
-- On the server instance that will host the secondary replica,
-- create a login for the service account 
-- of the server instance that will host the primary replica, DOMAIN1\user1, 
-- and grant this login connect permissions on the endpoint:
USE master;
GO

CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;
GO
GRANT CONNECT ON ENDPOINT::dbm_endpoint 
   TO [DOMAIN1\user1];
GO

-- On the server instance that will host the primary replica, 
-- create the availability group, MyAG: 
CREATE AVAILABILITY GROUP MyAG 
   FOR 
      DATABASE MyDB1, MyDB2 
   REPLICA ON 
      'COMPUTER01\AgHostInstance' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ),
      'COMPUTER02' WITH 
         (
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
         FAILOVER_MODE = AUTOMATIC
         ); 
GO

-- On the server instance that hosts the secondary replica, 
-- join the secondary replica to the availability group:
ALTER AVAILABILITY GROUP MyAG JOIN;
GO

-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:
RESTORE DATABASE MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NORECOVERY
GO

RESTORE DATABASE MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH NORECOVERY
GO

-- Back up the transaction log on each primary database:
BACKUP LOG MyDb1 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH NOFORMAT
GO

BACKUP LOG MyDb2 
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITHNOFORMAT
GO

-- Restore the transaction log on each secondary database,
-- using the WITH NORECOVERY option:
RESTORE LOG MyDb1 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak' 
    WITH FILE=1, NORECOVERY
GO
RESTORE LOG MyDb2 
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak' 
    WITH FILE=1, NORECOVERY
GO

-- On the server instance that hosts the secondary replica, 
-- join each secondary database to the availability group:
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;
GO

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;
GO

[TopOfExample]

相關工作

若要設定可用性群組和複本屬性

• 變更可用性複本的可用性模式 (SQL Server)

• 變更可用性複本的容錯移轉模式 (SQL Server)

• 建立或設定可用性群組接聽程式 (SQL Server)

• 設定彈性容錯移轉原則以控制自動容錯移轉的條件 (AlwaysOn 可用性群組)

• 在加入或修改可用性複本時指定端點 URL (SQL Server)

• 設定可用性複本的備份 (SQL Server)

• 設定可用性複本上的唯讀存取 (SQL Server)

• 設定可用性群組的唯讀路由 (SQL Server)

• 變更可用性複本的工作階段逾時期限 (SQL Server)

若要完成可用性群組組態

• 將次要複本聯結至可用性群組 (SQL Server)

• 針對可用性群組手動準備次要資料庫 (SQL Server)

• 將次要資料庫聯結至可用性群組 (SQL Server)

• 建立或設定可用性群組接聽程式 (SQL Server)

建立可用性群組的其他方法

• 使用新增可用性群組精靈 (SQL Server Management Studio)

• 使用新增可用性群組對話方塊 (SQL Server Management Studio)

• 建立可用性群組 (SQL Server PowerShell)

若要啟用 AlwaysOn 可用性群組

• 啟用和停用 AlwaysOn 可用性群組 (SQL Server)

若要設定資料庫鏡像端點

• 針對 AlwaysOn 可用性群組建立資料庫鏡像端點 (SQL Server PowerShell)

• 建立 Windows 驗證的資料庫鏡像端點 (Transact-SQL)

• 使用資料庫鏡像端點憑證 (Transact-SQL)

• 在加入或修改可用性複本時指定端點 URL (SQL Server)

若要疑難排解 AlwaysOn 可用性群組組態

• 疑難排解 AlwaysOn 可用性群組組態 (SQL Server)

• 疑難排解失敗的加入檔案作業 (AlwaysOn 可用性群組)

[回到頁首]

相關內容

• **部落格： **

  AlwaysON - HADRON 學習系列：資料庫啟用 HADRON 時工作者集區的使用方式

  SQL Server AlwaysOn 團隊部落格：官方 SQL Server AlwaysOn 團隊部落格

  CSS SQL Server 工程師部落格

• **影片： **

  Microsoft SQL Server Code-Named "Denali" AlwaysOn 系列第 1 部：新一代高可用性解決方案簡介

  Microsoft SQL Server Code-Named "Denali" AlwaysOn 系列第 2 部：使用 AlwaysOn 建立關鍵任務的高可用性解決方案

• **白皮書： **

  Microsoft SQL Server AlwaysOn 高可用性和災害復原方案指南

  Microsoft 的 SQL Server 2012 白皮書

  SQL Server 客戶諮詢團隊白皮書

[回到頁首]

請參閱

概念

資料庫鏡像端點 (SQL Server)

AlwaysOn 可用性群組概觀 (SQL Server)

可用性群組接聽程式、用戶端連接及應用程式容錯移轉 (SQL Server)

AlwaysOn 可用性群組的必要條件、限制和建議 (SQL Server)