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.
Upgrade Execution
Before you start your upgrade, make sure that you have the appropriate administrator permissions for the installations you're upgrading. For local installations, you must run SQL Server 2005 Setup as a local administrator. For remote installations, you must have administrator permissions on the local machine and on the computer where SQL Server is to be installed. If you install SQL Server from a remote share, you must use a domain account that has read and execute permissions on the remote share. Before you perform the production in-place upgrade or side-by-side migration, here are some steps that might be helpful post-upgrade or to rollback.
|
Upgrading the Relational Engine. After you have protected your data and ensured that you can recover from any failure, you can use SQL Server 2005 Setup to perform an in-place upgrade or install a new instance for side-by-side migration. (To learn how you can track your upgrade, see the sidebar "Monitoring the Upgrade Process," page 56.) In a side-by-side migration, you have several options for copying databases from legacy servers to the new SQL Server 2005 instance, including using detach/attach, backup/restore, the Copy Database Wizard, DTS/SSIS, manual scripts, or bulk loading the data.
For an in-place upgrade of a default SQL Server 2000 or 7.0 instance, select Default instance on the Instance Name screen during the setup. For a named SQL Server instance, specify the same name as the old instance, as Figure 1 shows. Click Installed instances to get a list of installed instances from which to select.
If Setup detects that the specified instance name already exists on the system, as Figure 2 shows, it prompts you for an in-place upgrade. Click Details to see more information about the components being upgraded, as Figure 3 shows. Check the components to upgrade on the Existing Components screen, and click Next.
SQL Server 2005 Setup begins by installing setup prerequisites. You can reduce the upgrade downtime by pre-installing setup prerequisites such as Microsoft .NET Framework 2.0, Microsoft SQL Native Client, and setup support files. Setup executes a process called setup consistency checker, which scans the destination computer for conditions that might block setup. Next, Setup installs the new SQL Server 2005 binaries. For an in-place upgrade, the old instance is still running and available during this step, but at this point, Setup stops the SQL Server service, points the service to the new binaries, and starts the service in single-user mode. After this point, the old instance is no longer available.
Next, Setup attaches the Resource database, which is a new, hidden, read-only database containing system objects. Setup then restarts the SQL Server service and starts updating all databases. Depending on available system resources, the databases might be upgraded in parallel. Once the databases are upgraded, Setup executes replication and SQL Server Agent upgrade scripts. Setup makes the new instance fully available after removing the old binaries.
You can also run SQL Server 2005 Setup from a command prompt. Setup.exe accepts various parameters, and you can set various property values or pass an .ini file that contains the setup settings. Refer to SQL Server 2005 BOL for complete details about setup.exe command prompt parameters.
Migrating DTS to SSIS. In SQL Server 2005, DTS is replaced by SSIS, which Microsoft designed and developed from scratch to improve performance, provide better control over data flow, and introduce conditional flow and looping. As I mentioned earlier, you can't upgrade DTS packages to SSIS. Instead, SQL Server 2005 Setup includes the DTS 2000 runtime engine, which lets you run your DTS packages alongside SSIS packages. SQL Server 2005 also provides an SSIS Package Migration Wizard that you can use to migrate DTS packages to SSIS. You can install the DTS 2000 runtime engine and the migration wizard during SQL Server 2005 Setup by selecting Legacy Components from the Client Components list on the Feature Selection screen.
You can launch the Migration Wizard by running dtsmigrationwizard.exe. Alternatively, from the SQL Server 2005 Business Intelligence Development Studio (BIDS), create or open an Integration Services project, right-click the SSIS Packages folder in Solution Explorer, and select Migrate DTS 2000 Package. To use the Migration Wizard to migrate DTS packages to SSIS, you must specify the package source, which can be a structured storage file containing packages, Meta Data Services, or packages saved in a SQL Server msdb database; specify the package destination, which can be an msdb database in a SQL Server 2005 instance or a .dtsx disk file; select one or more packages to migrate; specify a password for password-protected packages; and specify the migration log-file location.
After the Migration Wizard finishes, you might have to manually edit some parts of the SSIS package. For example, if a package contains an ActiveX Script Task, you'll need to change the script. You'll know a package needs to be updated if it fails during execution. Also note that not all the tasks in DTS 2000 packages can be migrated. Tasks such as Data Pump, Transform Data, and custom tasks can't be migrated to SSIS. SSIS creates a SQL Server 2000 package for each of these tasks. Then, the Execute DTS 2000 Package task, which I explain in a moment, executes the SQL Server 2000 package. Thus, the migrated package contains one Execute DTS 2000 Package task for each task that can't be migrated from DTS 2000. The Migration Wizard doesn't support migrating packages stored as Visual Basic files.
The Execute DTS 2000 Package Task is an SSIS control flow task. Although Microsoft recommends that you migrate your DTS packages to SSIS to reap the new product's performance, extensibility, and functionality benefits, you can use the Execute DTS 2000 Package Task to execute DTS packages. Alternatively, if you have a DTS package that requires substantial revision, you can use the Execute DTS 2000 Package Task to execute part of the package as is and rewrite it later.
Upgrading Analysis Services. Analysis Services 2005 builds on the solid foundation of Analysis Services 2000 and introduces several new features such as the Unified Dimension Model (UDM); multi-instance and failover clustering support; enhancements to cubes, dimensions, data mining, the security model, and tools; and native XMLA support.
Because Analysis Services 2000 doesn't support named instances, you can upgrade your default Analysis Services instance in two ways. You can upgrade during setup by selecting the Default instance option in the Instance Name window. Alternatively, you can install a named Analysis Services 2005 instance by using the Analysis Services Migration Wizard to migrate Analysis Services 2000 databases and metadata to Analysis Services 2005 format. In the latter case, you have to uninstall the Analysis Services 2000 default instance and use the Analysis Services Instance Rename utility (%ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ASInstanceRename.exe) to rename the Analysis Services 2005 instance as the default instance.
You can also install a named instance of Analysis Services 2005 alongside a default instance of Analysis Services 2000. In this case, use the Analysis Services Migration Wizard to upgrade Analysis Services 2000 databases to Analysis Services 2005 format. To launch the Migration Wizard, run the MigrationWizard.exe file from the %ProgramFiles%\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder. When the Migration Wizard has completed its migration process, you can reprocess the cubes and verify the data and reports. Note that the Migration Wizard doesn't optimize Analysis Services objects; it merely recreates the Analysis Services 2000 objects on an instance of Analysis Services 2005. You can optimize the cubes once the migration process completes.
Alternatively, you can create cubes from scratch to leverage new features and optimize cube design. This choice requires less effort than you might think because Analysis Services 2005 uses Intellicube technology. If you know your underlying schema well and it's in a star or snowflake schema format, the Cube Wizard can make an accurate determination when it invokes Intellicube about which tables should become dimensions and which should become fact tables to feed the cubes. Because you need to reprocess all cubes with either the Migration Wizard or the Cube Wizard, the latter option is worth considering.
Upgrading Reporting Services. Reporting Services was originally released in January 2004 as a SQL Server 2000 add-on. The product is now natively integrated in SQL Server 2005 and introduces several enhancements, including the ability to specify multiple values for a parameter, enhanced printing support, interactive sorting in reporting, SharePoint integration, and programmability enhancements. Reporting Services 2005 also includes a new tool called Report Builder that lets business users create ad-hoc reports based on the report models that designers and developers create.
You can upgrade a default Reporting Services 2000 installation by using the SQL Server 2005 Setup, which takes care of upgrading published reports and snapshots. If you've customized the Reporting Services 2000 installation, you must install a new Reporting Services instance, migrate the instance data to the new installation, and deploy your reports on the new instance. Report definitions are upgraded when you open the reports in the designer.
SQL Server 2005 also includes a tool called Report Server Configuration Manager, which Figure 4 shows, that you can use to configure a local or remote report server instance. If you used the files-only installation option for your report server, you must use this tool to configure the server so that Reporting Services can use it. If you used the default configuration installation option, you can use the tool to verify or modify the settings that you specified during setup. This tool also lets you upgrade the report server database to the new format and restore the encryption key.
Before upgrading the Reporting Services installation, back up the report server database and configuration files. Also, use the rskeymgmt.exe utility to back up the symmetric key for the report server database.
Upgrading Tools and Utilities. Enterprise Manager, Query Analyzer, and the administration part of Analysis Manager are now integrated into SQL Server Management Studio. To add SQL Server 2000 Enterprise Manager registered servers into Management Studio, right-click the Database Engine node in the Registered Servers window, and select Previously Registered Servers. Note that database diagrams aren't upgraded during in-place upgrade or side-by-side migration. When you set up database diagramming in Management Studio and open the database diagrams, SQL Server 2005 automatically upgrades them.
Client Network Utility, Server Network Utility, and Service Manager tools are now integrated into SQL Server Configuration Manager. You'll use the Configuration Manager tool to enable or disable network protocols and manage aliases and services. In addition, SQL Server 2005 no longer ships DB-Library–based isql.exe. And Microsoft has deprecated ODBC-based osql.exe; you'll need to update your jobs and maintenance procedures to use the new OLE DB–based sqlcmd.exe. Microsoft has also deprecated MAPI-based SQL Mail and for side-by-side migration, it's disabled by default. If you use SQL Mail to send email messages, consider upgrading your messaging to SMTP-based Database Mail.
Additionally, SQL Server 2005 introduces a new .NET-based management API called SQL Server Management Objects (SMO) that supersedes SQL-DMO. In addition to providing maximum coverage of SQL Server 2005 features, the SMO API contains several other improvements, including a cached object model, delayed instantiation of objects for improved scalability and performance, enhanced scripting capabilities, and improved ease of use. If you have DMO-based scripts or applications, consider rewriting those to benefit from SMO.
Upgrading Other SQL Server Components. Unlike in previous releases, SQL Server 2005's Full-Text Search doesn't share the MSSearch service with other server products that use the MSSearch. Instead, one instance of the Microsoft Full-Text Engine for SQL Server (MSFTESQL) service is installed for each SQL Server 2005 instance. After the upgrade, you must manually repopulate the catalogs. Note that in SQL Server 2005, Full-Text Search requires more disk space than earlier releases.
SQL Server 2005 Mobile Edition provides a command-line upgrade tool (upgrade .exe) in the %ProgramFiles%\Microsoft Visual Studio 8\SmartDevices\SDK\SQL Server\Mobile\v3.0\\[platform\]\\[processor\] folder that you can use to upgrade SQL Server CE 2.0 databases to the SQL Server 2005 Mobile Edition.
Notification Services 2.0 instances aren't automatically upgraded to SQL Server 2005 Notification Services when you run Setup. After installing SQL Server 2005 and upgrading the database engine, you have to manually migrate the Notification Services 2.0 instance metadata by using Management Studio or the nscontrol command-prompt utility.
Post-Upgrade Checklist
A successful upgrade doesn't necessarily indicate the end of the upgrade process. Your upgrade plan must include the post-upgrade tasks you'll perform immediately after the upgrade and in the days and weeks following the upgrade. You also need to plan how you will take advantage of the power of SQL Server 2005 such features as T-SQL enhancements, Service Broker, snapshot isolation, and .NET integration. To be sure your post-upgrade plan is on the right track, you can use the following checklist.
- Run sp_updatestats against each upgraded database to update statistics information about the distribution of key values. This step ensures optimal query performance with upgraded databases. In addition to updating statistics, you should also run DBCC UPDATEUSAGE on all databases following upgrade. This corrects any page and row-count inaccuracies.
- Review the Upgrade Advisor report and perform the recommended post-upgrade tasks.
- For an in-place upgrade, verify the database compatibility level and set it to 90, if required. Several new features, such as Service Broker, require that the compatibility level be set to 90, but be aware that a 90 compatibility level might break some of your existing scripts; for instance, scripts that use *= LEFT OUTER JOIN syntax won't work at 90 compatibility level.
- Use the Surface Area Configuration tool or the sp_configure system stored procedure to determine whether features such as SQL Mail are disabled and to enable or disable such features.
- Verify high-availability and disaster-recovery planning tasks, SQL Server Agent jobs, and maintenance tasks. Reconfigure log shipping.
- Migrate DTS packages to SSIS to improve performance and use the new functionality benefits of SSIS.
- Build the Analysis Services cubes.
- Repopulate the full-text catalogs.
- If you've created any post-upgrade tests and scripts for validating and benchmarking the upgrade, run those tests.
- Plan for implementing new SQL Server 2005 features such as table partitioning, snapshot isolation, security enhancements, Service Broker, XML and .NET support, and Analysis Services enhancements. Upgrade your management and maintenance scripts to use the new catalog views and dynamic management objects.
Finally, you can bring the system online and monitor system activity to be sure the upgrade is working well.
Planning Pays Off
In addition to polishing many of the rough spots of earlier SQL Server versions and providing long-requested features, SQL Server 2005 introduces several enhancements in the areas of management, performance, scalability, high availability, programmability, and business intelligence (BI). If you're responsible for managing one or more solutions based on SQL Server 2000 or 7.0, the first step to reaping SQL Server 2005 benefits is to upgrade legacy SQL Server versions to SQL Server 2005. Although taking on a major upgrade might seem like a daunting task, Microsoft has invested a lot of time and resources in simplifying the SQL Server 2005 upgrade process. Tools such as Upgrade Advisor improve the overall upgrade experience, and careful planning and testing will help you identify and resolve any problems for a smooth upgrade.
