April 30, 2020 at 9:39 pm
Hello --
We're currently running a SQL 2008R2 cluster with the plan to move that servers database (1TB) to an Azure IaaS infrastructure setup running a SQL 2017 Availability Group. What I'm looking for is a path forward that would allow me to log ship or mirror into an already set up Availability Group but that doesn't seem to be a valid approach.
At the end of the day, what I'm looking for is a low downtime approach to migrating that 2008R2 database to a waiting 2017 availability group. Maybe asking too much? 🙂
Does anyone have experience doing this type of activity or close to it? Would love to hear strategies or lessons learned.
TIA,
kevine323
May 1, 2020 at 8:10 am
You would be able to log ship to the primary node of the availability group, you just wont be able to add the databases into the group until after the migration, as the database cannot come online otherwise you will have syncing issues.
You could look at things like dbatools.io and their migration pieces, DataMasterminds did a similar thing (loom.ly/yzjozwE) using dbatools.
Logship from 2008R2 cluster to AG-Node1, then add DB1 into the AG.
Backup restore, but downtime for that may be extreme depending on the link to Azure. But you could prep and again sync a full and logs manually a few days before then play catch up on the day. Again need to add it into the group.
Could build a multisite cluster. For your Azure cluster, add a node that's on prem and do multi subnetting, sync on-prem old to on-prem new, add database to AG let it sync then you can fail over to Azure when ready, destroy the on-prem node.
Also remember to sync things server side, logins, linked servers, jobs etc. You then need to change jobs to be AG Primary aware etc.
The biggest thing your going to get hit with though is the cardinality estimator changes in 2014 onwards, I take it you have tested a production workload out on a 2017 instance and made the needed changes to queries which are slower than on 2008R2?
Sure other will chip in with out solutions.
May 1, 2020 at 9:20 pm
Anthony --
Thanks for the feedback. I had a similar thought to log ship to the primary in the AG while also manually copying the log files to the other 2 nodes in the AG. The thought is that while the log shipping is keeping the primary in sync, I can manually keep the other two nodes in sync by restoring the logs with scripts. Then, at go-live, cut the log shipping, recover the primary in the AG, and setup the AG secondaries with "join only".
As for the Cardinality Estimator, we're planning on keeping the DB in 2008R2 compatibility mode in the short term while we stabilize the environment and then look to move into full 2017 compatibility.
Thoughts?
May 2, 2020 at 7:50 am
You could just get logshipping to apply the logs to all the nodes in the AG setup. What I haven't done though is put a DB into a AG from a logship 2008R2 to 2017.
I want to say it works, but as the database will need to be internally upgraded as part of the change from 2008R2 to 2017, I am not going to say 100% for sure that you would be able to use the join only mode.
I would test that out, take the full backup restore with recovery on one, no recovery on the other and try and join the DB, if it works great if not you'll have to backup restore again after primary 2017 is made available.
For cardinality, I would also enable the query store and track the plans for the 2008R2 plans, then the 2017 plans and track regressed queries to see where the problems are when you switch to 2017 compat mode
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply