March 24, 2010 at 2:13 pm
Hi
I am in the middle of this huge data center movement project. well actually its more of data movement project. The new servers are having a great config and can do the job of 2-3 servers in the old environment.
Thats exactly what we are doing. Merging multiple servers into 1.
We already imported the MSDB database from 1 of the old db servers to the new one. This way we got all the objects/ data comprehensively from old the new. eg.... the jobs, dts and ssis packages including their history.
Now, we need to get the databases from another server on this server. We also need to bring in the existing MSDB db. but if we try to restore MSDB of the 2nd server on our destination then we will loose everything in the MSDB from the 1st server on our destination.
just asking, is there a better way to handle this, because I am getting pretty confident that will not be able to use backup-restore.
here is the final summary of my requirement.
i need to merge the msdb db of 2 servers into 1. I already have 1 and need to get in the other.
My last option is to bring in all the jobs and DTS/SSIS packages manually.Is there a better way to do this. we dont really mind if the history does not comein but if we can they we will definitely like it.
March 24, 2010 at 2:51 pm
If you are consolidating on a single instance of SQL Server, your only option is going to be scripting out the jobs and rebuilding on the new server.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 24, 2010 at 2:51 pm
you are going to have to do this manually, scripting out jobs and saving and importing DTS/SSIS. (you have both?)
Is this actually SQL2005?. If so if the server has a different name from the one you restored the msdb from you are going to hit problems if you try and edit any maintenance plans copied across. Restoring msdb across servers is not advisable in SQL 2005 and above.
---------------------------------------------------------------------
March 24, 2010 at 3:58 pm
yes
the server is a different one with a different name altogether, I agree with you that the maintenance plans will take a hit. We are actually going to have to drop the existing maintenance jobs and recreate fresh jobs. basically the Maint jobs are the least of our concern, we have jobs in excess of 100 of each of the 3 servers that we are importing data from. The no. of DTS packages are also very high and we will be changing the connections etc in the packages and jobs as well.
Scripting the jobs and saving the DTS/SSIS packages is still on but we are exploring other options.
Your inputs are appreciated.
We do still have the backup of the original MSDB so we are ready and will definitely roll back if things dont work out.
March 26, 2010 at 10:29 am
Check-->http://www.pragmaticworks.com/Products/Business-Intelligence/DTSxChange/
DTS xChange - DTS to SSIS Migration
DTS xChange is an enterprise ready tool to convert DTS packages to SSIS 2005/2008 with very little manual effort. DTS xChange will automatically convert most of your tasks, variables, connections and any other package level settings automatically. DTS xChange offers 3-Phase approach to convert your DTS packages.
MJ
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply