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.

5
  • 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 '21 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 '21 at 14:05
  • 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 '21 at 14:26
  • Aaron, we run a new edition of SQL Server in Azure. I'm not sure if you can run AG with mixed sql server editions. Don't think that's possible. Anyway we are not looking to deviate what we have built. We are just looking for the best and most efficient way to stop connections to on-prem after the switch. I could configure on-prem AG to do "manual failover" and pause SQL server on the primary replica. I think that should take care of that. Just not sure if this recommended or there's better way. Thanks
    – sydney
    Sep 14 '21 at 14:44
  • Azure runs sql 2019 while on-prem 2017.
    – sydney
    Sep 14 '21 at 17:14
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 '21 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 '21 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 '21 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 '21 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 '21 at 18:23
-2

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.