November 16, 2022 at 4:02 pm
Hello all,
Wow, haven't been here in a while.
We have a weird thing happening in our new production environment that has two servers in a SQL AG.
First off, we are running both servers as VM's (enough vCPU's and about 50GB RAM a piece), SQL Standard Edition 2019, so there are some license limitations. We are using Hyper-V as our hypervisor. The two SQL Servers are patched but are slightly out of sync in terms of the version (think the secondary replica is missing one or two CU's).
We have set up listeners for ALL of our databases running on the servers, and have switched over all of our applications to use the listener names, and tested it and the failover works great, no downtime failing over from primary to replica.
However, if we fail over some of the databases (e.g. App1, App2, App4, assuming these three are used by one or more apps), but leave App3 on the primary (which is not used by any application that was dependant on the other three), then we start getting random errors on our applications stating that the App3 database is participating in an AG group that has not failed over (example error below):
"The target database, 'App3', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online."
However, our applications do not even use App3 (no connection string is present, or any hard-coded values), or have any SQL logins associated to this DB, yet it is causing problems when we switch over to our secondary replica. If we fail App3 over to our secondary replica, all is working fine.
I do not believe this is an app and/or connection string issue since the errors we have been getting for the various databases, are not even used in the affected applications.
If we fail over the entire server, then we have no issues.
Has anyone encountered this before?
Any help would be great!!
November 16, 2022 at 8:07 pm
You may not think that database is being used - however, there is an access request trying to access that database. Let's say application B is using a connection string that connects to listener B, where the default database is set to Database B. Now, somewhere in the application code - using that connection - an attempt to switch database context to Database C (which is currently not hosted on the same node) you will receive the above error.
If there is any code in database B - that uses 3-part naming to access objects in database C, you will also get that error.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply