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.
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)