The migration to SQL Server 2005 is certainly an undertaking that requires a holistic view of the environment as well as a detailed plan to achieve the goal. As such, here are additional technical considerations that are key to the migration:
- Execute DBCC CHECKDB to validate that the previous environment (SQL Server 7.0 or 2000) is free of corruption prior to the upgrade.
- Issue final backups and ensure a rollback plan is in place to continue business operations in case an unforeseen problem is encountered.
- Develop enterprise standards for SQL Server 2005 configurations, operations (security, maintenance, etc.) and development.
- Archive unneeded data and perform data cleansing for the migrated data.
- Ensure all code is operating accurately prior to migration so that migration is the blame for broken code.
- Perform database maintenance such as UPDATE STATISTICS, DBCC CHECKDB and index rebuilds post upgrade.
- Validate that the SQL Server 2005 configurations and database compatibility modes are accurate post upgrade.
Notes from the field
At Edgewood Solutions, we conducted a SQL Server 2005 Adoption Rate Survey and one of the major findings was that organizations of all sizes were split 50/50 between migrating in less than one year and migrating in more than one year. The race is on, but make sure you do not have any false starts -- instead complete your race with the checkered flag flying!
Migrating to SQL Server 2005
Step 1: What to upgrade
Step 2: Upgrade prerequisites
Step 3: Migration options
Step 4: Additional considerations
ABOUT THE AUTHOR:
Jeremy Kadlec is the Principal Database Engineer at Edgewood Solutions, a technology services company delivering professional services and product solutions for Microsoft SQL Server. He has authored numerous articles and delivers frequent presentations at regional SQL Server Users Groups and nationally at SQL PASS. Jeremy is also SearchSQLServer.com's Performance Tuning expert.
More information from SearchSQLServer.com
- Tip: Reduce upgrade unknowns with SQL Server 2005 Upgrade Advisor
- Learning Guide: SQL Server 2005 Learning Guide
- Book Excerpts: SQL Server 2005 Management Studio