SQL Server Component Considerations

In this section, we discuss individual components along with their respective considerations that should be evaluated during an upgrade process. Components not covered here are covered in their individual chapters.

Upgrading Full-Text Catalog to SQL Server 2005

During the upgrade process, all databases with full-text catalog are marked full-text disabled. This is because of the potential time involved in rebuilding the catalog. Before you upgrade your Full-Text Search environment, you should familiarize yourself with some of the enhancements. In the area of manageability, there is now a Full-Text Search (MSFTSSQL) service for each SQL Server 2005 instance. Full-Text Search now supports XML data types, XML indexes, and XML queries. Full-text catalogs are now a fully integrated part of the database back-up and restore process. This functionality is also supported in the database attach and detach processes. I also recommend reading BOL to learn about additional behavior changes (for example, running the full-text actions against master and tempdb is no longer supported).

Upgrading DTS to SQL Server 2005 Integration Services (SSIS)

First, understand that SSIS is a new application. Other than in a few areas for the purpose of backward compatibility, most DTS codes never made it over to SSIS. As part of an upgrade scenario, DTS will continue to run as is. SQL Server 2000 runtime and backward-compatibility tools are installed automatically to facilitate this as a part of an upgrade process. If you are doing an install on new hardware, you can get the DTS Designer Components (2000 runtime) or backward-compatibility components from the SQL Server 2005 download site (feature pack). The DTS Designer Components will enable developers and DBAs to manage and edit DTS packages and components in a SQL Server 2005 environment. The SQL Server Backward Compatibility package includes all of the DTS Designer Components and additional support for SQL-DMO, DSO, and SQL VDI technologies. These components emulate SQL Server 2004 SP4 functionality.

The DTS 2000 packages will continue to run in a mixed environment. For example, packages can call each other via DTS API from one version to the other. In our experience, most DTS 2000 packages continue to run at plus or minus 10 percent of their original performance level. To achieve the promise of SQL Server Integration Services, you will most likely need to redesign your existing packages. Microsoft provides the means of migrating your existing packages from the command line via DTSMigrationWizard.exe or from applications like Business Intelligence (BI) Studio and SQL Server Workbench. These wizards are only available in the Standard, Enterprise, and Developer editions of the product.

These wizards provide a "best attempt style" migration; that is, the wizard will do a task-for-task migration. Complex tasks that cannot be converted get encapsulated in a 2000 construct as part of the package migration. To avoid errors with the migration of your DTS packages, you should test them with the Upgrade Advisor tool, covered later in this chapter. Neither Upgrade Advisor nor the DTS Migration Wizard can process SQL Server 7.0 packages that have not been saved to disk. To make things worse, SQL Server 7.0 packages must be upgraded to SSIS completely, because they cannot be maintained with the 2000 runtime after upgrade.

Figure 3-8 shows a SQL Server 2000 managed by Management Studio. As you can see, the "Northwind Orders Schema" package is stored locally and can be found nested in the DTS folder, which is in the legacy folder that can be found under the management folder.

Figure 3-8

As an example, we'll walk through migrating this simple DTS 2000 package. This package builds four dimensions and a fact table from Northwind database. With the exception of the "Time Dimension," which relies on a Visual Basic script to parse out levels of a time dimension, this package is simple in nature.

From the Management Studio environment, right-click the Northwind Orders Schema package and select the Migrate option. The first step of the migration wizard asks for the source of the package or packages, specifically, which SQL Server or directory they reside in. In this example, the file resides on a SQL 2000 Server. The wizard then asks you to select a destination location for the migrated package. As you can see in Figure 3-9, the options are for a SQL Server destination or a DTSX file repository.

Figure 3-9

Select a local SQL Server 2005 instance as the destination. The next step presents you with a listing of packages that reside on the source server, as shown in Figure 3-10. After selecting the lone package, enter the location of the error log to be captured for the migration process. Note that only copies of the original packages actually get migrated. The original packages are left functional and intact.

Figure 3-10

Note

