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 的故障发现和问题解决可能更高效

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。