Database Synchronization with Symmetric DS

Abstract

Commonly, OpenMRS is deployed and implemented in resource-poor settings where internet connectivity is not 100% reliable. At the same time, many implementations in these environments want a unified system in which each facility has a common view of all data. To address this, OpenMRS has invested a lot of time and effort into the development of a Synchronization module that performs bi-directional synchronization across all configured servers. Partners In Health supports implementations in both Rwanda and Haiti that use this synchronization module to support local servers that provide fast, reliable access to data across multiple facilities.

The paradigm employed by the current synchronization module is one of API-level synchronization. All changes made via the API and persisted to the database via hibernate are captured, logged, and transmitted to the other servers in the network through a central "parent" node. This approach, although relatively successful, has a number of drawbacks. One is that it puts the burden of maintaining database synchronization on the application. This opens up a number of failure points and has proven fairly maintenance intensive. Another is that it does not allow for any direct manipulation of the underlying database, limiting certain system integration options, and complicating support and maintenance.

The goal of this project is to develop an alternative synchronization paradigm using SymmetricDS. SymmetricDS, from it's website, describes itself as "an asynchronous data replication software package that supports multiple subscribers and bi-directional synchronization. It uses web and database technologies to replicate tables between relational databases, in near real time if desired." Phase 1 of this project would be to build the necessary tools and configuration to successfully synchronize a single database table across 2 OpenMRS instances. Phase 2 would be to support all tables (via web configuration) in the OpenMRS schema.

Objectives

1. Set up two duplicate OpenMRS instances and configure SymmetricDS to successfully synchronize the "person" table.
2. Support bi-directional sync that supports periods of downtime, and which implements simple conflict resolution:
   • Likely involves customization of SymmetricDS via creation of a custom IDataLoaderFilter.
   • Likely involves matching of uuids rather than primary key ids if uuids are available on the table. Otherwise a last-update-wins approach.
3. Develop a new OpenMRS module
   • Ideally "embeds" symmetricDS as a library rather than depending on an external setup
   • Facilitates configuration by automatically setting up synchronization for all tables in the OpenMRS schema
   • Provides a UI for changing / enabling / disabling synchronization on individual tables.

Resources

SymmetricDS
Sync Module
Symmetric Thread 1
Symmetric Thread 2

Progress

Steps Done upto now

1. Setup two OpenMRS instances

• Done. Completed as specified on Wiki - Multiple Instances with the renamed .wars of omrsroot and omrsclient running on 2 ports for jetty. Independent two databases and 'omrsroot' and 'omrsclient' for sync, and having individual application data directories.

2. Configure SymmetricDS for the two OpenMRS instances. Then Synchronize the Person table.

• Done. (minor issue reported)
• Configuring using properties files
  •  omrsclient.properties
  •  omrsroot.properties
• Inserting symmertic specific tables to root database using an .sql file.

  openmrs_sds_sql.sql

• • Triggers
  • Channels
  • Routers
  • Trigger-Router links
• Registering and starting nodes
• Send loads, push/(and) pull data.

Timeline

The testing and documenting for early stages will go inline with development.

UI Mockups Proposed

Linking page from Personal Space