0

We have two SQL Server nodes running in an AlwaysOn Availability Group. I'm looking for some suggestions and/or best practices on how to temporarily suspend connection or prevent users from connecting the Availability Group? I was thinking to pause SQL Server running on the primary node and this will prevent new connections. If I did that, would it failover to the secondary?

Basically we have new AG running in Azure. We will pick a date and switch from on-prem to Azure. After the switch, we don't want new connections to on-prem. However, if there are issues after the switch, we need to be able to fall back to on-prem and resume the operations just like before.

What's the best way to handle this?

Thank you


Follow up: Yes, you can simply place the primary on 'pause'. Just did a test and it worked.

8
  • 2
    You created a new AG in Azure and are going to change all your connection strings etc. at the same time? An easier transition (and also would have solved this problem you're asking about) would have been to add a new node to the existing AG in Azure, failed over to it, then failed back if there were problems. Connection strings using the listener wouldn't have to change and no pausing or data loss for any writes that happened in Azure. Sep 14 at 13:50
  • Just to clarify, we created two SQL VM's in Azure. We built these two nodes to match exactly how it is running in on-prem. On cutover day, we will update DNS to point to the Azure listener. In theory, no users should be able to connect to the on-prem AG after this DNS update.
    – sydney
    Sep 14 at 14:05
  • But we just want to be absolutely 100% sure all client connections will point to Azure and absolutely zero connectin to on-prem. At the same time, we need the most efficient and safest way to fall back to on-prem should something goes wrong in Azure. Could you elaborate about creating a new node in Azure? I'm not exactly following your suggestion. Thanks
    – sydney
    Sep 14 at 14:05
  • I just mean that instead of creating a new AG in Azure you could have stood up one or more nodes and added them as replicas to the AG you already have. Then failing over is as easy as failing over, no DNS nonsense, waiting for TTL, locking users out, etc. necessary. If your connection strings are using listener name for that AG then there is no possible way for them to connect to the wrong server. Sep 14 at 14:22
  • 1
    Or you can create Distributed Availability Group between your on-prem cluster and your Azure cluster. docs.microsoft.com/en-us/sql/database-engine/… Sep 14 at 14:26
0

The simpliest way seems to simply shut down both nodes. That way, no one will be able to connect to them. If ever you need to roll back to them, just turn one of them (or both) on and they will then be accessible.

If you do not want to stop the SQL instances, then you could delete the listener from the AG.

As all you users/apps should be using the listener to connect, that will prevent from any connection, leaving the servers and AG online. In this case, if you need to rollback, then you just need to re-create the listener in the AG (Do not delete it from the active directory)

5
  • dominique-boucher, I didn't think of deleting the AG listener. This def. will work. But I hate to perform a DELETE which means I'm altering the setup and potentially run a CREATE. I prefer to leave everything intact. Sounds like the two least painful options are 1) shutdown both nodes or 2) stop the sql service on both nodes. Option 2 most likely will give us the fastest 'fall back' plan to execute.
    – sydney
    Sep 14 at 15:21
  • Or maybe option 3) pause sql service on both nodes leaving existing transactions to complete. Maybe this is better?
    – sydney
    Sep 14 at 15:23
  • I just did a test in a lab environment and answered my own question. All I had to do was simply place the primary replica on 'pause'. Once paused, I wasn't able to connect to it. My other node was still hosting as a secondary and since the AG listener listens on the primary, no connection can go to both. Once resumed, things went back to normal as if nothing had happened. Thanks for everyone's ideas.
    – sydney
    Sep 14 at 17:40
  • To add to your test... once it's pause, what happen if the primary node is rebooted (let's say your windows update ran) Sep 15 at 15:19
  • 1
    If rebooted due to windows updates, then of course the service will resume. For us, this won't happen because our updates take place on weekends. Good thinking. If you need the 'pause' to stay longer (say > 60 minutes), then I suggest to disable the sql service on both nodes.
    – sydney
    Sep 15 at 18:23
0

To answer your ultimate need, which was to migrate to azure VM and give yourself a way to revert, Aaron Bertrand's solution was the way to go.

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.