Bi-directional data and file synchronization with SymmetricDS

Background

For developing countries like Nigeria, internet stability and slow internet speed or non availability/congestion of internet services have not been encouraging, frustrating real time online transactions. As at the time of writing this tutorial, remote communities and villages have suffered restricted access to online resources and data. Local stores, marts, POS want to monitor sales from different outlets. Files and data are most times sent over the internet as attachments on emails.

There is good news here. The herald of symmetricds synchronization helps resolves problems of internet connectivity. A well setup symmetricds synchronization will not only reduce dependencies on internet availability but also reduce cost yet achieving real time management and monitoring transactional activities. N-number of activity nodes can be synchronized and tasks can be performed on either sides.

SymmetricDS is open source software for both file and database synchronization with support for multi-master replication, filtered synchronization, and transformation across the network in a heterogeneous environment. It supports multiple subscribers with one direction or bi-directional, asynchronous data replication. It uses web and database technologies to replicate data as a scheduled or near real-time operation. The software was designed to scale for a large number of nodes, work across low-bandwidth connections, and withstand periods of network outage. It works with most operating systems, file systems, and databases, including Oracle, MySQL, MariaDB, PostgreSQL, MS SQL Server (including Azure), IBM DB2, H2, HSQLDB, Derby, Firebird, Interbase, Informix, Greenplum, SQLite (including Android), Sybase ASE, and Sybase ASA (SQL Anywhere) databases.

See more from
here
or
features

Introduction

I was tasked to implement an offline or intranet solution model embedded in a browser based Community Health Insurance Enrolment and Authentication System CHIEASY chieasy. Software captures digital images/pictures of the clients on the OS. 

CHIEASY is developed by Management Sciences for Health  - MSH supported by SPDC.
CHIEASY is a simple (easy to use), enterprise and scalable web based health insurance enrolment and authentication software.
CHIEASY a JEE application runs on Apache Tomcat, MySQL.

Case:

N-th number of data capture points synchronizes into the hospital server via the hospital LAN which in turn synchronizes into a satellite (cloud) server. The points of data capture can also alternatively and directly synchronize data to the satellites store. Users with sufficient internet access can capture client information using the satellite server which in turns replicate the local nodes.

Steps:

