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 |
sql | SQL Server > sqlsrv | HA >