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.
- Back up all SQL Server database files from the existing instance so that you can cancel the upgrade and go back to the initial state, if necessary. Be sure to validate the backup so that you know you can actually restore it.
- Run the appropriate DBCC consistency check commands on databases to be upgraded and system databases (except tempdb) to ensure that they're in a consistent state.
- Disable all startup stored procedures.
- For an in-place upgrade, ensure that system databases are configured to autogrow and that you have adequate hard-disk space.
- If you have any trace flags turned on, ask your Microsoft representative whether those trace flags are supported or required during and after the upgrade.
- Stop replication and make sure that the replication log is empty.
- If you have replication set up, script the replication configuration.
|
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.
Prev. page
1
[2]
3
next page 
