Ubuntu上配置SQL Server Always On Availability Group

共计 8983 个字符，预计需要花费 23 分钟才能阅读完成。

下面简单介绍一下如何在 Ubuntu 上一步一步创建一个 SQL Server AG（Always On Availability Group），以及配置过程中遇到的坑的填充方法。

目前在 Linux 上可以搭建两种类型的 SQL Server AG，一种是高可用性的结构同时使用 Cluster 服务器提供业务连续性。这种结构包括 read-scale 节点。接下来就会介绍这种 AG 的搭建方法。另外一种是没有 Cluster 服务的 read-scale AG，这种结构仅仅提供只读的可扩展性，不提供高可用性功能。关于如何创建这种简单的 AG 请参考：Configure read-scale availability group for SQL Server on Linux。

另外在 CREATE AVAILABILITY GROUP 时可以指定 CLUSTER TYPE：

WSFC：Windows server failover cluster。这个是 Windows 系统上的默认值；
EXTERNAL：非 Windows server 上的 failover cluster，比如 Linux 上的 Pacemaker；
NONE：不包含 cluster manager，指的是创建 read-scale 类型的 Availability Group。

其中 Linux 可以使用 EXTERNAL 或 NONE，我理解的是 EXTENRAL 功能就是类似目前 SQL Server 中的 AG，NONE 则是一种新类型，没有 Cluster 功能的不支持高可用性和灾难恢复的 AG。主要作用是分担主服务器的负载，支持多个只读备用节点，同时这种类型也支持 Windows 上使用，是 SQL Server 2017 新支持的功能。更多详细的信息请参考这里：Read-scale availability groups。

接下来进入主题主要介绍一下高可用性结构的 Availability Group 的搭建方法。

一个 SQL AG 至少有两个以上的节点，由于环境有限，这里只安装一个最简单的包含两个节点的 AG。首先是按照 Red Hat Enterprise Linux 上安装部署 SQL Server 2017 中的介绍，安装两个 Ubuntu 机器和 SQL Server。

Note：同一个 AG 的多个节点必须都是实体机或者虚拟机，当都是虚拟机的时候也必须都在同一个虚拟化平台上，原因是由于 Linux 需要用 fencing agent 去隔离节点上的资源，不同平台 fencing agent 类型是不同的，详细参考 Policies for Guest Clusters。

在 Linux 上，必须先创建 AG 才能把它当成一个资源加到 Cluster 中进行管理。下面介绍一下如何创建 AG。

a) 准备工作：

更新每一个节点服务器的机器名符合这个要求：15 个字符或者更少；网络上是唯一的。如果不符合要求可以使用如下命令更改机器名：

sudo vi /etc/hostname

使用如下命令修改 Hosts文件以保证同一个 AG中多个节点可以互相通信：

sudo vi /etc/hosts

这里一定注意：修改后可以用 ping 命令尝试 ping hostname，必须返回对应的真正 IP 地址才行，也就是 Hosts 文件中不能包含类似 hostname 和 127.0.0.1 的对应记录，配置后如下，注意其中”127.0.1.1 Ubuntu1604Bob2”这行被我注释了，否则开启 Cluster 服务的时候可能会有问题：

Ubuntu 上配置 SQL Server Always On Availability Group

如果不注释，ping hostname 的返回结果是 127.0.1.1，注释后返回的是真正 IP：

Ubuntu 上配置 SQL Server Always On Availability Group

需要返回真正 IP 后期配置才好使。

另外可以用这个命令查看当前 server 的 IP：

sudo ip addr show

b) 在所有节点 SQL Server 上开启 Always On Availability Group 功能并重启服务：

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

c) 在所有节点上执行 SQL 语句开启 AlwaysOn_health 事件会话以方便诊断问题：

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

更多关于 Event Session 信息可以参考：AlwaysOn Extended Events。

d) 创建 db mirroring endpoint 使用的用户：

CREATE LOGIN dbm_login WITH PASSWORD = '**<Your Password>**';
CREATE USER dbm_user FOR LOGIN dbm_login;

e) 创建证书：

Linux 上的 SQL Server Mirroring Endpoint 是用证书去认证通信的。下面的命令创建一个 master key 和证书并备份。连接到 Primary 端 SQL Server 并执行如下命令：

Ubuntu 上配置 SQL Server Always On Availability Group

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>**'
       );

f) 把证书的备份复制到所有的非 Primary 节点上，同时使用它创建证书：

先在 Primary 节点上执行如下命令复制证书的备份到其它节点上：

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

