Solved

How to merge two SQL servers onto one

Posted on 2010-11-15
6
355 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 40
find SQL job run average duration 24 75
sql server major issue  need help 2 81
SQL SERVER 2008 R2 Problem copying database 10 68
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…
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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