SQL SERVER Always on 監(jiān)控腳本及誤解的示例分析

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

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

里先舉幾個問題

1   Always on 的健康監(jiān)測的時間間隔是多少

2   你現(xiàn)在的集群中的某臺機器工作的狀態(tài)，這里面包含你所處的群組中如果有多個數(shù)據(jù)庫，其中一個數(shù)據(jù)庫因為某些原因的不同步，如何快速發(fā)現(xiàn)

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

其實將這些信息配置在監(jiān)控中，實時監(jiān)控將有利于即使發(fā)現(xiàn)ALWAYS ON 集群是否工作正常，能否正常提供服務(wù)。

在講這些之前，先簡短的說一下 ALWAYS ON 架構(gòu)

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

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

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

下面是一個相關(guān)監(jiān)控的腳本，這個腳本在primary 主機和 secondary 主機上執(zhí)行后的展示是不一樣的。

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 

主要展示相關(guān)的服務(wù)是否在線

2 synchronization_health_desc  當前同步的狀態(tài)

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

大致上面的腳本是這樣。

最后在說一下 RTO 

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

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

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

借用Micorsoft 官方的 statement 

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

我們從下面的 系統(tǒng)  DMV 中獲取相關(guān)的參數(shù)sys.dm_hadr_database_replica_states 中的  redo_queue_size（KB/S），redo_rate secondary節(jié)點上做REDO的速率（KB/S）

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

下面有一個腳本可以來自動判斷，如果在主節(jié)點上執(zhí)行，則自動會忽略，不顯示數(shù)據(jù)，只有在從節(jié)點上執(zhí)行，才會顯示出當前節(jié)點的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

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

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