The steps here describes symmetricds as a web application. It will generate a war file and deployed on Apache tomcat
1. Download the latest version or source code and build from download
    unzip the file into <installation_dir>
    or
    source code using bazaar or svn (to use svn download tortoise svn from tortoisesvn
    open command and issue or use the GUI of the tortoise
    svn co https://svn.code.sf.net/p/symmetricds/code/trunk symmetricds
  
   

2.  Create property files
     Use a notepad to create two (2) properties files. One for the satellite and the other for the hospital server
     Or you can modify the sample configuration properties files from the <installation_dir>/samples/corp-000

     See

*****hospital server properties file*****
 engine.name=intra
# The class name for the JDBC Driver
db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver

# The JDBC URL used to connect to the database
db.url=jdbc:mysql://localhost:3309/dbname?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:corp
# The user to login as who can create and update tables
db.user=dbuser

# The password for the user to login as
db.password=dbpassword

registration.url=http://localhost/sym/sync/intra
sync.url=http://localhost/sym/sync/intra
auto.registration=true

# Do not change these for running the demo
group.id=intra
external.id=000

# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000


*****satellite server properties file*****
 engine.name=cloud
# The class name for the JDBC Driver
db.driver=com.mysql.jdbc.Driver
#db.driver=oracle.jdbc.driver.OracleDriver

# The JDBC URL used to connect to the database
db.url=jdbc:mysql://public_ip:3309/dbname?tinyInt1isBit=false
#db.url=jdbc:oracle:thin:@127.0.0.1:1521:corp
# The user to login as who can create and update tables
db.user=dbuser

# The password for the user to login as
db.password=dbpassword

registration.url=http://localhost/sym/sync/intra
sync.url=http://localhost/sym/sync/intra
auto.registration=true

# Do not change these for running the demo
group.id=cloud
external.id=000

# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000

*********************end******************************************************

3   Copy and put the files into engine folder of the <installation_dir>

4. Generate symmetric tables from command line into the hospital server
     cd <installation_dir>/bin>  symadmin --engine intra create-sym-tables
5. Populate the symmetric tables using 
     <installation_dir>/bin> dbimport intra_cloud_mysql.sql



*****************intra_cloud_mysql.sql***********************************************
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;

insert into sym_node_group (node_group_id, description)
values ('intra', 'Obio CH');
insert into sym_node_group (node_group_id, description)
values ('cloud', 'Cloud');


insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('cloud', 'intra', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('intra', 'cloud', 'W');

insert into sym_node (node_id, node_group_id, external_id, sync_enabled)
values ('000', 'intra', '000', 1);

insert into sym_node_security (node_id,node_password,registration_enabled,registration_time,initial_load_enabled,initial_load_time,initial_load_id,initial_load_create_by,rev_initial_load_enabled,rev_initial_load_time,rev_initial_load_id,rev_initial_load_create_by,created_at_node_id)
values ('000','changeme',0,current_timestamp,0,current_timestamp,null,null,0,null,null,null,'000');
insert into sym_node_identity values ('000');

--
-- Channels
--
---insert into sym_channel
---(channel_id, processing_order, max_batch_size, enabled, description)
---values('sale_transaction', 1, 100000, 1, 'sale_transactional data from register and back office');

insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('patient_enrolment', 1, 100000, 1, 'patient enrolment');

insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('trx', 1, 100000, 1, 'patient enrolment trx');

insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('payment_history', 1, 100000, 1, 'payment history');

insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('family_members', 1, 100000, 1, 'family members');

--
-- Triggers
--
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('patient_enrolment','patient_enrolment','patient_enrolment',current_timestamp,current_timestamp);

insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('trx','patient_enrolment_trx','trx',current_timestamp,current_timestamp);

insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('payment_history','payment_history','payment_history',current_timestamp,current_timestamp);

insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('family_members','family_members','family_members',current_timestamp,current_timestamp);

-- Example of a `dead` trigger, which is used to only sync the table during initial load
---insert into sym_trigger
---(trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete, last_update_time,create_time)
--values('sale_transaction_dead','sale_transaction','sale_transaction',0,0,0,current_timestamp,current_timestamp);

--
-- Routers
--

-- In this example, two routers pass everything all the time, and a third router
-- passes information to the specific store mentioned in the column 'store'

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('intra_2_cloud', 'intra', 'cloud', 'default',current_timestamp, current_timestamp);

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('cloud_2_intra', 'cloud', 'intra', 'default',current_timestamp, current_timestamp);

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,router_type,router_expression,create_time,last_update_time)
values('intra_2_one_cloud', 'intra', 'cloud', 'column','STORE_ID=:EXTERNAL_ID or OLD_STORE_ID=:EXTERNAL_ID',current_timestamp, current_timestamp);

--
-- Trigger Router Links
--


insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('patient_enrolment','intra_2_cloud', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trx','intra_2_cloud', 100, current_timestamp, current_timestamp);


insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('payment_history','intra_2_cloud', 100, current_timestamp, current_timestamp);


insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('family_members','intra_2_cloud', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('patient_enrolment','cloud_2_intra', 100, current_timestamp, current_timestamp);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trx','cloud_2_intra', 100, current_timestamp, current_timestamp);


insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('payment_history','cloud_2_intra', 100, current_timestamp, current_timestamp);


insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('family_members','cloud_2_intra', 100, current_timestamp, current_timestamp);

***************************end********************************************

6. Generate and create war file
     <installation_dir>/bin> -p intra.properties create-war C:/sym.war

7. Deploy the war to Apache tomcat

8. Edit the web.xml of the sym and uncomment the multiServer. Then reload the sym application on tomcat. This will create an engine folder outside the webapps directory.

9. Copy and paste the two (2) properties files created earlier into the engines folder.

10. Restart tomcat or the sym application.

Note: The mysql jdbc driver are usually not deployed in the lib when generating the sym.war
          Also the log4j. Just copy the two jar files into the lib of sym application and restart tomcat.


 After then, everything should be fine.