March 2, 2020 at 11:27 am
Hi All,
Not sure whether to post it here or in SQL 2017, but here goes....
We have a Win 2012 R2 / SQL 2014 cluster for a mission critical database. It's a 3 node cluster with nodes in different subnets. As DR we logship to a 4th off site server. We want to upgrade this to a Win 2016 / SQL 2017 environment. The cluster consists of 2 physical nodes and one VM. The VM is asynchrounous, the physical nodes synchronous. The off-site logship is also a physical server. There's no additional stuff on the servers (no reporting services, integration services or what not) Due to financial considerations, new physical servers are a no-go.
Now, upgrading the windows version is relatively "easy", using a rolling upgrade process.
I've searched the usual resources, but only get information on upgrading to SQL 2017, not upgrading the windows server version also. I will need to reinstall the server with windows, and then install SQL Server, not upgrade an existing instance. Does anyone have experience with the path we have planned, and what are the pitfalls in upgrading with this scenario?
Thanks in advance for the answers
March 2, 2020 at 11:29 am
Oh my, i forget the most important. It's always-on naturally....
March 2, 2020 at 12:43 pm
The biggest issue is the "what if this all goes pear shaped" what is your rollback option, how long can you be down, how much data loss can you afford.
The issue with in place upgrades is that there is no easy rollback other than destroy everything and put it back to the old versions. Now that your DB is upgraded to 2017 you cannot put that back on a 2014 server. So you have to revert to your "pre-upgrade" backup, then you have lost everything that has been done since backup to rollback time. So once you've hit the switch and opened the flood gates and let the business back in, going back to 2014 is going to be a real headache.
Now if you did this side by side you could compare 2014 to 2017 and knock up scripts to put data back in its right places if you knew what needed to be done.
With the AG you would switch everything to async mode, then upgrade from as far away as primary as possible, so your 3rd node, then 2nd node, then logship, then primary. Once you fail the primary to the 2nd node there again is no going back so you need to make sure this is something you really want to do even before hitting the failover button to move your 2014 DB from primary to the newly upgraded 2017 secondary's.
March 3, 2020 at 9:27 am
Hi Anthony,
Thanks for the answer. Basically, what you're saying is that the main issue will be data-loss in case of all going to hell.
I am considering to shut down access to the production database/application, and use the log ship as a read only database, so the users have access to all data up to that point and will need to use the hospital emergency protocols for additions. It's a pain, but something we've done before with application updates.
I'd do that just before failing over to the primary node. This way we have the logship frozen in time and if all goes to hell, we can use the logship envorinment to come back to SQL2014. The emergeny protocoll additions will have to be entered manually after the maintenance anyway.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply