Tsql - getting list of instances in the alwayson Availability group

Question

I want to connect to the listener in the AlwaysOn availability group and by running a query I will get the list of instances that are part of the AlwaysOn . For example: I has listener "MylistenerHost" and there are 2 instances connecting to him: SqlHost1(primary-readwrite) and SqlHost2(secondary - readonly) .

I need a query that I will run and he will show me these instantiates, SqlHost1 and SqlHost2 and that, SqlHost1 is the primary and SqlHost2 is the secondary.

10X

Answer 1

You can perform a query like this on the primary replica.

SELECT C.name, CS.replica_server_name, CS.join_state_desc, RS.role_desc, RS.operational_state_desc, RS.connected_state_desc, RS.synchronization_health_desc
    FROM sys.availability_groups_cluster AS C
        INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS CS
            ON CS.group_id = C.group_id
        INNER JOIN sys.dm_hadr_availability_replica_states AS RS
            ON RS.replica_id = CS.replica_id;

See Books Online > Monitor Availability Groups (Transact-SQL): https://msdn.microsoft.com/en-us/library/ff878305.aspx#AGlisteners