Other than maintenance packages and simple import/export wizard-based packages, authoring of packages is no longer supported on the server side. All packages (SSIS or DTS) must be authored in the BI workbench and imported to production server from development.

Finally, you'll see a summary screen of all your previous choices, allowing you to do a final check of everything. With the next click, the process of migrating a copy of the actual package is kicked off. Figure 3-11 identifies the migrated package shown in the SQL Server 2005 Integration Services instance. Meanwhile, the original package remains intact as part of the SQL Server 2000 "SharePointPortal" named instance.

Figure 3-11

We opted to do the migration from the SQL Server Workbench to make a point: The packages are independent of each other and fully functional. The ideal place to perform this migration would have been in the BI Workbench. To accomplish this, you would have started a new SSIS project and selected a migration option. The steps for the migration would have been identical from that point on. What makes the BI Workbench the ideal location for the migration is that it provides the ability to edit the package after migration.

Although we will not introduce SSIS to you here, we will briefly discuss the concepts for ease of conversation. SSIS will be presented in greater detail in the BI chapters of the book: 6, 7, and 8. An SSIS package is made up of one control-flow construct (or container) and one or many data-flow constructs (or containers). The control flow is more of a process construct made up of tasks such as FTP, e-mail, Sequence Containers, and Data Flow Tasks. The Data Flow Task is a pipeline-transformation construct optimized to transform the data from process to process (or task to task) all in-flight. Tasks that perform actions such as sorting, aggregation, data-type conversions, and conditional splits make up the data-flow pipeline. Figure 3-12 shows a control-flow view of the migrated package.

Figure 3-12

As part of the migration, each of the DTS pipeline tasks is transformed into distinct tasks. This creates a total of six tasks: one Execute SQL task for the truncate tables statements, a data-flow task for each of the pipeline tasks (one per dimension), and one to load the fact table. The highlighted component called "transform time" is the "Time Dimension" that we mentioned as not being migrateable, because of the use of Visual Basic. If you edit this task, you'll be presented with properties about this task. The cool thing to remember here is that this task now has the capabilities of a SQL Server 2005 environment, meaning that you can take advantage of feature-like expressions by wrapping this task as part of a looping transform or a sequence container. To see the actual task, all you have to do is to edit it again, and it will be presented in a DTS 2000 environment, as shown in Figure 3-13.

Figure 3-13

Remember that although the SSIS team tried to preserve the look and feel of DTS, the tool has been completely redesigned from the ground up. SSIS is not included as a feature of Express; all existing packages will now have to be managed by another edition. If you try to save a DTS package in the Meta Data repository after applying SP4, you'll find that this feature is no longer supported. So, it's now official - SQL server 2000 Meta Data Services is a deprecated feature. To learn more about SSIS, read Books Online, or see Professional SQL Server 2005 Integration Services, by Brian Knight, et al (Wrox, 2006).

Log Shipping

SQL Server 7.0 and 2000 log-shipping sessions cannot be directly upgraded. SQL Server 2000 implemented log shipping through xp_sqlmaint, which is no longer available in SQL Server 2005. Because of this, you will have to reconfigure these sessions after upgrading to SQL Server 2005 or evaluate a different option. Refer to Chapter 19 for available upgrade options and details.

Failover Clustering

Failover clustering has undergone a few modifications that make the upgrade process a bit more complex. You will not be able to downgrade your cluster from SQL Server 2000 Enterprise Edition to SQL Server 2005 Standard Edition. Upgrading your existing cluster is discussed in detail in Chapter 20.

Upgrading to 64-bit

Upgrading from a SQL Server 7.0 or 2000 32-bit to SQL Server 2005 64-bit platform is not supported. Although running SQL Server 2000 with Service Pack 4 32-bit on a Windows on Windows 64-bit (WOW) subsystem is supported, upgrading this configuration to SQL Server 2005 64-bit environment is not supported. Side-by-side migration is the only supported upgrade path for migrating databases from 32-bit to 64-bit platform.