SQL Server可用性组（AG）场景咨询：如何强制两个AG故障转移至同一节点？

完全理解你的需求——希望当主服务器（服务器1）故障时，AG1和AG2的写入副本能同时切换到同一台服务器，保持两个数据库的写入节点一致。下面是几个可行的方案，你可以根据自己的环境选择：

1. 统一配置AG的故障转移优先级与自动故障转移伙伴 #

如果你的AG使用同步提交模式（支持自动故障转移），可以为AG1和AG2统一设置同一个目标服务器作为首选故障转移节点：

• 在SQL Server Management Studio（SSMS）中，分别打开AG1和AG2的属性，找到副本列表。
• 对目标服务器（比如你想优先转移到的服务器3），将其故障转移优先级设为最高（数值越大优先级越高，默认主服务器是100，副本可以设为90，其他副本设更低）。
• 同时，为两个AG都将该目标服务器设置为自动故障转移伙伴（需要确保该副本已经同步完成，满足自动故障转移的条件）。
• 如果你习惯用T-SQL，可以执行类似命令：

  -- 为AG1修改服务器3的故障转移优先级
  ALTER AVAILABILITY GROUP AG1 
  MODIFY REPLICA ON N'Server3' WITH (FAILOVER_PRIORITY = 90);
  
  -- 启用AG1与服务器3的自动故障转移
  ALTER AVAILABILITY GROUP AG1 
  MODIFY REPLICA ON N'Server3' WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT, FAILOVER_MODE = AUTOMATIC);
  

  对AG2执行完全相同的命令，把AG1换成AG2即可。
• 注意：异步提交模式的AG不支持自动故障转移，这种情况下只能用手动或脚本触发。

2. 用自动化脚本触发同步故障转移 #

如果自动故障转移的条件不满足（比如用了异步提交），或者你需要更灵活的控制，可以写一个监控脚本（比如PowerShell），在检测到服务器1故障时，同时触发两个AG的故障转移：

• 脚本核心逻辑：定期检查服务器1的可用性，当确认其离线后，连接到目标服务器，执行故障转移命令：

  # 检测服务器1是否在线
  $server1Online = Test-Connection -ComputerName "Server1" -Count 1 -Quiet
  if (-not $server1Online) {
      # 触发AG1故障转移到服务器3
      Invoke-SqlCmd -ServerInstance "Server3" -Query "ALTER AVAILABILITY GROUP AG1 FAILOVER;"
      # 触发AG2故障转移到服务器3
      Invoke-SqlCmd -ServerInstance "Server3" -Query "ALTER AVAILABILITY GROUP AG2 FAILOVER;"
  }
  

• 可以把这个脚本部署到监控服务器，用Windows任务计划或者SQL Agent作业定期运行。
• 注意：执行故障转移的账号需要有ALTER AVAILABILITY GROUP的权限，而且要确保目标副本的状态允许故障转移（比如异步模式下可能需要强制故障转移，命令是ALTER AVAILABILITY GROUP AG1 FORCE_FAILOVER_ALLOW_DATA_LOSS;，但会有数据丢失风险，需谨慎）。

3. 合并两个AG为一个（最彻底的方案） #

如果业务场景允许，把DB1和DB2放到同一个可用性组里是最简单的方式——同一个AG中的所有数据库会一起故障转移，自然保持写入副本在同一台服务器：

• 操作步骤：
  1. 先将DB1从AG1中移除，DB2从AG2中移除。
  2. 创建一个新的可用性组（比如AG_Combined），将DB1和DB2添加进去。
  3. 配置所有4台服务器作为该AG的副本，设置服务器1为主副本，其他为只读副本。
• 这种方式的好处是无需额外配置故障转移规则，AG本身就会保证所有数据库的故障转移一致性，但需要评估两个数据库的资源需求、备份策略是否兼容，避免合并后出现性能或管理上的问题。

关键注意事项 #

• 无论用哪种方案，都要先在测试环境中验证故障转移流程，确保能达到预期效果，再部署到生产环境。
• 目标服务器需要有足够的CPU、内存和存储资源，同时承载DB1和DB2的写入负载，避免出现性能瓶颈。
• 如果使用自动故障转移，必须确保目标副本与主副本的同步状态正常，否则自动故障转移不会触发。

内容的提问来源于stack exchange，提问作者Sanjeev Dhiman