By: Jeremy Kadlec | Comments | Related: > Upgrades and Migrations
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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
