3

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?

2
  • 1
    The normal way to achieve what you want is create a listener, and then have the connection string point to the listener. Then you use the listener name in the connection string. Failover partner is for the deprecated mirroring feature. I'm guessing that "we do not have an internal DNS" is the reason you can't go this route? May 12, 2021 at 18:13
  • Exactly, furthermore Windows container networking on virtual machines / Hyper-v has issues. I don't really know how containers would respond to that. For instance, I cannot ping using Workgroup machine names from the container. Should I try resolving stuff manually by retry with other instance on exception? I really don't want to go that way.
    – Yamuk
    May 12, 2021 at 19:07

1 Answer 1

4

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.