Upgrading your database environment from SQL Server 2000 to SQL Server 2005 can present many challenges. Many of the new release's features fundamentally alter the mechanics of how database professionals interact with SQL Server; other features will have a long-term effect on how developers build database applications and the demands those applications put on DBAs. But regardless of the changes you expect, you want to be sure that after your upgrade, you're ready to take full advantage of the power and functionality that SQL Server 2005 provides.
Microsoft's SQL Server team has gone to great lengths to simplify the upgrade process and to provide an upgrade path for every SQL Server component that has shipped since SQL Server 7.0, including SQL Server 2000 Reporting Services and SQL Server 2000 Notification Services. You can move to SQL Server 2005 in one of two ways: performing an in-place upgrade, which completely replaces an existing instance, or performing a side-by-side migration, which requires installing a new instance, then manually migrating objects from the old instance to the new instance. Let's begin by understanding in-place upgrade and side-by-side migration in little more detail. Then, I'll give you an overview of the process of transitioning to SQL Server 2005.
Choosing Your Upgrade Strategy
SQL Server 2005 supports upgrading from SQL Server 2000 Service Pack 4 (SP4) and from SQL Server 7.0 SP4, including migrating sub-components such as SQL Server Agent, Full-Text Search, Replication, Analysis Services 2000, Reporting Services, Notification Services, and DTS (which becomes SQL Server Integration Services—SSIS). For each SQL Server component, there is either an upgrade or migration path. And you can upgrade 32-bit as well as 64-bit systems, including systems on Itanium and x64 platforms.
The first option, an in-place upgrade, uses SQL Server 2005 setup to overwrite an existing instance while preserving the user data and metadata. When the setup finishes, the old instance is no longer available and the new instance has the same name as the old instance.
An alternative option is a side-by-side migration, in which you use SQL Server 2005 setup to install a new instance—on the same machine or a different machine—then manually copy the user data and metadata from the old instance to the new instance. When the migration is complete and verified, you can direct applications to access the new SQL Server 2005 instance and manually remove the legacy SQL Server instance.
You can use the in-place upgrade or side-by-side migration technique for the SQL Server database engine, Analysis Services, and Reporting Services. SQL Server 2005 provides wizards for migrating DTS packages to SSIS and Analysis Services 2000 metadata to Analysis Services 2005 format. Additional configuration tools let you upgrade and configure Reporting Services and Notification Services. A new tool called SQL Server 2005 Upgrade Advisor (which I discuss later) can help you better prepare for the upgrade. Let's look at the pros and cons of the two techniques.
In-place upgrade pros and cons. Using the in-place upgrade technique offers several benefits. First, since the instance name is retained, you don't need to change the application to point to the upgraded instance. Second, an in-place upgrade is automatic and easier and faster than a side-by-side migration, which requires several manual steps. Thus, the in-place upgrade is probably the best option for small systems. In addition, an in-place upgrade doesn't require any additional hardware. You might need some extra hard-disk space or memory for components such as Full-Text Search, but unlike side-by-side migration, in-place upgrade doesn't require extra hardware resources to run two instances.
The in-place upgrade technique does have limitations. Notably, the instance you're upgrading must remain offline during part of the upgrade process, which can disrupt user and application access to the databases involved. Also, an in-place upgrade doesn't let you control which parts of your environment you upgrade; you must upgrade the entire instance. Thus, you must have a more comprehensive (and better-tested) recovery plan than you would for a side-by-side migration. Finally, for some SQL Server components, in-place upgrade either isn't an option (e.g., you can't upgrade DTS packages; you can only migrate them to SSIS) or isn't a best practice (e.g., Microsoft recommends you migrate Analysis Services 2000 cubes to Analysis Services 2005 instead of upgrading).
Side-by-side migration pros and cons. Although side-by-side migration might require more manual work, it provides more benefits and flexibility than the in-place upgrade. For example, running your old and new instances side by side greatly simplifies testing and verification of your new instance. And during a side-by-side migration, the original SQL Server instance remains online. After the migration is complete and you've verified that the new instance works, you can direct applications to the new instance. Side-by-side migration also gives you the advantage of fine-grained control over the upgrade process. You have full control over which components, databases, and objects to migrate. And because your original SQL Server instance is available after the migration, this technique gives you the option of reverting back to the old instance in the case of a severe upgrade failure.
Like in-place upgrade, side-by-side migration has limitations. Whether you're installing a new instance on the same machine or a different machine, migration might require additional hardware resources. Some organizations might view this requirement as an opportunity to upgrade hardware and the OS, but you have to plan for the possible added expense. In addition, after your migration, you have to update application references (e.g., connection strings) to point to the new SQL Server instance—a manual process that can be time-consuming.
In any upgrade or migration process, you can't underestimate the importance of effective planning and testing. A smooth and successful upgrade requires a good plan, thorough testing of that plan with multiple scenarios, a complete test of your rollback or abort strategy, and finally execution of the plan. You can devise an upgrade plan by dividing the tasks into the following three steps: pre-upgrade, upgrade execution, and post-upgrade. Let's look at each of these steps in more detail.
Pre-Upgrade Checklist
The first step in moving up to SQL Server 2005 is to prepare your environment by using tools such as the Upgrade Advisor to analyze legacy SQL Server components (including the relational engine, Analysis Services, Notification Services, Reporting Services, and DTS) and identify and resolve compatibility problems. To ensure your environment is ready for the change, use the following checklist.
- Learn about upgrade and migration tools such as SQL Server 2005 Setup, SQL Server Upgrade Advisor, the SSIS and Analysis Services Migration Wizards, and the Reporting Services Configuration tool. For a description of the Upgrade Advisor and a walkthrough of how to use it, see the sidebar "Leverage the Upgrade Advisor."
- Familiarize yourself with SQL Server 2005 hardware and software requirements.
- Create an inventory of applications and legacy systems that use your SQL Server environment. Be sure to include information such as the SQL Server editions, service-pack levels, and languages those systems use.
- Learn about the SQL Server 2005 product family, which includes four editions: Enterprise, Standard, Workgroup, and Express. Learn which features each edition supports.
- Prepare a list of the servers, databases, applications, and SQL Server components that you want to upgrade. What you include on your list depends on many factors, including your business requirements, application architecture and complexity, and the number and magnitude of any potential upgrade and compatibility problems. For an explanation of the kind of problems you might look for, see the Web-exclusive sidebar "Head Off Compatibility Problems" at InstantDoc ID 47785.
- Choose an in-place upgrade or migration strategy for each SQL Server component. Depending on how you use SQL Server components in your environment, you might choose different techniques for different components and you might need to use different upgrade or migration tools. For each component, prepare a test environment that has a hardware and software configuration comparable to your production environment.
- Devise a test plan and create scripts and procedures that you can use to validate the upgrade.
- Execute an upgrade test run, trying multiple upgrade scenarios and testing your rollback procedures so that you're sure you can cancel the upgrade and return to your environment's initial state if needed.
- Determine the best time to perform the upgrade or migration.
- Define what constitutes a successful upgrade for your organization.
- Use tools such as PerfMon and SQL Server Profiler to generate execution plans and statistics to create a pre-upgrade baseline of your environment's performance. You can use this data to measure the upgraded environment's performance and behavior.
- Review SQL Server 2005 Books Online (BOL) and the SQL Server 2005 Readme document for known upgrade problems and workarounds.
Once you have completed the pre-upgrade tasks and have confidence in your upgrade plan, you can perform the production upgrade of one or more SQL Server components.
Prev. page
[1]
2
3
next page 
