February 28, 2008 at 9:38 pm
I have this one DB called Service and another one called Corp.
Both these DBs are on different machines, different locations... and are the DBs for the company websites.
The task is to merge Service and Corp.
What is the best way to do this.. with no downtime..
Also, there are two tables in both DBs that have the same name. How do I handle this?
I need help.. I'm no expert but I've been given this task. HELP!
February 28, 2008 at 9:47 pm
It's a pretty open-ended question. I assume that the databases have nothing in common.
Firstly, can your web site code point to a different database without much effort?
They're in different locations... Are you also moving the web site server from the remote location? If not, you had better want a really good link between the two locations.
Before I go further, I think perhaps you're looking at this the wrong way (or I've misunderstood your question). Who has asked you to "merge" the databases and for what purpose?
If it is just so that you can report off data in both databases from within a single report then I would suggest you create a third database and load portions of the data from each one into the new third database. Usually this is what management want. Merging two distinct databases for the sake of it is not commonly done without good reason.
Perhaps by "merge" you mean that both are to be now hosted on the same server. If that's the case then be aware that you can have many databases on a single SQL Server. This will suit you nicely.
If the web applications are very poorly written and require exclusive use of SQL Server (perhaps they use the sa account with a fixed password) then you can set up what's known as a second instance of SQL Server on the server. Effectively you are running two completely separate copies of MS SQL Server on a single physical server.
Can you elaborate a bit more about what you mean by "merge" and what the end goals are?
February 28, 2008 at 9:53 pm
Here's how I would do it:
Step 1 - determine if the tables with the same names be merged into a single table in a new db or do is the data different enough that you need to keep them in separate tables.
Step 2 - create a new db
Step 3 - use SSIS Transfer SQL Server objects to transfer the objects to the new database, minus the duplicated tables
Step 4 - if the duplicate tables can be merged to 1 table then import 1 of the tables using SSIS and then use SSIS to load the data from the other system. If not create the different tables in the new db and then use SSIS to load the data.
Step 5 - change data access code to use new objects (hopefully it is all in stored procedures so you only change the sql code)
Step 6 - deploy after testing
I would also ask why the data needs to be in one DB. What is the business reason for this change and is it valid? Is it to just house the data in one place? If so, just move the DB's to one server and point the web site(s) to that server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 29, 2008 at 9:03 am
Do they have any tables in command in so are there any common columns and the list goes. do an impact analysis and then use the SSIS to move tbales from one database to the other.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
March 2, 2008 at 10:37 am
The task is to merge Service and Corp.
What exactly do you mean by merge? Does it mean that you want to consolidate the data from two databaeses to one? Or, does it mean that you want to have two applications hit the same database?
Also, there are two tables in both DBs that have the same name. How do I handle this?
One way would to be set up schemas or synonyms, for each application. This may incurr downtime to implement, if the code has fully qualified names.
It is never a good idea to house to different applications in a single database. What is the purpose of the merge?
March 2, 2008 at 11:22 am
Not sure why you're merge two disparate applications, but running multiple applications off a single database makes perfect sense to me. If the data is related.
I might have an application for vacation tracking that uses the same database as the HR overall application, etc. The important thing is that the data be related. If it's not, why not use two databases?
March 3, 2008 at 10:53 pm
Ok.. first.. sorry about the time lapse between my first post and this reply.
I guess I hurriedly posted my query and I was very vague.
Let me tell you what's really happening here:
1) Service and Corp are 2 different websites with DB space on 2 different servers.
2) The organization wishes to have only one website, one DB, at one location. That way they can save costs incurred.
3) Since the sites are related and can be merged (or ideally SHOULD HAVE BEEN one in the first place!), I was asked to do it.
4) Service has only tables (no other data objects) with reletaively less amount of data so it would be easier to move this into Corp.
Ian Yates:
Perhaps by "merge" you mean that both are to be now hosted on the same server. If that's the case then be aware that you can have many databases on a single SQL Server. This will suit you nicely.
Problem 1: Neither the server that houses Corp, nor the server that houses Service is permitting us to have another DB on their server.
Problem 2: Two tables in Service are identical (in name not schema) to the ones in Corp.
What I finally ended up doing was simple : Data Export Wizard! And the 2 tables, I moved them into Corp as _Service... new tables!
I was having a lot of problems initially.. thinking of setting up a linked server, then using openrowset.. but I kept having trouble.
See post :
http://www.sqlservercentral.com/Forums/Topic462985-338-2.aspx#bm463109"> http://www.sqlservercentral.com/Forums/Topic462985-338-2.aspx#bm463109
But then Adam Haines and ALZDBA helped me out there!! Thanks a ton, people!
Thank you, all ye guys here.. you save me each time! 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply