1

We are thinking about an upgrade of our Windows 2012 R2 + SQL server 2012 failover cluster and AlwaysOn availability group.

Microsoft has guidelines for the SQL upgrade and for the failover cluster upgrade. However, it is emphasized that this is a one-way AlwaysOn upgrade - once the primary is switched from SQL server 2012 to SQL Server 2016 the AlwaysOn cannot be fallen back to the old node before that node is upgraded.

We are talking about a 24/7 production system but a full test of the new server is possible only once the new server becomes the primary.

Is there any way to manage the upgrade in such a way that a rollback is still possible without loosing data for the time frame the new server was the primary sql server AND without having an extensive downtime?

The SQL servers are virtual machines. We have two new virtual SQL servers setup with Windows Server 2016 and SQL Server 2016. So the original servers don't need to be changed during the upgrade.

Still as MS writes that a failover from higher SQL server to lower is not possible, then this sounds like not the option for us.

If there is no documented and safe way to rollback the changes then we would stay on the old servers for another year or two and wait for a downtime window in the production, support for Windows 2012 R2 ends in 2023, so we have a little more time.

Improve this question
4
  • (Extended) Support for SQL Server 2012, however, ends July next year, so if you're planning to run into 2023, just note that although you'll have some support on the OS, you won't on the DBMS.
    – Larnu
    Aug 17, 2021 at 10:53
  • @Larnu - thanks a lot, somehow I missed that deadline. That leaves a lot less time for the upgrade.
    – AndyZ
    Aug 17, 2021 at 12:45
  • @AaronBertrand , thanks, if we encounter any issues that cannot be fixed within an hour or two we need a quick rollback plan to be up and running in a short time . There are no specific issues we expect, but we fear the unexpected. We can test every feature individually on the new servers, but we cannot test all working together under load.
    – AndyZ
    Aug 18, 2021 at 6:49
  • @AaronBertrand - thanks for the idea, but adding network changes increases the number of teams involved, that increases the complexity and possible errors. For redundancy reasons our cluster is distributed on two server sites, on two different subnets, it took a lot of effort for the network team to get it running (years ago when it was set up) and I would bet that it would not run smoothly if they had to setup it again.
    – AndyZ
    Aug 19, 2021 at 6:22

1 Answer 1

Reset to default
1

Is there any way to manage the upgrade in such a way that a rollback is still possible without loosing data for the time frame the new server was the primary sql server AND without having an extensive downtime?

Yes, with a hodgepodge of 3rd party or self developed applications/services to keep data in sync across multiple versions of databases, networking layers to seamlessly switch client connections without changing connection string, etc. Then you need to create and test your rollback/failover/whatever plan with that.

Good luck, it's inviting in massive trouble to your environment. I would, instead, say we're going forward no matter what and fix the issues that arise in the new environment.

Improve this answer

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.