SQL Server Migration

 

SQL Server 2005

There are several ways to upgrade to SQL server 2005. The upgrade strategy and method that is chosen really depends on many factors like, down time, size of the database, custom applications vs Commercial applications, customizations, simultaneous migration of hardware, etc.

SQL Server 2005 Upgrade: Available Strategies

 

In-Place Upgrade

This method is best if the database is going to be hosted on the same server after the upgrade, the database reasonably sized (in GB's). 

  • Backup the database and make sure you can recover using the backups
  • Have CD's of old version of SQL server, in case of abandoning in the middle (hopefully you did all the testing before attempting on production)

 

Side-by-Side Migration

  • This is the best if additional resources are available on the hardware to accommodate SQL server 2005 installation and able to accommodate the database. This way the old version of the database is preserved while the upgrade is performed.
  • Check with documentation on if the side-by-side upgrade process is supported for the version of SQL server that is in production now.

 

SQL Server 2005 Upgrade: Available Methods

 

Detach/Attach

  • Database size: If the database size is very large and moving data is a concern, this is a nice method to try.
  • Down time: While the upgrade is being performed, the database is down, plan for it.
  • Make sure you have the complete set of backups, if something goes wrong in the upgrade.
  • This method really needs to be tested, commercial apps and custom apps both will have problems/errors that need to be fixed.

 

Backup/Restore 

  • Provides a way to back out easily, run in parallel with old database
  • Database Size: For very large databases, this method could be a problem.
  • Down time: Either lock the database down since the final backup started for upgrade or add the transactions manually later.

 

Copy Database Wizard 

  • Recommended for small databases
  • Data moves from one place to another
  • Less down time

 

Schema Rebuild and Data Export/Import 

  • If the application being hosted is custom built, some of the objects may need to be recompiled.
  • Generate scripts form old version, recompile them in the new database
  • Export and Import data.
  • Database size is a concern with export, import operation
  • Down time - Plan for final export and import time line