Database Synchronization for Retail

Having just returned from the National Retail Foundation’s “Big Show” in New York City (where we also rooted on our hometown football team in the national championship game!), I thought it might be a good time to reflect on SymmetricDS’s roots in retail.

O-H from our #JumpMind booth 1225 at #nrf15 #SymmetricDS pic.twitter.com/vMf0o99EFF

— JumpMind Inc (@JumpMind) January 12, 2015

Back in 2007, SymmetricDS was born as an open source product that was architected to solve a problem that had not yet been solved.

A 1400+ store retailer was doing a point-of-sale implementation. Their stores had a very low latency network connection back to the corporate office. The new point-of-sale used a MySQL database that was hosted at an on-premise store server. The point-of-sale also had a local Apache Derby offline database.

The exact same schema existed in an Oracle RAC corporate data center deployment. Instead of taking the approach many other implementations had taken of writing custom integrations that delivered data to the store and inserted directly into the store database, we decided to leverage the fact that we had a centralized database.

We wanted a mechanism to replicate whatever changes were made to the corporate database out to the store databases and then on to the point-of-sale databases. With infrastructure in place to replicate out to thousands of heterogeneous databases, fixes to data would be simplistic. Update the corporate database and voila … data is fixed all the way out to the point-sale-sale devices.

Not only did we need to deliver items, prices and tax information to the store, but we also needed to trickle sales back. When the point-of-sale was customized or a new feature was added we didn’t want to have to change code or deploy configuration to add the new feature to the trickle back. New columns should just show up automatically. New tables should be able to be added by making a simple centralized configuration change.

The solution needed to be scaleable. The best scaleable architecture that we knew of was and still is a web application.

The solution needed to be platform and database agnostic. The best database support across all platforms we found was JDBC. Using Java also allowed us to be platform independent. We took inspiration from Hibernate and built the software to detect the database type and install a very thin pluggable dialect to account for database specific requirements.

The solution needed to be simple to configure and manage. We carefully thought out the configuration patterns. We wanted the majority of the configuration to be in the database so we could manage it centrally. We wanted to “eat our own dogfood” so we made the software responsible for synchronizing its own configuration. We also made sure the software was durable enough to sync between databases with slight schema differences.

Hence, SymmetricDS was born!

In the last 8 years SymmetricDS has grown up quite a bit. Although retail was the first vertical market SymmetricDS was used in, many more use cases across many markets have surfaced.

SymmetricDS is used on trucks and ships, and is used at doctors offices and law offices. It runs in the cloud and on Android devices.

Many new features have been added as well. We now support transformations, conflict resolution and even file synchronization. It is our belief that if you have one infrastructure to move data, then it is easier to support.

The pluggable database dialect and the developer friendly architecture has allowed us to quickly add support for around 20 databases at this point. If a database has the features that SymmetricDS requires, then we can typically add support for the database in less than a week.

In the last several years we have developed a professional version of the product that comes with a support subscription. The professional version gives the SymmetricDS user a nice web user interface that makes the software that much easier to configure and manage. The support subscription allows us to guarantee response times to help you out when you hit a tough situation.

With an average of several hundred downloads a week of the open source SymmetricDS engine and a continuously growing customer base we are looking forward to tackling all new SymmetricDS challenges in 2015!