The Blog Of Nathan McMinn
Written by Nathan McMinn
Thursday, 24 June 00:00
Some problems are hard. Really hard. So hard that the slightest thought of wrestling with said problem causes cold sweats, sleepless nights and panic attacks. Data synchronization, for me, is one of those problems. Just to be crystal clear, I'm not talking about configuring MySQL / Oracle replication, or a simple one-way sync of some data for use offline. I'm talking about true multi-master replication with potentially hundreds of systems. These remote databases are not always connected to the home office, will be edited independently, and need to be able to synchronize all of their changes with a central database while at the same time receiving changes submitted by other remote systems.
Recently I was faced with just such a problem. Hundreds of instances of a client application in the field, and a need to synchronize all of their databases with a central store when they could manage an internet connection. I looked at writing a solution from scratch, but thankfully found Pervasync before it came to that. Pervasync is a tiny Java client/server application that synchronizes changes between two Oracle or MySQL databases. It consists of two parts. The first is a Java web application that serves two functions: handling synchronization requests and providing a management interface. The second part is a synchronization client, offered as both a standalone client application and an embeddable agent class.
The server side is a bit unorthodox. Unlike many web applications which come packaged as a .war file for deployment, Pervasync installs by default into its own directory. The product comes packaged with a configuration tool that will create a config file and drop it into TOMCAT/conf/Catalina/localhost. This context file has a docBase element which points to the web application root, which is located in the Pervasync installation directory. I'm not going to go through all the configuration details here, since the Pervasync documentation does a much better job than I probably would. What I am going to cover here are a few ways to tweak the product to serve a specific need. One of my requirements revolved around LDAP. I didn't want to manage my user passwords through Pervasync's UI when I have a perfectly good directory! Pervasync does not support LDAP out of the box (would be a welcome addition in a future release though, hint hint), but they do provide a UserManager interface that you can use to implement your own user management class. Again, see the docs for details.
Pervasync also has a client API that you can use to integrate the synchronization client into your own applications. This client can be packaged as a .jar file and deployed with your application. Two classes are provided for working with Pervasync. The first, SyncClientAdmin, allows the developer to interact with the Pervasync client administrative functions to perform tasks such as configuring user sync accounts. The second, SyncAgent, provides an API for initiating a refresh-only or two-way synchronization. Although no API is provided at this time for conflict resolution, Pervasync supports three modes of resolution on the server side: server wins, client wins, or raise an error. In our case we raise an error for the rare event where a conflict occurs and resolve it by hand. Hopefully in the future Pervasync will add a full featured conflict resolution API.
Last Updated on Thursday, 24 June 09:54
#5 04-13 01:35
I did look at both Oracle and Sybase. Pervasync was less expensive, easier to implement and embed (in my specific case) and solved my particular problem quickly.
Almost a year later and I am convinced we made the right decision. Pervasync is still running strong, even as we have more than doubled the number of remote clients.
#4 02-03 13:55
If you're looking for a full featured conflict resolution API today, you should really check out Oracle Database Lite.
The Mobile Server component features a highly configurable sync engine, including support for custom conflict resolution. It also has allows you to deploy and control you apps from a centralized management console, and even supports device management.
Be mindful however, that it only works with Oracle Database on the server side. But if you’re already using Oracle for your Database, you really should give Oracle Database Lite a look. On the client side, it now supports SQLite databases natively, so you can use the built-in Blackberry database. Android is supported as well.
You can read more here:
You can download it directly from the website. Just click on the download tab to try it out.
Oracle Product Manager
#3 01-20 13:29
Just curious, but did you consider Sybase SQL Anywhere and MobiLink. MobiLink is the sync server and handles concurrency, conflict detection/resolution among other things. It can sync data to SQL Server, Oracle, MySQL, Sybase, DB2.
Or you just didn't know about Sybase's data sync?
Bets of luck in your project!
#2 01-19 12:02
Pervasync has continued to perform well for us. We have a few hundred clients in the field and the replication system has performed admirably. Note that it isn't quite as simple as "install and go" though, unless your database is REALLY simple.
Pervasync does a good job at detecting changes to a database, replicating those changes and detecting conflicting changes. It is up to you to make sure that the data is replicable. This means avoiding key collisions in your client databases (we use a per-client key-prefixing scheme), avoiding constraint violations (such as when one client enters a record that refers to an entity that another client is trying to delete), etc. Basically all of the usual challenges with a distributed database.
One thing to keep in mind is that Pervasync relies on triggers. MySQL has a limit of one trigger per table/action. If you need triggers for your app, that could be a problem.
#1 01-11 17:06
Thanks for the article on Pervasync. I wanted to follow up with you on what your thoughts are today now that you are 6 months down the road.
Our company is looking to solve a similar data synch problem - 1 home office database, offline desktop apps for field employees, and we need a way to synchronize changes across all databases.
We are in the design phase of a new product and haven't made a final decision on database, but have narrowed it down to mySQL and SQL Server 2008 and it is coming down to the ease in which this synchronization can be implemented.
SQL Server 2008 has more robust out-of-box replication/synchronization features than mySQL for our use cases (particularly the multi-master use case) until we stumbled upon Pervasync.
Now we are curious how well it performs over time. Sounds like a great solution, but almost sounds too good to be true.
Appreciate the feedback!