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
