参考:https://www.cnblogs.com/chenmh/p/6972007.html
集群节点监控
master 库下的系统视图
视图 | 说明 |
---|---|
sys.availability_groups | 组信息 |
sys.availability_group_listener_ip_addresses | 监听地址信息 |
sys.availability_replicas | 节点配置信息 |
sys.dm_hadr_availability_replica_cluster_nodes | 组和组节点 |
sys.dm_hadr_availability_replica_cluster_states | |
sys.dm_hadr_availability_replica_states | 各节点角色 |
sys.dm_hadr_availability_group_states | 集群组状态 |
sys.dm_hadr_database_replica_states | 库同步状态 |
sys.dm_hadr_database_replica_cluster_states | 同步的库信息 |
监控节点状态可通过集群地址(VIP)获取 sys.availability_replica_states 视图节点状态,来判断节点角色变动,也可以直接从 sys.dm_hadr_availability_group_states 获取主节点信息。
注意!AlwaysOn 也是库级 HA(Windows系统的故障转移集群以实例的维度,库为最小实体),需要根据关联查询对应库同步状态和节点库的状态。
监控
SELECT
dharcn.group_name, dharcn.node_name,
dhars.replica_id, dhars.group_id,
-- database info
dhdrs.database_id, dhdrs.database_state, dhdrs.database_state_desc,
dhdrs.synchronization_state, dhdrs.synchronization_state_desc,
-- database name
dhdrcs.database_name,
-- database role
dhars.role, dhars.role_desc, dhars.operational_state, dhars.operational_state_desc,
dhars.connected_state, dhars.connected_state_desc, dhars.recovery_health, dhars.recovery_health_desc,
dhars.synchronization_health, dhars.synchronization_health_desc,
-- replication config
ar.endpoint
FROM
sys.dm_hadr_availability_replica_states dhars
INNER JOIN sys.dm_hadr_availability_replica_cluster_states dmarcs ON dhars.replica_id = dmarcs.replica_id
INNER JOIN sys.availability_replicas ar ON dhars.replica_id = ar.replica_id
INNER JOIN sys.dm_hadr_availability_replica_cluster_nodes dharcn ON ar.replica_server_name = dharcn.replica_server_name
INNER JOIN sys.dm_hadr_database_replica_cluster_statues dhdrcs ON dhars.replica_id = dhdrcs.replica_id
INNER JOIN sys.dm_hadr_database_replica_states dhdrs ON dhars.replica_id = dhdrs.replica_id AND dhdrcs.group_database_id = dhdrs.group_database_id
WHERE dhars.is_local = 1