SQLServerCentral Article

Migrating Data Warehouse Systems to SQL Server 2005


How do we migrate or upgrade a data warehouse system from SQL Server 2000 to

SQL Server 2005? Must we rewrite all DTS packages in SSIS? Can't we use migration

wizard to upgrade them to .dtsx or get SQL 2005 to execute SQL 2000 DTS packages?

Will there be any problems if we upload Reporting Services 2000 RDL files onto

2005 Report Manager? Will we be able to open 2000 reports in 2005 BI Development

Studio and add the long awaited end user sorting and freezing table header,

or do we need to redesign the reports completely from scratch?

Since last year some of us who build or run data warehouse systems in Microsoft

SQL Server 2000 platform have been asking those questions, especially when Microsoft

positioned SQL Server 2005 as the end-to-end platform for business intelligence

systems. New features and Software Assurance encourage us to upgrade from SQL

Server 2000 to SQL Server 2005. It's now July 2006, 8 months since SQL Server

2005 went to production, and SP1 is already out so what are we waiting for?

In this illustration the data warehouse system we want to upgrade consists

of 16 DTS packages for the ETL from source systems, 4 SQL Server databases (Stage,

Operational Data Store, Dimensional Data Store and Control database), a batch

architecture consisting of 128 transact SQL stored procedures including data

quality procedures, 27 Reporting Services 2000 RDLs and 6 Analysis Services

databases / cubes. The SQL Server 2000 runs reporting services and analysis

services. We want to migrate this system to a new server running SQL Server

2005 database engine, integrating services, reporting services and analysis


Step 1. Databases

First, we backup all data warehouse databases. We then restore the database

backups on the SQL 2005 server one by one. During the restore process, the databases

will be upgraded to 2005 format automatically. Don't forget to specify 'with

move' option when restoring if your folder structure on the new server is different

from the old server. After all databases have been restored on the new server,

create necessary logins which are required by the data warehouse applications,

leave the user mappings blank for now. Drop the application users from all DW

databases. If this fails, it is because SQL Server 2005 automatically creates

schema for each user and assigns these schemas. We need to drop the schemas

first before dropping the users, see picture below. Afterwards we recreate the

application users with appropriate permissions and database roles.


Step 2. Service Area Configuration

At this stage all 4 DW databases have been restored including all the users,

stored procedures and functions. Next, we run the DW batch, which in this case

it will run all 128 stored procedures in a systematic way, i.e. one calling

the other until eventually all procedures are run. If we use SQL Mail or xp_cmdshell

in any stored procedure used in the batch, we must enable these features on

the Surface Area Configuration: click on Features, View by Instance, SQL Mail,

click Enable as pictured below. Likewise with xp_cmdshell.

Step 3. Reporting Services

Next, we configure

reporting services on the new server (click for details), as pictured below,

to make sure that necessary settings are correct and that reporting services

are working normally. If you use web farm or scale-out deployment, follow this

MSDN article, as well as this

one. We then go to Report Manager and upload all the RS 2000 RDLs into RS2005.

Yes, 2000 RDLs runs perfectly fine on 2005 RS engine, unless you use the features

mentioned on this

article, i.e. rsactivate, WMI provider, SetReportParameters, etc. In these

cases you will need to make some changes to the reports. In our case we don't

find any problems and all reports ran OK.

Don't forget to point the data sources to the correct server and database as

pictured on the right below. We then run the reports. If there are errors when

running the reports, make sure that the report server is configured properly.

We can upload and run a very simple report to verify and test the configuration

and base build.

After we are satisfied that the reports run OK on 2005 report server, we then

open each report definition file (RDL) one by one in Business Intelligence Development

Studio 2005 / Report Designer 2005. When we do this it will ask us if we want

to upgrade the RDL to 2005 or not. The difference between 2005 RDL and 2000

RDL are: namespace, actions element, CustomReportItem element and custom element.

So say yes to the dialog box that you want to upgrade the RDL to 2005, then

save the RDL. Please beware that once we do this we can not open the RDL in

Report Designer 2000 anymore. So if you are upgrading development or QA infrastructure,

you may want to save a copy of the RDL folder structure, in case you need to

do a fix on the production reports. For further details about opening RDL in

Report Designer 2005, see this article.

Step 4. DTS Packages

This Books

Online article explains the options we have when upgrading or migrating

DTS packages to SQL Server 2005. Basically we have 2 options: either we rewrite

them in SSIS, or we run them as they are. SQL 2005 comes with a migration wizard

which can migrate DTS packages to SSIS automatically. In our case the wizard

