SQL Server 故障轉移與服務重啟自動通知：基於 Agent 作業的輕量級監控方案

業務場景：非集群感知應用的痛點

許多歷史應用（如樓宇自控系統）不支持連接字符串 MultiSubnetFailover=True 或重試邏輯。當 SQL Server 可用性群組/故障轉移叢集 (FCI) 發生故障轉移時，SQL Server 服務在新節點重啟，非感知應用連接中斷導致業務凍結。雖可部署 SCOM/監控平台，但跨團隊協調成本高。本文提供零依賴、極低維護的 Agent 作業通知方案。

核心原理：Agent 服務啟動觸發器

SQL Server Agent 作業支持 「啟動時執行」 排程類型。故障轉移本質是資源組移動 → SQL Server 服務重啟 → Agent 服務跟隨啟動 → 作業自動觸發 → 發送郵件通知。

步驟級實施指南

前置條件

1. 已配置 Database Mail：msdb.dbo.sysmail_account、profile 測試發送成功。
2. Agent 服務設置為自動啟動，且與 SQL Server 服務同賬號/依賴關係正確。
3. 叢集角色包含 SQL Server Agent 資源（FCI 場景）。

1. 創建通知存儲過程（復用、參數化）

USE msdb;
GO
CREATE OR ALTER PROC dbo.usp_Notify_Failover
    @Subject NVARCHAR(255) = N'SQL Server 服務啟動/故障轉移告警',
    @Body NVARCHAR(MAX) = N'',
    @ProfileName SYSNAME = 'DBA_Alert_Profile',
    @Recipients NVARCHAR(MAX) = 'dba-team@company.com;vendor-support@vendor.com'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Instance NVARCHAR(128) = @@SERVERNAME;
    DECLARE @Time DATETIME = GETDATE();
    DECLARE @FullBody NVARCHAR(MAX) = CONCAT(
        N'實例: ', @Instance, CHAR(13), CHAR(10),
        N'時間: ', CONVERT(NVARCHAR, @Time, 120), CHAR(13), CHAR(10),
        N'類型: 服務啟動 (可能為故障轉移/計劃重啟/崩潰恢復)', CHAR(13), CHAR(10),
        N'詳情: ', ISNULL(NULLIF(@Body, ''), N'無附加信息'), CHAR(13), CHAR(10),
        N'請登錄叢集管理器/可用性群組儀表板核實狀態。'
    );
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = @ProfileName,
        @recipients = @Recipients,
        @subject = @Subject,
        @body = @FullBody,
        @importance = 'HIGH';
END
GO

2. SSMS GUI 創建作業（圖文步驟）

1. 常規頁：名稱 DBA_Notify_Failover_Restart、類別 Alerts、擁有者 sa、啟用 ✅。
2. 步驟頁：新建步驟 Send_Alert、類型 Transact-SQL、命令：

   EXEC dbo.usp_Notify_Failover 
       @Body = N'由 Agent 啟動觸發器自動發送。當前節點: ' + CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NVARCHAR(128));
   

   高級頁：輸出文件 C:\SQLLogs\Agent_Failover_Notify_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).log（便於事後審計）。
3. 排程頁：新建排程 On_Agent_Start、類型 「SQL Server Agent 啟動時」（關鍵！）。
4. 通知/目標：保持默認。

3. T-SQL 腳本化（版本控制、多實例部署）

USE msdb;
GO
DECLARE @jobId BINARY(16);
EXEC dbo.sp_add_job
    @job_name = N'DBA_Notify_Failover_Restart',
    @enabled = 1,
    @category_name = N'[Uncategorized (Local)]',
    @owner_login_name = N'sa',
    @job_id = @jobId OUTPUT;

EXEC dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'Send_Alert',
    @subsystem = N'TSQL',
    @command = N'EXEC dbo.usp_Notify_Failover @Body = N''Agent 自動通知。節點: '' + CAST(SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS NVARCHAR(128));',
    @database_name = N'msdb',
    @flags = 4, -- 輸出到文件
    @output_file_name = N'C:\SQLLogs\Agent_Failover_Notify_$(ESCAPE_SQUOTE(STRTDT))_$(ESCAPE_SQUOTE(STRTTM)).log';

EXEC dbo.sp_add_jobschedule
    @job_id = @jobId,
    @name = N'On_Agent_Start',
    @freq_type = 64, -- Agent 啟動時
    @active_start_time = 0;

EXEC dbo.sp_add_jobserver @job_id = @jobId;
GO

驗證與測試

1. 手工測試：SSMS 右鍵作業 →「啟動作業」，確認收件箱收到郵件、日誌文件生成。
2. 故障轉移演練：叢集管理器 →「移動角色」→ 觀察新節點 Agent 啟動 → 郵件送達時間 < 30 秒。
3. 誤報過濾：計劃維護窗口內，可通過作業步驟首行判斷 IF EXISTS (SELECT 1 FROM dbo.MaintenanceWindow WHERE GETDATE() BETWEEN StartTime AND EndTime) RETURN; 抑制通知。

進階增強：結合錯誤日誌分析

-- 作業步驟增強：附帶最近 5 條錯誤日誌
DECLARE @ErrLog TABLE (LogDate DATETIME, ProcessInfo NVARCHAR(50), Text NVARCHAR(MAX));
INSERT INTO @ErrLog EXEC sp_readerrorlog 0, 1, N'Recovery', N'Starting up'; -- 或其他關鍵字
SELECT TOP 5 @Body = COALESCE(@Body + CHAR(13)+CHAR(10), '') + CONVERT(NVARCHAR, LogDate, 120) + N' ' + Text
FROM @ErrLog ORDER BY LogDate DESC;
EXEC dbo.usp_Notify_Failover @Body = @Body;

局限性與補充方案

這個「幾分鐘部署、零代碼依賴、原生集成」的方案，雖簡單卻極其有效，能第一時間讓相關方知悉核心數據庫狀態變化，為非集群感知應用的故障恢復爭取寶貴時間窗口。建議納入 DBA 標準化交付清單，每個生產叢集實例必備。