4

I currently have on a large database (~500GB) on SQL Server 2008 that is going to be relocated to a new SQL Server 2014 server.

My aim is to keep migration time down to a minimum.

Migrating the database by backing it up to a network share and then restoring is going to be very time-consuming.

My plan is trying to find some way of keeping the databases in sync until the cut-over.

I’ve been looking into log shipping, but I’m not sure of the process completely (I’m not a DBA, I don’t even play one on TV), and since it doesn’t leave the database in an available state, I’m not sure that’s practical.

I’m open to suggestions, as well any tool recommendations that might assist.

4

I'd suggest setting up database mirroring.

Assuming you setup mirroring in synchronous, high-safery, mode, the 2008 server will send data over to the 2014 server, as it is modified in the source database.

Once you decide to cut over to the new machine, you simply initiate a failover to the mirror database, "et voila", after a minute or two required for the 2014 machine to "upgrade" the database, it will come online and be ready to use.

  • 1
    Log shipping can be even simpler (especially in the event you have to fail back over). – Aaron Bertrand Oct 7 '15 at 20:45
4

Within the constraints of "I'm not a DBA" I would consider

  • Restore a full backup out of hours with NORECOVERY
  • At migration time, take a final DIFFerntial backup and restore

I've migrated multi TB databases this way.

Although migration time is longer, it should not be much more than using mirroring depending on how much data changes, which you can manage by say, disabling index maintenance between FULL and DIFF backups

2

My aim is to keep migration time down to a minimum.

Log Shipping is a great way to perform a database migration that minimizes system downtime.

You can begin by setting up Log Shipping (many different articles on the Web for doing this) from the current server to the new server. The first is considered your Primary in the configuration and your new server is considered the Secondary. Changes are then "shipped" from the Primary to the Secondary on a specified time interval.

When it's time to actually migrate, utilize a solution such as this to:

  1. Bring the Secondary fully up to date with the Primary.
  2. Fail over to the Secondary.
  3. Perform work on the Secondary to ensure your application is able to correctly use the new server without any issues.
  4. If everything looks good, proceed in setting up a maintenance plan, etc. If it doesn't work as expected, fail back to the Primary and resume operations there.

Please note that when you fail over to the Secondary this solution will leave the Primary database(s) inaccessible. This protects you from a situation where work was performed on both the Primary and Secondary and you're left trying to manually merge the changes on each back into one database.

One advantage of using Log Shipping here is that it will be faster than most other methods. Let's take method A and method B as an example. Method A uses Log Shipping and Method B is going to use a differential at the time of migration.

Method A:

  1. Perform a final transaction log backup.
  2. Apply all remaining transaction log backups on the Secondary.
  3. Make the database(s) available for use on the Secondary.

Method B:

  1. Perform a differential backup.
  2. Apply differential backup on the Secondary.
  3. Make the database(s) available for use on the Secondary.

Method A will only have to apply the last few transaction log backups on the Secondary and it's then ready to use. Method B will have to apply all changes that have occurred since the last Full backup. The difference between these two methods could range from minimal to substantial, depending on how big the differential turns out to be. Method A will almost certainly win every time as there is less data to back up and apply to the Secondary.

1

If you are sure about the consistency of your Database, and you have a single SAN Storage to be used in both servers, a very fast way is to detach the database and attach in on the other server. there are limitations to this method, but if your situation matches the conditions, I think its the fastest of them all.

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.