T-SQL Commands to View AGS Details

Summary

The following Transact-SQL commands are used to retrieve the AGS details displayed in DxAdmin.

Information

Use the following command to retrieve replica details.

SELECT ars.role_desc,ar.replica_server_name,ar.endpoint_url,ar.availability_mode_desc,ars.connected_state_desc,ars.operational_state_desc
FROM sys.dm_hadr_availability_replica_states ars
JOIN sys.availability_replicas ar on ars.replica_id = ar.replica_id
JOIN sys.availability_groups ag on ar.group_id = ag.group_id
WHERE ag.name = '<AG_Name>'

Use the following command to retrieve database details.

SELECT ar.replica_server_name, drs.database_id,db_name(drs.database_id)[database_name], drs.is_local, drs.is_primary_replica, drs.is_suspended, drs.suspend_reason_desc, drs.database_state_desc, drs.synchronization_state_desc,drs.synchronization_health_desc
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar on drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag on ar.group_id = ag.group_id
WHERE ag.name = '<AG_Name>'