SQL Server Availability Group – Issue with Adding a Database

availability-groupssql serversql-server-2016

I am having an issue with a particular AG where I am adding a DB (test123 below) to the AG and on primary it will show everything OK as follows:

enter image description here

However, on secondary it shows with an exclamation mark as follows and is not added to the list of DBs on the secondary instance:

enter image description here

I also see this message in the error log:

Unable to access availability database 'test123' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role. Try the operation again later. Nonqualified transactions are being rolled back in database test123 for an Always On Availability Groups state change. Estimated rollback completion: 100%. This is an informational message only. No user action is required.

I'm using automatic seeding to populate the database on the secondary.

I have added the same DB to a different AG and this issue did not happen and it was added successfully.

The DB is in full recovery model and a full backup has been taken as well and it meets prerequisites. AG and listener looks fine, any ideas what can be the cause of this?

Best Answer

Unable to access availability database 'test123' because the database replica is not in the PRIMARY or SECONDARY role. Connections to an availability database is permitted only when the database replica is in the PRIMARY or SECONDARY role.

You reported that the database was not showing under the databases folder when viewing your secondary instance with SSMS.

If you refreshed and it was still not there, then this means that the database does not exist. The reason would/could be that something went wrong with automatic seeding.

You should try executing these commands on the secondary instance:

ALTER AVAILABILITY GROUP [agname] JOIN; 
GO 
ALTER AVAILABILITY GROUP [agname] GRANT CREATE ANY DATABASE; 
GO

After this you should retry automatic seeding & validate that the database was created and fully seeded on the secondary instance.

To validate the automatic seeding process , you could use the sys.dm_hadr_automatic_seeding DMV.

SELECT start_time,
    ag.name,
    db.database_name,
    current_state,
    performed_seeding,
    failure_state,
    failure_state_desc
FROM sys.dm_hadr_automatic_seeding autos 
    JOIN sys.availability_databases_cluster db 
        ON autos.ag_db_id = db.group_database_id
    JOIN sys.availability_groups ag 
        ON autos.ag_id = ag.group_id;

Source

Related Question