SQL Server AlwaysOn Availability Group On Linux

• 2019 年 12 月 25 日
• 筆記

SQL Server Always On Availability Group 配置

步驟：

1. 配置三台 Linux 集群節點
2. 創建 Availability Group
3. 配置 Cluster Resource Manager, 比如 Pacemaker
4. 在集群中添加 Availability Groups

詳細解說

1. 配置三台 Linux 集群節點

• 在集群中的三台服務器之間可以互相通信
• 安裝 SQL Server

在 Linux 上，往集群管理器中添加集群資源時，一定是先建立集群資源，接着將新建的資源加入到集群中去。

所以建立 SQL Server Always On Availability Groups 也一樣，先建立 AG, 完了之後添加到集群管理器（ Cluster Manager ）中去。

三台節點之間可以相互通信，該如何了解？

使用 ssh 可以相互無障礙地登錄

1. 設置每台計算機的機器名
2. 加入同一個域
3. 配置 RSA 使得無密碼 ssh 登錄

1 設置每台計算機的機器名

vi /etc/hostname

分別設置為 centos00, centos01, centos02.

centos00 為主庫所在服務器節點

2 加入同一個域

作用就是了互相識別，假如 node1 , node2, node3 是我們新建的三台集群服務器，互為 Availability Groups 成員，且 node1 是主庫，node2, node3 為從庫，三台機器之間互相識別的方法有兩種，一是加入同一個域； 二是在各自的 /etc/hosts 文件中，添加機器名和 IP 地址的映射：

127.0.0.1 localhost  ::1 localhost    192.168.1.10 centos00  192.168.1.11 centos01  192.168.1.12 centos02

把以上的配置腳本放入各自服務器的 /etc/hosts 中去，不需要配置域即可互相識別。

安裝 SQL Server 已有介紹，不展開了

2. 啟動 AlwaysOn Availability Group 特性

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

可以檢查服務器是否啟用了 hadr :

select serverproperty('HadrManagerStatus')

適用於: SQL Server 2012 (11.x) through SQL Server 2017. 提示 SQL Server 是否啟用了高可用災備特性： 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input is not valid, an error, or not applicable.

開啟 AlwaysOn_health 擴展事件（Extended Events），用來檢測 Always On Availability Group 的故障所在：

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

在每一台節點實例上都開啟 AlwaysOn_health 擴展事件。

關於如何使用 AlwaysOn_health 擴展事件，另開一篇文章解釋，詳見有道與筆記中 SQL Server 目錄下 eXtended Events(XE) 的操作文檔。稍後公開

3. 新建證書 (certificate)

證書(Certificate)的作用，是為了讓 SQL Server Mirroring Endpoints 之間可以相互通信。

理念和兩台 Linux 服務器之間無密通信一樣，一個公鑰，一個私鑰。公鑰用來核實私鑰的有效性，分發到各個服務器上，作為訪問遠程服務的憑證。

新建 master key ：

create master key encryption by password='MasterKey123.'

create master key 是 T-SQL 命令，不能直接在 shell 裏面運行！

新建證書：

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='PrivateKey123.')

現在有了公鑰，私鑰和證書，則需要保證所有的集群節點都有私鑰和證書，如果沒有則複製私鑰和證書到相同目錄下，以便之後的安裝證書。

所以配置 AG 時，複製 dbm_certificate.cer, dbm_certificate.pvk 到其他兩台節點相同目錄下。

cd /var/opt/mssql/data  scp dbm_certificate.* root@centos01: /var/opt/mssql/data/  scp dbm_certificate.* root@centos02: /var/opt/mssql/data/

把這兩個文件的權限賦給運行 ms sql server 服務的賬戶 mssql：

chown mssql:mssql dbm_certificate.*

4. 還原從庫的證書

從庫已經有了主庫私鑰的副本，這些副本可以還原出證書

create master key encryption by password='MasterKey123.'    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='PrivateKey123.')

在所有的從庫都執行上述的腳本，來創建證書。

其中會遇到一點小麻煩：

Msg 15208, Level 16, State 6, Server centos02, Line 1  The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

