We've setup a SQL Server 2019 Enterprise High Availability cluster with an availability group listener (AGL) in front. We are experiencing strange (?) behavior when testing automatic (dirty) failovers compared to when we do manual/planned ones. The most pressing issue is when for clients using Microsoft ODBC Driver 17.7 for SQL Server on Centos 7. But the same happens for .net framework/core as well.
Scenarios
Manual/planned failover
Test
We tell the AG to fail over the cluster from the primary to the secondary replica.
AG-behavior
The AG fails over to the secondary replica.
Client behavior
The clients are instantly disconnected from the AG, the ODBC-drive tries to reconnect, succeeds and the client can continue working.
Automatic/dirty failover (doesn't work as we expect)
Test
We assert the clients are connected to the primary replica through the AG then pull the plug on the primary replica machine (to simulate a power outage).
AG-behavior
The AG fails over to the secondary replica.
Client behavior
The clients doesn't get disconnected and instead continues waiting for a response until the query timeout kicks in (this takes several minutes, way too long).
Question
From what we understand the AG is supposed to disconnect the clients both during manual/planned and automatic failovers, but for us this only happens during the manual/planned ones. Is this the inteded behavior for this solution or is there something wrong with our SQL Server/AG-configuration?
