Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Whar are the step need to follow for SQL Server 2005 to 2008 migration mainly SSIS packages?

Posted on 2008-10-17
Last Modified: 2013-11-10
In my client place they plan to migrate SQL Server 2005 enterprise version (64 bit) to SQL Server 2008 Enterprise edition (64 bit). (Clean installation not upgrades).
Mostly we are using SQL Server SSIS for ETL tool. We created many SSIS package Job and Schedule it to run every day. I know all the job details are stored in msdb database.And we are taking user and msdb, master and model database backup every day.
1. Can you please clarify me what are the steps I need to take for this migration?

2. Can I restore 2005 user database in SQL 2008. Let me know the practical problems while restoring in 2008.

3. How to restore system database like msdb, master and model. Or do I need to take all job script and run in 2008. If it is so again I have to deploy the packages in SQL 2008.

Please share your migration experience and practical issues.

Thanks in advance.
Question by:PKTG
  • 3
LVL 51

Accepted Solution

Mark Wills earned 375 total points
ID: 22752221
Well, if it a clean installation then the first step is to make sure that all security paradigms are in place - that is often the "sticky" area where SSIS can get into trouble...

Yes, you can restore your 2005 database to 2008. Should be no real problems. If you do want to use some of the newer features, then will need to update the compatability mode of the database - which is now done using alter database SET COMPATIBILITY_LEVEL = 100

Cannot really restore those "master" databases from 2005 - that is probably the single most compelling reason as to why you run an upgrade in place rather than start with a brand new install.

While it may appear to be a "better" option to start with a new install, unless you want to go through the pain of establising all those environmental and security models then it can be painful. Mind you, last time I did it, took the opportunity to actually put in place some of those "much needed" constraints and a "clean sheet" view of security to set it up properly (2005 was different to 2000, and didn't want to popogate the "gotta get it fixed" approach to security from the previous upgrade).

But if you do have a good structure and very happy with your 2005 environment, really would do the upgrade in place...

Now, should be no problems if really SSIS upgrade, but, if still DTS then there are some problems ahead. Similarly active X scripts will need attention, and VSA will be updated to VSTA... But the biggest problem will be (once and for all) getting rid of all that dtsrun type stuff and making it all dtsexec - ie migrating from DTS to SSIS - which a lot of people did not do when upgrading to 2005 for the first time (and now pay the price).

Best to have a look at the Upgrade Advisor and is available in the Servers\redist\Upgrade Advisor folder of the SQL Server installation media - or online downloads.
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 375 total points
ID: 22752246
I may have given the wrong impression - you can still use dtsrun, but is only 32-bit, and need to make sure that the dts runtime support is installed when setting up SQl2008, and, if you happen to install SQL2000 / 2005 afterwards then some of the registery seetings drop off and you have to run Microsoft SQL Server Backward compatibility wizard from control panel / add / remove (then "change" to launch the wizard)...  And if you need design time support (which is my real point above), then have to install Microsoft SQL Server 2000 DTS Designer Components - as a seperate download from MS...

Assisted Solution

by:Tone' Shelby
Tone' Shelby earned 125 total points
ID: 22753495
Fyi: Newest MSDN guidelines have just been published (August 2008) on migration process & consideration & can be found @ http://msdn.microsoft.com/en-us/library/bb677622.aspx
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 375 total points
ID: 22754332

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question