Migrating VLDB to Availability Group

  • 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 weren't 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.

  • I'm not feeling very well (especially mentally) right now, but after upgrading to 2012 can't you do a new FULL backup and restore that to the secondary and start rolling tlog backup/restores and when you AG the database just say it is already synced up to the latest tlog backup and then it will simply replay the existing tlog activity and you will be good to go?? Same for DR location, although much bigger delay due to time to copy full backup over. This could all be done in less than half a day, right? Hope I am not sounding like a complete moron here ... :hehe:

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • gwellbrock (2/4/2015)


    The database takes 4 hours to backup after tuning extensively(I hate TDE!!)

    Yep, unfortunately TDE negates backup compression 😉

    gwellbrock (2/4/2015)


    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.

    Why is it an issue, why don't you just your existing backup file chain to initialise the database?

    gwellbrock (2/4/2015)


    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.

    There's no mention of backup and restoring the certificate used to protect the TDE database, presumably you intend to do this?

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Yeah I am restoring the TDE cert sorry for not mentioning. Unless I am crazy when migrating from 2008 to 2012 you have to back up the database in 2012 to meet the prerequisite to add to AG group and I just don't have enough time to do that in maintenance window.

  • gwellbrock (2/5/2015)


    Yeah I am restoring the TDE cert sorry for not mentioning. Unless I am crazy when migrating from 2008 to 2012 you have to back up the database in 2012 to meet the prerequisite to add to AG group and I just don't have enough time to do that in maintenance window.

    You're just creating multiple copies of the 2008 database on some 2012 instances which just happen to in an Availability group.

    Restore the 2008 full backup and at least one log backup to the primary replica and bring it online, Then just initialise other databases on any secondarys from the same backups but with norecovery.

    When you add them to the AlwaysOn Availability group you can select the join only option as each database has been initialised.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply