因为项目需要在私网环境搭建 SqlServer 主备，并且需要实现自动故障转移，经过近段时间的研究终于在模拟环境构建成功，在此记录一下整体的搭建过程。
一、实验环境
1. Ubuntu 20.04
名为 nethis-master,nethis-slave 的两台虚拟机
配置双机互信
双机 SqlSerer 2019 安装环节(略)
数据库管理工具直接使用 SSMS
二、SQLServer 证书及权限配置
1.启用 Always On Availabilit Groups 功能
sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1sudo systemctl restart mssql-server
复制代码
启用成功后将给你在服务器的属性中看到，如下所示:
2.在所有服务器安装集群资源代理程序 mssql-server-ha
它是 Pacemaker 和 SqlServer 之间的桥梁，也就是 mssql-server-ha 这个包儿实现了 packmaker 的一组接口
sudo apt-get install mssql-server-ha
复制代码
3.更新防火墙
sudo ufw allow 5022/tcpsudo ufw reload
复制代码
4.创建数据库主密钥
4.1 在 nethis-master 执行
use mastergo
— 数据库主密钥是用于保护数据库中存在的证书和非对称密钥的私钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'xxxxx';GO
--创建用于加密可用性终结点(endpoint)的证书CREATE CERTIFICATE nethis_master_cert WITH SUBJECT = 'ag1 Certificate';GO--备份，之后将需要把证书传给其它服务器BACKUP CERTIFICATE nethis_master_certTO FILE = '/var/opt/mssql/data/nethis_master_cert.cer';GO
--使用证书创建AG的终结点并进行身份验证CREATE ENDPOINT AGEPSTATE = STARTEDAS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL)FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE nethis_master_cert, ROLE = ALL);GO
复制代码
4.2 在 nethis-slave 执行
use mastergo
— 数据库主密钥是用于保护数据库中存在的证书和非堆成密钥的私钥CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yutang666';GO
--创建用于加密可用性终结点(endpoint)的证书CREATE CERTIFICATE nethis_slave_cert WITH SUBJECT = 'ag2 Certificate';GO--备份，之后将需要把证书传给其它服务器BACKUP CERTIFICATE nethis_slave_certTO FILE = '/var/opt/mssql/data/nethis_slave_cert.cer';GO
--使用证书创建AG的终结点并进行身份验证CREATE ENDPOINT AGEPSTATE = STARTEDAS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL)FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE nethis_slave_cert, ROLE = ALL);GO
复制代码
5.删除数据库主密钥
在想重置的场景下想删除数据库主密钥，需要先删除终结点和证书，然后再执行
6.将证书互传给对方
使用 scp 命令即可将证书传递到/var/opt/mssql/data/目录下
7.命令恢复证书并给服务端点分配权限
-- 在nethis-master上执行
--创建供nethis-slave可登录的实例级账号--实例级连接SqlServer会使用下面的证书做验证保证安全CREATE LOGIN slave_login WITH PASSWORD = 'yutang666';CREATE USER slave_user for LOGIN slave_login;
--在nethis-master上还原nethis-slave上生成的证书CREATE CERTIFICATE nethis_slave_cert AUTHORIZATION slave_user FROM FILE = '/var/opt/mssql/data/nethis_slave_cert.cer';go
--授予上面创建的用户名为slave_login有使用名称为AGEP的终结点的权限GRANT CONNECT ON ENDPOINT::AGEP TO slave_login;go
复制代码
创建的服务端点在这里
-- 在nethis-slave上执行
--创建供nethis-master可登录的实例级账号CREATE LOGIN master_login WITH PASSWORD = 'yutang666';CREATE USER master_user for LOGIN master_login;go
--在nethis-slave上还原nethis-master的证书CREATE CERTIFICATE nethis_master_cert AUTHORIZATION master_user FROM FILE = '/var/opt/mssql/data/nethis_master_cert.cer';go
--授予上面创建的用户名为master_login有使用名称为AGEP的终结点的权限GRANT CONNECT ON ENDPOINT::AGEP TO master_login;go
复制代码
三、使用 SSMG 创建可用性组
1.步骤 1
1.1 external 选项的意思是可用性组由外部的 Packmaker 集群来管理，来实现高可用性和灾难恢复
1.2 none 当可用性组不管在不由集群技术管理的 SQL Server 的实例上使用的时候，可实现的场景是
读取的缩放
1.3 要勾选数据库级别运行状态检测
2.步骤 2
端点要确认是 tcp URL 可通的端点
创建可用性组侦听器
3.步骤 3
数据库同步选择自动种子设定即可，使用自动种子设定的前置条件是 SqlServer 上的安装和使用目录都一致
4.步骤 4
可用性组创建成功了
执行此查询
select * from sys.availability_group_listener_ip_addressesgo
复制代码
如看到 state_desc = OFFLINE 的话，只能是表明可用性组是创建成功了，但现在还是无法进行故障转移
要通过侦听器访问可用性组，必须在 Linux 群集管理器上注册它，会需要在群集上创建一个群集的资源。
四、创建 Pacemaker 集群
4.1 安装
sudo apt-get install pacemaker pcs fence-agents resource-agents -y
复制代码
0.pcs 集群管理命令
1.pacemaker 核心群集组件,可以在群集计算机之间进行协调.
2.fence-agents 脚本/功能，有助于隔离节点并在遇到问题时对其进行处理
3.resource-agents 提供特定功能，以便应用程序可以与 Pacemaker 集成
4.2 设置密码
为安装 Pacemaker 和 Corosync 包时创建的默认用户设置密码。 在所有节点上使用相同的密码
4.3 设置 pcds 启动
sudo systemctl start pcsdsudo systemctl start pacemaker
sudo systemctl enable pcsdsudo systemctl enable pacemaker
复制代码
4.4 更新防火墙规则
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
sudo ufw reload
复制代码
4.5 创建集群
1.在安装pcs的时候会默认启动一个集群，因此需要先移除默认的集群配置(要注意对corosync.conf文件的备份) sudo pcs cluster destroy sudo systemctl enable pacemaker
2.在任意一台服务器上执行多个节点之间设置身份验证 sudo pcs host auth nethis-master nethis-slave -u hacluster
输出 如下内容即表示成功 nethis-slave: Authorized nethis-master: Authorized
3.创建Linux集群,名称为nethiscluster的集群,创建后立刻启动并设置开机启动 sudo pcs cluster setup nethiscluster nethis-master nethis-slave --start —enable
4.启动，停止服务的命令 sudo pcs cluster start --all sudo pcs cluster stop —all sudo pcs cluster enable --all
5.#由于节点级别隔离配置在很大程度上取决于硬件及软件环境; #同时会为配置虚拟IP的时候构成影响，因此在此要禁用此配置 sudo pcs property set stonith-enabled=false
6.集群根据资源的当前故障计数和迁移阈值决定是否再次尝试在同一节点上启动 pcs property set start-failure-is-fatal=false
7.为Pacemaker创建SQL Server登录账号，授予可用组nethis-ag权限 CREATE LOGIN PMLogin WITH PASSWORD ='yutang666'; GO
GRANT VIEW SERVER STATE TO PMLogin; GO
GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::"nethis-ag" TO PMLogin; GO
//在所有服务器执行 vim /var/opt/mssql/secrets/passwd，前两行写上 PMLogin iHealth123
#保存退出 #执行 chmod 400 /var/opt/mssql/secrets/passwd
#其它的服务器重复执行上述给Pacemaker的登录授权
复制代码
4.6 设置侦听器
上面已经配置好了 AG 和 Pacemaker，但是还不能进行故障转移为了设置高可用，需要添加侦听器，用侦听器去访问数据库。群集资源代理程序 mssql-server-ha 是 Pacemaker 和 SQL Server 之间的接口。现在将创建和配置 SQL Server Always On Availability Group 的过程，并将相应的侦听器作为群集资源。
sudo pcs resource create ag_cluster ocf:mssql:ag ag_name=nethis-ag meta failure-timeout=30s promotable notify=true
#ag_cluster: Pacemaker集群资源的名称#ocs:mssql:ag 由mssql-server-ha提供的Open Cluster Framework(OCF)资源代理的名称,如果没安装这个包就会#ag_name=nethis-ag 指定AG的可用性组的名称
复制代码
要是 mssql-server-ha 就会爆这个错误，这个时候再更新一下 mssql-server-ha 的包就好了
4.6 为 AG 侦听器创建虚拟 IP 地址资源
sudo pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=192.168.2.88 cidr_netmask=24 op monitor interval=30s
#ocf:heartbeat:IPaddr2: 管理虚拟IPv4地址的Open Cluster Framework（OCF）资源代理的名称#ip AlwaysOn AG 的可用性组的侦听器IP#cidr_netmask AlwaysOn AG 的可用性组的侦听器子网掩码
复制代码
4.7 将 AG 资源配置为在与虚拟 IP 地址资源相同的计算机上运行
sudo pcs constraint colocation add virtualip with master ag_cluster-clone INFINITY with-rsc-role=Master
#ag_cluster-clone AG资源的克隆别名
#INFINITY: 分配给资源约束的分数;这意味着约束是必需的
#with-rsc-role=Master: 约束的附加属性;这意味着此约束与主克隆（或Always On Availability Group主副本）相关联
#由于Always On Availability Group侦听器名称只能将客户端应用程序重定向到主副本，因此可用性组和侦听器名称必须始终在同一群集节点中运行
复制代码
4.8 配置群集资源应该开始/停止的顺序
sudo pcs constraint order promote ag_cluster-clone then start virtualip
#promote: 约束行为，将资源从slave 提升为 master 资源#ag_cluster-clone AG资源的克隆别名#start: 初步操作完成后下一步的动作
复制代码
(see https://docs.microsoft.com/zh-cn/sql/linux/sql-server-linux-availability-group-cluster-ubuntu?view=sql-server-ver15)
五、查看状态
输入如下 pcs status，看到如下输出就 OK 了
使用如下 SQL 来查询 AG 得状态
use mastergo
SELECT @@SERVERNAME as replica_name, @@VERSION as SqlServerVersion, host_platform, host_distribution, host_releaseFROM sys.dm_os_host_infoGO
SELECT a.name as AG_Name, a.cluster_type_desc,b.dns_name,c.ip_address, c.ip_subnet_maskFROM sys.availability_groups aINNER JOIN sys.availability_group_listeners b ON a.group_id=b.group_idINNER JOIN sys.availability_group_listener_ip_addresses c ON b.listener_id=c.listener_idGO
复制代码
六、测试故障转移的状态
6.1 测试自动故障转移
把 master 网路断开，在 nethis-slave 输入
一直 ping 侦听器可以看到只丢了 3 个包就实现了自动故障转移
再把 master 网络打开，可以看到
pcs 记录了发生故障转移的时间，和目前集群的状态
6.2 测试手动故障转移
pcs resource move ag_cluster-clone nethis-master --master
pcs resource move ag_cluster-clone nethis-slave --master
#ag_cluster-clone AG资源的克隆别名
复制代码
手动故障转移成功的截图
七、自动故障转移所需的条件
1.存在自动故障转移集: 此自动故障转移集由主要副本和次要副本（自动故障转移目标）构成，主要副本和次要副本都配置为同步提交模式并且设置为自动故障转移。如果主要副本设置为手动故障转移，即使次要副本设置为自动故障转移，也无法发生自动故障转移
2.自动故障转移目标具有正常运行的同步状态（这指示故障转移目标上的每个辅助数据库都与其相应的主数据库同步）
3.要设置故障转移模式为“自动”的前提是可用性模式是“同步提交”。
八、不能进行自动故障转移的情况
在数据库级别，诸如因数据文件丢失而使数据库成为可疑数据库、删除数据库或事务日志损坏之类的数据库问题不会导致可用性组进行故障转移
评论