SHARE
TWEET

Snippet: Check if db not in AG secondary role

SQLSoldier Oct 28th, 2016 85 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Insert Into @DBs (DBName)
  2. Select D.name
  3. From sys.databases As D
  4. Where D.database_id > 4
  5. And D.state = 0
  6. And is_read_only = 0
  7. And D.source_database_id Is Null
  8. And Not Exists (SELECT 1 FROM sys.dm_hadr_database_replica_states AS hdrs
  9.             INNER JOIN sys.availability_replicas AS ar ON hdrs.replica_id = ar.replica_id
  10.             INNER JOIN sys.dm_hadr_availability_replica_states AS hars ON hdrs.replica_id = hars.replica_id
  11.             Where hars.role_desc = 'SECONDARY'
  12.             And ar.replica_server_name = @@SERVERNAME
  13.             And hdrs.database_id = D.database_id);
RAW Paste Data
Top