Entity Framework Core for SQL Server Failover Basic Availability Groups does not work

All we need is an easy explanation of the problem, so here it is.

I have set up an SQL Server Standard 2019 with Basic Availability Groups on a Windows Failover cluster. It is not like the Always On AG, but more of a limited version of it where DB operations are on the primary replica and the secondary assumes the primary role only if there is a failover.

I have a .NET Core 2.1 application with two instances that runs in docker Windows containers on Windows Server 2019, on different VMs with SQL. Since our host does not have nested virtualization enabled, we are stuck with Windows containers. They connect to the SQL using the following connection string:

Server=TCP:172.18.18.11; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.24

There are some weak points though. Since we had a small number of VMs and we use containers mostly, we did not care much for setting up the Active Directory due to overhead and some bad previous experience with Windows Container Networking (especially DNS issues) when using AD.

We have successfully created the Failover Cluster but they are on different subnets and different physical locations 172.18.18.0/24 and 192.168.11.0/24. This caused some problems with allocating the cluster IP resource, and since we do not have an internal DNS, we are stuck with IP addresses. And we cannot use cluster domain name or use the listener IP address since instances are on different subnets as well. This is a problem with our host and we could not really resolve.

So the problem is, SQL Failover works, but client applications do not get aware of the change, despite the "Failover Partner" in the connection string. In case of a failover, our app responds with a SQL Exception:

The target database, ‘db’, 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. Cannot continue the execution because the session is in the kill state. A severe error occurred on the current command. The results, if any, should be discarded.

Instead I would expect it to direct the query to Failover Partner. I did some research and apparently specifying the failover partner in connection string works (not sure if it works for BAG or Always On though).


I tried this connection string:

Server=TCP:172.18.18.19; Database=db; User Id=****; Password=****;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.31

The IPs here are the listener IP addresses, I get this message:

Server TCP:192.168.11.31, database GWP2 is not configured for database mirroring.

So it looks like Failover Partner keyword works as it connects to 192.168.11.31. However it looks like it requires mirroring, maybe Always On Availability groups rather than BAG?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Ok, so I had to go out of my way and took a risk to setup DNS without AD. It wasn’t a big deal since we had only 2 SQL instances and 3 client VMs. Apparently "Failover Partner" keyword only works with the deprecated "mirroring" feature. And the listeners are to be only accessed with their names. I don’t think there is a solution to not using DNS without a number of ugly hacks.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply