SQL query to check if secondary database is removed from availability group

Question

I have an AG with one primary replica and one secondary replica. I have created a script that queries transaction log backups from these database replicas and restores them to a target database.

Recently one user removed one secondary database from the AG and started using it as a standalone database as explained here https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/remove-a-secondary-database-from-an-availability-group-sql-server?view=sql-server-2017. The user also started a backup chain of this database.

The script now fails to restore as the recovery fork guid has changed on the secondary database.

Is there a way to detect if a database has left the AG so that in the script while I query the backupsets, I can skip the database if it is no longer part of the AG?

Thanks!

Answer 1

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG.

Returns a row for each database that is participating in an Always On availability group for which the local instance of SQL Server is hosting an availability replica. This dynamic management view exposes state information on both the primary and secondary replicas. On a secondary replica, this view returns a row for every secondary database on the server instance. On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database

Here is the query:

if exists(
    select 
        DBName = db_name(s.database_id)
        ,s.is_local
        ,s.synchronization_state_desc
        ,s.synchronization_health_desc
    from sys.dm_hadr_database_replica_states s
    where db_name(s.database_id) = 'your_database'
    )
begin
--do something useful
end
else
begin
--exit, probably without error
end

This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.

Answer 2

You can use the following query to determine if a given database has a replica on a given server. Replace the placeholders in the WHERE clause to specify the database and server you are checking and run it on the primary.

SELECT 
  * 
FROM 
  sys.dm_hadr_availability_replica_states rs 
  JOIN sys.dm_hadr_database_replica_cluster_states dcs ON dcs.replica_id = rs.replica_id 
  JOIN sys.dm_hadr_availability_replica_cluster_states acs ON acs.replica_id = rs.replica_id 
WHERE 
  dcs.database_name = '<databasename>' 
  AND acs.replica_server_name = '<servername>'