SQL: Always On Availability Group - Server Replica Info
Author: jason
Date: 2014-05-08 14:28:21
Category: Musing

Here is small script to review the Availability Group Server Replica info with tSQL.

Monitor Availability Groups: http://technet.microsoft.com/en-us/library/ff878305.aspx





Code

--Replica Information
SELECT ag.name AvailabilityGroupName, r.replica_server_name, ars.is_local, ars.role_desc
, ars.operational_state_desc, ars.connected_state_desc, ars.recovery_health_desc
, REPLACE(ars.synchronization_health_desc, '_', ' ') synchronization_health_desc
, ars.last_connect_error_description
, REPLACE(r.availability_mode_desc, '_', ' ') availability_mode_desc, r.failover_mode_desc
, r.primary_role_allow_connections_desc, r.secondary_role_allow_connections_desc, r.backup_priority
FROM sys.dm_hadr_availability_replica_states ars
INNER JOIN sys.availability_replicas r ON r.replica_id = ars.replica_id and r.group_id = ars.group_id
INNER JOIN sys.availability_groups ag ON ag.group_id = ars.group_id








jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009