June 18, 2019 at 7:45 pm
We have a SQL Server 2012 R2 failover two-node cluster that we'd like to upgrade to SQL Server 2016. Is it possible to add new nodes to the cluster and install SQL Server 2016 on them, then migrate the user databases from the SQL Server 2012 instances?
I've seen a number of articles about in-place upgrading, but the powers-that-be are a little nervous about that. So I'm looking for alternatives.
June 19, 2019 at 1:03 pm
Yes it is possible, however once you fail-over from the old nodes to the new 2016 Node, first thing to do is prevent failback to the old nodes. Second thing is eventually you'll wait quite a lot of time until the upgrade is finished.
Your other option would be to set up a separate cluster with 2016 only nodes and do the Backup & Restore Game, which one is faster or suits your needs you'll have to decide.
June 29, 2019 at 12:22 pm
Thanks, DinoRS. That's kind of what I expected. I suggested the upgrade-in-place option, since it is a cluster and if things went south we could make the 2012 node active while restoring the upgraded node. But the powers-that-be wouldn't go for it.
June 29, 2019 at 3:05 pm
Thanks, DinoRS. That's kind of what I expected. I suggested the upgrade-in-place option, since it is a cluster and if things went south we could make the 2012 node active while restoring the upgraded node. But the powers-that-be wouldn't go for it.
The in-place upgrade would be performed on the non-active instance anyways - and if it fails then you would never fail over to that node. In that situation you would have to remove the node from SQL Server and rebuild at the current level...which could be a significant enough risk to avoid the process.
There are 3 ways to perform an upgrade like this - in-place upgrade of SQL Server, backup/restore to new hardware, or cluster migration. Each has different risks that should be outlined...
For example - backup/restore provides an easier method for recovery but could incur a larger downtime. It also requires updating all application entry points (web servers, app servers, clients, etc...) with updated connection information.
In-place upgrades avoid the overhead of changing connection settings - but a rollback to the previous version may require rebuilding the cluster. If you upgrade the node and fail over successfully - then find that there are issues with the application that cannot be addressed and you need to roll back, you have to restore from the backups taken prior to the upgrade and incur data loss since that time.
I have never performed a cluster migration - just too many things that have to be done exactly right to make it work that the risk is too high for me.
The advantage of backup/restore is that you can upgrade the hardware - implementing a newer OS on the new cluster as well as upgrading SQL Server. It also allows for testing and validation since you can fully test the application against the new cluster for performance and usability. The downtime can be mitigated for the actual migration - where you restore a full backup with norecovery, then right before the migration perform a differential and restore with no recover - take the application down - perform the final transaction log backup and restore on new cluster with recovery and move all connections.
Whichever method is chosen - you need to validate the process and insure you have a path to recover if the process goes sideways.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
June 30, 2019 at 10:23 pm
I don't think this is possible, a cluster with SQL2012, then adding a new node with SQL2016 fresh install(instead of upgrading one node from SQL2012). In-place upgrade is the only option, upgrade one SQL2012 node to SQL2016.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply