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
  • 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

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 shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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