August 19, 2020 at 1:46 pm
What is the best way to upgrade a SQL Server 2016 AlwaysOn cluster w/ Availability Groups to SQL Server 2019?
The servers are physical and we're not intending to buy new hardware at the moment, therefore a side-by-side installation and a migration to new servers is not possible at this point.
Has anyone experience of doing an in-place upgrade, or is it not recommended at all?
Sincerely,
Gord
August 20, 2020 at 7:38 am
Hm,
this would not be easy. If you try an in-place upgrade, you are not able to fallback is something happens.
You could split your cluster to 2 single nodes, update the first one to 2019, check if your database / application would run fine. If so, just update the other one. Than build your AOAG.
But it would be more easier if you could by new server. With new server your risk is less and you need less downtime I think.
Kind regards,
Andreas
August 20, 2020 at 1:06 pm
Thank you very much for your reply!
Well, you confirm what I thought myself, that this would not be an easy upgrade.
Also ... IF we would go down this slippery and windy path. We cannot handle the listener address in an easy fashion I suppose?
The listener is registered in the DNS with a name, and the SQL Server listener configuration uses that name together with a TCP port. I guess you'd have to point the listener address in the DNS to one of the server's IP after the cluster has been broken, since the listener would be not be valid anymore, when the AG is removed? Or you have to change the connection string in every application, pointing to the stand alone SQL Servers name or IP address instead?
Sounds kinda trixy!
August 21, 2020 at 7:58 am
Well I would never recommend an in-place upgrade as you've no quick and easy backout and it'll be a whole world of pain to go backwards. But then again once you switched it to 2019 then its a whole world of pain anyway side by side after you have let users loose on the system.
First off do you have a test area where you've upgraded and tested everything with 2019 and got signoff everywhere to do prod?
Are the servers running Windows Server 2016 or 2019? If 2012R2 or below then you cant install SQL 2019
Depending on your data rate of change, you may be able to do the following but it depends how long you can be down as you're going to have no HADR while this goes on.
BACKUP BACKUP BACKUP
Backup databases
Backup servers/os etc etc
Change secondary replica to be async mode manual failover
Wait for data sync to complete
Pause data movement
Upgrade secondary replica
Resume data movement
Set replica to sync manual failover
Wait for data sync to complete
FAILOVER
Repeat the above again for the other node.
Set back to sync auto failover, or whatever mode it should be in for normal operation
BIG NOTE - I have never done this and would not recommend doing it this way but I appreciate your limitation on not being able to have new hardware for a side by side migration.
Remember once you have done the FIRST failover there is no going back so ensure this is exactly what you want to do.
August 21, 2020 at 9:13 am
Thank you very much Anthony!
I've discussed the in-place upgrade approach with the developers and application team, and there are too many alarm bells ringing and "maybe's" for this to be a serious task to perform.
We'll have to wait and put the upgrade into the upcoming budget and hope that it can be done in near future, on new hardware.
Sincerely,
Gord
August 21, 2020 at 9:25 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