How to merge two SQL servers onto one

Posted on 2010-11-15
Last Modified: 2012-05-10
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 !
Question by:Swamp_Thing
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
  • 2

Assisted Solution

DFW_Ed earned 300 total points
ID: 34140969
Here's a script that might be helpful if you have xp_cmdshell enabled
LVL 50

Accepted Solution

Lowfatspread earned 200 total points
ID: 34143161
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)...?


Author Comment

ID: 34144930
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...
Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.


Assisted Solution

DFW_Ed earned 300 total points
ID: 34145672
SQL 2008 standard will support up to 16 instances.  Enterprise will support up to 50


Author Comment

ID: 34147110
yup - according to I'll be able to create a named instance for each of the "old" servers.  This is going to make life So much easier...

Author Closing Comment

ID: 34147175
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 ).

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

717 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