在Linux中安装MSSQL 2017 Always On Availability Group
Tags： Always OnAvailability GroupLinuxMSSQLMSSQL2017可用性组
👉 本文共约8317个字，系统预计阅读时间或需32分钟。
本页目录 隐藏
SQL Server 2017 最多支持9个副本（1个主副本和8个辅助副本），其中最多支持3个自动故障转移和3个同步提交的可用性副本。Linux 环境的AlwaysOn AG 不依赖于 Active Directory，可以在没有 Windows Server故障转移群集（WSFC）和 Pacemaker（Linux集群资源管理器）情况下搭建 Always On Availability Groups。以前是先配置 WSFC 再配置 Always On AG。Linux 环境下可以先配置 Always On AG ，再配置群集资源管理器。
Linux 上安装 SQL Server AG 步骤：
- 在 Linux 群集节点上安装和配置 SQL Server 2017
- 创建 SQL Server 2017 Always On Availability Group
- 配置 Pacemaker - Linux 集群资源管理器
- 将 SQL Server 2017 Always On Availability Group 添加为群集中的资源
相关包目录：https://packages.microsoft.com/
- 搭建MSSQL 2008R2高可用之发布订阅（数据库复制）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhifabudingyueshujukufuzhi.html
- 搭建MSSQL 2008R2高可用之日志传输（Log Shipping）：https://www.xmmup.com/dajianmssql-2008r2gaokeyongzhirizhichuanshulog-shipping.html
- 在非域环境下搭建MSSQL 2016高可用之镜像传输：https://www.xmmup.com/zaifeiyuhuanjingxiadajianmssql-2016gaokeyongzhijingxiangchuanshu.html
- 在Windows非域环境中安装使用MSSQL 2016 Always On Availability Group：https://www.xmmup.com/zaiwindowsfeiyuhuanjingzhonganzhuangshiyongmssql-2016-always-on-availability-group.html
- 在Windows域环境中安装MSSQL 2016 Always On Availability Group：https://www.xmmup.com/zaiwindowsyuhuanjingzhonganzhuangmssql-2016-always-on-availability-group.html
一、架构
安装配置环境
现在创建三台CentOS 7 的服务器： 172.72.9.100，172.72.9.101，172.72.9.102
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | -- 创建专用网络 docker network create --subnet=172.72.9.0/24 mssql-network docker rm -f lhrmssql100 docker run -d --name lhrmssql100 -h lhrmssql100 \ --net=mssql-network --ip 172.72.9.100 \ -p 61100:1433 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker rm -f lhrmssql101 docker run -d --name lhrmssql101 -h lhrmssql101 \ --net=mssql-network --ip 172.72.9.101 \ -p 61101:1433 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init docker rm -f lhrmssql102 docker run -d --name lhrmssql102 -h lhrmssql102 \ --net=mssql-network --ip 172.72.9.102 \ -p 61102:1433 \ -v /sys/fs/cgroup:/sys/fs/cgroup \ --privileged=true lhrbest/lhrcentos76:8.5 \ /usr/sbin/init |
安装MSSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | cat >> /etc/hosts <<"EOF" 172.72.9.100 lhrmssql100 172.72.9.101 lhrmssql101 172.72.9.102 lhrmssql102 EOF # 下载 SQL Server repository curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo # 下载并安装 SQL Server（211MB） yum install -y mssql-server systemctl status mssql-server # 启动配置（包括sa密码,此处设置为：lhr@xxt123） /opt/mssql/bin/mssql-conf setup # 客户端 curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/7/prod.repo yum install -y mssql-tools unixODBC-devel mssql-cli echo "export PATH=/opt/mssql-tools/bin/:$PATH" >> /root/.bashrc source /root/.bashrc sqlcmd -S 127.0.0.1 -U SA -P 'lhr@xxt123' exec sp_helpdb; GO |
配置过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | [root@lhrmssql101 /]# /opt/mssql/bin/mssql-conf setup Choose an edition of SQL Server: 1) Evaluation (free, no production use rights, 180-day limit) 2) Developer (free, no production use rights) 3) Express (free) 4) Web (PAID) 5) Standard (PAID) 6) Enterprise (PAID) 7) Enterprise Core (PAID) 8) I bought a license through a retail sales channel and have a product key to enter. Details about editions can be found at https://go.microsoft.com/fwlink/?LinkId=852748&clcid=0x409 Use of PAID editions of this software requires separate licensing through a Microsoft Volume Licensing program. By choosing a PAID edition, you are verifying that you have the appropriate number of licenses in place to install and run this software. Enter your edition(1-8): 6 The license terms for this product can be found in /usr/share/doc/mssql-server or downloaded from: https://go.microsoft.com/fwlink/?LinkId=855862&clcid=0x409 The privacy statement can be viewed at: https://go.microsoft.com/fwlink/?LinkId=853010&clcid=0x409 Do you accept the license terms? [Yes/No]:yes Enter the SQL Server system administrator password: Confirm the SQL Server system administrator password: Configuring SQL Server... The licensing PID was successfully processed. The new edition is [Enterprise Edition]. ForceFlush is enabled for this instance. ForceFlush feature is enabled for log durability. Created symlink from /etc/systemd/system/multi-user.target.wants/mssql-server.service to /usr/lib/systemd/system/mssql-server.service. Setup has completed successfully. SQL Server is now starting. |
二、SQL Server 证书及权限配置
既然没有了域认证，需要配置证书认证，步骤：
- 启用SQL Server Always On Availability Groups功能
- 在所有服务器上为SQL Server Always On Availability Groups安装Linux资源代理
- 在所有Linux服务器启用防火墙上的Always On Availability Group端点端口
- 在主副本上创建数据库主密钥
- 创建将用于加密可用性组端点的证书
- 使用证书为主副本创建Always On Availability Group端点进行身份验证
- 将证书导出到文件
- 将证书文件复制到辅助副本
- 授予证书文件的SQL Server帐户权限
- 在主副本上创建登录账号以供辅助副本使用
- 为登录账号创建用户
- 将步骤5中创建的证书与用户关联
- 授予登录账号的CONNECT权限
1.所有服务器：启用 hadr 功能（Always On Availability Groups）
在托管 SQL Server 实例的每个节点上启用 AlwaysOn 可用性组。 然后重新启动 mssql-server。 运行以下脚本：
1 2 | /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 systemctl restart mssql-server |
启用 AlwaysOn_health 事件会话
可选择性地启用 AlwaysOn 可用性组的扩展事件，以便在对可用性组进行故障排除时帮助诊断根本原因。 在每个 SQL Server 实例上运行以下命令：
1 2 | ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE=ON); GO |
2.所有服务器：为 SQL Server AlwaysOn AG 安装Linux资源代理
群集资源代理程序 mssql-server-ha 是 Pacemaker 和 SQL Server 之间的接口
1 2 | yum install -y mssql-server-ha yum info mssql-server-ha |
结果：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | [root@lhrmssql100 /]# yum info mssql-server-ha Loaded plugins: fastestmirror, ovl Loading mirror speeds from cached hostfile * base: mirrors.aliyun.com * extras: mirrors.aliyun.com * updates: mirrors.aliyun.com Installed Packages Name : mssql-server-ha Arch : x86_64 Version : 14.0.3421.10 Release : 2 Size : 12 M Repo : installed From repo : packages-microsoft-com-mssql-server-2017 Summary : High Availability support for Microsoft SQL Server Relational Database Engine License : Commercial Description : The mssql-server-ha package contains high availability support for the Microsoft SQL Server Relational Database Engine. |
3.所有服务器：启用防火墙上的 AlwaysOn AG 端点默认端口5022 （或关闭防火墙！）
1 2 3 4 | systemctl status firewalld.service firewall-cmd --zone=public --add-port=5022/tcp --permanent firewall-cmd --reload |
跑脚本完成第4-7步
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 | ---------------------------------------------------------------------- -- #【 172.72.9.100 】 ---------------------------------------------------------------------- USE master GO -- 4. 创建数据库主密钥 -- （数据库主密钥是对称密钥，用于保护数据库中存在的证书和非对称密钥的私钥） CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO -- 5. 创建将用于加密可用性组端点的证书 CREATE CERTIFICATE LINUXHA_SQLAG1_cert WITH SUBJECT = 'server172.72.9.100 certificate for Availability Group' GO -- 6. 主副本：使用证书创建 AlwaysOn AG 端点并进行身份验证 CREATE ENDPOINT Endpoint_AvailabilityGroup STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG1_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO -- 7. 主副本：将证书导出到文件 BACKUP CERTIFICATE LINUXHA_SQLAG1_cert TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer'; GO ---------------------------------------------------------------------- -- #【 172.72.9.101 】 ---------------------------------------------------------------------- USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO CREATE CERTIFICATE LINUXHA_SQLAG2_cert WITH SUBJECT = 'server172.72.9.101 certificate for Availability Group' GO CREATE ENDPOINT Endpoint_AvailabilityGroup STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG2_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO BACKUP CERTIFICATE LINUXHA_SQLAG2_cert TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer'; GO ---------------------------------------------------------------------- -- 【 server172.72.9.102 】 ---------------------------------------------------------------------- USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master@123456'; GO CREATE CERTIFICATE LINUXHA_SQLAG3_cert WITH SUBJECT = 'server113 certificate for Availability Group' GO CREATE ENDPOINT Endpoint_AvailabilityGroup STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL ) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE LINUXHA_SQLAG3_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL ); GO BACKUP CERTIFICATE LINUXHA_SQLAG3_cert TO FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer'; GO |
8.各服务器上的证书相互传给其他服务器
1 2 3 4 5 6 7 8 9 10 11 | # lhrmssql100(172.72.9.100) 上执行 scp /var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer root@lhrmssql101:/var/opt/mssql/data/ scp /var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer root@lhrmssql102:/var/opt/mssql/data/ # lhrmssql101(172.72.9.101) 上执行 scp /var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer root@lhrmssql100:/var/opt/mssql/data/ scp /var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer root@lhrmssql102:/var/opt/mssql/data/ # lhrmssql102(172.72.9.102) 上执行 scp /var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer root@lhrmssql100:/var/opt/mssql/data/ scp /var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer root@lhrmssql101:/var/opt/mssql/data/ |
3台查询：
1 2 3 4 | [root@lhrmssql102 /]# ll /var/opt/mssql/data/*.cer -rw-r----- 1 root root 767 Dec 17 10:47 /var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer -rw-r----- 1 root root 767 Dec 17 10:48 /var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer -rw-rw---- 1 mssql mssql 749 Dec 17 10:37 /var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer |
9.所有服务器：授予证书文件所有者权限
1 2 | chown mssql:mssql /var/opt/mssql/data/LINUXHA_SQLAG* ll /var/opt/mssql/data/LINUXHA_SQLAG* |
10.各实例证书还原及账号授权
SSMS 连接到任意一个实例，查询选项启用[SQLCMD模式]，批量执行以下脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 | ---------------------------------------------------------------------- --#【 172.72.9.100 】 ---------------------------------------------------------------------- USE master GO -- 11 创建登录账号以供辅助副本使用 CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456'; GO -- 12. 为登录账号创建用户 CREATE USER login_ag FOR LOGIN login_ag GO -- 13. 创建其他副本的证书 CREATE CERTIFICATE LINUXHA_SQLAG2_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer' GO CREATE CERTIFICATE LINUXHA_SQLAG3_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer' GO -- 14. 授予登录账号的CONNECT权限 GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup TO [login_ag]; GO ---------------------------------------------------------------------- --#【 172.72.9.101 副本】 ---------------------------------------------------------------------- USE master GO -- 11 创建登录账号以供辅助副本使用 CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456'; GO -- 12. 为登录账号创建用户 CREATE USER login_ag FOR LOGIN login_ag GO -- 13. 创建其他副本的证书 CREATE CERTIFICATE LINUXHA_SQLAG1_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer' GO CREATE CERTIFICATE LINUXHA_SQLAG3_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG3_cert.cer' GO -- 14. 授予登录账号的CONNECT权限 GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup TO [login_ag]; GO ---------------------------------------------------------------------- -- #【 172.72.9.102 副本】 ---------------------------------------------------------------------- USE master GO -- 11 创建登录账号以供辅助副本使用 CREATE LOGIN login_ag WITH PASSWORD = 'AG@123456'; GO -- 12. 为登录账号创建用户 CREATE USER login_ag FOR LOGIN login_ag GO -- 13. 创建其他副本的证书 CREATE CERTIFICATE LINUXHA_SQLAG1_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG1_cert.cer' GO CREATE CERTIFICATE LINUXHA_SQLAG2_cert AUTHORIZATION login_ag FROM FILE = '/var/opt/mssql/data/LINUXHA_SQLAG2_cert.cer' GO -- 14. 授予登录账号的CONNECT权限 GRANT CONNECT ON ENDPOINT::Endpoint_AvailabilityGroup TO [login_ag]; GO |
三、AG配置
现可在 Windows 上使用 SSMS 连接 Linux 上的 SQL Server 配置 AlwaysOn AG。
脚本运行
创建AG
创建具有三个同步副本的 AG，只在lhrmssql100上运行：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 | SELECT * FROM sys.dm_xe_sessions; ALTER ENDPOINT [Endpoint_AvailabilityGroup] STATE = STARTED ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON); ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START; CREATE AVAILABILITY GROUP [LINUX_SQLAG] WITH (DB_FAILOVER = ON, CLUSTER_TYPE = EXTERNAL, AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DTC_SUPPORT = NONE, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0 ) FOR REPLICA ON N'lhrmssql100' WITH ( ENDPOINT_URL = N'tcp://lhrmssql100:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE ( ALLOW_CONNECTIONS = ALL ), SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) ), N'lhrmssql101' WITH ( ENDPOINT_URL = N'tcp://lhrmssql101:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE ( ALLOW_CONNECTIONS = ALL ), SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) ), N'lhrmssql102' WITH( ENDPOINT_URL = N'tcp://lhrmssql102:5022', AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = EXTERNAL, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE ( ALLOW_CONNECTIONS = ALL ), SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL ) ); ALTER AVAILABILITY GROUP [LINUX_SQLAG] GRANT CREATE ANY DATABASE; /* CREATE AVAILABILITY GROUP [LINUX_SQLAG] WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY, DB_FAILOVER = ON, DTC_SUPPORT = NONE, CLUSTER_TYPE = EXTERNAL, REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0) FOR DATABASE [Demo] REPLICA ON N'server111' WITH ( ENDPOINT_URL = N'TCP://server111:5022', FAILOVER_MODE = EXTERNAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'server112')), SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://server111:1433', ALLOW_CONNECTIONS = ALL) ), N'server112' WITH ( ENDPOINT_URL = N'TCP://server112:5022', FAILOVER_MODE = EXTERNAL, AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = (N'server111')), SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://server112:1433', ALLOW_CONNECTIONS = ALL) ), N'server113' WITH ( ENDPOINT_URL = N'TCP://server113:5022', FAILOVER_MODE = EXTERNAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, PRIMARY_ROLE(READ_ONLY_ROUTING_LIST = ((N'server111',N'server112'),N'server113')), SECONDARY_ROLE(READ_ONLY_ROUTING_URL = N'TCP://server113:1433', ALLOW_CONNECTIONS = ALL) ); GO */ USE [master] GO ALTER AVAILABILITY GROUP [LINUX_SQLAG] ADD LISTENER N'LINUX_LSNAG' ( WITH IP ((N'172.72.9.105', N'255.255.255.0')), PORT=1433 ); GO |
将次要副本联接到 AG
在lhrmssql101和lhrmssql102上运行：
1 2 3 | USE MASTER; ALTER AVAILABILITY GROUP [LINUX_SQLAG] JOIN WITH (CLUSTER_TYPE = EXTERNAL); ALTER AVAILABILITY GROUP [LINUX_SQLAG] GRANT CREATE ANY DATABASE; |
运行完成后：
将数据库添加到可用性组
确保添加到可用性组的数据库处于完全恢复模式，并具有有效的日志备份。 如果是测试数据库或新建的数据库，请执行数据库备份。
只在主服务器上运行：
1 2 3 4 5 6 7 8 | mkdir /bk chown mssql.mssql /bk create database lhrdb; ALTER DATABASE [lhrdb] SET RECOVERY FULL; backup database lhrdb TO DISK = N'/bk/lhrdb_full.bak' with format,stats=5,compression; ALTER AVAILABILITY GROUP [LINUX_SQLAG] ADD DATABASE [lhrdb]; |
验证是否已在辅助服务器上创建了数据库：
1 2 3 | SELECT * FROM sys.databases WHERE name = 'lhrdb'; GO SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states; |
1 | select * from sys. availability_group_listener_ip_addresses; |
侦听器状态：
图形运行
1. 在节点 lhrmssql100 实例上创建可用性组，名为 LINUX_SQLAG
- Windows Server 故障转移群集：当可用性组托管在属于 Windows Server 故障转移群集的 SQL Server 的实例上时使用，以实现高可用性和灾难恢复。 适用于所有受支持的 SQL Server 版本。
- EXTERNAL ：当可用性组托管在由外部群集技术（例如 Linux 上的 Pacemaker）管理的 SQL Server 的实例上时使用，以实现高可用性和灾难恢复。 适用于 SQL Server 2017 (14.x) 及更高版本。
- NONE ：当可用性组托管在不由群集技术管理的 SQL Server 的实例上时使用，以实现读取缩放和负载均衡。 适用于 SQL Server 2017 (14.x) 及更高版本。
选择 EXTERNAL，Linux 环境下，创建并配置Always On Availability Group后，将安装和配置Pacemaker。“数据库级别运行状况检测” 勾选此框，为可用性组启用数据库级别运行状况检测 (DB_FAILOVER)。 数据库运行状况检测会说明数据库何时不再处于联机状态、何时出错以及何时触发可用性组的自动故障转移。（更多参考：Always On 可用性组的“指定可用性组选项”页）
2. 选择可用性数据库
先创建数据库，然后添加到可用性组里。
1 2 3 4 5 | chown mssql.mssql /bk create database lhrdb; backup database lhrdb TO DISK = N'/bk/lhrdb_full.bak' with format,stats=5,compression; |
3. 添加副本并设置
可以看到，故障转移模式从以前的“手动” 和“自动”，变为 “外部（External）”。故障转移模式值 External 用于与 Pacemaker 等Linux集群资源管理器一起处理故障转移。
其他选项配置都差不多：
4. 侦听器配置
必须手动将侦听器名称添加为DNS记录： LINUX_LSNAG , IP为 192.168.2.119
创建侦听器：
5. 只读路由配置
6. 选择数据库同步
这里选择 “自动种子设定” ，因为数据库较小。SQL Server 自动为此组中的每个数据库创建次要副本。 自动种子设定要求数据和日志文件路径在参与此组的每个 SQL Server 实例上均相同。（更多参考：“选择初始数据同步”页（AlwaysOn 可用性组向导））
7. 验证及检查
完成后结果：
但此时侦听器的状态是离线的：
虽然 SQL Server Always On Availability Group 安装成功了，但是现在无法进行故障转移
这是因为它仍然没有暴露给 Linux 操作系统。要通过侦听器名称访问 Always On Availability Group，必须在Linux群集资源管理器上注册它。下一篇将安装并配置 Linux 集群资源管理器 Pacemaker。配置完成后，可在 Pacemaker 上注册 SQL Server Always On Availability Group 和侦听器名称。
四、Linux 集群资源管理器 Pacemaker 安装配置
现在将完成 Linux 集群资源管理器 Pacemaker 的安装和配置。Pacemaker 相当于 Windows 中的故障转移群集 (WSFC)工具。Linux 环境下部署 always on 的高可用，微软官方推荐使用开源软件 Pacemaker。Pacemaker 适用于大多数Linux发行版，但SQL Server Always On Availability Groups 目前仅在 Red Hat Enterprise Linux 7.3 / 7.4 / 7.5 / 7.6，SUSE Linux Enterprise Server 12 SP2 和 Ubuntu 16.04 上受支持。
WSFC 和 Pacemaker 存在更多的相似之处。 两者都提供这样一种方式：使用多个单独的服务器，在配置中将它们合并，从而提供可用性；此外两者都具有资源、约束（尽管实施方式不同）、故障转移等概念。 为支持 Pacemaker 的可用性组和 FCI 配置（包括自动故障转移等），Microsoft 为 Pacemaker 提供了 mssql-server-ha 包，它与 WSFC 中的资源 DLL 类似但不完全相同。 WSFC 和 Pacemaker 之间的区别之一是 Pacemaker 中没有网络名称资源，该组件有助于提取 WSFC 上的侦听器名称（或 FCI 名称）。 DNS 在 Linux 上提供名称解析。
Pacemaker 的安装和配置基本步骤：
安装Pacemaker软件包
启动pcs守护程序并强制它在系统引导时启动
配置Linux防火墙以允许Pacemaker通信
强制Pacemaker和Corosync守护进程在系统引导时启动
为默认的hacluster帐户分配密码
在Linux集群节点之间设置身份验证
创建Linux集群
启动Linux群集
配置防护
配置资源级策略
为Pacemaker创建SQL Server登录账号
在本地文件系统上保存Pacemaker登录账号凭据
所有服务器：安装 Pacemaker
1 | yum install -y pacemaker pcs fence-agents-all resource-agents |
- 所有服务器：启动pcs守护程序并强制它在系统引导时启动
1 2 3 | systemctl start pcsd systemctl enable pcsd systemctl status pcsd |
- 所有服务器：配置Linux防火墙以允许Pacemaker通信（或禁用防火墙）
1 2 3 4 5 6 7 | firewall-cmd --add-service=high-availability --zone=public --permanent firewall-cmd --zone=public --add-port=2224/tcp --permanent firewall-cmd --zone=public --add-port=3121/tcp –permanent firewall-cmd --zone=public --add-port=5405/udp --permanent firewall-cmd --reload |
如果防火墙没有内置高可用性配置，请打开 Pacemaker 的以下端口。
TCP：端口 2224、3121、21064
UDP：端口 5405
- 所有服务器：先设置自启动 pacemaker & corosync 服务(暂不启动)，后续再添加 AlwaysOn AG 资源
1 2 | systemctl enable pacemaker systemctl enable corosync |
- 所有服务器：充当 Pacemaker 群集的节点，安装时创建的默认账户 hacluster
1 | echo "hacluster:lhr" | chpasswd |
- 主服务器(选任意一台作为主服务器来配置)：在Linux集群3个节点之间设置身份验证(将输入账户hacluster的密码验证)
1 | pcs cluster auth lhrmssql100 lhrmssql101 lhrmssql102 -u hacluster |
- 主服务器：创建Linux集群，名称为 LINUXHACLUSTER,添加3个节点
1 | pcs cluster setup --name LINUXHACLUSTER lhrmssql100 lhrmssql101 lhrmssql102 |
- 主服务器：启动Linux群集服务
1 2 | pcs cluster start --all pcs status --full |
运行过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | [root@lhrmssql100 bk]# pcs cluster auth lhrmssql100 lhrmssql101 lhrmssql102 -u hacluster Password: lhrmssql100: Authorized lhrmssql101: Authorized lhrmssql102: Authorized [root@lhrmssql100 bk]# pcs cluster setup --name LINUXHACLUSTER lhrmssql100 lhrmssql101 lhrmssql102 Destroying cluster on nodes: lhrmssql100, lhrmssql101, lhrmssql102... lhrmssql100: Stopping Cluster (pacemaker)... lhrmssql101: Stopping Cluster (pacemaker)... lhrmssql102: Stopping Cluster (pacemaker)... lhrmssql101: Successfully destroyed cluster lhrmssql100: Successfully destroyed cluster lhrmssql102: Successfully destroyed cluster Sending 'pacemaker_remote authkey' to 'lhrmssql100', 'lhrmssql101', 'lhrmssql102' lhrmssql102: successful distribution of the file 'pacemaker_remote authkey' lhrmssql101: successful distribution of the file 'pacemaker_remote authkey' lhrmssql100: successful distribution of the file 'pacemaker_remote authkey' Sending cluster config files to the nodes... lhrmssql100: Succeeded lhrmssql101: Succeeded lhrmssql102: Succeeded Synchronizing pcsd certificates on nodes lhrmssql100, lhrmssql101, lhrmssql102... lhrmssql100: Success lhrmssql101: Success lhrmssql102: Success Restarting pcsd on the nodes in order to reload the certificates... lhrmssql100: Success lhrmssql101: Success lhrmssql102: Success [root@lhrmssql100 bk]# pcs cluster start --all lhrmssql100: Starting Cluster (corosync)... lhrmssql101: Starting Cluster (corosync)... lhrmssql102: Starting Cluster (corosync)... lhrmssql100: Starting Cluster (pacemaker)... lhrmssql102: Starting Cluster (pacemaker)... lhrmssql101: Starting Cluster (pacemaker)... [root@lhrmssql100 bk]# pcs cluster status Cluster Status: Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 17 14:54:28 2021 Last change: Fri Dec 17 14:53:21 2021 by hacluster via crmd on lhrmssql101 3 nodes configured 0 resource instances configured PCSD Status: lhrmssql101: Online lhrmssql102: Online lhrmssql100: Online [root@lhrmssql100 bk]# pcs status --full Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (2) (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 17 15:01:37 2021 Last change: Fri Dec 17 14:55:59 2021 by root via cibadmin on lhrmssql100 3 nodes configured 0 resource instances configured Online: [ lhrmssql100 (1) lhrmssql101 (2) lhrmssql102 (3) ] No resources Node Attributes: * Node lhrmssql100 (1): * Node lhrmssql101 (2): * Node lhrmssql102 (3): Migration Summary: * Node lhrmssql101 (2): * Node lhrmssql102 (3): * Node lhrmssql100 (1): Fencing History: PCSD Status: lhrmssql101: Online lhrmssql102: Online lhrmssql100: Online Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/enabled |
- 所有服务器：配置防护
Hyper-V和Azure尚不支持使用 STONITH RHEL 与 Pacemaker 群集，这里禁止。（参考：对于 Linux 部署 SQL Server 可用性基础知识）
1 | pcs property set stonith-enabled=false |
- 所有服务器：配置资源级策略。
以下设置禁用,集群根据资源的当前故障计数和迁移阈值决定是否再次尝试在同一节点上启动
1 | pcs property set start-failure-is-fatal=false |
- 为Pacemaker创建SQL Server登录账号，授予可用组 LINUX_SQLAG 权限，在所有服务器运行：
1 2 3 4 5 6 7 8 | USE master GO CREATE LOGIN pacemakerLogin WITH PASSWORD = 'lhr@xxt123'; GO GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::LINUX_SQLAG TO pacemakerLogin GO GRANT VIEW SERVER STATE TO pacemakerLogin GO |
- 所有服务器：在本地文件系统上保存Pacemaker登录凭据(Pacemaker通过MSSQL账号pacemakerLogin连接)
1 2 3 4 5 6 7 | cat > /var/opt/mssql/secrets/passwd <<"EOF" pacemakerLogin lhr@xxt123 EOF ls -l /var/opt/mssql/secrets chmod 400 /var/opt/mssql/secrets/passwd |
Linux 集群资源管理器 Pacemaker 已安装配置完成，接下来则是将配置SQL Server 2017 Always On Availability Group和侦听器名称的虚拟IP地址作为群集中的资源。
五、配置监听器测试故障转移
前面已经配置好了 AlwaysOn AG 和 Pacemaker ，但是还不能进行故障转移。为了设置高可用，需要添加侦听器，用侦听器去访问数据库。群集资源代理程序 mssql-server-ha 是 Pacemaker 和 SQL Server 之间的接口。现在将创建和配置SQL Server Always On Availability Group的过程，并将相应的侦听器作为群集资源。
选择任意一个节点来做配置：
- 在Pacemaker上创建Always On Availability组资源
1 | pcs resource create LINUX_SQLAG ocf:mssql:ag ag_name=LINUX_SQLAG master notify=true |
LINUX_SQLAG: Pacemaker 集群资源的名称（可不必与 AlwaysOn AG 的名称相同，此测试设置相同）
ocf:mssql:ag: 由mssql-server-ha提供的Open Cluster Framework（OCF）资源代理的名称
ag_name=LINUX_SQLAG: AlwaysOn AG 的可用性组的名称
master: 将资源定义为 master/slave 克隆资源
notify=true:
- 为Always On Availability Group侦听器创建虚拟IP地址资源
1 2 3 4 5 6 7 8 9 10 | pcs resource create AGListener_VIP ocf:heartbeat:IPaddr2 ip=172.72.9.105 cidr_netmask=24 USE [master] GO ALTER AVAILABILITY GROUP [LINUX_SQLAG] ADD LISTENER N'LINUX_LSNAG' ( WITH IP ((N'172.72.9.105', N'255.255.255.0')), PORT=1433 ); GO |
AGListener_VIP: 虚拟IP地址资源的名称
ocf:heartbeat:IPaddr2: 管理虚拟IPv4地址的Open Cluster Framework（OCF）资源代理的名称
ip=172.72.9.105: AlwaysOn AG 的可用性组的侦听器IP
cidr_netmask=24: AlwaysOn AG 的可用性组的侦听器子网掩码
查看：
1 2 3 4 5 6 7 8 9 10 11 | [root@lhrmssql100 bk]# ip addr 1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000 link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00 inet 127.0.0.1/8 scope host lo valid_lft forever preferred_lft forever 49: eth0@if50: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc noqueue state UP group default link/ether 02:42:ac:48:09:64 brd ff:ff:ff:ff:ff:ff link-netnsid 0 inet 172.72.9.100/24 brd 172.72.9.255 scope global eth0 valid_lft forever preferred_lft forever inet 172.72.9.105/24 brd 172.72.9.255 scope global secondary eth0 valid_lft forever preferred_lft forever |
- 将Always On Availability Group资源配置为在与虚拟IP地址资源相同的计算机上运行
1 | pcs constraint colocation add AGListener_VIP LINUX_SQLAG-master INFINITY with-rsc-role=Master |
AGListener_VIP: 虚拟IP地址资源的名称
LINUX_SQLAG-master: AlwaysOn AG 资源的克隆别名
INFINITY: 分配给资源约束的分数;这意味着约束是必需的
with-rsc-role=Master: 约束的附加属性;这意味着此约束与主克隆（或Always On Availability Group主副本）相关联
由于Always On Availability Group侦听器名称只能将客户端应用程序重定向到主副本，因此可用性组和侦听器名称必须始终在同一群集节点中运行
- 配置群集资源应该开始/停止的顺序
1 | pcs constraint order promote LINUX_SQLAG-master then start AGListener_VIP |
promote: 约束行为，将资源从slave 提升为 master 资源
LINUX_SQLAG-master: AlwaysOn AG 资源的克隆别名
start: 初步操作完成后下一步的动作
AGListener_VIP: 虚拟IP地址资源的名称
在WSFC中，事件序列如下：
停止当前主副本上的可用性组
在当前主副本上停止侦听器名称
在新的主副本上启动侦听器
在新主副本上启动可用性组
在侦听器名称上定义约束时，事件序列如下：
停止当前主副本上的虚拟IP地址资源
停止当前主副本上的可用性组
在新的主副本上启动虚拟IP地址资源
在新主副本上启动可用性组
- 验证Always On可用性组配置是否正常
1 2 3 4 5 6 7 8 9 10 11 12 | pcs status SELECT @@SERVERNAME as replica_name, @@VERSION, host_platform, host_distribution, host_release FROM sys.dm_os_host_info GO SELECT a.name as AG_Name, a.cluster_type_desc,b.dns_name,c.ip_address, c.ip_subnet_mask FROM sys.availability_groups a INNER JOIN sys.availability_group_listeners b ON a.group_id=b.group_id INNER JOIN sys.availability_group_listener_ip_addresses c ON b.listener_id=c.listener_id GO |
过程：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | [root@lhrmssql100 ~]# pcs status Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 24 10:15:09 2021 Last change: Fri Dec 24 10:15:00 2021 by root via cibadmin on lhrmssql100 3 nodes configured 4 resource instances configured Online: [ lhrmssql100 lhrmssql101 lhrmssql102 ] Full list of resources: Master/Slave Set: LINUX_SQLAG-master [LINUX_SQLAG] Masters: [ lhrmssql100 ] Slaves: [ lhrmssql101 lhrmssql102 ] AGListener_VIP (ocf::heartbeat:IPaddr2): Started lhrmssql100 Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/enabled |
配置读写分离
1 | sqlcmd -S 172.72.9.105 -U SA -P 'lhr@xxt123' -K ReadOnly -Q "select @@servername;" |
配置：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 | -- -查询可用性副本信息 SELECT * FROM master.sys.availability_replicas -- 建立read指针 -- 在当前的primary上为每个副本建立副本对于的tcp连接 ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql100' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://lhrmssql100:1433')) ; ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql101' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://lhrmssql101:1433')) ; ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql102' WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://lhrmssql102:1433')) ; ---- 为每个可能的primary role配置对应的只读路由副本 -- list列表有优先级关系，排在前面的具有更高的优先级,当db02正常时只读路由只能到db02，如果db02故障了只读路由才能路由到DB03 ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql100' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('lhrmssql101','lhrmssql102'),'lhrmssql100'))); ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql101' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('lhrmssql102','lhrmssql103'),'lhrmssql101'))); ALTER AVAILABILITY GROUP [LINUX_SQLAG] MODIFY REPLICA ON N'lhrmssql102' WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('lhrmssql100','lhrmssql101'),'lhrmssql102'))); -- 查询优先级关系 SELECT ar.replica_server_name , rl.routing_priority , ( SELECT ar2.replica_server_name FROM sys.availability_read_only_routing_lists rl2 JOIN sys.availability_replicas AS ar2 ON rl2.read_only_replica_id = ar2.replica_id WHERE rl.replica_id = rl2.replica_id AND rl.routing_priority = rl2.routing_priority AND rl.read_only_replica_id = rl2.read_only_replica_id ) AS 'read_only_replica_server_name' FROM sys.availability_read_only_routing_lists rl JOIN sys.availability_replicas AS ar ON rl.replica_id = ar.replica_id ; |
故障切换
自动故障转移测试
现在算是配置好了，可以模拟服务器宕机让其自动进行故障转移。现在主节点在 lhrmssql100 上，把这台服务器关机测试看看！！
连接：
1 2 3 4 | [root@docker35 ~]# sqlcmd -S 172.72.9.105 -U SA -P 'lhr@xxt123' -Q "select @@servername;" -------------------------------------------------------------------------------------------------------------------------------- lhrmssql100 |
将lhrmssql100 服务器关机：
本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 | docker stop lhrmssql100 [root@docker35 ~]# sqlcmd -S 172.72.9.105 -U SA -P 'lhr@xxt123' -Q "select @@servername;" -------------------------------------------------------------------------------------------------------------------------------- lhrmssql102 (1 rows affected) |
同时查看侦听器的连接情况：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | [root@lhrmssql101 /]# pcs status Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 24 11:48:32 2021 Last change: Fri Dec 24 10:15:00 2021 by root via cibadmin on lhrmssql100 3 nodes configured 4 resource instances configured Online: [ lhrmssql101 lhrmssql102 ] OFFLINE: [ lhrmssql100 ] Full list of resources: Master/Slave Set: LINUX_SQLAG-master [LINUX_SQLAG] Masters: [ lhrmssql102 ] Slaves: [ lhrmssql101 ] Stopped: [ lhrmssql100 ] AGListener_VIP (ocf::heartbeat:IPaddr2): Started lhrmssql102 Daemon Status: corosync: active/disabled pacemaker: active/disabled pcsd: active/enabled |
可以看到已经连接到lhrmssql102了，此时再重启lhrmssql100服务器：
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | docker start lhrmssql100 [root@lhrmssql100 /]# pcs status Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 24 12:03:53 2021 Last change: Fri Dec 24 10:15:00 2021 by root via cibadmin on lhrmssql100 3 nodes configured 4 resource instances configured Online: [ lhrmssql100 lhrmssql101 lhrmssql102 ] Full list of resources: Master/Slave Set: LINUX_SQLAG-master [LINUX_SQLAG] Masters: [ lhrmssql102 ] Slaves: [ lhrmssql100 lhrmssql101 ] AGListener_VIP (ocf::heartbeat:IPaddr2): Started lhrmssql102 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled |
可以看到，集群恢复正常状态！！
手动故障转移测试
现在主副本在 lhrmssql102 上面了，我将切回到 lhrmssql100 上，在任意节点执行：
1 | pcs resource move LINUX_SQLAG-master lhrmssql100 --master |
一会后，发现副本角色进行了切换。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | [root@lhrmssql100 /]# pcs resource move LINUX_SQLAG-master lhrmssql100 --master [root@lhrmssql100 /]# [root@lhrmssql100 /]# [root@lhrmssql100 /]# pcs status Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 24 12:06:58 2021 Last change: Fri Dec 24 12:06:55 2021 by root via crm_resource on lhrmssql100 3 nodes configured 4 resource instances configured Online: [ lhrmssql100 lhrmssql101 lhrmssql102 ] Full list of resources: Master/Slave Set: LINUX_SQLAG-master [LINUX_SQLAG] Masters: [ lhrmssql102 ] Slaves: [ lhrmssql100 lhrmssql101 ] AGListener_VIP (ocf::heartbeat:IPaddr2): Stopped Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled [root@lhrmssql100 /]# [root@lhrmssql100 /]# [root@lhrmssql100 /]# [root@lhrmssql100 /]# pcs status Cluster name: LINUXHACLUSTER Stack: corosync Current DC: lhrmssql101 (version 1.1.23-1.el7_9.1-9acf116022) - partition with quorum Last updated: Fri Dec 24 12:07:35 2021 Last change: Fri Dec 24 12:06:55 2021 by root via crm_resource on lhrmssql100 3 nodes configured 4 resource instances configured Online: [ lhrmssql100 lhrmssql101 lhrmssql102 ] Full list of resources: Master/Slave Set: LINUX_SQLAG-master [LINUX_SQLAG] Masters: [ lhrmssql100 ] Slaves: [ lhrmssql101 lhrmssql102 ] AGListener_VIP (ocf::heartbeat:IPaddr2): Started lhrmssql100 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled [root@docker35 ~]# sqlcmd -S 172.72.9.105 -U SA -P 'lhr@xxt123' -Q "select @@servername;" -------------------------------------------------------------------------------------------------------------------------------- lhrmssql100 (1 rows affected) |
AlwaysOn AG 的切换是通过系统命令去切换的，SSMS 操作界面禁用了次此操作。
SQL Server Always On AG on Linux 配置起来确实麻烦，还需要基于linux的群集，了解相关命令。对习惯用windows处理的人员来说，运维是比较麻烦的。
数据字典
1 2 3 4 | SELECT * FROM master.sys.availability_replicas; select * from master.sys.availability_groups; |
