1

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

itzik Paz
  • 403
  • 5
  • 14
  • 1
    This really doesn't have anything to do with **programming**, but with DB administration - so it's off-topic here and belongs on [dba.stackexchange.com](http://dba.stackexchange.com) - voting to move. – marc_s May 04 '16 at 12:26

1 Answers1

4

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;

enter image description here

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

RichardCL
  • 1,432
  • 10
  • 9