How to merge two SQL servers onto one

We have two servers each running MS/SQL 2005.  Each has several databases. each has ( one several and one many ) SQL Agent Jobs and Local DTS Packages.   We are replacing our two existing SQL Servers with one beefy server and will be installing a single instance of MS/SQL 2008.  I need to migrate and consolidate the two existing servers onto this one new server.  I'm OK with the databases as they all have unique names but I'm concerned about the Agent Jobs and DTS Packages.  How do I consolidate these onto the new machine?  I suspect I can use the Generate SQL Scripts on the Jobs but have no clue as far as the DTS Packages are concerned...  Help ( and cautionary tales ) from this community GREATLY appreciated ( the more detailed the better )...

Thanks !
LVL 2
Swamp_ThingAsked:
Who is Participating?
 
LowfatspreadConnect With a Mentor Commented:
why aren't you migrating to 2 instances of sql 2008?
surely that will be an easier migration path and still allow for greater control/tuning (that you currently have with the two servers)...?

0
 
DFW_EdConnect With a Mentor Commented:
Here's a script that might be helpful if you have xp_cmdshell enabled
 copy-down-DTS-packages.txt
0
 
Swamp_ThingAuthor Commented:
LowFat:  One of the two current servers serves databases that are static and used for historical reporting only.  We'll reduce our hardward footprint, licensing costs, maintenance plan fees etc. etc. by consolidating.

Now - if I could install multiple instances of SQL Server 2008 on the same server I'd go for that but I don't believe MS/SQL Server 2008 Standard allows for that...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
DFW_EdConnect With a Mentor Commented:
SQL 2008 standard will support up to 16 instances.  Enterprise will support up to 50

0
 
Swamp_ThingAuthor Commented:
yup - according to http://msdn.microsoft.com/en-us/library/ms143531.aspx I'll be able to create a named instance for each of the "old" servers.  This is going to make life So much easier...
0
 
Swamp_ThingAuthor Commented:
Splitting the points 3 ways with the lion's share to DFW_Ed as he answered the question directly.  The best answer, from both Ed and Lowfat, is multiple instances ( which I did not know were supported ).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.