info@techdevops.com
TechDevOps.com
Explore Tools
Experts in Microsoft SQL Server Operations, Performance, Migrations, Replication & Cloud



Always On - Check if Primary Replica
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2020-05-12








Always On Script to check if Primary Replica


IF SERVERPROPERTY ('IsHadrEnabled') = 1
BEGIN
SELECT
ARS.Role_desc AS 'Replica Role'
, AGC.Name AS 'Availability Group'
, ARCS.Replica_server_name AS 'SQL Instance'
, AGL.Dns_name AS 'Listener Name' -- Listener Name
, AGL.Port AS 'Listner Port' -- Listener Port
, AGL.IP_configuration_string_from_cluster -- Listener Config
, AGC.Failure_condition_level
, AGC.Health_check_timeout
, AGC.Automated_backup_preference_desc
, ARS.Is_local
, ARS.Operational_state_desc
, ARS.Connected_state_desc
, ARS.Synchronization_health_desc
FROM
sys.availability_groups_cluster AS AGC
INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AGC.group_id
INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = ARCS.replica_id
INNER JOIN sys.availability_group_listeners AS AGL ON AGL.group_id = ARS.group_id
WHERE
ARS.role_desc = 'PRIMARY'
END