Why the plan?
The key to a successful upgrade project is proper planning. Without a proper plan, there's no way to guarantee the upgrade project will be successful and completed on time. Moving from SQL Server 2000 to SQL Server 2005 is a major upgrade, much like the upgrade from SQL Server 6.5 to SQL Server 7. You should perform careful testing at all phases of the project to ensure a successful migration.
Phases of the plan
Most upgrade projects will have at least these three phases:
- Development code review, modification and testing
- Quality assurance testing
- Production release
Development
The development phase of the project will probably be the longest phase because of the large amount of code review and testing involved in the process. Because of the complexity of SQL Server 2005 and the differences between SQL Server 2000 and SQL Server 2005, a full code review and integration testing is recommended. Some differences that can cause problems are in the way SQL Server 2005 handles print statements. In SQL Server 2000, print statements are not accessed as record sets, but in SQL Server 2005 print statements are returned with an empty record set. This can cause problems with stored procedures that have print statements within the procedure between record sets or above the first record set.
You don't want to cut yourself short on development time and have to leave some things unconverted. In addition, you should allow plenty of time for the learning curve that goes with some of the new technologies, such as converting DTS packages to Integration Services packages.
Quality assurance
The QA testing phase of the project is a must. While developers will try to catch every issue, the size of some systems makes this very hard to do. It's common for developers to get wrapped up in one part of the system, leaving another part with less attention than it...
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
needs. The quality assurance phase of the project should include full regression testing of the application. Every screen, every action, every field change and every import and export should be tested. This way you'll ensure that the system will work correctly when it's released to production.
Your QA systems should be as perfect a match as possible to your production environment. This includes drive letters, firewalls, RPC settings, server rights, database rights, etc.
Production release
No upgrade project is complete without the production release phase. Without this phase, there is no project. Begin by writing out each step of the product release beforehand and use those steps as a checklist. It may seem rather silly to go through the trouble of making a checklist and including things like the folder to install SQL Server to and which service pack and hotfix to install, but you're better safe than sorry.
Imagine going through the upgrade and expecting to have Service Pack 2 installed, but you install Service Pack 1. The result is some of the bugs in SP1 affect your production system, but not your development and QA systems. You'll have a heck of a time trying to duplicate this problem in development. Or worse, say you are expected to set up database mirroring but you forget to install a Microsoft SQL Server Service Pack, leaving the database mirroring part of the database engine in beta and unsupportable in a production environment. While you would be able to fix this with a trace flag, it would probably be better to have the correct service pack installed instead.
In-place upgrade vs. a hardware migration
When working a production release, there are two techniques: You can upgrade the existing server by doing an in-place upgrade or you can migrate the database to new hardware along with the new database platform. Both are valid upgrade paths, but you should choose carefully. The hardware migration path provides greater rollback capabilities; however, the cash outlay is greater because it requires a new server – or servers in the case of a clustered installation. With the in-place upgrade, no hardware costs make it the economical choice, but the rollback is much harder as you have to upgrade the existing database platform.
An upgrade to SQL Server 2005 can be a great time to refresh your hardware. Many SQL Server 2000 systems have been in place for years and are now out of warranty. Not only will a hardware refresh put your hardware under warranty, but it will also make it faster. Faster performance from the newer hardware will make your upgrade look even more impressive to the business unit.
When you perform a hardware migration, I recommend that you keep the old hardware around for a couple of weeks – just powered off. That way you can get any last-minute data you need from the system. You probably won't need to, but, once again, you're better safe than sorry.
Rolling back in case of problem or failure
One of the most important parts of the upgrade project plan is the part you hope you'll never have to use. It's the rollback plan. No upgrade project should move into production without a properly documented rollback plan. While we hope to never need a rollback from SQL 2005 to SQL 2000, having the plan is essential in case there is a problem and a rollback is required.
In-place upgrade rollback
In the event of a rollback of an in-place upgrade, your basic rollback will be to uninstall SQL Server 2005, install SQL Server 2000 back to the same patch level you were at before, and then restore the backups you took just before you upgraded the system. Don't forget to restore the system databases as well to get back your logins, jobs, dts packages, etc.
Hardware migration rollback
In the event of a rollback of a hardware migration, your basic rollback will be to bring down the new hardware and bring the old hardware back online. Your logins, jobs, dts packages and so on will still be there because the old system wasn't changed.
While upgrading from SQL Server 2000 to SQL Server 2005, the only way to ensure the best possible outcome is proper planning, followed by careful execution.
ABOUT THE AUTHOR:
[IMAGE]Denny Cherry has over a decade of experience managing SQL Server, including MySpace.com's more than 175-million-user installation, one of the largest in the world. Denny's areas of expertise include system architecture, performance tuning, replication and troubleshooting. He uses these skills in his role as a senior database administrator and architect at Awareness Technologies. Denny is a longtime member of PASS and Quest Software's Association of SQL Server Experts and has written numerous technical articles on SQL Server management.
Copyright 2008 TechTarget
