設定 SQL Server 可用性群組供 Linux 上的讀取級別使用

發行項
05/04/2023

適用於：SQL Server - Linux

本文說明如何在不使用叢集管理員的情況下，在 Linux 上建立 SQL Server Always On 可用性群組 (AG)。此結構只會提供讀取級別。它不提供高可用性。

AG 有兩種結構類型。 高可用性結構會使用叢集管理員提供更高的商務持續性。若要建立高可用性結構，請參閱設定 SQL Server Always On 可用性群組以確保 Linux 上的高可用性。

CLUSTER_TYPE = NONE 的可用性群組可包含裝載於不同作業系統平台上的複本。它無法支援高可用性。

必要條件

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

設定您的環境，讓所有將要裝載可用性複本的伺服器能夠通訊。
安裝 SQL Server。

注意

在 Linux 上，您必須先建立可用性群組，再將它新增為叢集資源，以供叢集管理。本文件提供建立可用性群組的範例。如需建立叢集並將可用性群組新增為叢集資源的發行版本特定指示，請參閱＜後續步驟＞底下的連結。

更新每一部主機的電腦名稱。

每個 SQL Server 名稱必須是：
- 15 個字元以下。
- 在網路內是唯一的。
若要設定電腦名稱，請編輯 /etc/hostname。下列指令碼可讓您使用 vi 編輯 /etc/hostname：
```
sudo vi /etc/hostname
```
設定 hosts 檔案。

注意

如果在 DNS 伺服器中註冊了主機名稱及其 IP 位址，您就無須執行下列步驟。驗證所有要作為可用性群組設定一部分的節點都可以彼此通訊。 (對主機名稱的 Ping 應以對應的 IP 位址回覆)。此外，請確定 /etc/hosts 檔案不包含對應 localhost IP 位址 127.0.0.1 與節點主機名稱的記錄。

每一部伺服器上的 hosts 檔案包含將參與可用性群組之所有伺服器的 IP 位址和名稱。

下列命令會傳回目前伺服器的 IP 位址：
```
sudo ip addr show
```
更新 /etc/hosts。下列指令碼可讓您使用 vi 編輯 /etc/hosts：
```
sudo vi /etc/hosts
```
下列範例顯示 node1 上的 /etc/hosts，並新增了 node1、node2 和 node3。在此範例中，node1 是指裝載主要複本的伺服器，node2 和 node3 則是裝載次要複本的伺服器。
```
127.0.0.1    localhost localhost4 localhost4.localdomain4
::1          localhost localhost6 localhost6.localdomain6
10.128.18.12 node1
10.128.16.77 node2
10.128.15.33 node3
```

安裝 SQL Server

安裝 SQL Server。下列連結指向各種發行版本的 SQL Server 安裝指示：

啟用 Always On 可用性群組

為每個裝載 SQL Server 執行個體的節點啟用 Always On 可用性群組，然後重新啟動 mssql-server。執行下列指令碼：

sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1
sudo systemctl restart mssql-server

啟用 AlwaysOn_health 事件工作階段

對可用性群組進行疑難排解時，您可以選擇性地啟用擴充事件 (XE) 以利診斷根本原因。在每個 SQL Server 執行個體上執行下列命令：

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

如需此 XE 工作階段的詳細資訊，請參閱設定 Always On 可用性群組的擴充事件。

建立憑證

Linux 上的 SQL Server 服務使用憑證來驗證鏡像端點之間的通訊。

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';
BACKUP CERTIFICATE dbm_certificate
   TO FILE = '/var/opt/mssql/data/dbm_certificate.cer'
   WITH PRIVATE KEY (
           FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
           ENCRYPTION BY PASSWORD = '**<Private_Key_Password>**'
        );

此時，您的 SQL Server 主要複本含有一個位於 /var/opt/mssql/data/dbm_certificate.cer 的憑證，以及一個位於 var/opt/mssql/data/dbm_certificate.pvk 的私密金鑰。將這兩個檔案複製到將裝載可用性複本的所有伺服器上的相同位置。使用 mssql 使用者，或授與 mssql 使用者存取這些檔案的權限。

例如，在來源伺服器上，下列命令會將檔案複製到目標電腦。將 **<node2>**值取代為要裝載複本的 SQL Server 執行個體的名稱。

