sql | SQL Server‎ > ‎sqlsrv | HA‎ > ‎

sql.ha | AG info

DECLARE @AG_DB TABLE (DatabaseName NVARCHAR(MAX))
  ;WITH AG AS (
SELECT  
ISNULL(DB.database_id, -1) LOCAL_DB_ID
, DRCS.database_name DB_NAME
, DRCS.group_database_id CLUSTER_DB_ID
, DRS.is_local IS_LOCAL_DB
, AGC.name AG_NAME
, DRS.group_id AG_ID
, ARS.role_desc ROLE
, AR.replica_server_name CLUSTER_NODE
, DRCS.replica_id CLUSTER_NODE_ID
, AR.availability_mode_desc AVAILABILITY_MODE
, AR.failover_mode_desc FAILOVER_MODE
, DRCS.is_database_joined IS_DB_JOINED
, DRCS.is_failover_ready FAILOVER_READY
, DRS.database_state DB_STATE
, DRS.synchronization_state SYNC_STATE
, DRS.synchronization_health SYNC_HEALTH
, ARS.is_local IS_LOCAL_REPLICA
, ARS.connected_state CONN_STATE
, ARS.operational_state OP_STATE
FROM 
sys.dm_hadr_database_replica_cluster_states DRCS
LEFT JOIN SYS.DATABASES DB
ON DB.group_database_id = DRCS.group_database_id
AND DB.replica_id = DRCS.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states DRS
ON DRCS.replica_id = DRS.replica_id
AND DRCS.group_database_id = DRS.group_database_id
LEFT JOIN sys.availability_groups_cluster AGC
ON AGC.group_id = DRS.group_id
LEFT JOIN sys.dm_hadr_availability_replica_states ARS
ON ARS.group_id = DRS.group_id
AND ARS.replica_id = DRS.replica_id
LEFT JOIN sys.availability_replicas AR
ON AR.group_id = DRS.group_id
AND AR.replica_id = DRS.replica_id
  ) INSERT INTO @AG_DB
  SELECT DB_NAME FROM AG
  WHERE IS_LOCAL_DB = 1
AND ROLE <> 'PRIMARY' 




:setvar PrimaryNode "xxx\xxx"
:setvar SecondaryNode "yyy\yyy"
:CONNECT $(PrimaryNode)
select * from [DBA_Admin].[dbo].[DatabaseSelect]('user_databases')
go
:CONNECT $(SecondaryNode)
select * from [DBA_Admin].[dbo].[DatabaseSelect]('user_databases')
go
Comments