4

I want to make the fastest migration plan to move a SQL Server database in simple recovery from a standalone server (SQL 2005) to a two node AlwaysOn cluster in full recovery.

Some databases are larger than 200gb so restore them in primary node, switch to full mode and add them to AlwaysOn by selecting full in initial data synchronization is not an option because that would make another backup/restore, taking a lot of time and resources (Time is critical!).

What I want is to restore the database in parallel in all the nodes at the same time (using norecovery I think? ) and then join them to the AlwaysOn group. I have already read the documentation about AlwaysOn and it's not clear about this.

Would it be ok if I follow these steps:

  1. In Standalone server: Set database to single user mode, switch database to full recovery
  2. Backup database from standalone server and restore it in primary node with recovery and restore it in secondary node with no recovery
  3. Add database to AvailabilityGroup by selecting "Join only" in Initial Data Synchronization

Extra info:

  • The standalone server is 2005 standard, so it's not possible to join it as a new node.
  • I want to make as less backup/restore as possible.
  • What I meant with set the database to single user is that there will be no changes in the database during the migration. It's important because I could make a full backup and then I wont need a differential backup.

Questions:

  • After restoring database in Primary node and Secondary node: if I want to join the database to an AG Do I also need to make log backup/restore even though the database had just change to full recovery and no one but me has connected to it?
  • Wich option should I choose in Select Initial Data Synchronization Page on the AG Wizard ? Join Only or Skip data synchronization?

Thanks!

  • 2
    Once your database is restored to the initial node of the AG (either by backup and restore, putting the current server into the cluster, etc.) it doesn't matter how long it takes to get the secondary nodes up and running. There's no additional production outage at this point. – mrdenny Jul 6 '14 at 4:33
  • @mrdenny Thanks, that would shorten the maintenance window but I'd rather not do it until all the cluster is set up because I want to avoid backups/restore while the application is online. – Gabriel.ARG Jul 6 '14 at 19:51
  • 1
    I have a feeling you'll need to take another backup once you've restored the DB onto the new node as it will need to be upgraded from 2005. I'd be inclined to test out the strategy on a small database as a POC. – dwjv Nov 26 '15 at 13:27
1

The fastest way would be to create a cluster an join your current stand alone server to the cluster as a new node. Set the database to full recovery model, take a full backup and a transaction log backup and restore them to the instance on the other node with norecovery.

If you have a good full backup (most recent), it would be possible to set the recovery model to full, take a differential and then take a log backup using the last full from the simple recovery model as your base. The differential should bridge the lsn gap.

To answer your questions:

  1. I'm not sure why you're switching the database into single_user except that you want no changes to be made. If you add the server as a node as I've set forth, this won't be an issue. Otherwise, yes, you'll need downtime to migrate the database. Your other option would be to set the recovery model to full, get it in full, setup mirroring to the new instance on the cluster and then cut over during a downtime which would be much less time.

  2. That's entirely possible to do, but in your initial question you said you didn't want to do this.

  3. You must restore at least one log or differential to bring the lsn gap as close as possible.

  4. I would not use the wizard, but the join only means you've staged a database that is currently in norecovery and it only needs to join the AOAG. The wizard will want to pre-stage the databases for you and ask you for shared locations, etc. Like I said it's best to pre-stage and not use the wizard for the best flexibility.

  • I edited my question to clarify – Gabriel.ARG Jul 6 '14 at 19:49
  • The changes are that you're running 2005 standard and can't simply join the node. In that case I'd setup mirroring or log shipping to the new cluster environment and cut over during an outage time. This way it'd be as simple as applying the last log backup in log shipping or making sure that the synchronous mirroring is hardened. The Log shipping you can actually hit two targets at once for the restore and thus be ready to go instantly. Though as @MrDenny has already said, the second node restore doesn't affect downtime. – Sean Gallardy Jul 7 '14 at 12:49

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.