Dealing With an Unjoined Database On a Primary Replica

  • Article

Suppose you force your availability group to failover to a secondary replica, and when you open Object Explorer on the new primary you see the following:

Why does this database have a “Warning” icon? This icon specifies that the database "DB-5" is not joined to the availability group. You can query the sys.dm_hadr_database_replica_cluster_states DMV and inspect the “is_database_joined” column to verify this. Here is a sample query:

select ar.replica_server_name, dbrcs.database_name, dbrcs.is_database_joined
from sys.dm_hadr_database_replica_cluster_states as dbrcs
join sys.availability_replicas as ar on dbrcs.replica_id = ar.replica_id

Here's the result of this query run on the server shown above:

replica_server_name database_name is_database_joined
WSNAVELY1-fs821 DB-5 1
WSNAVELY1-fs821 DB-2 1
WSNAVELY1-fs821 DB-4 1
WSNAVELY1-fs821 DB-1 1
WSNAVELY1-fs821 DB-3 1
WSNAVELY1-lhi72 DB-5 0
WSNAVELY1-lhi72 DB-2 1
WSNAVELY1-lhi72 DB-4 1
WSNAVELY1-lhi72 DB-1 1
WSNAVELY1-lhi72 DB-3 1

Notice that "DB-5" has a 0 in the "is_database_joined" column. So, to summarize our situation, we have an unjoined database on the primary replica. The main scenario in which this occurs is failing over to a secondary replica that has unjoined databases. Note that this kind of failover definitely results in data loss, so this must be a forced failover.

What should you do in this situation? You have a few options:

  1. Failover back to the original primary, then ensure the database is joined on all secondaries.
  2. On the new primary, remove the unjoined database from the availability group, then re-add the database to the availability group. Note that before adding the database, you will need to manually synchronize the database across all replicas, restoring from backups if necessary.

Removing an unjoined database from the primary is not supported in the UI, so you will need to run a Transact-SQL statement to accomplish this task:

ALTER AVAILABILITY GROUP [agname] REMOVE DATABASE [databasename]

You should get the error message below upon executing this query, however the database will still be removed from the availability group. 

Msg 41133, Level 16, State 2, Line 3

Cannot remove database 'DB-4' from availability group 'agname'. Either the database does not belong to the availability group, or the database has not joined the group. Verify the database and availability group names, and retry the command.

Msg 41189, Level 16, State 8, Line 3

Availability group 'VLEApplication' failed to process the remove-database command. The availability group configuration has been updated. However, the operation encountered SQL Server error 41133 while applying the new configuration to the local availability replica, and has been terminated. Refer to the SQL Server error log for details. Verify that the local Windows Server Failover Clustering node is online. Use an ALTER AVAILABILITY GROUP command to undo the changes to the availability group configuration.

This error occurs because the local availability group configuration does not recognize this database as part of the availability group, but the cluster-level configuration does. Therefore, the database is successfully removed at the cluster level, but an error occurs when the command attempts to remove the database from the local configuration.