List database names, which are in AO AG:
|
1 2 3 4 5 6 7 8 9 |
SELECT name FROM sys.sysdatabases where dbid>4 and name in( SELECT DB_NAME(drs.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY' ) |
Local databases , which are outside of AO AG:
|
1 2 3 4 5 6 7 |
SELECT name FROM sys.sysdatabases where dbid>4 and name not in( SELECT DB_NAME(drs.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY') |
Writeable databases : primary db or only in local instance
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT name FROM sys.sysdatabases where dbid>4 and name not in( SELECT DB_NAME(drs.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' OR ars.role_desc='SECONDARY') UNION SELECT name FROM sys.sysdatabases where dbid>4 and name in( SELECT DB_NAME(drs.database_id) as name FROM (( sys.availability_groups AS ag JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id ) JOIN sys.dm_hadr_availability_replica_states AS ars ON ar.replica_id = ars.replica_id) JOIN sys.dm_hadr_database_replica_states AS drs on ag.group_id = drs.group_id and drs.replica_id = ars.replica_id where ars.role_desc='PRIMARY' --OR ars.role_desc='SECONDARY' |