This blog is part of the blog series named SQL Server Monitoring.
Purpose
To get a quick overview of the Always On configuration.
Sources
This feature queries the 7 DMV’s sys.availability_groups, sys.availability_read_only_routing_lists, sys.availability_replicas, sys.dm_hadr_availability_group_states, sys.dm_hadr_cluster, sys.dm_hadr_cluster_members and sys.dm_hadr_database_replica_states.
Report
The report are showing 6 tables where each are showing selected information about the Windows Server Failover Clustering (WSFC) and Always On configurations.
WSFC quorum state
The output here is based on the DMV sys.dm_hadr_cluster which returns a row for the WSFC cluster used by the instance.
WSFC quorum members
The output here is based on the DMV sys.dm_hadr_cluster_members which returns rows for every member of the WSFC cluster.
Always On group states
The output here is based on the DMV sys.dm_hadr_availability_group_states which returns rows for all Always On groups and the servers belonging to the groups.
Always On read only routing
The output here is based on the DMV sys.availability_read_only_routing_lists which returns rows for the Always On read-only configuration..
Always On replicas
The output here is based on the DMV sys.availability_replicas which return rows for all the configured Always On replicas.
Always On database states
The output here is based on the DMV sys.dm_hadr_database_replica_states which returns rows for every database configured to be member of Always On groups.
Model
The model for the report uses the facts [Always On database states], [Always on group states], [Always On read only routing], [Always on replica], [WSFC quorum members] and [WSFC quorum state], and the dimension [Always On group-replica].
Configuration
The feature is part of the Database configuration service and where there is no configuration for this feature.
Scheduling
The default schedule for the feature is once every hour.
Retention
The default retention for the table dbo.fhsmAlwaysOnState is 1.825 days (5 years).