cd /var/opt/mssql/data
scp dbm_certificate.* root@**<node2>**:/var/opt/mssql/data/

在每個目標伺服器上，授與 mssql 使用者存取憑證的權限。

cd /var/opt/mssql/data
chown mssql:mssql dbm_certificate.*

在次要伺服器上建立憑證

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

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate
    FROM FILE = '/var/opt/mssql/data/dbm_certificate.cer'
    WITH PRIVATE KEY (
           FILE = '/var/opt/mssql/data/dbm_certificate.pvk',
           DECRYPTION BY PASSWORD = '**<Private_Key_Password>**'
        );

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

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

下列 Transact-SQL 指令碼會為可用性群組建立名為 Hadr_endpoint 的接聽端點。它會啟動端點，並將連線權限授與您建立的憑證。執行指令碼之前，請取代 **< ... >** 之間的值。您可以選擇性地包含 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;

注意

如果您在某個節點上使用 SQL Server Express Edition 來裝載僅限設定複本，則唯一有效的 ROLE 值為 WITNESS。在 SQL Server Express Edition 上執行下列指令碼：

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = WITNESS,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );

ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;

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

重要

對於 SQL Server 2017 版，資料庫鏡像端點唯一支援的驗證方法是 CERTIFICATE。未來的版本將啟用 WINDOWS 選項。

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

建立可用性群組

建立 AG。設定 CLUSTER_TYPE = NONE。此外，將每個複本設定為 FAILOVER_MODE = MANUAL。執行分析或報告工作負載的用戶端應用程式可以直接連線到次要資料庫。您也可以建立唯讀路由清單。連線到主要複本會從路由清單，以循環配置資源的方式將讀取連線要求轉送至每個次要複本。

下列 Transact-SQL 指令碼會建立名為 ag1 的 AG。該指令碼會將 AG 複本設定為 SEEDING_MODE = AUTOMATIC。此設定會使 SQL Server 在新增至 AG 之後，在每個次要伺服器上自動建立資料庫。更新您環境中的下列指令碼。將 <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 執行個體加入 AG

下列 Transact-SQL 指令碼會將伺服器加入名為 ag1 的 AG。更新您環境中的指令碼。在每個 SQL Server 次要複本上，執行下列 TRANSACT-SQL 指令碼以加入 AG：

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

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

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

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'/var/opt/mssql/data/db1.bak';

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

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

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

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

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;

此 AG 不是高可用性設定。如果您需要高可用性，請遵循為 Linux 上的 SQL Server 設定 Always On 可用性群組中的指示進行。具體而言，請建立 CLUSTER_TYPE=WSFC (Windows) 或 CLUSTER_TYPE=EXTERNAL (Linux) 的 AG。接著，您可以使用 Windows Server 容錯移轉叢集 (Windows) 或 Pacemaker (Linux) 與叢集管理員整合。

連線到唯讀次要複本

有兩種方式可連線到唯讀次要複本。應用程式可以直接連線到裝載次要複本的 SQL Server 執行個體，並查詢資料庫。它們也可以使用唯讀路由，這需要接聽程式。

容錯移轉讀取級別 AG 上的主要複本

每個可用性群組只有一個主要複本。主要複本允許讀取和寫入。若要變更作為主要的複本，您可以進行容錯移轉。在一般可用性群組中，叢集管理員會自動化容錯移轉程序。在叢集類型為 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 可用性群組。

設定 SQL Server 可用性群組供 Linux 上的讀取級別使用

必要條件

安裝 SQL Server

啟用 Always On 可用性群組

啟用 AlwaysOn_health 事件工作階段

建立憑證

在次要伺服器上建立憑證

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

建立可用性群組

將次要 SQL Server 執行個體加入 AG

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

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

連線到唯讀次要複本

容錯移轉讀取級別 AG 上的主要複本

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

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

意見反應

意見反應

其他資源

設定 SQL Server 可用性群組供 Linux 上的讀取級別使用

必要條件

安裝 SQL Server

啟用 Always On 可用性群組

啟用 AlwaysOn_health 事件工作階段

建立憑證

在次要伺服器上建立憑證

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

建立可用性群組

將次要 SQL Server 執行個體加入 AG

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

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

連線到唯讀次要複本

容錯移轉讀取級別 AG 上的主要複本

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

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

相關內容

意見反應

意見反應

其他資源