多資料庫環境下 SQL Server 鏡像自動故障轉移協調機制實作

前言：單庫故障轉移不夠，需要編排

SQL Server 2005 資料庫鏡像的高可用性模式支援單一資料庫自動故障轉移。但在多資料庫應用系統中，若僅主庫故障轉移而其他關聯資料庫留在原主體，將導致應用層連線字串指向錯誤、跨資料庫查詢失敗、分散式交易不一致。本文實作基於 WMI 事件警示 + Service Broker 的協調機制，實現「一庫倒，全庫倒」的編排故障轉移。

一、問題場景建模

1. 典型多資料庫拓撲

應用系統
   │
   ├── DB_Orders (主體: NodeA, 鏡像: NodeB, 見證: NodeW)
   ├── DB_Inventory (主體: NodeA, 鏡像: NodeB, 見證: NodeW)
   ├── DB_Accounting (主體: NodeA, 鏡像: NodeB, 見證: NodeW)
   └── DB_Reporting (主體: NodeA, 鏡像: NodeB, 見證: NodeW)

2. 失效協調缺口

• DB_Orders 主體故障 → 自動故障轉移至 NodeB
• DB_Inventory/Accounting/Reporting 仍在 NodeA 為主體
• 應用連線字串 `Failover Partner=NodeB` 僅對 DB_Orders 生效
• 跨資料庫查詢 `SELECT * FROM DB_Orders..o JOIN DB_Inventory..i` 失敗 (雙方主體分離)
• 分散式交易 (MSDTC) 協調器無法聯繫所有參與者

二、解決方案架構

1. 核心組件

• WMI 事件提供者：`__InstanceModificationEvent` 監聽 `MIRRORING_STATE_CHANGE`
• SQL Server Agent 警示：訂閱 WMI 事件，觸發作業
• Service Broker：跨實例可靠訊息傳遞，協調故障轉移指令
• 協調預存程序：執行 `ALTER DATABASE ... SET PARTNER FAILOVER` 序列

2. 事件流程

1. 任一鏡像資料庫狀態變更 (SYNCHRONIZED → FAILOVER / DISCONNECTED)
2. WMI 發射 `__InstanceModificationEvent`
3. SQL Agent 警示捕獲 → 啟動協調作業
4. 協調作業透過 Service Broker 發送故障轉移指令至所有關聯資料庫主體
5. 各資料庫依序執行 `SET PARTNER FAILOVER`
6. 完成後發送確認訊息，更新應用層配置快取 (可選)

三、前置準備：Service Broker 啟用

-- 1. 檢查現狀
SELECT name, is_broker_enabled FROM sys.databases WHERE name IN ('msdb', 'master', 'DB_Orders', 'DB_Inventory');

-- 2. 啟用 msdb (Agent 警示需要) 及各業務資料庫
ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE DB_Orders SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
-- ... 其他資料庫

-- 3. 建立訊息類型、合約、佇列、服務、路由 (簡化版，實際需雙向)
CREATE MESSAGE TYPE [Mirroring/FailoverCommand] VALIDATION = WELL_FORMED_XML;
CREATE CONTRACT [Mirroring/FailoverContract] ([Mirroring/FailoverCommand] SEND BY INITIATOR);
CREATE QUEUE [Mirroring/FailoverQueue];
CREATE SERVICE [Mirroring/FailoverService] ON QUEUE [Mirroring/FailoverQueue] ([Mirroring/FailoverContract]);
-- 路由需指向見證/夥伴實例的 Service Broker 端點

四、建立 WMI 事件警示

1. 透過 SSMS 圖形化介面

1. 展開 SQL Server Agent → 右鍵 Alerts → New Alert
2. Name: `Mirroring_State_Change_Failover`
3. Type: WMI Event Alert
4. Namespace: `\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER` (預設實例)
5. Query: SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA 'Database_Mirroring_State_Change' AND TargetInstance.NewState = 8
   • `WITHIN 10`：10 秒輪詢間隔
   • `NewState = 8`：對應 `FAILOVER` 狀態 (參考 `Database_Mirroring_State_Change` 類別定義)
6. Response: 勾選「Execute Job」，選擇協調作業

2. 透過 T-SQL 腳本化 (利於版控部署)

EXEC msdb.dbo.sp_add_alert
    @name = N'Mirroring_State_Change_Failover',
    @wmi_namespace = N'\\.\root\Microsoft\SqlServer\ServerEvents\MSSQLSERVER',
    @wmi_query = N'SELECT * FROM __InstanceModificationEvent WITHIN 10 WHERE TargetInstance ISA ''Database_Mirroring_State_Change'' AND TargetInstance.NewState = 8',
    @job_name = N'Job_Coordinated_Failover',
    @enabled = 1;

五、協調故障轉移作業實作

1. 作業步驟邏輯

