Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Speed up the SQL Server 2005 migration process


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations


SQL Server Conference Giveaway - click to learn more


Problem

It just figures that when you have a large database you have little down time to upgrade, but for the small databases you have an entire weekend to complete the process. This gives you plenty of time to double check the output and test the application with little or no pressure. Insufficient time to upgrade is a significant issue that at times is difficult bordering on impossible to negotiate with the business and user community. They certainly understand the upgrade to SQL Server 2005 is needed, but are also well aware of the business implications for down time.

Solution
For small databases the in place, Copy Database Wizard, Backup\Restore and Detach\Attach database are all viable migration options, but for very large databases (VLDB) the time can be extensive. One twist on the Backup\Restore approach is to use one of the specialized SQL Server backup products.

Leveraging a specialized SQL Server backup product is a viable alternative to accelerate the migration process if the backup and restore option is used to migrate from SQL Server 2000 to 2005. This is because these products use the Virtual Device Interface (VDI) and natively are able to have the database engine work through the upgrade process just like using the native backup and restore process.


If you are faced with migrating large databases these tools should be reviewed. As a DBA, you get a number big wins:

  • First, the backup time is much shorter than the native process.
  • Second, the storage needed to support the backups on the SQL Server 2000 and 2005 servers is much less than the native process.
  • Third, the copy over the network is quicker with a smaller backup file to move between the machines.
  • Fourth, the restore time is much shorter than the native process.
  • Fifth, the makers of the specialized SQL Server backup products are supporting both SQL Server 2000 and 2005 platforms.
Next Steps
  • Determine the technical approach that is going to be used when migrating from SQL Server 2000 to SQL Server 2005.
  • Test the process to determine how much time and disk space would be needed by using the native backup and recovery process.
  • Meet with your technical and business teams to find out how much time is available for the migration and the benefits of using a specialized SQL Server backup and recovery product.
  • Download a trial version of the products to test how significant the time and disk savings are for your environment during the migration. In addition, during the analysis, also determine the time and disk savings for daily backups as well as recovery times in case your disaster recovery plans need to be put into place.
  • Document and communicate the advantages with your team and determine whether or not you should leverage one of these products during your upcoming migration project.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools