The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Synching two databases

Before I begin on this process, I thought i'd get some ideas from you gurus who are way smarter then me.  Lets say I have three client notebooks.  They all want to synch their databases together.  (As opposed to one central server)  What is the best way at accomplishing this task?  I can create a date field, and if the date is more recent on one, use that record as the most recent, but lets say a customer record was created on both systems when they were out of synch.  What's the best way to ensure he/she doesn't get duplicated?  Anyone have any good design docs on this process?
Phil Send private email
Saturday, January 28, 2006
 
 
Most database systems support replication so there is no need to add a date field and reinvent the wheel.

For example, if you're using MySQL, refer to http://dev.mysql.com/doc/refman/5.0/en/replication.html to learn how to set it up.
GinG
Saturday, January 28, 2006
 
 
Amen to the previous answer. See what the vendor has provided. It must be fascinating writing replication code, but unless you work for a database vendor it isn't what you should be doing.
George Jansen Send private email
Saturday, January 28, 2006
 
 
Umm... I'm going to go against the grain here and say that replication can be a real pain in the butt. We made the mistake of assuming that we shouldn't write our own replication system. After all, database vendors do this stuff for a living right? Well, the fact of the matter is that database vendors have to write full blown replication systems that are capable of taking care of the most complex replication scenarios. They write systems that require DBA's to manage them. For our special needs, this was overkill and ended up being a total pain in the butt. We spent way more time trying to manage remote replication sites than we would have spent writing a simple one-way replication mechanism to fit our needs.

It is hard to say which will be harder for you because we don't know what your exact replication requirements are (one way, two way, central server, ditstributed servers, etc.). But don't make the mistake of assuming that database vendors write replication systems that will work for YOUR scenario. It simply isn't always true...
Turtle Rustler
Saturday, January 28, 2006
 
 
I was afraid you might say that... lets say there is no built in support for replication (there isn't in this case).  Lets say there is not a full blown database on every client, but they still need to synch their data up.
Phil Send private email
Sunday, January 29, 2006
 
 
Can you tell us what they are using for a database? 

For 2 laptops you could go peer to peer but for 3+ I think you're better to get a desktop that acts as a master server for all 3.  I've done a setup like that using MSDE 2000 on a desktop and then had 4 laptops with MSDE 2000 sync with it.  Write up a little apps to trigger the sync on the laptops and they double click it when they are online.  It then gives a success or fail message back.  Once this is working the only time there was a problem was when one of the laptops had to be reformatted.  Other then that it been running great for over a year now.
Lee
Sunday, January 29, 2006
 
 
It is really hard to say without a LOT more information.

How often is data changed? What type of database is it? Do these laptops have access to a centralized server? Does a change on one laptop need to be sent to all other laptops? Does all data need to be replicated or just a subset? Where are these laptops, who controls them? Are the people using the laptops techinical at all or will they need support if replication dies? Are there any other business processes that need access to this information as well? Are there any plans to add a significant number of additional laptops? Can the laptop users come in a sync up to a server by connection locally or does this replication have to work over the Internet? How does the application currently handle concurrency? How often does the database schema change? How do you send database schema changes to the laptops? What is the impact on the business if some of the data isn't replicated correctly or is lost?

Without the answers to these questions (and possibly many more) anything we say would be a completely uneducated guess.
Turtle Rustler
Sunday, January 29, 2006
 
 
I have a - thoroughly untested - idea to offer.  I've been looking a little bit at syncing between relational databases, and I've come across Sync4J.  It's an open source implementation of SyncML, which is apparently an industry standard XML language for syncing PDAs & such.  Sync4J apparently uses SyncML and a Java application server, but if it lives up to its claims, then it wraps the SyncML fully and contains a build that includes all the server stuff you need.

Most importantly, it can sync arbitrary relational data through JDBC connections, given the right metadata & mapping.  It's a client-server setup, not peer-to-peer, but it may be of use to you.

No idea what language you're looking for.  Personally, I use .NET more than Java, but I've had good luck so far using IKVM to get other Java components working in C#.  It's kind of weird writing JDBC in C#, though.

I looked a little bit over the design and it does cover all the pitfalls I had already thought of.  But as I said, I haven't tested it.  But if anybody else out there knows more about it, I'd love to hear a review.
Chaz Haws Send private email
Sunday, January 29, 2006
 
 
That's interesting Chaz. It gives a third option that I hadn't really thought of before. So the options now become:

1) Use the DBMS replication system.
2) Use a third part data synchronization system (possibly open source).
3) Roll your own.
Turtle Rustler
Sunday, January 29, 2006
 
 
Exactly.  Which prompts a new thought:  at the very least looking over the design and features of the open source one would help one see what potential issues before rolling your own.
Chaz Haws Send private email
Sunday, January 29, 2006
 
 
I've been interested in SyncML for a while and know of Sync4J, but haven't had a chance to play with any of this yet.

I'd be interested to know how you go.
Neville Franks Send private email
Monday, January 30, 2006
 
 
If I read the OP correctly, you're trying to do a three-way peer-to-peer synch, not a publish-subscribe synch where there's a "home" or "master" db.  If this is the case, be sure that whatever synch mechanism you use can support this -- IME they want to have a "master" DB somewhere.

I'll also chime in and confirm that synching can be a major pain depending on your scenario.  I used to do mobile CRM deployments, and getting sych set up and running right was a major PITA, including ongoing monitoring and repairing when there were synch conflicts.

You may not run into problems if your use cases are simple or if the application itself is naturally inclined to reduce conflics, but keep an eye on this as you progress.

BTW, anyone seen MS's "sharing extensions" for RSS?
http://msdn.microsoft.com/xml/rss/sse/

If I'm reading this right, this is supposed to be able to do synch like this via RSS.  Not baked yet, but could be pretty cool.
D. Lambert Send private email
Monday, January 30, 2006
 
 
I'll check that out Chaz.... Turtle, sorry I was very generic and non-specific on purpose.  I was mainly curious if there is a general methodology to follow when synching between anything, say two Access databases or two CSV files, it could be anything.  I'm just not sure what to look for on Google.
Phil Send private email
Monday, January 30, 2006
 
 
In general, you're looking at the problem of conflict resolution in multi-master replicated databases.

Search for some of those words and you'll dind things like this:
 http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96567/repconflicts.htm#22336

In my experience, this stuff is impossible to do properly unless the app is specifically designed for it or you are free to modify it extensively.

Read some of the stuff out there before you decide if you really, really want to do this..
Abstract Typist
Monday, January 30, 2006
 
 
Thanks Abstract, that article looks like exactly the kind of thing I wanted.

"Read some of the stuff out there before you decide if you really, really want to do this.."

Unfortunately it's not a question of want, but need :\.
Phil Send private email
Monday, January 30, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz