MSSQL AG组故障转移后SQL代理作业重复运行问题咨询

问题根因 #

13.0.5888.11为SQL Server 2016早期RTM版本，该版本存在SQL Server代理服务的已知bug：AG发生故障转移后，本地代理服务不会自动刷新可用性组副本角色的感知缓存，导致新辅助副本上的代理服务误判自身角色，会将本地触发的作业请求错误路由到当前主副本执行，最终出现两个节点作业同时运行、输出的@@servername均为主节点名的现象。重启SQL Server代理服务仅能临时清空缓存，下次故障转移后问题会再次复现。

长效修复方案 #

• 升级SQL Server版本修复底层bug
  该代理角色缓存不刷新的问题在SQL Server 2016后续服务包和累积更新中已经官方修复，优先将实例升级到SQL Server 2016 SP2及以上的最新累积更新版本，从底层解决代理角色感知异常的问题。
• 给所有AG关联作业增加内置角色判断逻辑
  不要依赖SQL代理自带的"作业仅在主副本运行"内置选项，该选项在低版本中同样受缓存bug影响会失效。直接在每个AG相关作业的首个执行步骤开头加入副本角色判断代码，只有当前节点持有对应AG的主副本角色时才继续执行后续逻辑，否则直接正常退出作业，从作业逻辑层面彻底杜绝重复执行，参考代码如下：

-- 请替换成实际环境的可用性组名、作业关联的数据库名
DECLARE @AGName NVARCHAR(128) = N'你的可用性组名称'
DECLARE @TargetDBName NVARCHAR(128) = N'作业访问的AG数据库名称'

IF NOT EXISTS (
    SELECT 1
    FROM sys.dm_hadr_availability_replica_states ars
    JOIN sys.availability_groups ag 
        ON ars.group_id = ag.group_id
    JOIN sys.dm_hadr_database_replica_states drs 
        ON ars.group_id = drs.group_id AND ars.replica_id = drs.replica_id
    WHERE ag.name = @AGName
      AND drs.database_id = DB_ID(@TargetDBName)
      AND ars.is_local = 1
      AND ars.role = 1 -- role=1代表当前是主副本，role=2为辅助副本
)
BEGIN
    PRINT '当前节点为辅助副本，作业终止执行'
    RETURN 0
END

-- 以下放作业原有业务逻辑
PRINT '当前节点为主副本，开始执行业务逻辑，当前实例名：' + @@SERVERNAME

• 部署主动刷新代理角色缓存的定时任务
  在所有AG副本节点上创建一个系统作业，设置为每1~2分钟执行一次，主动触发代理刷新AG角色缓存，即使不升级版本也能避免缓存长期不更新的问题，作业执行的命令如下：

DBCC TRACEON(4618, -1) WITH NO_INFOMSGS
EXEC msdb.dbo.sp_agent_refresh_ag_role
DBCC TRACEOFF(4618, -1) WITH NO_INFOMSGS

• 配置故障转移联动的作业启停规则
  创建AG角色变更告警，绑定故障转移触发的响应脚本：当实例检测到本地副本从辅助切换为主时，自动启用本地所有AG相关作业；当检测到本地副本从主切换为辅助时，自动禁用本地所有AG相关作业，从作业配置层面避免双节点作业同时处于可运行状态。

注意：所有方案中，作业内置角色判断逻辑的可靠性最高，不受版本、缓存、代理服务状态的影响，建议优先配置。

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