Solved

How to merge two SQL servers onto one

Posted on 2010-11-15
6
352 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MSSQL Speen Degradation 4 22
kill process lock Sql server 9 51
Changing the datatype of a column from nvarchar to date 47 18
sql 2014,  lock limit 5 32
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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