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

發行項
05/04/2023

適用於：SQL Server

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

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

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

注意

CLUSTER_TYPE = NONE 的可用性群組可包含裝載於各種作業系統平台上的複本。它無法支援高可用性。針對 Linux 作業系統，請參閱設定 SQL Server 可用性群組供 Linux 上的讀取縮放使用。

必要條件

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

設定您的環境，讓所有將要裝載可用性複本的伺服器能夠通訊。
安裝 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 事件工作階段

當您針對可用性群組進行疑難排解時，若要協助診斷根本原因，您可以選擇性地啟用 AlwaysOn 可用性群組擴充事件(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 的使用者。請以強式密碼更新指令碼。若要建立資料庫鏡像端點使用者，請在所有 SQL Server 執行個體上執行下列命令：

CREATE LOGIN dbm_login WITH PASSWORD = '**<1Sample_Strong_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。

下列指令碼會將名為 ag1 的可用性群組上的 REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT 設為 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) 上線，請立即讓原始主要複本 (N1) 上的 AGRScale 可用性群組離線：
```
ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
```
如果有資料或未同步的變更，請透過備份或其他符合您業務需求的資料複寫選項來保留此資料。
接下來，從原始的主要複本 (N1) 移除該可用性群組：
```
DROP AVAILABILITY GROUP [AGRScale];
```
在原始主要複本 (N1) 上卸載可用性群組資料庫：
```
USE [master]
GO
DROP DATABASE [AGDBRScale]
GO
```
(選擇性) 如有需要，您現在可以將 N1 以新次要複本的形式重新新增回 AGRScale 可用性群組。

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

共用方式為

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

必要條件

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

啟用 AlwaysOn_health 事件工作階段

資料庫鏡像端點驗證

服務帳戶

憑證驗證

在次要伺服器上建立憑證

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

建立可用性群組

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

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

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

連線到唯讀次要複本

容錯移轉讀取級別可用性群組上的主要複本

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

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

後續步驟

意見反應

其他資源

共用方式為

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

必要條件

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

啟用 AlwaysOn_health 事件工作階段

資料庫鏡像端點驗證

服務帳戶

SQL 登入驗證

憑證驗證

在次要伺服器上建立憑證

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

建立可用性群組

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

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

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

連線到唯讀次要複本

容錯移轉讀取級別可用性群組上的主要複本

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

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

後續步驟

意見反應

其他資源