Conversion from SQL Server 2000

In this section we discuss issues around converting an existing SQL Server 2000 DW/BI system to SQL Server 2005. Some components, like the relational database, will convert smoothly. For other components , we recommend a more drastic approach.

Relational Data Warehouse

It should be quite straightforward to upgrade your relational data warehouse database from SQL Server 2000 to SQL Server 2005. Use the Copy Database Wizard to perform the upgrade, as described in Books Online. Note that indexes and constraints are automatically disabled during the upgrade process. You must manually re-enable them.

Although were certainly not guaranteeing that an upgrade of your relational database to SQL Server 2005 will be trouble-free, we expect a minimum of problems and fuss.

After youve successfully upgraded the existing databases, consider whether to change the databases structure to take advantage of new features in the relational engine. For a relational data warehouse database, the most interesting new feature of the engine is partitioned tables, as we describe in Chapter 4. If youre using UNION ALL views to simulate partitioning in SQL Server 2000, you should convert the structures to partitioned tables. There is no wizard to do this for you, but the conversion should be straightforward. If you used SQL Server 2000 partitioned views you have already tackled the maintenance problem of creating new partitions every new time period. You will need to modify that program to create the new partition and update the partitioning scheme. The logic of the program can probably remain the same, although the exact syntax will differ .

Even if you didnt use partitioned views in SQL Server 2000, you should consider whether to implement partitioning in SQL Server 2005. However, you will need to modify your ETL and database maintenance systems to support your partitioning scheme.

Integration Services

Although Integration Services is descended from SQL Server 2000 DTS, its substantially different. The fundamental way you approach solving ETL problems is different. Microsoft provides a DTS Migration Wizard to help you upgrade your existing packages.

Dont spend much time migrating packages. Instead, leave most or all of your existing DTS system in production. You can continue to run the DTS packages on a SQL Server 2000 instance. Microsoft includes the DTS runtime executables with the new SQL Server, so you can run DTS packages on SQL Server 2005 if you want to. Lets hope you followed DTS best practices and used variables to set connection properties. If not, youll need to edit the packages to point to the new server. Your developers will still need access to an instance of SQL Server 2000 in order to edit the old packages.

Whenever you need to perform a substantial edit to a DTS package, strongly consider rewriting it as an Integration Services package. If you used master packages and subpackages in DTS, youll probably want to rewrite the master package. Recall from Chapter 5 that theres an Integration Services control flow task to execute a DTS package. Of course there is no corresponding DTS task to execute an Integration Services package.

The more expert you are with DTS, the more careful you need to be when upgrading and rewriting your packages. DTS experts rely heavily on the relational database to perform transformations. Integration Services best practice uses the in-memory data flow task. DTS experts wrote strange scripts to modify the flow of data through a package at runtime. These scripts will not upgrade to Integration Services, and this entire technique is supplanted by new constructs like looping containers, conditional flows, error flows, and event handlers.

Analysis Services

Microsoft provides an Analysis Services Migration Wizard, which is available from the Start menu (Start All Programs SQL Server 2005 Analysis Services Migration Wizard). After the database has been migrated , you can use BI Studio to examine and edit its structure.

Its instructive to explore the structure of the migrated database. Most interesting are the dimension structures, and the MDX script thats generated from Analysis Services 2000 calculated members and cells . However, the best database that you would build from scratch in Analysis Services 2005 is usually different from the migrated structure. The two biggest differences are:

• The dimension structures, discussed at length in Chapter 7

• The database structure, notably the shift to a single cube with multiple measure groups replacing multiple cubes

The most interesting new functionality in Analysis Services isnt available through the migration process. You must explicitly design that functionality, like new hierarchies or many-to-many dimensions, into the cube. And, frankly, its easier to redesign from scratch than to perform massive edits on a migrated database.

If is perfectly reasonable to migrate old databases as a temporary measure, until your team has time to tackle a fresh design. The migration wizard works pretty well, and a simple migration is straightforward.

Reporting Services

Reporting Services 2000 was released at the end of 2003, and is very closely related to the new 2005 product. As a result, there are no significant upgrade issues to discuss.

The biggest improvements with Reporting Services 2005 are incremental, filling in functionality that was missing from the Reporting Services 2000. The Report Model and Report Builder features provide some functionality for business users to develop ad hoc reports. And, both business users and developers can much more easily develop standard reports using Analysis Services as a source. Not only should query performance improve for dynamic reports , but you will be able to present more complex business measures in a simple report interface.

Data Mining

You can use the same Analysis Services Migration Wizard to help you migrate a data mining model to Analysis Services 2005. After you run the Migration Wizard, use BI Studio to examine and edit the data mining models structure.

The wizard converts models and their contents to SQL Server 2005, and you can execute a prediction query directly on a converted model. As we describe in Chapter 10, most uses of data mining in production are based on a custom application that issues prediction queries against a trained data mining model. This application, typically written in a Visual Studio language, should continue to function unchanged with the converted data mining model.