Get Availability group information
T-SQL :
GO select name,group_id,health_check_timeout from master.sys.availability_groups GO
Get Availability group Replica information
T-SQL :
go select replica_server_name,create_date, modify_date,endpoint_url,read_only_routing_url,avalibility_mode, failover_mode,session_timeout,backup_priority from master.sys.avalibility_replicas go
Get Availability group databases
T-SQL :
select database_name,is_failover_ready,is_pending_secondary_suspend,is_database_joined from master.sys.dm_hadr_database_replica_cluster_states go select * from sys.dm_hadr_database_replica_states go
Get Availability group listeners
T-SQL:
go select * from sys.availability_group_listeners go
Join all DMV’s for AlwaysOn configuration
T-SQL :
select ag.name,replicas.replica_server_name,replicas.availability_mode_desc, replicas.failover_mode_desc,replicas.session_timeout, agd.database_name,agd.is_database_joined,drs.is_local, drs.is_primary_replica,drs.synchronization_state_desc, drs.synchronization_health_desc,drs.database_state_desc,agl.dns_name, agl.port,agl.ip_configuration_string_from_cluster from master.sys.availability_replicas replicas inner join master.sys.availability_groups ag on replicas.group_id=ag.group_id inner join master.sys.dm_hadr_database_replica_cluster_states agd on agd.replica_id=replicas.replica_id inner join master.sys.dm_hadr_database_replica_states drs on drs.replica_id=replicas.replica_id inner join master.sys.availability_group_listeners agl on agl.group_id=ag.group_id go