6

I have been using AlwaysON AG for a long time now and currently have about 10TB of data across 120 databases and 3 AG groups for any application that is on SQL 2012 with great success. Each AG group is running on patch level 11.0.5058.0 with 2 synchronous replica(on different SANS) in Primary Data center and 1 ASYNC replica in DR. Migration has been a non-issue because none of the databases were substantial enough that I could not fit into my maintenance window which is 12-4AM on SAT morning.

My issue is that my last application to migrate to 2012 includes a 4TB TDE encrypted databases database which is about 10x larger than any of the previous ones I have migrated. The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

The restore to the primary replica is instant because of seeding incremental but the issue comes from having to backup the database before adding to the availability group. 4 hours is my exact outage window and I can't get any more.

My plan to migrate application is to -

First Outage Window

  1. Restore Database from 2008 to 2012 Primary Replica

  2. Change application ARECORD(or cname not sure which) to Primary replica

  3. Run database on single node until next outage window

Week Later :

  1. Add database to availability group
  2. Change ARECORD/CNAME to listener

What I don't like about this is I am going an entire week with 1 node instead of 3 which is worrisome. If anyone else has any better ideas on how to accomplish this I would love to hear from you or any type of comment from people who have worked with VLDB in availability groups and what you like/hate/loved about doing it. I am trying to go all in on this software and have loved it so far but getting worried when it comes to the VLDB migration.

  • 4
    What you can do is initialize the secondary from the backup of primary (you can physically ship the backup to a different datacenter and then you can use that). This way, you can use the database backup of the primary database. Then on the secondary, restore the full backup of primary (+ any diff) and subsequent log backups RESTORE WITH NORECOVERY. Check : Manually Prepare a Secondary Database for an Availability Group – Kin Shah Feb 4 '15 at 21:12
  • The problem that you need to backup the Primary database before being able to add it to the AG, once primary is set-up I can do secondary whenever without outage. The issue is I can't restore to new server and do backup before outage window is over. – Greg Wellbrock Feb 4 '15 at 21:22
  • 2
    I dont see a problem why you cant backup and restore to secondary ? There is no downtime doing this. You can kickoff backup during a quiet time , ship it to secondary and restore it with NORECOVERY. – Kin Shah Feb 4 '15 at 21:26
  • +1 to Kin - that is exactly how we did our entire data center migration and it worked flawlessly – Steve Mangiameli Dec 9 '15 at 17:40
1

The requirement that a database have a full backup is only enforced by the Wizard. If you just Add the database after the Availability group is already created you can restore the same backup on both the Primary and Secondary and then add the database to the Availability Group. To save time during the maintenance window we usually restore Full backups ahead of time and take a differential backup during the maintenance window.

  1. Take Full backup of the database being moved.
  2. Restore the full backup on the New primary WITH NORECOVERY this ensures the database isn't used until the secondary is ready.
  3. Restore the same full backup on the new secondary WITH NORECOVERY this is needed to add them to the AG.
  4. Restore the primary database WITH RECOVERY. RESTORE DATABASE [MyBigDB] WITH RECOVERY;
  5. On the Primary instance navigate to AlwaysOn High Availability > Availability Groups > MyAG > Right-Click on Availability Databases > Add Database
  6. Select your database enter image description here
  7. Select Join only enter image description here
  8. Connect to the secondary instance enter image description here
  9. All the validation should be skipped enter image description here
  10. Verify everything and click finish or Save the script. enter image description here
  11. Verify syncronization is happening by viewing the Availability Group Dashboardenter image description here

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.