Note：如果遇到 Permission denied，可以使用 sz 和 rz 命令通过主机来传输文件。

再在目的端 Secondary 节点上执行如下命令给用户 mssql 添加足够的权限：

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

最后在目的端 Secondary 节点上利用备份的证书创建证书：

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '**<Master_Key_Password>**';
CREATE CERTIFICATE dbm_certificate  
    AUTHORIZATION dbm_user
    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>**'
            );

g) 在所有节点上创建 database mirroring endpoint：

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_IP = (0.0.0.0), LISTENER_PORT = **<5022>**)
    FOR DATA_MIRRORING (ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
        );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [dbm_login];

Note：这里 Listener IP 暂时不能修改，只能是 0.0.0.0，目前有 BUG，未来可能会修复。

h) 在 Primary 节点上创建 AG：

CREATE AVAILABILITY GROUP [UbuntuAG]
    WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL)
    FOR REPLICA ON
        N'**<node1>**'
        WITH (ENDPOINT_URL = N'tcp://**<node1>**:**<5022>**',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = EXTERNAL,
            SEEDING_MODE = AUTOMATIC
            ),
        N'**<node2>**'
        WITH (ENDPOINT_URL = N'tcp://**<node2>**:**<5022>**',
            AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
            FAILOVER_MODE = EXTERNAL,
            SEEDING_MODE = AUTOMATIC
            );
ALTER AVAILABILITY GROUP [UbuntuAG] GRANT CREATE ANY DATABASE;

Note：执行过程中可能会出现这个警告”Attempt to access non-existent or uninitialized availability group with ID”，暂时忽略即可，未来版本可能会修复。

下图中 UbuntuAG2 是新创建的 AG，Secondary 节点还处于 OFFLINE 状态：

Ubuntu 上配置 SQL Server Always On Availability Group

i) 把其它 Secondary 节点加入到 AG 中：

ALTER AVAILABILITY GROUP [UbuntuAG] JOIN WITH (CLUSTER_TYPE = EXTERNAL);
ALTER AVAILABILITY GROUP [UbuntuAG] GRANT CREATE ANY DATABASE;

下图为添加完节点后的状态：

Ubuntu 上配置 SQL Server Always On Availability Group

j) 测试：创建一个 DB 并加入到刚刚创建的 AG 中：

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

k) 验证：在 Secondary 端查看 DB 是否已经成功同步过去了：

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;

Ubuntu 上配置 SQL Server Always On Availability Group

这时，一个简单的 AG 就创建好了，但是它不能提供高可用性和灾难恢复功能，必须配置一个 Cluster 技术才能好使。如果上述 h)和 i)步骤的 TSQL 更换成以下两个，则创建出来的就是 read-scale 类型的 AG。

创建 AG 命令：

CREATE AVAILABILITY GROUP [UbuntuAG]
    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 [UbuntuAG] GRANT CREATE ANY DATABASE;

把 Secondary 节点加到 AG 中命令：

ALTER AVAILABILITY GROUP [UbuntuAG] JOIN WITH (CLUSTER_TYPE = NONE);
ALTER AVAILABILITY GROUP [UbuntuAG] GRANT CREATE ANY DATABASE;

Note：这时的 AG 是没有 Listener 的，目前版本也暂时无法创建 Listener。

具体步骤如下：

a) 在所有的 Cluster 节点上安装和配置 Pacemaker：

先设置防火墙允许相关端口通过（包括 Pacemaker high-availability service、SQL Server Instance 和 Availability Group Endpoint），

sudo ufw allow 2224/tcp
sudo ufw allow 3121/tcp
sudo ufw allow 21064/tcp
sudo ufw allow 5405/udp
sudo ufw allow 1433/tcp # Replace with TDS endpoint
sudo ufw allow 5022/tcp # Replace with DATA_MIRRORING endpoint
sudo ufw reload

或者也可以直接禁用防火墙：

sudo ufw disable

在所有节点上安装 Pacemaker 软件包：

sudo apt-get install pacemaker pcs fence-agents resource-agents

设置 Pacemaker 和 Corosync 软件包在安装时创建的默认用��的密码，需保证所有节点上密码一样：

sudo passwd hacluster

b) 启用并开启 pcsd 和 Pacemaker 服务：

sudo systemctl enable pcsd
sudo systemctl start pcsd
sudo systemctl enable pacemaker

执行过程中可能出现这个错误”pacemaker Default-Start contains no runlevels, aborting.”，可以暂时忽略。

c) 创建 Cluster 并启动：

