[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

How to merge two SQL servers onto one

Posted on 2010-11-15
6
Medium Priority
?
360 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
[X]
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
6 Comments
 
LVL 3

Assisted Solution

by:DFW_Ed
DFW_Ed earned 1200 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 800 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 3

Assisted Solution

by:DFW_Ed
DFW_Ed earned 1200 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

656 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