mssql 是用來運行 sql server on linux 的賬戶，因此他需要被賦予可以訪問證書和私鑰文件的權限。

chown mssql:mssql dbm_certificate.*

證書是從主庫上「移栽」過去的，那麼從庫是否也需要搭建自己的證書，「移栽」給主庫使用呢？

5. 新建 mirroring endpoints

SQL Server AG 節點之間使用 TCP 通信，通過指定的端口傳送消息。

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

打開 防火牆對 endpoint 對應的端口的支持。

    firewall-cmd --zone=public --add-port=5022/tcp --permanent      firewall-cmd --reload

6. 新建 AG (Availability Groups)

兩種新建 AG 的方法：

• 3 份同步副本 (Three synchronous replicas)
• 2 份同步副本 + 1份配置副本(Two synchronous replicas plus a configuration replica)

這兩種方法都可以保護數據和實現高可用性，但 3份同步副本的方法更能在主庫失效的情況下，做出自動切換的動作，等待老主庫回線之後，繼續事務操作。

如果僅僅是實現保護數據的目的，那麼直接採用 2 副本即可，還省去了集群管理器的配置。

搭建三副本同步的腳本：

create availability group [crmag]      with (db_failover=on,cluster_type=external)      for replica on          N'centos00'          with (              endpoint_url = N'tcp://centos00:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              ),           N'centos01'          with (              endpoint_url = N'tcp://centos01:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              ),           N'centos02'          with (              endpoint_url = N'tcp://centos02:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              );        alter availability group [crmag]          grant create any database ;

我們在這裡指定了 external 的集群管理方式，那麼就需要在 Linux 上安裝獨立的集群管理軟件，通常用 Pacemaker. 但理論上也應該有其他的集群管理軟件，比如 Mesos, Linux Cluster Manager(LCM) 等。

這是配置 Availability Group 中比較重要的一大步，有必要在這一步做一些測試來提早預知，是不是配置得有問題。

endpoint_url 被配置成了 namepipeline, 我覺得有必要修改一下：

create availability group [crmag]      with (db_failover=on,cluster_type=external)      for replica on          N'192.168.1.10'          with (              endpoint_url = N'tcp://192.168.1.10:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              ),           N'192.168.1.11'          with (              endpoint_url = N'tcp://192.168.1.11:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              ),           N'192.168.1.12'          with (              endpoint_url = N'tcp://192.168.1.12:5022',              availability_mode=synchronous_commit,              failover_mode=external,              seeding_mode=automatic              );        alter availability group [crmag]          grant create any database ;

理論上所有 availability_mode 標記是 synchronous_commit 的節點，都需要同步完成之後，主庫的事務才能被正確提交。但有一個參數可以控制從庫副本同步的最小數量 – Required_Synchronized_Secondaries_To_Commit. 上面的配置看上去是要 2 個從庫都同步之後，事務才會在主庫提交，但如果配置了 required_synchronized_secondaries_to_commit 為1， 則只需要一台從庫同步即可。

7. 連接從庫與添加 AG 數據庫

連接從庫：

需要將集群中的從庫，添加到 AG 中來，在每個從庫上執行下面的命令：

ALTER AVAILABILITY GROUP [crmag]      Join WITH(cluster_type=external);    ALTER AVAILABILITY GROUP [crmag]      Grant Create Any Database ;

添加 AG 數據庫：

選擇性的添加我們要同步的數據庫，使其實現三副本同步：

create database [crm];  alter database [crm] set recovery full ;      backup database [crm]      to disk = N'/var/opt/mssql/data/crm.bak';        alter availability group [crmag]      add database [crm]

驗證同步情況：

select db_name(database_id)  as 'database'      ,   synchronization_state_desc  from sys.dm_hadr_database_replica_states ;

目前為止實現的功能是數據保護，即主庫失效的情況下，數據都備份在兩台從庫上。但並沒有實現自動切換功能，即主庫失效了，其他兩台庫也就不能訪問了。所以下一篇講解如何使用 pacemaker 實現自動切換失效主庫到從庫，達到7*24 小時無故障在線。