首先为了防止有 Cluster 的残余配置文件影响后期搭建，可以先执行如下命令删除已经存在的 Cluster：

sudo pcs cluster destroy # On all nodes
sudo systemctl enable pacemaker

然后创建并配置 Cluster：

sudo pcs cluster auth **<nodeName1>** **<nodeName2>**  -u hacluster -p **<password for hacluster>**
sudo pcs cluster setup --name **<clusterName>** **<nodeName1>** **<nodeName2…>**
sudo pcs cluster start --all

这时可能会出现这个错误”Job for corosync.service failed because the control process exited with error code. See “systemctl status corosync.service” and “journalctl -xe” for details.”诊断解决方法如下：

根据 2 -a)中的描述查看是否有问题；
使用如下命令查看配置文件中的 Log 路径是什么。

vi /etc/corosync/corosync.conf

Ubuntu 上配置 SQL Server Always On Availability Group

查看相关文件是否存在，如不存在，则创建相关文件，假设上图中 logfile 路径为 /var/log/cluster/corosync.log 同时该文件还不存在，则执行如下命令，

sudo mkdir /var/log/cluster
sudo chmod 777 /var/log/cluster
sudo echo >> /var/log/cluster/corosync.log

d) 配置隔离：STONITH。目前测试环境为了简单，暂时不配置了，以后会更新。正常来说生产环境需要一个 fencing agent 去隔离资源，关于支持信息请参考这里：Support Policies for RHEL High Availability Clusters – Virtualization Platforms。

另外我们这里先执行以下命令禁用隔离：

sudo pcs property set stonith-enabled=false

e) 设置 start-failure-is-fatal 为 false：

pcs property set start-failure-is-fatal=false

默认值是 true，当为 true 的时候，如果 Cluster 第一次启动资源失败，在自动 Failover 操作后，需要用户手动清空资源启动失败的数量记录，使用这个命令重置资源配置：

pcs resource cleanup <resourceName>

具体步骤如下：

a) 在所有节点上安装与 Pacemaker 集成的 SQL Server 资源包：

sudo apt-get install mssql-server-ha

b) 在所有节点上创建 Pacemaker 用的 SQL Server 登录用户：

USE [master]
GO
CREATE LOGIN [pacemakerLogin] with PASSWORD= N'<Your Password>'
ALTER SERVER ROLE [sysadmin] ADD MEMBER [pacemakerLogin]

也可以不给 sysadmin 权限，给上如下足够的权限即可：

GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::UbuntuAG TO pacemakerLogin

c) 在所有节点上，保存 SQL Server Login 的信息：

echo 'pacemakerLogin' >> ~/pacemaker-passwd
echo '<Your Password>' >> ~/pacemaker-passwd
sudo mv ~/pacemaker-passwd /var/opt/mssql/secrets/passwd
sudo chown root:root /var/opt/mssql/secrets/passwd
sudo chmod 400 /var/opt/mssql/secrets/passwd # Only readable by root

d) 在 Cluster 中 Primary 节点上创建 AG 的资源：

sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=UbuntuAG --master meta notify=true

e) 在 Cluster 中 Primary 节点上创建虚拟 IP 资源：

sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=**<10.2.38.204>**

f) 配置 Cluster 资源的依赖关系和启动顺序：

sudo pcs constraint colocation add virtualip ag_cluster-master INFINITY with-rsc-role=Master
sudo pcs constraint order promote ag_cluster-master then start virtualip

g) 最后查看 Cluster 状态如下：

sudo pcs status

Ubuntu 上配置 SQL Server Always On Availability Group

可以用虚拟 IP（10.2.38.204）访问这个 AG：

Ubuntu 上配置 SQL Server Always On Availability Group

至此，Ubuntu 上的 Cluster 管理的 SQL Server Always On Availability Group 就搭建完成了。

Note：

当把 AG 加入到 Cluster 中作为一个资源的时候，就不能再使用 TSQL 去 failover AG 了。SQL Server 服务端是不知道 Cluster 的存在的，整个系统是通过 Linux Cluster 来控制的，在 Ubuntu 和 RHEL 中用 pcs 命令，在 SLES 中用 crm 命令。
全部配置完成后，可以使用虚拟 IP 去访问整个 AG，这时可以在 DNS 中手动注册一个 Listener 名字指向这个虚拟 IP，就可以当成 Windows 中的 AG Listener 使用了。
SQL Server 2017 CTP 1.4 中新引入了一个 sequence_number 的概念防止数据丢失，详细参考 Understand SQL Server resource agent for pacemaker（https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-ubuntu）。

参考链接：