SQL SERVER Always on 监控脚本与一些听到的误解

最新推荐文章于 2022-03-24 14:04:50 发布

Austindatabases

最新推荐文章于 2022-03-24 14:04:50 发布

阅读量511

点赞数

本文链接：https://blog.csdn.net/liuhuayang/article/details/106681751

版权

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