修正后的完整查詢
SELECT ar.replica_server_name AS [副本名稱],ar.availability_mode_desc AS [同步模式],DB_NAME(dbr.database_id) AS [數據庫名稱],dbr.database_state_desc AS [數據庫狀態],dbr.synchronization_state_desc AS [同步狀態],dbr.synchronization_health_desc AS [同步健康狀態],ISNULL(CASE dbr.redo_rateWHEN 0 THEN -1ELSE CAST(dbr.redo_queue_size AS FLOAT) / dbr.redo_rateEND, -1) AS [Redo延遲(秒)],ISNULL(CASE dbr.log_send_rateWHEN 0 THEN -1ELSE CAST(dbr.log_send_queue_size AS FLOAT) / dbr.log_send_rateEND, -1) AS [Log傳送延遲(秒)],dbr.redo_queue_size AS [Redo等待隊列(KB)],dbr.redo_rate AS [Redo速率(KB/S)],dbr.log_send_queue_size AS [Log傳送等待隊列(KB)],dbr.log_send_rate AS [Log傳送速率(KB/S)] -- 修正反斜杠為斜杠
FROM master.sys.availability_replicas AS ar
INNER JOIN master.sys.dm_hadr_database_replica_states AS dbr -- 補全表名ON ar.replica_id = dbr.replica_id -- 添加關聯條件AND ar.group_id = dbr.group_id; -- 按可用性組關聯
SQL 查詢數據詳解及監控用途
以下 SQL 查詢用于獲取 SQL Server AlwaysOn 可用性組中數據庫副本的詳細狀態和性能指標。這些數據非常適合用于實時監控和高可用性環境的健康檢查。
可查詢的關鍵數據及監控用途
字段名 | 數據來源 | 監控用途 |
---|---|---|
副本名稱 | sys.availability_replicas | 標識每個副本的服務器名稱,用于區分主副本和輔助副本。 |
同步模式 | sys.availability_replicas | 顯示副本的同步模式: ? SYNCHRONOUS_COMMIT (同步提交)? ASYNCHRONOUS_COMMIT (異步提交)。 |
數據庫名稱 | sys.dm_hadr_database_replica_states | 標識可用性組中的具體數據庫,用于定位問題數據庫。 |
數據庫狀態 | sys.dm_hadr_database_replica_states | 監控數據庫是否在線(ONLINE )或處于恢復中(RESTORING )。 |
同步狀態 | sys.dm_hadr_database_replica_states | 判斷數據同步是否正常: ? SYNCHRONIZED (已同步)?? SYNCHRONIZING (同步中)? NOT SYNCHRONIZING (未同步)。 |
同步健康狀態 | sys.dm_hadr_database_replica_states | 健康狀態分級: ? HEALTHY (健康)?? PARTIALLY_HEALTHY (部分健康)? NOT_HEALTHY (異常)。 |
Redo延遲(秒) | 計算字段(redo_queue_size / redo_rate ) | 輔助副本應用日志的預計耗時。 閾值建議:>30 秒需檢查輔助副本性能。 |
Log傳送延遲(秒) | 計算字段(log_send_queue_size / log_send_rate ) | 主副本發送日志到輔助副本的耗時。 閾值建議:>10 秒需優化網絡帶寬。 |
Redo等待隊列(KB) | sys.dm_hadr_database_replica_states | 輔助副本待應用的日志量。 閾值建議:>100,000 KB 需排查磁盤 I/O。 |
Redo速率(KB/S) | sys.dm_hadr_database_replica_states | 輔助副本每秒應用的日志量。 閾值建議:<500 KB/S 表示性能不足。 |
Log傳送等待隊列(KB) | sys.dm_hadr_database_replica_states | 主副本待發送的日志量。 閾值建議:>50,000 KB 需檢查主副本負載或網絡。 |
Log傳送速率(KB/S) | sys.dm_hadr_database_replica_states | 主副本每秒發送的日志量。 閾值建議:<1,000 KB/S 需優化網絡或啟用壓縮。 |
監控場景示例
1. 實時同步狀態儀表盤
將查詢結果集成到 Grafana 或 Power BI 中,生成以下可視化圖表:
? 同步健康狀態:用紅/黃/綠顏色標記各副本狀態。
? 延遲趨勢圖:展示 Redo延遲
和 Log傳送延遲
的歷史變化。
? 隊列積壓熱力圖:按數據庫和副本顯示 Redo等待隊列
和 Log傳送隊列
。
2. 自動化告警
通過 Zabbix 或 Prometheus 設置警報規則:
# Prometheus 警報規則示例
- alert: HighRedoLatencyexpr: redo_latency_seconds > 30annotations:summary: "高 Redo 延遲 ({{ $value }}秒)"description: "副本 {{ $labels.replica }} 的數據庫 {{ $labels.database }} Redo 延遲過高!"- alert: LogSendQueueOverflowexpr: log_send_queue_kb > 50000annotations:summary: "Log 傳送隊列積壓"description: "主副本的 Log 傳送隊列積壓 {{ $value }} KB,需立即處理!"
3. 日常巡檢報告
使用 PowerShell 或 Python 定時運行查詢并生成 CSV/Excel 報告:
# PowerShell 腳本示例
$query = @"
SELECT [副本名稱], [同步模式], [數據庫名稱], [同步健康狀態], [Redo延遲(秒)]
FROM (...完整查詢...)
"@
Invoke-SqlCmd -Query $query -ServerInstance "YourServer" | Export-Csv -Path "AlwaysOn_Report_$(Get-Date -Format 'yyyyMMdd').csv"
權限要求
? 最低權限:VIEW SERVER STATE
(允許查詢動態管理視圖)。
? 推薦權限:CONTROL AVAILABILITY GROUP
(可結合故障轉移操作)。
總結
通過這些 SQL 查詢,您可以監控以下核心內容:
- 同步健康性:快速發現
NOT_HEALTHY
狀態并介入處理。 - 延遲與性能:定位網絡瓶頸(高
Log傳送延遲
)或副本性能問題(低Redo速率
)。 - 隊列積壓:預警數據同步滯后風險,防止故障轉移時數據丟失。
建議將查詢集成到自動化監控系統中,并定期優化閾值規則,以保障 AlwaysOn 環境的穩定性和高可用性。