SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Migrating Data Warehouse Systems to SQL Server 2005

By Vincent Rainardi,

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 services.

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 article.

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 article.

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

Total article views: 16070 | Views in the last 30 days: 19
Related Articles

SQL Server 2008 Reporting Services

SSRS 2008 Report Service on SQL Server 20005 Database


Reporting Services driven DTS Package?!

Can you execute a DTS Package from within Reporting Services


SQl Server Reporting Services

Reporting Services connection error


SQL Server Reporting Services

Reporting Services Connection Problem


Report Server Database Configuration

Report Server Database Configuration : Error