設定 Always On 可用性群組的讀取級別

發行項
2025-03-15

適用於：SQL Server

您可以在 Windows 上設定 SQL Server Always On 可用性群組，用於讀取縮放工作負載。可用性群組有兩種類型的架構：

高可用性結構，其使用叢集管理員來提供改善的商務持續性，並能夠包含可讀取的次要複本。若要建立此高可用性結構，請參閱在 Windows 上建立和設定可用性群組。
僅支援讀取縮放工作負載的結構。

本文說明如何建立不使用叢集管理員的可用性群組，供讀取縮放工作負載使用。此結構只會提供讀取級別。它不會提供高可用性。

注意

CLUSTER_TYPE = NONE 的可用性群組可包含裝載於各種作業系統平台上的複本。它無法支援高可用性。針對 Linux 作業系統，請參閱在 Linux 上為讀取負載配置 SQL Server 可用性群組。

必要條件

建立可用性群組之前，您需要：

設定您的環境，讓所有將要裝載可用性複本的伺服器能夠通訊。
安裝 SQL Server。如需詳細資訊，請參閱安裝 SQL Server。

啟用 AlwaysOn 可用性群組並重新啟動 mssql-server

注意

下列命令使用發佈在 PowerShell 資源庫中的 sqlserver 模組之 Cmdlet。您可以使用 Install-Module 命令安裝此模組。

在每個裝載 SQL Server 執行個體的複本上啟用 AlwaysOn 可用性群組。然後重新啟動 SQL Server 服務。執行下列命令以啟用並重新啟動 SQL Server 服務：

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

啟用 AlwaysOn_health 事件會話

當您針對可用性群組進行疑難排解時，若要協助診斷根本原因，您可以選擇啟用 Always On 高可用性群組的延伸事件 (XEvents) 會話。若要這樣做，請在每個 SQL Server 執行個體上執行下列命令：

ALTER EVENT SESSION  AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON);
GO

如需此 XEvents 工作階段的詳細資訊，請參閱 AlwaysOn 可用性群組擴充事件。

資料庫鏡像端點驗證

為了讓同步處理正常運作，參與讀取縮放可用性群組的複本必須透過端點進行驗證。以下幾節將說明可用於這類驗證的兩個主要案例。

服務帳戶

在所有次要複本都會加入相同網域的 Active Directory 環境中，SQL Server 可以利用服務帳戶進行驗證。您必須在每個 SQL Server 執行個體上，為服務帳戶明確建立登入：

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

在次要複本可能不會加入 Active Directory 網域的環境中，您必須利用 SQL 驗證。下列 Transact-SQL 指令碼會建立名為 dbm_login 的登入，以及名為 dbm_user 的使用者。以有效的密碼取代 <password>。若要建立資料庫鏡像端點使用者，請在所有 SQL Server 實例上執行下列命令。

CREATE LOGIN dbm_login WITH PASSWORD = '<password>';
CREATE USER dbm_user FOR LOGIN dbm_login;

憑證驗證

如果您利用需要使用 SQL 驗證進行驗證的次要複本，請使用憑證在鏡像端點之間進行驗證。

下列 Transact-SQL 指令碼會建立主要金鑰和憑證。然後它會備份憑證，並使用私密金鑰保護檔案。請以強式密碼更新指令碼。在主要 SQL Server 執行個體上執行指令碼來建立憑證：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
   WITH PRIVATE KEY (
       FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
       ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
   );

此時，您的 SQL Server 主要複本含有一個位於 c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer 的憑證，以及一個位於 c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk 的私密金鑰。將這兩個檔案複製到將裝載可用性複本的所有伺服器上的相同位置。

在每個次要複本上，請確定 SQL Server 執行個體的服務帳戶都有存取憑證的權限。

在次要伺服器上建立憑證

下列 Transact-SQL 指令碼會從您在 SQL Server 主要複本上建立的備份來建立主要金鑰和憑證。此命令也會授權使用者存取憑證。請以強式密碼更新指令碼。解密密碼與您在上一個步驟中用來建立 .pvk 檔案的密碼相同。若要建立憑證，請在所有次要複本上執行下列指令碼：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
    );

在所有複本上建立資料庫鏡像端點

