Solved

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

Posted on 2008-10-17
4
334 Views
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.
0
Comment
Question by:PKTG
  • 3
4 Comments
 
LVL 51

Accepted Solution

by:
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.
0
 
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...
0
 
LVL 9

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
0
 
LVL 51

Assisted Solution

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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now