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?

Just to clarify, we created two SQL VMs 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.

But we just want to be absolutely 100% sure all client connections will point to Azure and absolutely zero connection 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.

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.

Azure runs SQL Server 2019 while on-prem is 2017.

Improve this question
2

1 Answer 1

Reset to default
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)

Improve this answer
5
  • 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
    Commented Sep 14, 2021 at 15:21
  • Or maybe option 3) pause sql service on both nodes leaving existing transactions to complete. Maybe this is better?
    – sydney
    Commented Sep 14, 2021 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
    Commented Sep 14, 2021 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)
    – Dominique Boucher
    Commented Sep 15, 2021 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
    Commented Sep 15, 2021 at 18:23

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.