locked
"Proper" 2008 R2 Migration Requires Rebuilding the DB from Scratch? RRS feed

  • Question

  • We are migrating a production database from SQL 2005 to SQL 2008 R2.  Normally, I would just backup the database on 2005 and then load it on SQL 2008 R2 as the primary mechanism for migration.

    I was told by a member of our vendor's DBA Support that the database will perform more efficiently if we completely reverse and forward engineer it at the script level and then reload all the data on a table by table basis.  I was told that this is the "proper" way to do the migration.

    This database has over 1200 tables and about 60GB of data (not too big).  There are several problems with this approach.  First, the entire process would have to be scripted and tested first in a test environment.  Second, we would have to schedule down time on our critical 7 x 24 production platform to do this.

    Is it true that this is the "proper" way to perform the migration?  Are there any performance or other benefits to be had by doing this versus using the usual (more concurrency-friendly) optimization methods that are available like index rebuilds, update statistics, etc?

    Thank You

    -TuxedoDBA

    Thursday, April 3, 2014 4:15 PM

Answers

  • No that is not the "proper" way to do an upgrade.  There is no reason to do a data dump from 2005 to 2008 and reimport.  The only thing that might gain you is to defragment your data.  However, this is done by a clustered index.

    Please see:

    http://technet.microsoft.com/en-us/library/bb933942(v=sql.105).aspx

    • Marked as answer by tracycai Tuesday, April 22, 2014 2:59 AM
    Thursday, April 3, 2014 4:27 PM
  • No, you will be good if you use upgrade advisor to validate the current sql 2005 instance and fix those potential problem either before/after the upgrade.You should be in a position to validate the all the deprecated parameters. You need to perform all the database related maintenance task as part of your post upgrade steps.

    -Prashanth

    • Marked as answer by tracycai Tuesday, April 22, 2014 2:59 AM
    Thursday, April 3, 2014 4:33 PM

All replies

  • No that is not the "proper" way to do an upgrade.  There is no reason to do a data dump from 2005 to 2008 and reimport.  The only thing that might gain you is to defragment your data.  However, this is done by a clustered index.

    Please see:

    http://technet.microsoft.com/en-us/library/bb933942(v=sql.105).aspx

    • Marked as answer by tracycai Tuesday, April 22, 2014 2:59 AM
    Thursday, April 3, 2014 4:27 PM
  • No, you will be good if you use upgrade advisor to validate the current sql 2005 instance and fix those potential problem either before/after the upgrade.You should be in a position to validate the all the deprecated parameters. You need to perform all the database related maintenance task as part of your post upgrade steps.

    -Prashanth

    • Marked as answer by tracycai Tuesday, April 22, 2014 2:59 AM
    Thursday, April 3, 2014 4:33 PM
  • Tom and Prashanth, thanks so much for your responses.  This is exactly what I thought as well, but it is good to get a confirmation on this from some other professionals. 

    Thanks Again - TuxedoDBA

     
    Thursday, April 3, 2014 4:44 PM