0

I've set up an Availability Group on SQL Server (on Azure VMs) and created a Distributed Network Name as the listener for this. (SQL Server 2019 on Windows 2019)

If I connect to that listener in SMS then it works fine. If I manually failover and re-connect in SSMS it again works fine. Running select @@SERVERNAME in both cases returns the primary server.

However, if I leave the original connection open, and later try select @@SERVERNAME then it still returns the server it connected to (which is now secondary). I can verify this by trying to INSERT a row and it tells me this server now needs a READ ONLY INTENT connection and I can't do an update.

Is it expected that you have to open a new connection after a (manual) failover event?

3
  • 3
    It's normal behavior for SSMS, which on the whole is a poor client to test that kind of thing with. A true database application would have retry logic built-in, which would/should include disposing of and re-establishing the connection. Feb 17, 2021 at 18:16
  • Thanks - that's what I thought, but there was a doc page that implied it would work (learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/…) If you want to put as an answer I'm happy to ok it. Feb 18, 2021 at 9:38
  • I'll give it a while before doing so, in case someone who knows more comes along. What version of SSMS were you using? Feb 18, 2021 at 13:19

1 Answer 1

2

What you describe is normal behavior for SSMS, at least all the times I've done failover testing.

But Management Studio is a poor tool to test this type of thing. Best practice for an application is to have retry logic built into it that will upon error dispose of the current connection and re-establish, which will connect to the now primary side. And SSMS does not do that.

I've never seen or caught SSMS auto-magically re-connecting to the primary side once it's connection is established.

3
  • 1
    @MrPurpleStreak This answer makes perfect sense and I recommend accepting it. Basically, just because your server failed over successfully and the two replicas switched places as primary and secondary, doesn't mean SSMS automatically reconnects to the new primary instance, rather the same connection is still open on the original server (which is now the secondary and a read-only replica). As Jonathan (hey we share the same name ;) mentioned, it's the onus of the application to have retry logic built in to reconnect to your database server when there's a failure and...
    – J.D.
    Feb 21, 2021 at 4:59
  • ...actually in a real-world scenario, theoretically your original server that is now the secondary would actually be down and your application's connection would be subsequently closed, which forces you to implement retry logic. SSMS for the better? (worse for testing this as Jonathan mentions) appears to smoothly either keep the original connection open still or reconnects seemlessly to the original server once it's back online as a secondary. But essentially what you're experiencing is the retry logic of SSMS.
    – J.D.
    Feb 21, 2021 at 5:01
  • Aha - I've found it CAN be made to work! I'd configured the DNN listener on port 1433 (like you can do with a VNN) but the docs at learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/… now say you can't do that and why. Then if you add a multisubnetfailover=true in as well, you can do an insert, failover the AG and re-run the query and it works on the new server!! Mar 26, 2021 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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