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
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
  • 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 @
LVL 51

Assisted Solution

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Creating a SQLServer BI cube from Oracle data source 4 39
SSIS Column mapping 5 39
Need more granular date groupings 4 42
Section based report in SSRS 14 20
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

738 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