SQL SERVER Always on 監控腳本及誤解的示例分析

SQL SERVER Always on 監控腳本及誤解的示例分析，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

SQL SERVER Always on 監控貌似少有人提起，大部分都是通過操控面板，或者發現了某些可用性組中的某些數據庫的已經不再同步了，才意識到出現了問題。 

里先舉幾個問題

1   Always on 的健康監測的時間間隔是多少

2   你現在的集群中的某臺機器工作的狀態，這里面包含你所處的群組中如果有多個數據庫，其中一個數據庫因為某些原因的不同步，如何快速發現

3   如果主機故障，則主庫切換到從庫的 RTO 是多長時間

其實將這些信息配置在監控中，實時監控將有利于即使發現ALWAYS ON 集群是否工作正常，能否正常提供服務。

在講這些之前，先簡短的說一下 ALWAYS ON 架構

從圖中我們可以看出，SQL SERVER Always on 大致的復制原理和傳輸方式，這里需要強調一件事情，就是 Always on 的同步模式和異步模式，很多人認為，我只要選擇了同步的模式就可以，從庫就一定是實時的和主庫同步，數據在任意時間點上不會有任何差池。

這樣的理解其實從絕對值上來理解是錯誤，從圖中看，我們的從庫在log Hardened，就已經 commit ACK 了，意思就是數據僅僅是寫到了從庫的LDF 文件里面，就已經告知主庫，可以進行數據的commit了，但這時候數據并未刷進數據文件，所以就算是同步的模式，primary 和 standby 數據庫之間的同步到的數據也是有差異的時間的。具體看REDO 工作做的速度情況。

OK 現在講清楚同步名詞產生的一些誤解。下面就的說說 SQL SERVER ALWAYS ON 的一些監控SCRIPT 的問題，后續可以通過這些 SCRIPT 和 DMV ，來將一些 always on 的STATUS 進行圖形化的展示，而不非要進入SQL SERVER 才能得到這些數據，和狀態的顯示。

下面是一個相關監控的腳本，這個腳本在primary 主機和 secondary 主機上執行后的展示是不一樣的。

SELECT

    ag.name AS 'GroupName' 

   ,cs.replica_server_name AS 'Replica'

   ,rs.role_desc AS 'Role'

   ,ag.health_check_timeout as health_check_timeout_ms

   ,case ag.failure_condition_level

   when 1 then 'service down'

   when 2 then 'the server is out of control'

   when 3 then  'default value,or spin lock'

   when 4 then  'Please check your memory resource'

   when 5 then  'automatic failover'

   end as failure_condition_level

   ,REPLACE(ar.availability_mode_desc,'_',' ') AS 'AvailabilityMode'

   ,ags.primary_recovery_health_desc

   ,ags.secondary_recovery_health_desc

   ,ar.failover_mode_desc AS 'FailoverMode'

   ,rs.recovery_health_desc

   ,rs.synchronization_health_desc

   ,ar.seeding_mode_desc AS 'SeedingMode'

   ,ar.endpoint_url AS 'EndpointURL'

   ,al.dns_name AS 'Listener'

FROM sys.availability_groups ag

JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states cs ON ags.group_id = cs.group_id 

JOIN sys.availability_replicas ar ON ar.replica_id = cs.replica_id 

JOIN sys.dm_hadr_availability_replica_states rs  ON rs.replica_id = cs.replica_id 

LEFT JOIN sys.availability_group_listeners al ON ar.group_id = al.group_id

這里主要由幾個字段，需要介紹一下

1 primary_recovery_health_desc  ,  secondary_recovery_health_desc 

主要展示相關的服務是否在線

2 synchronization_health_desc  當前同步的狀態

3 recovery_health_desc   判斷當前同步組中的所有數據庫是否都在同步中，如果展示為 ONLINE_IN_PROGRESS， 則說明在復制組中的某個數據庫不在同步狀態， 如果展示為 PROGRESS ,則說明在這個同步組中的所有數據庫均在正常同步狀態

大致上面的腳本是這樣。

最后在說一下 RTO 

Estimating failover time (RTO), 這個名詞，其實就是要評估一下，如果我們的集群中的primary 失敗，我們需要多長的時間進行failover

一個 failover 主要需要的時間是有以下幾點組成的

主機失敗的診斷和決策時間，進行數據的redo時間，以及最后的切換時間

借用Micorsoft 官方的 statement 

我們能判斷的就是本地的需要redo的隊列和當前的 redo rate 之間的比率

我們從下面的 系統  DMV 中獲取相關的參數sys.dm_hadr_database_replica_states 中的  redo_queue_size（KB/S），redo_rate secondary節點上做REDO的速率（KB/S）

通過兩個的比值就可以得到一個需要多長時間完成這個隊列的時間

下面有一個腳本可以來自動判斷，如果在主節點上執行，則自動會忽略，不顯示數據，只有在從節點上執行，才會顯示出當前節點的TREDO/S

SELECT

    ag.name AS 'GroupName' 

    ,db_name(hst.database_id) as dbname

   ,cs.replica_server_name AS 'Replica'

   ,ag.health_check_timeout as health_check_timeout_ms

   ,cast(hst.redo_queue_size as float) / hst.redo_rate as Tredo/S

   ,ags.primary_recovery_health_desc

   ,ags.secondary_recovery_health_desc

   ,ar.failover_mode_desc AS 'FailoverMode'

FROM sys.availability_groups ag

JOIN sys.dm_hadr_availability_group_states ags ON ag.group_id = ags.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states cs ON ags.group_id = cs.group_id 

Join sys.dm_hadr_database_replica_states as hst on ags.group_id = hst.group_id

JOIN sys.availability_replicas ar ON ar.replica_id = cs.replica_id 

where hst.database_id = db_id('test') and ar.replica_metadata_id is not null and ags.primary_recovery_health is null

其實如果將這樣的查詢做到監控界面上，將對ALWAYSON 的故障發現和問題解決可能更高效

看完上述內容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業資訊頻道，感謝您對億速云的支持。