did not work well: it provides a starting point (rather than starting from scratch

but there are still a lot of effort required to make the packages work. In our

case we decided to run them as they are, as our goal was to get the data warehouse

system migrated as soon as possible. We can then rewrite the DTS packages in

SSIS at our leisure. We can even run them side-by-side: some of the packages

in SSIS and some still in DTS. So, the next step is to download

and install DTS designer (click this link and search for Microsoft SQL Server

2000 DTS Designer Components on that page). We save all DTS packages as structured

files, then open these files using Management Studio as pictured on the left

below. If the location of the .ini file on the new server is different from

existing server, modify the dynamic properties tasks on the packages to point

to the correct ini file (see the picture on the right below), otherwise the

packages are ready to go. That's one of the advantages of using dynamic properties

tasks: when it comes to migration we don't have to edit every single connection.

Save the package in SQL Server. Verify that the package is listed under Management,

Legacy, Data Transformation Services on Object Explorer.

At this stage we have 2 options to execute the DTS packages: using DTSRUN,

or create an SSIS package to execute them. Allan

Mitchell, one of the authors of the SSIS book, wrote a good article

about this. We decided to use SSIS to execute the DTS packages. We think that

this way we more flexibility in arranging DTS and SSIS packages running side-by-side

later on, i.e. the top level package (written in SSIS) can have a mix of 2nd

level packages, some SSIS and some DTS.

To do this, on Business Intelligence Development Studio, create a new SSIS

package (File, New, Project, type the name, specify location and solution name,

click OK). Drag Execute DTS 2000 Package onto the canvas, double click it to

edit, storage location: SQL Server, SQLServer: type the server name, package

name: select the package we saved above, click OK, save the SSIS package as

.dtsx file. Detailed instruction about creating an Execute DTS 2000 Package

task can be found on this MSDN


We then store the package on SSIS package store. On Management Studio, connect

to Integration Services instance, expand Stored Packages, right click on a folder,

click on Import Package. Package location: File System, package path: select

the dtsx file we saved earlier, package name: type a name. (see below). The

SSIS package should now be listed under the folder (see below). Detailed instruction

about importing a SSIS package can be found on this MSDN


We then schedule the package to run as a job, as follows: on Management Studio,

under SQL Server Agent, right click on Jobs, new job, type the job name, click

on Steps on the left panel, click New to create a new step, type the step name,

choose the type as SSIS package, package source: SSIS Package Source, server:

type the server name, package: choose the package we have just stored above,

click OK to create the job step (see below). Click on schedules and set the

time we want to run the package and click OK to create the job.

Execute the job to test the package. Verify the results in the database. In

our case we have several source systems and each packages import from a different

source system. So after each DTS packages have been wrapped in SSIS, create

a top level SSIS package which calls those SSIS packages. We then test the overall

ETL package is working properly, importing data from multiple source system

into the staging database.

Step 5. Analysis Services

The last step is to migrate the Analysis Services databases to SSAS 2005. From

Management Studio, we connect to SSAS, then right click on the instance name

and select Migrate Database (see below). Click next on the welcome screen, then

specify the AS 2000 server name and AS 2005 server name, Select the source database

to migrate. It validates the metadata then it migrates the database to 2005

version. After all the AS databases have been migrated, we need to edit their

data sources to point to the 2005 dimensional database. Then we process these

SSAS 2005 databases. Browse them all to verify. More detailed information is

available on this article.

It is also a good idea to read the considerations

before migrating.

All the 5 steps above migrates (rather than upgrade) a data warehouse system

from SQL Server 2005 to SQL Server 2000. If we upgrade (rather than migrate)

an instance of SQL Server 2000 to SQL Server 2005, i.e. install SQL Server 2005

on top of a SQL Server 2000 installation, the setup program will upgrade all

the databases, the Reporting Services reports, and upgrade all the Analysis

Services databases to 2005 version. If we upgrade, we only need to do step 4,

i.e. Data Transformation Services, and optionally step 2 if we use xp_cmdshell

and/or xp_sendmail.

The migration process is not as difficult as it seemed in the first place,

especially the databases and the reporting services. It is very useful to find

out that SQL Server 2005 can run DTS packages. When installing SSIS, setup upgrade

the run time support so that DTS packages can connect to SQL Server 2005 databases.

Analysis services was also quite trivial (the user interface of the cube browser

is a lot better in 2005). If we don't have new server but only one server, we

have to decide whether we want to perform a side-by-side upgrade (install as

a second instance) then migrate or an in-place upgrade (install on the same

instance, overwriting it). In this case the up time requirement as well as the

hardware capacity would dictate the approach.

Good luck with your upgrades and migrations.

Vincent Rainardi

5 July 2006


5 (3)




5 (3)