1

I have an SQL Server 2019 Cluster with a high availability DB. I set my .NET 5.0 (using Entity Framework) app to connect to the listener, expecting that if the cluster fails over, things would just go on without me having to do anything about it.

Turns out that's not entirely the case - in my testing, with triggered and dirty failovers, I ran into transitional states when performing a DB operation would result in errors. A few of them I got when doing my testing were

A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable)

The target database, 'mydb', 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.

and

Unable to access availability database 'mydb' 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.

Looking at what Microsoft has to say on the topic, the only thing I found was that you had to manually retry in this state. Now while writing a manual 'wait for X seconds, then try again' is easy to write, what concerns me is catching the right errors so I only have to retry when I'm on one of the states where the HA DB is temporarily unavailable.

does anyone have a list of exceptions, error codes to look for? trial & error seems to be rather dangerous here because it's all timing related, so I might miss some if I happen to launch an operation in the wrong moment of time and issues may only present when there's some real load on the system (then forcing me to scramble and deliver hotfixes)

1

EF has this built-in: Connection Resiliency

the SQL Server provider includes an execution strategy that is specifically tailored to SQL Server (including SQL Azure). It is aware of the exception types that can be retried and has sensible defaults for maximum retries, delay between retries, etc.

1
  • So, I implemented this, but it seems it isn't really a fix for all. I just had the chance to watch what the app does if they kill one of the two DB servers. And I'm still getting A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable) What am I missing here? Aug 17 '21 at 8:09

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.