0

I'm working with a team to upgrade a four node AlwaysOn Availability Group from SQL Server 2012 to 2016. Two of the nodes are local and two are in Azure. The database holds a large amount of data (2 Tb) and it can take hours to sync the nodes.

We're trying to minimize the downtime during the changeover. We were thinking that we could take a full backup of the primary and install it on the other three nodes right before the 'go live' but we're not sure if that will work. If you have experience setting up AG's what would be your advice on how to best handle this?

  • You have 4 options. Automatic seeding is probably not your choice here as you have a 2tb database and 3 secondaries which is going to take multiple hours to sync, at best. So you'll probably want to do the backup/restore. Check out the section "To prepare secondary databases manually". – Jacob H Mar 13 at 18:47
0

You could look at log shipping to keep the new 2016 instances up to date well ahead of your upgrade. If you log ship from the current primary to all four new replicas then the cutover and sync is much quicker.

Assuming you're creating a second 2016 AG (and not joining the 2016 instances to the 2012 AG) then at the point of cutover:

  1. Take a log backup on 2012 Primary and restore on 2016 servers with norecovery
  2. Recover DB on 2016 primary
  3. Add DB to AG on 2016 primary and take a log backup
  4. Restore log backup on 2016 secondaries and join to AG
  5. Remove listener from 2012 AG
  6. Add listener to 2016 AG.

If you run into issues post-upgrade and need to revert to 2012, simply remove the listener from 2016 AG an add it back to 2012 AG. The 2012 AG will have carried on like normal and will continue working. The caveat here is that once 2016 is live (after any post-upgrade testing) you won't be able to rollback to 2012 without data loss.

If you're adding 2016 to 2012 AG then log shipping can still be used, but the cutover process is much more complicated as you have to replace the 2012 replicas in a piecemeal fashion as 2012 only allows 4 replicas. Rollback is also more complicated and may leave you without HADR after rolling back until you resynchronise the 2012 replicas.

You need to define this process thoroughly and run through the whole process in a lower environment multiple times to make sure you've got everything aligned and working.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.