資料庫鏡像端點使用「傳輸控制通訊協定」(TCP)，在參與資料庫鏡像工作階段或裝載可用性複本的伺服器執行個體之間傳送和接收訊息。資料庫鏡像端點會在唯一的 TCP 通訊埠編號上接聽。

下列 Transact-SQL 指令碼會為可用性群組建立名為 Hadr_endpoint 的接聽端點。它會啟動端點，並為服務帳戶或您在上一個步驟中建立的 SQL 登入提供連線權限。執行指令碼之前，請取代 **< ... >** 之間的值。您可以選擇性地包含 IP 位址 LISTENER_IP = (0.0.0.0)。接聽程式 IP 位址必須是 IPv4 位址。您也可以使用 0.0.0.0。

請在所有 SQL Server 執行個體上更新您環境的下列 Transact-SQL 指令碼：

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

防火牆上的 TCP 連接埠必須開啟以作為接聽程式連接埠。

如需詳細資訊，請參閱資料庫鏡像端點 (SQL Server)。

建立可用性群組

建立可用性群組。設定 CLUSTER_TYPE = NONE。此外，將每個複本設定為 FAILOVER_MODE = NONE。執行分析或報告工作負載的用戶端應用程式可直接連線至次要資料庫。您也可以建立唯讀路由清單。連線到主要複本將讀取連線請求以輪詢方式從路由清單中轉發至每個次要複本。

下列 Transact-SQL 指令碼會建立名為 ag1 的可用性群組。該指令碼會將可用性群組複本設定為 SEEDING_MODE = AUTOMATIC。此設定會使 SQL Server 在將次要伺服器新增至可用性群組之後，在每個次要伺服器上自動建立資料庫。

更新您環境中的下列指令碼。將 <node1> 和 <node2> 值取代為裝載複本之 SQL Server 執行個體的名稱。將 <5022> 值取代為您為端點設定的連接埠。在 SQL Server 主要複本上，執行下列 TRANSACT-SQL 指令碼：

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

將次要 SQL Server 執行個體加入可用性群組

下列 Transact-SQL 指令碼會將伺服器加入名為 ag1 的可用性群組。更新您環境中的指令碼。若要加入可用性群組，請在每個 SQL Server 次要複本上執行下列 Transact-SQL 指令碼：

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

將資料庫新增至可用性群組

確定您要新增至可用性群組的資料庫處於完整復原模式，而且具有有效的記錄備份。如果資料庫是測試資料庫或新建立的資料庫，請進行資料庫備份。若要建立和備份稱為 db1 的資料庫，請在主要 SQL Server 執行個體上執行下列 Transact-SQL 指令碼：

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

若要將稱為 db1 的資料庫新增至稱為 ag1 的可用性群組，請在 SQL Server 主要複本上執行下列 Transact-SQL 指令碼：

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

確認已在次要伺服器上建立資料庫

若要查看 db1 資料庫是否已建立且已同步處理，請在每個 SQL Server 次要複本上執行下列查詢：

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

這個可用性群組不是高可用性設定。如果您需要高可用性，請遵循為 Linux 上的 SQL Server 設定 AlwaysOn 可用性群組或在 Windows 上建立和設定可用性群組中的指示進行。

連線到唯讀次要資料庫複本

您可以使用下列兩種方式之一連線至唯讀次要複本：

應用程式可以直接連線到裝載次要複本的 SQL Server 執行個體，並查詢資料庫。如需詳細資訊，請參閱可讀取的次要複本。
應用程式也可以使用唯讀路由，這需要一個監聽服務。如果要在沒有叢集管理員的情況下部署讀取縮放案例，您仍然可建立一個接聽程式，其指向目前主要複本的 IP 位址，以及與 SQL Server 所接聽相同的連接埠。在容錯移轉之後，將須重新建立接聽程式，以指向新的主要 IP 位址。如需詳細資訊，請參閱唯讀路由。

在讀取擴展可用性群組上進行主要複本的故障轉移

每個可用性群組只有一個主要副本。主要複本允許讀取和寫入。若要變更作為主要的複本，您可以進行容錯移轉。在一般可用性群組中，叢集管理員會自動化故障轉移程序。在叢集類型為 NONE 的可用性群組中，容錯移轉程序是手動的。

叢集類型為 NONE 的可用性群組中，有兩種方式可進行主要複本容錯移轉：

手動接管，確保資料不會遺失
強制手動故障切換，導致資料遺失

手動容錯移轉 (不會遺失資料)

當主要複本可以使用，但您需要暫時或永久變更裝載主要複本的執行個體時，請使用這個方法。在進行手動故障轉移之前，為避免潛在的資料遺失，請確保目標的次要副本已更新至最新狀態。

若要手動容錯移轉 (不會遺失資料)：

請製作目前的主要複本與目標次要複本 SYNCHRONOUS_COMMIT。

ALTER AVAILABILITY GROUP [AGRScale] 
     MODIFY REPLICA ON N'<node2>' 
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

若要確認進行中的交易已提交至主要複本並至少一個同步次要複本，請執行下列查詢：

SELECT ag.name, 
   drs.database_id, 
   drs.group_id, 
   drs.replica_id, 
   drs.synchronization_state_desc, 
   ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

當 synchronization_state_desc 為 SYNCHRONIZED 時，即會同步處理次要複本。

將 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 更新為 1。

下列指令碼會將名為 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 的可用性群組上的 ag1 設為 1。執行下列指令碼之前，以您的可用性群組名稱取代 ag1：
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
```
這項設定可確保所有使用中交易已提交至主要複本及至少一個同步次要複本。

注意

這項設定並非專門針對故障移轉，應該根據環境的需求進行設定。
設定主要複本和未參與容錯移轉的次要複本為離線狀態，以準備進行角色變更：
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```

將目標次要複本升格為主要複本。

ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

將舊的主要複本和其他次要複本的角色更新為 SECONDARY，並在裝載舊主要複本的 SQL Server 執行個體上執行下列命令：
```
ALTER AVAILABILITY GROUP [AGRScale] 
     SET (ROLE = SECONDARY); 
```
注意

若要刪除可用性群組，請使用 DROP AVAILABILITY GROUP。針對以叢集類型 NONE 或 EXTERNAL 建立的可用性群組，請在屬於可用性群組的所有複本上執行此命令。
繼續進行資料移動，在裝載主要複本的 SQL Server 執行個體上，針對可用性群組中的每個資料庫執行下列命令：
```
ALTER DATABASE [db1]
     SET HADR RESUME
```
重新建立具讀取擴充功能且不由叢集管理員管理的任何接聽程式。如果原始接聽器指向舊的主節點，將其捨棄，然後重新建立接聽器以指向新的主節點。

強制手動容錯移轉 (可能遺失資料)

如果主要複本無法使用且無法立即復原，則您必須被迫執行容錯移轉至次要複本，這將導致資料遺失。不過，如果原始的主要複本在故障轉移後復原，其便會恢復為主要角色。若要避免讓每個副本處於不同的狀態，請在強制容錯移轉（可能會導致資料遺失）後，從可用性群組中移除原始的主要副本。一旦原始主要資料庫重新連線，請完全從其中移除可用性群組。

若要從主要複本 N1 強制手動移轉至次要複本 N2，並接受可能遺失資料的風險，請遵循下列步驟：

在次要複本 (N2) 上，起始強制容錯移轉：

ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

在新的主要複本 (N2) 上，移除原始的主要複本 (N1)：
```
ALTER AVAILABILITY GROUP [AGRScale]
REMOVE REPLICA ON N'N1';
```
驗證所有應用程式流量都指向監聽器和/或新的主要複本。
如果原始主要複本 (N1) 上線，應立即將 AGRScale 可用性群組在該原始主要複本 (N1) 上離線：
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
如果有資料或未同步的變更，請透過備份或其他符合您業務需求的資料複寫選項來保留此資料。
接下來，從原始的主要複本 (N1) 移除該可用性群組：
```
DROP AVAILABILITY GROUP [AGRScale];
```
在原始主要複本 (N1) 上刪除可用性群組資料庫：
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(選擇性) 如有需要，您現在可以將 N1 以新次要複本的形式重新新增回 AGRScale 可用性群組。

請注意，如果您使用監聽器來連線，則必須在執行容錯移轉之後重新建立監聽器。

共用方式為