SQL Server 2008 R2 複制監控腳本：分發服務器端監控復制進程、命令積壓、傳遞延遲與訂閱端等待

部署位置

**在分發服務器 (Distributor) 上執行**，定期作業 (SQL Agent Job) 每 5-15 分鐘跑一次，結果寫入監控表或觸發告警。

核心監控維度

1. 復制代理進程是否運行 (Log Reader / Distribution / Merge)
2. 發布服務器 → 分發服務器：未傳遞命令數、事務數 3. 分發服務器 → 訂閱服務器：未傳遞命令數、延遲秒數 4. 衝突檢測 (合併復制) 5. 代理歷史錯誤記錄

完整監控腳本框架

SET NOCOUNT ON;

-- 1. 臨時表收集所有代理狀態
CREATE TABLE #ReplStatus (
    dbname SYSNAME, name NVARCHAR(100), status INT,
    publisher SYSNAME, publisher_db SYSNAME, publication SYSNAME,
    start_time DATETIME, time DATETIME, duration INT,
    comments NVARCHAR(255),
    delivered_transactions INT, delivered_commands INT,
    delivery_rate INT, job_id VARCHAR(36),
    delivery_latency INT,
    subscriber SYSNAME, subscriber_db SYSNAME,
    subscription_type INT, subscriber_type TINYINT,
    publisher_insertcount INT, publisher_updatecount INT, publisher_deletecount INT, publisher_conflicts INT,
    subscriber_insertcount INT, subscriber_updatecount INT, subscriber_deletecount INT, subscriber_conflicts INT,
    agent_type NVARCHAR(4000)
);

-- 2. 收集各類代理狀態 (示例：分發代理)
INSERT INTO #ReplStatus
EXEC distribution.dbo.sp_replmonitorhelppublication @publisher = 'PublisherName';

-- 3. 關鍵指標檢查與告警邏輯
SELECT 
    publication, 
    delivery_latency AS [延遲秒數],
    delivered_commands AS [待傳遞命令],
    CASE WHEN status <> 1 THEN '代理異常' ELSE '正常' END AS [代理狀態]
FROM #ReplStatus
WHERE agent_type = 'Distribution Agent';

-- 4. 清理
DROP TABLE #ReplStatus;

關鍵系統存儲過程

告警閾值建議

• `delivery_latency` > 300 秒 → 告警
• `delivered_commands` > 100000 → 積壓告警
• `status` ≠ 1 (Started) → 代理停止告警
• 錯誤日誌含 `fatal`/`error` → 即時告警

擴展：注入監控表 + 報表可視化

CREATE TABLE dbo.ReplicationMonitorLog (
    LogID BIGINT IDENTITY PRIMARY KEY,
    CheckTime DATETIME2 DEFAULT SYSUTCDATETIME(),
    Publication SYSNAME,
    Subscriber SYSNAME,
    LatencySec INT,
    PendingCmds INT,
    AgentStatus VARCHAR(20),
    ErrorMsg NVARCHAR(MAX)
);