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

Posted on 2008-10-17
Medium Priority
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 1125 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 1125 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 375 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 1125 total points
ID: 22754332

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

801 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