-- Job_Coordinated_Failover 步驟 1: 掃描所有需協調的鏡像資料庫
DECLARE @Databases TABLE (DbName SYSNAME PRIMARY KEY, PartnerServer SYSNAME);
INSERT INTO @Databases VALUES
    ('DB_Orders', 'NodeB'),
    ('DB_Inventory', 'NodeB'),
    ('DB_Accounting', 'NodeB'),
    ('DB_Reporting', 'NodeB');

-- 步驟 2: 逐一發起故障轉移 (可平行化，這裡示範序列)
DECLARE @DbName SYSNAME, @Partner SYSNAME, @Sql NVARCHAR(MAX);
DECLARE cur CURSOR LOCAL FAST_FORWARD FOR SELECT DbName, PartnerServer FROM @Databases;
OPEN cur;
FETCH NEXT FROM cur INTO @DbName, @Partner;
WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        -- 確認目前在主體端才執行 FAILOVER
        IF (SELECT mirroring_role_desc FROM sys.database_mirroring WHERE database_id = DB_ID(@DbName)) = 'PRINCIPAL'
        BEGIN
            SET @Sql = QUOTENAME(@DbName) + N'.sys.sp_executesql N''ALTER DATABASE ' + QUOTENAME(@DbName) + N' SET PARTNER FAILOVER'';';
            EXEC @Sql;  -- 實際需動態 SQL 或連結伺服器
            -- 記錄成功
            INSERT INTO dbo.FailoverLog (DbName, Action, Status, RunTime) VALUES (@DbName, 'FAILOVER', 'SUCCESS', SYSUTCDATETIME());
        END
    END TRY
    BEGIN CATCH
        INSERT INTO dbo.FailoverLog (DbName, Action, Status, ErrorMsg, RunTime) VALUES (@DbName, 'FAILOVER', 'FAILED', ERROR_MESSAGE(), SYSUTCDATETIME());
    END CATCH
    FETCH NEXT FROM cur INTO @DbName, @Partner;
END
CLOSE cur; DEALLOCATE cur;

2. 進階：Service Broker 非同步協調 (生產建議)

-- 發送端 (觸發警示的主體實例)
DECLARE @msg XML = (
    SELECT DbName, 'FAILOVER' AS Action, SYSUTCDATETIME() AS Timestamp
    FROM @Databases
    FOR XML PATH('Database'), ROOT('FailoverCommand')
);
BEGIN DIALOG CONVERSATION @handle
    FROM SERVICE [Mirroring/FailoverService]
    TO SERVICE N'Mirroring/FailoverService'
    ON CONTRACT [Mirroring/FailoverContract]
    WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @handle MESSAGE TYPE [Mirroring/FailoverCommand] (@msg);
END CONVERSATION @handle;

-- 接收端 (各資料庫實例上的啟動佇列預存程序)
CREATE PROCEDURE dbo.usp_ProcessFailoverCommand
AS
BEGIN
    DECLARE @handle UNIQUEIDENTIFIER, @msg XML;
    WHILE (1=1)
    BEGIN
        BEGIN TRAN;
        WAITFOR (RECEIVE TOP(1) @handle = conversation_handle, @msg = message_body
                 FROM [Mirroring/FailoverQueue]), TIMEOUT 5000;
        IF @@ROWCOUNT = 0 ROLLBACK TRAN; ELSE COMMIT TRAN;
        IF @@ROWCOUNT = 0 BREAK;

        -- 解析 XML 執行故障轉移
        DECLARE @DbName SYSNAME;
        DECLARE cur CURSOR FOR
            SELECT T.c.value('(DbName)[1]', 'SYSNAME')
            FROM @msg.nodes('/FailoverCommand/Database') AS T(c);
        OPEN cur;
        FETCH NEXT FROM cur INTO @DbName;
        WHILE @@FETCH_STATUS = 0
        BEGIN
            EXEC ('ALTER DATABASE ' + QUOTENAME(@DbName) + ' SET PARTNER FAILOVER');
            FETCH NEXT FROM cur INTO @DbName;
        END
        CLOSE cur; DEALLOCATE cur;

        END CONVERSATION @handle;
    END
END;

六、應用層配合策略

七、測試與驗證清單

八、總結與演進建議

WMI + Agent 警示 + Service Broker 協調機制，是在 SQL Server 2005/2008/2008R2 時代實現多資料庫協同故障轉移的標準做法。但其複雜度高、維護成本大、邊界情況難測盡。

長期演進路徑：規劃遷移至 AlwaysOn 可用性群組 (SQL 2012+)，利用可用性群組聽眾 (Listener) 自動解決多資料庫故障轉移協調、唯讀路由、備份卸載等問題。若必須維持舊版本，請嚴格執行上述測試清單、建立完整操作手冊、定期演練。