如果您不希望pacemakerLogin具有sysadmin的权限，可以将之从sysadmin中移除，并授予如下权限。每个实例
ALTER SERVER ROLE [sysadmin] DROP MEMBER [pacemakerLogin] GO GRANT ALTER, CONTROL, VIEW DEFINITION ON AVAILABILITY GROUP::ag1 TO pacemakerLogin; GO GRANT VIEW SERVER STATE TO pacemakerLogin; GO添加数据库到AG,主副本执行
CREATE DATABASE [db1]; GO ALTER DATABASE [db1] SET RECOVERY FULL; GO BACKUP DATABASE [db1] TO DISK = N'nul'; GO ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1]; GO可用性数据库状态
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;在Pacemaker群集中配置AG
创建AG资源，ag_name要指定为之前创建AG名称
pcs resource create agcluster ocf:mssql:ag ag_name=ag1 meta failure-timeout=30s master notify=true创建虚拟IP资源
# 禁用fencing pcs property set stonith-enabled=false # 创建VIP pcs resource create virtualip ocf:heartbeat:IPaddr2 ip=172.17.2.7创建 colacation constraint，vip和master必需在同一个节点上启动
pcs constraint colocation add virtualip agcluster-master INFINITY with-rsc-role=Master创建 ordering constraint，vip要先于master副本资源启动
pcs constraint order promote agcluster-master then start virtualip # 查看当前的约束 pcs constraint show --full重新启用STONITH并查看群集状态
pcs property set stonith-enabled=true pcs status # 我的环境中的状态信息 --------------------------------------- Cluster name: agcluster Stack: corosync Current DC: SQL19N3 (version 1.1.20-5.el7_7.2-3c4c782f70) - partition with quorum Last updated: Wed Apr 29 04:24:50 2020 Last change: Wed Apr 29 04:24:45 2020 by root via cibadmin on SQL19N1 3 nodes configured 5 resources configured Online: [ SQL19N1 SQL19N2 SQL19N3 ] Full list of resources: rsc_st_azure (stonith:fence_azure_arm): Started SQL19N1 Master/Slave Set: agcluster-master [agcluster] Masters: [ SQL19N1 ] Slaves: [ SQL19N2 SQL19N3 ] virtualip (ocf::heartbeat:IPaddr2): Started SQL19N1 Daemon Status: corosync: active/enabled pacemaker: active/enabled pcsd: active/enabled测试Failover和Fencing
# 手动failover pcs resource move agcluster-master SQL19N2 --master pcs status # 手动 failover会生成一个constraint，避免AG资源再回到原来的节点 # 如果希望AG后续还能 failover回来，需要手动删除之 pcs constraint show --full pcs constraint remove cli-prefer-agcluster-master # 尝试Fencing群集节点，每个节点都试一下 # 如下命令的fencing只是重启node,如果要安全关闭node，使用--off参数 pcs stonith fence SQL19N3 --debug