SQL: Always On Availability Group - Group Info
Author: jason
Date: 2014-05-08 14:23:47
Category: Musing

Here is small script to review the Always On Availability Group status/settings with tSQL.

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


--Availability Group Info
SELECT name AvailabilityGroupName
, gs.primary_replica, gs.primary_recovery_health_desc, gs.synchronization_health_desc
, rs.failover_mode_desc primary_failver_mode_desc
, (CASE WHEN ag.failure_condition_level = 1 THEN 'Automatic Failover when SQL Service is down or AG Lease Expires'
WHEN ag.failure_condition_level = 2 THEN 'Automatic Failover when SQL Instance cannot connect or Availibility replica failed'
WHEN ag.failure_condition_level = 3 THEN 'Automatic Failover with Critical SQL internal errors'
WHEN ag.failure_condition_level = 4 THEN 'Automatic Failover with Moderate SQL internal errors'
WHEN ag.failure_condition_level = 5 THEN 'Automatic Failover Exhausted SQL Worker Threads or Unsolvable Deadlock'
ELSE CAST(ag.failure_condition_level AS VARCHAR(50)) END) [failure_condition_level]
, ag.automated_backup_preference_desc
FROM sys.availability_groups ag
INNER JOIN sys.dm_hadr_availability_group_states gs ON gs.group_id = ag.group_id
INNER JOIN sys.availability_replicas rs ON rs.group_id = ag.group_id AND rs.replica_server_name = gs.primary_replica

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