【MSSQL】AlwaysOn集群监控

参考：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