Post reply

Looking for data sync product

  • July 17, 2009 at 11:23 am

    #202590

    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

    #1026356

    Replication will do this, and you can transform the data somewhat on either side.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 17, 2009 at 12:02 pm

    #1026372

    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

    #1026382

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • July 17, 2009 at 12:50 pm

    #1026389

    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

    #1026390

    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

    #1026392

    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

    #1026414

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • August 6, 2009 at 4:09 pm

    #1035716

    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

    #1036414

    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

    #1041056

    Hi yonision

    I have a question before I try your tool, Wil your application be able to sync/compare the databases of >20GB and 2 remotely separated database servers.???:w00t:

    Thanks ,

    Shekhar

  • August 18, 2009 at 12:15 pm

    #1041066

    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

    #1041088

    Anybody has any idea of my requirement?..

    Thank you in advance

    Thanks ,

    Shekhar

  • August 18, 2009 at 1:43 pm

    #1041138

    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.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • August 19, 2009 at 1:30 am

    #1041303

    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

    Thanks ,

    Shekhar

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply