Hi,
I've been asked to develop an implementation plan for upgrading a SQL 2008R2 cluster to SQL 2017. I'm not as experienced with SQL as with other database technologies so I want to get a little help if i may. Here is the existing structure:
Failover Cluster Instance with Always On Availability Groups and Virtual Network Names
2008R2 Cluster: CLUSTER01 (Virtual Network Name)
NODE02 (Primary)
NODE03 (Failover)
What we would like to do is to build a Windows 2016 server and install SQL 2017 then migrate the data from the 2008 instance into the new one using something like DBATools, although an in-place upgrade has not been ruled out should it be necessary. The AAG and the VNN provide challenges and while I have been able to get information about most aspects of this upgrade I'm having trouble putting everything together due to my limited experience with SQL Server.
If someone could give a bit of a hand I would certainly appreciate it.
Thanks
Adam
May 28, 2020 at 3:16 pm
There isn't an Availability Group in SQL Server 2008 R2. That technology was introduced in 2012. You have a cluster, or really, a Failover Clustered Instance (FCI) with two nodes (I'm assuming it's 2).
I wouldn't do in place. Even if you need the virtual name the same, you can switch this after moving. You need to decide if you want to continue with an FCI or move to/add an Availability Group (AG). Forget Always On, it's a marketing term for all tech and gets confusing.
We have a Stairway (https://www.sqlservercentral.com/stairways/stairway-to-alwayson) on this topic, and maybe you want to read a bit about the tech before deciding. The simplest thing is to set up a new cluster with nodes, migrate logins/jobs/linked servers, then backup/restore your databases. You can migrate with dbatools, which works well, though I don't know if there are cluster issues. Shouldn't be, but a side by side system lets you test the process.
Script out things as you perform the cluster setup, so if you have issues, you can wipe things and reset them quickly. You ought to have this for DR anyway.
May 28, 2020 at 6:15 pm
I would not recommend even trying to perform an in-place upgrade. If it is even possible to upgrade the OS in a cluster - it is going to be an extremely complex operation.
Migrating to a new cluster will allow for building out the new system and fully testing it prior to migration. Since this is going from 2008R2 to 2017 you are definitely going to run into issues with the code that will need to corrected.
What is the purpose of moving from a standard FCI cluster to availability groups? Is there a specific reasoning behind making that change - other than AG is the new thing? An FCI cluster provides for HA - so the only addition to that would be a DR node. If that is the case then it might be easier to setup 2 of the nodes for HA using the traditional FCI - and the third node setup for AG to the DR site.
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 2, 2020 at 7:35 am
Thanks for your reply. I'm looking into DBATools for an FCI migration - I think it's possible but I haven't yet found a working example. As for availability groups I think they are required in the new cluster so I'll have to look into that. The biggest problem right now is preserving VNN in conjuction with the migration.
Thanks
Adam
June 2, 2020 at 7:37 am
Hi, thanks for your reply. At some point my manager sent me an email listing requirements and AG was one of them. I'm now getting the impression that you can't have FCI and AG at the same time, can you confirm this? If I have to choose between them then I'll choose FCI.
Thanks
Adam
June 2, 2020 at 7:45 am
A traditional clustered instance, along with a node for an AG can co-exist in the same cluster, but there are caveats to it.
Primarily is the fact that the AG replica is in async mode, which if that is for DR may not be a bad thing anyway. If its for HA then yeah probably not ideal.
Easiest way would be to build a traditional 2 node FCI as you would normally, then once done provision a 3rd node in the cluster with stand alone disk and then configure an AG over the top of the clustered instance and the standalone replica.
But it does come down to what you need and what the AG is going to give you over the traditional FCI and other means of DR.
June 2, 2020 at 7:47 am
Also are you deploying Standard Edition or Enterprise Edition in this new setup?
As if it's Standard you have other complications to contend with given how it all needs to work.
June 3, 2020 at 2:40 am
Hi Anthony.Green,
I can establish that AG is not required - It isn't in the requirements as I thought. HA/FCI is definitely the requirement here. There are two different sets of steps that I need to perform for the migration, after the new server and SQL 2017 have been set up:
Does it matter at which point I remove the old VNN and create the new one or is it only for incoming application requests that this would be important? Once I've worked out how these two tasks fit together I will be in a position to write my implementation plan.
Thanks
Adam
June 3, 2020 at 2:42 am
Hi Anthony.Green,
I'm trying to find out if the SQL is Enterprise or Standard - I will get back to you shortly.
Thanks
Adam
June 3, 2020 at 2:45 am
Hi Anthony.Green,
I can confirm the SQL Server is Enterprise Edition.
Thanks
You will need downtime to move the VNN, your probably best to create a DNS alias instead using a CNAME for when you do the actual switch.
Create the new cluster, setup the new VNN, test test test.
Then on migration, migrate the DB's, shutdown the old cluster, delete the A record for the old VNN, created a CNAME for the old VNN pointing to the new VNN.
You're going to need to test everything vigorously, moving from 2008R2 to 2017, you will be hit with the new cardinality estimator. So you need to test regressed queries quite heavily as the new CE may improve performance, not change it or make it very worse.
Maybe best to enable query store, set the compat level to 2012 or lower to use the old CE and capture the plans, then enable the newer compat levels and see what breaks performance wise and fix it.
June 9, 2020 at 4:56 am
Thanks Anthony,
I am now compiling all of the information into my document. Appreciate the help.
Cheers
Adam
June 16, 2020 at 5:04 am
Thank you again Anthony.Green, the first draft of my report is completed I just need to make a few changes.
Thanks
Adam
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply