1

I am currently working on migrating our single FCI Prod SQL Server 2008 Enterprise instance (on Win2008) multiple databases (>2TB total) to a new 2-node WSFC cluster (Win2012 R2 & SQL2014 enterprise) AlwaysOn AG.

The current single FCI prod server will be decommissioned after migration.

I could use LogShipping to migrate from our current single FCI Prod server to the new WSFC cluster Node 1 (Primary). Then, backup the Node 1 (Primary) db's and Restore on Node 2(Secondary) to prepare Node 2 (Secondary) and then Create/JOIN the AG group.

I would like to reduce the preparation time for the Node 2 (Secondary) i.e. downtime during migration i.e. to avoid separately backing up the Node 1 db's and Restoring on Node 2(Secondary).

If we Logship our current Prod db's to both Node 1 and Node 2 simultaneously, then is it possible to create an AG and JOIN db's on both these Nodes 1 and 2 successfully at the same time?

Improve this question
4
  • sorry to go out of scope but have you considered mirroring?
    – Ali Razeghi
    Jan 30 '17 at 0:33
  • Ali - we did not consider mirroring as MS recommendation is to go with AG for a hadr solution going forward. This would also allow us to scale out, upgrade to sql2016 on a new Win2016 node without much downtime later on.
    – DBAalwaysOn
    Jan 31 '17 at 1:51
  • oh absolutely, I meant to mirror up to 2014 from 2008. I found it way faster than transaction log shipping. If you go with either make sure your VLF count is low or you might face long restore times.
    – Ali Razeghi
    Jan 31 '17 at 2:24
  • I see now what you meant. Mirroring as an option was discussed. As we have previously used log shipping as a migration tool for all our server upgrades, we decided to stay with it. I will note down the point about the vlf counts.
    – DBAalwaysOn
    Feb 1 '17 at 3:00
0

To perform a server migration to an AG using log shipping, you should log ship to both servers. When you are ready to actually cut over to the new AG, follow the following steps:

  1. Take a final tail log backup on the old instance (BACKUP DATABASE...WITH NORECOVERY)
  2. Restore the backup to both AG nodes (or allow your log shipping to apply it) WITH NO RECOVERY.
  3. On Node 1, bring the the database online (RESTORE DATABASE MyDb WITH RECOVERY)
  4. Add the DB to the AG on Node 1.
  5. Join the secondary DB to the AG.

I'd recommend practicing this routine on your QA system before the real production upgrade. There are a lot of scripts/statements to be run, and lots of switching between servers to do it. It's best to have those steps carefully documented and scripts staged in advance.

Improve this answer
4
  • 1
    Thank you. This is exactly the kind of response I was looking for. I tried this on azure vm's as a poc and it worked. Are there any gotchas I need to know with this approach in certain situations?
    – DBAalwaysOn
    Jan 31 '17 at 1:52
  • Just make sure you're not taking backups on your new AG during the time between you bring the primary database online, and joining the secondary. If you do, you'll need to apply those log backups to the secondary. Also note that bringing the database online on primary will bring three database though upgrade steps, which will take some time
    – AMtwo
    Jan 31 '17 at 2:01
  • Good point. Does this also mean that the AG secondary db's which will be in synchronizing mode after joining are upgraded at the same time they are joined or will the upgrade stay pending till we failover and bring those online?
    – DBAalwaysOn
    Jan 31 '17 at 2:13
  • The upgrade is fully logged (it's basically just changes to system tables). The secondary will be upgraded when the log from primary is shipped to secondary--following my steps in my answer, that would happen on joining the secondary server to the AG
    – AMtwo
    Jan 31 '17 at 2:28

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.