Solved

How to merge two SQL servers onto one

Posted on 2010-11-15
6
350 Views
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 !
0
Comment
Question by:Swamp_Thing
  • 3
  • 2
6 Comments
 
LVL 3

Assisted Solution

by:DFW_Ed
DFW_Ed earned 300 total points
ID: 34140969
Here's a script that might be helpful if you have xp_cmdshell enabled
 copy-down-DTS-packages.txt
0
 
LVL 50

Accepted Solution

by:
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)...?

0
 
LVL 2

Author Comment

by:Swamp_Thing
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...
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 3

Assisted Solution

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

0
 
LVL 2

Author Comment

by:Swamp_Thing
ID: 34147110
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
 
LVL 2

Author Closing Comment

by:Swamp_Thing
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 ).
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

In this article I will describe the Detach & Attach 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.
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.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

758 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

22 Experts available now in Live!

Get 1:1 Help Now