July 17, 2009 at 11:23 am
I'm looking for a product that will do 2 way sync of ms sql databases.
I have data in X database that needs to move to Y database and then in
reverse. However data is being updated in both databases all the time.
So an application uses X that data should move to Y. Another
application uses Y that data should move to X. To make things worse.
The schema is not the same. The data is but the schema isn't so some
kind of translation has to occur. An example could be X has customers
table with phone number. That translates to Y customers table and
phone numbers table.
Anybody know of a product that can do this?
Many thanks for the help.
July 17, 2009 at 11:38 am
Replication will do this, and you can transform the data somewhat on either side.
July 17, 2009 at 12:02 pm
I'm not sure if I understand. Are you saying the replication service will be able to do the transformation of the data? Or you are suggesting to move the data with replication then manipulate it once you have it on the other side by other means.
My question would be does replication service have some built in way of doing the transformation? If I have to build the transformation myself then might as well do the move myself.
I'm essential looking for a product like this http://symmetricds.codehaus.org/ with the capability of handling differing schema. SymmetricDS can not do the one table into two tables type of thing.
Thanks
--Tigran
July 17, 2009 at 12:40 pm
You can write a query or view to change data around and handle some simple transformations.
Without knowing more about what you mean, it's hard to recommend something.
I think anything you do is going to be complicated. And anyone's product could get you 80% of the way there and cause issues.
Honestly I'd look at transforming the data on one side into the schema of the other side. SQL is good at this, and replicating that data over.
July 17, 2009 at 12:50 pm
Check out Toad for SQL Server (not the freeware edition). It has a Data compare and synch feature that allows you to do synchs in either direction. Data can be on same server or different. Tables can be named similarly or not. It can handle the data type differences in general as well...
July 17, 2009 at 12:51 pm
Here's some more info
Agreed, however I'm looking for a product that lists all tables in two databases and I just choose the from table to table from column to column and in the middle if it's not a one to one relationship do some kind of lookup.
So a simple user walk through would be.
Select source db (oldDB)
Select destination db (newDB)
Select source table (People)
Select destination table (Customers)
Select source column (Phone Number Type)
Select destination column (Phone Number Type ID)
Mark as a lookup field
Select lookup table (Phone Number Type Lookup)
Source column is phone number type which is a string, destination is an ID from Phone number type lookup table.
So in source db the field says "Home" for phone number type and in destination it must insert the ID of Home which it can lookup what it is in the lookup table.
--Tigran
July 17, 2009 at 12:57 pm
Sorry, probably not a good fit in your env where there's no 1:1 relationship...as far as the Compare/Synch GUI in Toad is concerned.
July 17, 2009 at 1:37 pm
What you probably want for complex lookups is an SSIS package that moves data from server A-> B. Then another package to move from B->A. However you'll need to somehow prevent circular movement of data.
August 6, 2009 at 4:09 pm
Thank you all for the replies.
We decided to develop our own app that will do this. Essentially what it does is setup triggers in each table that needs a sync. The triggers are CLR C# code so we can do all sorts of lookups and data manipulation if need be.
I would be interested in hearing from people that think this kind of application would be useful.
Thanks
--Tigran
August 8, 2009 at 11:15 am
While the ideas posted up here are good (replication, SSIS transformation) we have developed in my company a product that does just that. its in an initial state and we give it for free, for people who try it and give us feedback. essentially, you can give a bunch of queries on both databases, and it'll do a push so that one query result will equal the other, which is what you're looking for. and you can browse all your database's tables, have colorful report on the data, schedule it, etc. check it out here: http://www.nobhillsoft.com/Columbo.aspx
August 18, 2009 at 11:59 am
August 18, 2009 at 12:15 pm
Hi
The app is very good in finding differences in various tables, allowing you to choose what you want to sync. but for very large databases, it is not ideal. it does not compete with BCP and other 'native' tools out there, when it comes to performance. nothing is.
August 18, 2009 at 12:34 pm
August 18, 2009 at 1:43 pm
tigrank (8/6/2009)
Thank you all for the replies.We decided to develop our own app that will do this. Essentially what it does is setup triggers in each table that needs a sync. The triggers are CLR C# code so we can do all sorts of lookups and data manipulation if need be.
I would be interested in hearing from people that think this kind of application would be useful.
Thanks
--Tigran
Yes, this type of application is usefull.....its an ETL tool. SSIS does exactly what you are describing. You can pull up a source and destination table, map the columns, do lookups, transformations, etc.
August 19, 2009 at 1:30 am
I have a situation where in I have syncronize the secondary server with the primary server having 20GB database, and both are in distinct locations physically
(Log shipping and Transactional replication are not applicable to this situation)
is there any tool or approach to solve this...
thank you in advance
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply