如何配置WITNESS断连时发送SQL告警的定时作业

改造检测脚本 #

你原有查询可以直接调整为带告警触发逻辑的T-SQL脚本，只筛选见证节点断开的记录，存在异常时直接触发邮件告警，参考脚本如下：

DECLARE @ExceptionCount INT
DECLARE @AlertContent NVARCHAR(MAX)

-- 临时表存储异常记录
SELECT 
    d.name AS DatabaseName,
    d.database_id,
    m.mirroring_role_desc,
    m.mirroring_state_desc,
    m.mirroring_safety_level_desc, 
    m.mirroring_partner_name,
    m.mirroring_partner_instance,
    m.mirroring_witness_name,
    m.mirroring_witness_state_desc
INTO #DisconnectedWitnessExceptions
FROM sys.database_mirroring m 
INNER JOIN sys.databases d ON m.database_id = d.database_id
WHERE m.mirroring_state_desc IS NOT NULL
AND m.mirroring_witness_state_desc = 'DISCONNECTED'

SELECT @ExceptionCount = COUNT(*) FROM #DisconnectedWitnessExceptions

-- 存在异常时拼接告警内容并发信
IF @ExceptionCount > 0
BEGIN
    SET @AlertContent = N'告警：检测到数据库镜像见证节点连接断开，涉及数据库信息：' + CHAR(13) + CHAR(10)
    SELECT @AlertContent = @AlertContent + 
        N'数据库：' + DatabaseName + 
        N' | 见证节点地址：' + mirroring_witness_name + 
        N' | 连接状态：' + mirroring_witness_state_desc + CHAR(13) + CHAR(10)
    FROM #DisconnectedWitnessExceptions

    -- 需提前配置好数据库邮件功能，替换下方参数为实际环境值
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '实际环境的数据库邮件配置名',
        @recipients = '告警接收邮箱列表，多个邮箱用分号分隔',
        @subject = N'【数据库告警】镜像见证节点连接断开',
        @body = @AlertContent
END

DROP TABLE #DisconnectedWitnessExceptions

配置定时作业 #

直接用SQL Server代理实现定时巡检，步骤如下：

• 打开SSMS连接到目标实例，展开「SQL Server代理」节点，右键「作业」选择「新建作业」，填写作业名称比如「数据库镜像见证状态巡检」
• 切换到「步骤」页，新建作业步骤：步骤类型选择Transact-SQL(T-SQL)，运行数据库选择master，把上面调整好的脚本粘贴到命令框，保存步骤
• 切换到「调度」页，新建调度：调度类型选「重复执行」，频率设置为每天，按你的需求设置每隔2-4小时执行一次，执行时间范围覆盖全天即可
• 可选配置：切换到「通知」页，设置作业执行失败时额外通知DBA，避免脚本本身异常导致巡检失效

配置验证和注意事项 #

• 配置前先确认实例已经开启Database Mail功能，测试邮件收发正常，否则告警无法送达
• 作业运行的代理账号需要有系统视图查询权限、sp_send_dbmail存储过程的执行权限
• 首次配置完成后可以手动执行一次作业：临时把脚本里的判断条件改成mirroring_witness_state_desc = 'CONNECTED'测试告警链路是否正常，验证完成后改回原判断条件即可
• 建议设置作业历史记录保留至少30天，方便后续回溯故障发生的时间点和频率

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