Finding SQL Primary and Secondary Replica in SQL 2012 AG Groups

 

Whenever I try to find out which is primary or secondary in SQL 2012 AG groups, I find it out through SSMS by connecting to AG Group and then opening Availability Groups. This is a tedious process sometimes and I wanted to find much better process. I posted a question on Twitter and got great info from Allan Hirt @SQLHA. He suggested two links which give great information on using DMV. Here are the links

• http://t.co/pmRal95p4l
• http://t.co/Nu8LF3xAf1

Here is another script compiled by one of my co-worker (Tim) which displays the following

• Availability Group Listener
• Availability Group
• State
• IP Address
• Databases within AG groups
• Replicas
• Primary Replica

Hope this helps

   1:  --

   2:  -- This script provides a 1 line view by AG indicating the basic AG summary information 

   3:  --

   4:  SELECT AvailabilityGroupListener, AvailabilityGroup, state_desc, ip_address, Databases, Replicas, primary_replica

   5:  FROM  (

   6:  SELECT   distinct qo.*,qi.*

   7:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc, ags.primary_replica

   8:  from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

   9:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  10:  join sys.dm_hadr_availability_group_states ags on ag.group_id=ags.group_id ) qo

  11:  CROSS APPLY

  12:          (

  13:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  14:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  15:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qi

  16:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  17:          ORDER BY database_name  FOR XML PATH ('')

  18:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  19:                

  20:  ) Fo

  21:  CROSS APPLY

  22:          (

  23:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY replica_server_name) WHEN 1 THEN '' ELSE ', ' END + Fi.replica_server_name

  24:          FROM    (

  25:  SELECT   qo.*,qi.*,ar.replica_server_name

  26:  FROM    (select distinct ag.name as [AvailabilityGroup], ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  27:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id) qo

  28:  CROSS APPLY

  29:          (

  30:          SELECT  CASE ROW_NUMBER() OVER(ORDER BY database_name) WHEN 1 THEN '' ELSE ', ' END + qi.database_name

  31:          FROM    (select ag.name as [AvailabilityGroup],ag.group_id,agl.dns_name as [AvailabilityGroupListener],agl.port,aglip.ip_address,aglip.state_desc ,adc.database_name from sys.availability_group_listeners agl join sys.availability_group_listener_ip_addresses aglip on agl.listener_id=aglip.listener_id

  32:  join sys.availability_groups ag on ag.group_id=agl.group_id join sys.availability_databases_cluster adc on adc.group_id=ag.group_id

  33:         ) qi

  34:          WHERE   qi.AvailabilityGroup = qo.AvailabilityGroup

  35:          ORDER BY database_name  FOR XML PATH ('')

  36:          ) qi(Databases) join sys.availability_replicas ar on ar.group_id=qo.group_id

  37:  ) Fi

  38:  WHERE   Fi.AvailabilityGroup = FO.AvailabilityGroup

  39:  ORDER BY replica_server_name  FOR XML PATH ('')

  40:  ) Fi(Replicas)