January 8, 2021 at 1:00 am
hello guys,
We have a two node cluster(Win2012R2) with FS and has SQL server(SQL 2014) AG setup. We want to upgrade OS to Win 2019 before working on SQL upgrade.
We tested the below scenario on test environment:
AG was set to manual failover and removed secondary replica from AG and evicted the node2 from cluster. Upgraded the OS to Win2016(select option to keep files, not rebuild) on node2 and rejoined it to cluster and to AG as replica. Failed over to and fro multiple times and made sure replication and AGs behaving as expected. Now the current setup node1(Win2012R2 and SQL 2014) and node2(Win2016 and SQL 2014).
Made node2 primary replica and removed the node1 and upgraded it Win2019 and followed the same process. Everything went well with setup node1(Win 2019 and SQL 2014) and node2( Win 2016 and SQL 2014). In the final step updated node2 to Win 2019. Now both nodes are on Win 2019 with replication and AG working as expected. Also, made sure to upgrade cluster functional level to 10 at the end.
There are few misleading articles we went through which were throwing us off but our tests showed no issues without any problems. Want to make sure we are doing it right way. Also, planning to test OS upgrade from Win 2012R2 to Win 2019 in the first step to eliminate middle layer(to Win2016 then to Win2019) but thought it's a safe approach.
Appreciate if peers can weigh in on the approach and let me know if I am missing any steps.
Thanks.
January 8, 2021 at 9:58 pm
To me that sounds like it should be a good test set. I would also try forcing the failover by having the VM go offline (reboot the VM and see if it fails over the way you expect when it goes down and when it comes back up). Basically, simulate the failover, not just force the failover from the tools.
I would also do some queries against SQL after failing it over with a restricted user (ie non-sysadmin) just to make sure everything works there the way I expect.
As for skipping Windows Server 2016, I like to upgrade from version X to X+1 without skipping things in between. You may have some odd issues that you were not expecting.
Now, with all of that being said, my IT department doesn't like doing upgrades as you are inheriting the "junk". Their approach is to do fresh installs and migrate the data to the new server. The biggest advantage to this is you have an EASY rollback plan (turn the old server back on) and you can do most of the work with no downtime.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 9, 2021 at 3:26 pm
Microsoft only supports an OS cluster migration to a version + 1, and that only from Windows Server 2012 R2 and higher. So - you cannot go directly from 2012 to 2019 and must go to 2016 first.
They also do not encourage an in-place OS upgrade, rather - they encourage a clean build of the new server on the new OS. Apparently, this can work in certain scenarios but it really depends and could cause issues - especially around device drivers. This does require installing SQL Server again - and patching to the same level - but is much safer and cleaner than upgrading the OS.
I generally like to add new nodes with new hardware - and upgrade the CPUs and memory (if needed). But - if I had to re-use existing hardware this approach would work.
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
January 9, 2021 at 11:39 pm
Thanks Jeffery and Brian for your valuable inputs. My test failed when tried to add 2019 node to 2012 cluster. The original plan is to add Win 2019 new nodes to cluster but to do that we have to upgrade our existing Win 2012 to Win 2016 and then join to Win 2019 nodes and decommission the old nodes.
We encountered a problem with replication, the redirect publisher is always looking for the Win 2012 (node1) from where we setup replication in first place. If we decommission the node1 and node2 after moving to node3 and node4, we need to update distributor to look for new servers as actual publishers( I don't know how at this moment). As all the publications appear under original publisher in replication monitor. We also encountered errors while setting up replication from replicas other than original publisher(node1) while setting replication for new databases which are not part of replication before.
Not sure if there's a work around for this, may be I have to reset replication from scratch.
January 19, 2021 at 7:47 am
This was removed by the editor as SPAM
January 19, 2021 at 11:31 am
This was removed by the editor as SPAM
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply