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