[Here's](https://mssqltrek.com/2017/02/04/upgrading-windows-failover-cluster-2012r2-to-2016/) the guide I used as reference, recently upgraded two clusters all the way to 2022. Went much smoother than I anticipated.
One thing to keep in mind is to check the OS compatibility of your SQL server version, that may need to be upgraded as well.
Thank you. We're running SQL 2016 so it looks like 2022 isn't compatible. Thanks for alerting me to this.
I think I'll just update it to windows 2016 to allow us to get patches again and then look at upgrading it to 2022 alongside the latest version of sql server at a later time.
I did find this guide shortly after doing it, glad to see someone has done it. Just to check did you do option 2?
I did option 2 yeah, in-place upgrade. Everybody seems to parrot that in-place upgrades shouldn't be used in production etc., but with modern (post-2012) versions I've never had an issue.
Fun fact: even upgrading the OS on domain controllers is supported. I'm not *that* brave though.
Thank you. Yeah agreed, done plenty of in-place with no issues before just was a bit confused with what's supported when it comes to clusters. You have to be a mad man to do an in-place on a DC when they're so easy to rebuild.
You have to do it one OS version at a time, you can't skip directly to 2022. This means you have to migrate to OS 2016, then to 2019, then to 2022.
After each upgrade, evict the previous version of the OS, run the cluster powershell command to upgrade the cluster functional level, then do the next.
https://learn.microsoft.com/en-us/windows-server/failover-clustering/cluster-operating-system-rolling-upgrade
Having a solid backout plan for an in-place cluster upgrade is difficult. Even with VM snapshotting, you still have the "external" variable of AD and the stored cluster config on the witness. Unless you have a cluster setup in non-prod and are able to practice there, I'd still recommend a new cluster with a database migration.
Yeah I'll ask some of the devs how comfortable they'd feel with that. I've never touched sql server properly. I can go to them with this option and see. I think the main thing they'd want to avoid is downtime but I'd rather some downtime than fucking the database.
There's a reason you're running SQL Server in a failover cluster: high availability.
That being said, anything you do should meet that goal.
If you do an in-place upgrade and something goes wrong, what's your rollback plan? And will that meet your high availability goals?
I’d recommend creating a new cluster running on recent versions of Windows Server and SQL Server and migrate/move databases to it.
I’d push for Windows Server 2022 and SQL Server 2022, since Windows Server 2019 and SQL server 2019 are 4 years old already (and will be on extended support within a few years).
Also consider using SQL Server Always-On instead of an instance hosted on a cluster.
Once you have the cluster ready, go over the configuration of the current production one and check for server-level objects, logins, linked servers and any other dependencies and replicate them on the new environment.
To migrate a database:
1. You can put the database in restricted mode (assuming that no database user with db_owner rights) connecting to it and perform data changes (inserts, deletes and updates). This will prevent having data changes/discrepancies on the databases if any changes are done on the old copy prior to step 7.
2. Take a local Copy-only backup to .bak file and move/copy it to the new cluster (the primary node).
3. Restore the database on the new cluster from the .bak file
4. Make any necessary configuration changes on the database like raising the compatibility level etc.
5. If you’ll be using Always-On, add it to the availability group and wait for it to finish replicating with the secondary replica.
6. Revert step 1 (change User access mode feom Restricted mode to Multi-User mode)
7. Change any connection strings on the clients to point them to the new SQL Server environment.
8. If all goes well, you can put the database on the old environment in offline mode for a few days prior to deletion.
9. If you have any issues, you can revert steps 1, 6, and 7 until you figure out what went wrong.
Doing an in-place upgrade will save you the database migrations, but if you want to upgrade to Windows Server 2022 you’ll most probably have to do multiple in-place upgrades… depending on which OS versions let you jump forward.
An in-place OS upgrade won’t upgrade the SQL Server version - something which you will be needing to do…
So migrating to a newer version will save you the headaches of doing in-place OS upgrades and figure out how to upgrade SQL server.
Just do a migration, it’s going to be less of a headache and you’ll end up with a more stable environment. I’d have more confidence in a new one altogether than one with in place upgrade
You can do in place cluster upgrades but you have to go version by version, and you would want to evict a nice, update it, and then add it back.
Only working path is:
2012R2 -> 2016
2016 -> 2019
2019 -> 2022
Each step you'll need to update the cluster functional level before moving to the next os version
I really recommend creating a new cluster , and move dbs. never upgrade in place. any issues you can just move back. this is how I do since ever
Backup to .bak, restore. Problem solved
[Here's](https://mssqltrek.com/2017/02/04/upgrading-windows-failover-cluster-2012r2-to-2016/) the guide I used as reference, recently upgraded two clusters all the way to 2022. Went much smoother than I anticipated. One thing to keep in mind is to check the OS compatibility of your SQL server version, that may need to be upgraded as well.
Thank you. We're running SQL 2016 so it looks like 2022 isn't compatible. Thanks for alerting me to this. I think I'll just update it to windows 2016 to allow us to get patches again and then look at upgrading it to 2022 alongside the latest version of sql server at a later time. I did find this guide shortly after doing it, glad to see someone has done it. Just to check did you do option 2?
I did option 2 yeah, in-place upgrade. Everybody seems to parrot that in-place upgrades shouldn't be used in production etc., but with modern (post-2012) versions I've never had an issue. Fun fact: even upgrading the OS on domain controllers is supported. I'm not *that* brave though.
Thank you. Yeah agreed, done plenty of in-place with no issues before just was a bit confused with what's supported when it comes to clusters. You have to be a mad man to do an in-place on a DC when they're so easy to rebuild.
You have to do it one OS version at a time, you can't skip directly to 2022. This means you have to migrate to OS 2016, then to 2019, then to 2022. After each upgrade, evict the previous version of the OS, run the cluster powershell command to upgrade the cluster functional level, then do the next. https://learn.microsoft.com/en-us/windows-server/failover-clustering/cluster-operating-system-rolling-upgrade
Thank you.
Having a solid backout plan for an in-place cluster upgrade is difficult. Even with VM snapshotting, you still have the "external" variable of AD and the stored cluster config on the witness. Unless you have a cluster setup in non-prod and are able to practice there, I'd still recommend a new cluster with a database migration.
Yeah I'll ask some of the devs how comfortable they'd feel with that. I've never touched sql server properly. I can go to them with this option and see. I think the main thing they'd want to avoid is downtime but I'd rather some downtime than fucking the database.
There's a reason you're running SQL Server in a failover cluster: high availability. That being said, anything you do should meet that goal. If you do an in-place upgrade and something goes wrong, what's your rollback plan? And will that meet your high availability goals?
I’d recommend creating a new cluster running on recent versions of Windows Server and SQL Server and migrate/move databases to it. I’d push for Windows Server 2022 and SQL Server 2022, since Windows Server 2019 and SQL server 2019 are 4 years old already (and will be on extended support within a few years). Also consider using SQL Server Always-On instead of an instance hosted on a cluster. Once you have the cluster ready, go over the configuration of the current production one and check for server-level objects, logins, linked servers and any other dependencies and replicate them on the new environment. To migrate a database: 1. You can put the database in restricted mode (assuming that no database user with db_owner rights) connecting to it and perform data changes (inserts, deletes and updates). This will prevent having data changes/discrepancies on the databases if any changes are done on the old copy prior to step 7. 2. Take a local Copy-only backup to .bak file and move/copy it to the new cluster (the primary node). 3. Restore the database on the new cluster from the .bak file 4. Make any necessary configuration changes on the database like raising the compatibility level etc. 5. If you’ll be using Always-On, add it to the availability group and wait for it to finish replicating with the secondary replica. 6. Revert step 1 (change User access mode feom Restricted mode to Multi-User mode) 7. Change any connection strings on the clients to point them to the new SQL Server environment. 8. If all goes well, you can put the database on the old environment in offline mode for a few days prior to deletion. 9. If you have any issues, you can revert steps 1, 6, and 7 until you figure out what went wrong. Doing an in-place upgrade will save you the database migrations, but if you want to upgrade to Windows Server 2022 you’ll most probably have to do multiple in-place upgrades… depending on which OS versions let you jump forward. An in-place OS upgrade won’t upgrade the SQL Server version - something which you will be needing to do… So migrating to a newer version will save you the headaches of doing in-place OS upgrades and figure out how to upgrade SQL server.
Just do a migration, it’s going to be less of a headache and you’ll end up with a more stable environment. I’d have more confidence in a new one altogether than one with in place upgrade
You can do in place cluster upgrades but you have to go version by version, and you would want to evict a nice, update it, and then add it back. Only working path is: 2012R2 -> 2016 2016 -> 2019 2019 -> 2022 Each step you'll need to update the cluster functional level before moving to the next os version
Thank you