Multimaster replication with Symmetric DS

Symmetric DS is an awesome tool for trigger based replication whcih works for all major database vendors, including but not limited to PostgreSQL, MySQL, MSSQL, Oracle and many others. Symmetric-DS is a java application and can execute on any platform on whcih JRE is available including Windows and Linux.

Trigger based replication, in constrast to disk based (eg. DRBD) or transaction log file shipping based or statement based, works by registering triggers on DMLs and sending the data thus generated to remote machines. Another very popular trigger based DB replication tool is Slony.
Symmetric-DS in addition to being database agnostic also supports multi-master replication (MMR). MMR usecase involves multiple database nodes, connected in a pool with DML updates coming from any of them. This is different from the normal master-slave replication, where slaves are not expected to generate any data events, and the sole authority of database is the master. MMR requirement causes data conflict issues, and Symmetric-DS provides various strategies to resolve them like source-win, target-win and manual.


The synchronization participants have one special registration node, which is responsible only for storing the configuration for the entire collection. It is required only while start-up of the sychronization cluster, so that every node can get it's configuration. In this set-up, the node named corp0-000 is the main node.

Symmetric-DS synchronizes among "groups". Groups are collection of nodes with same synchronization properties like source table, transformations to data and other attributes that specify a synchronization. For implementing an MMR, each participant should be assigned a different group. In this sample experiment we'll be having MMR among 3 nodes with groups assigned corp0, corp1 and corp2. Nodes in a group are identified with external ids. Together with groups, external ids form unique identifiers for every node. The three nodes are uniquely identified as, corp0-000, corp1-001 and corp2-002 respectively.

Other important configuration parameters are,

1. node-security: which describes registration parameters.
2. node: stores identity information of the ndoes.
3. channel: describes one logical flow of data synchrnonization which is elaborated in subsequent tables.
4. router: describes the data sources, data endpoints, conditions for synching and data transformations (if any).
5. trigger: describes when to initiate the data chance trigger, on which source table and on what kind of DML (insert, update or delete).
6. trigger-router: links triggers and router to describe a data synchronization flow.

Symmetric DS requires 2 sets of configurations,

1. The per node properties file which specifies the identity of the node. This is named as -.properties. 
2. The sychronization configuration at registration ndoe, from where other nodes get their specific configuration post successful registration. This is written in database via an sql script.

The following is the per node properties file for corp0-000. The database associated with this node is btest0. Note that registration.url and sync.url are exactly the same to specify that it's a master/registation node.
 

The following is the per node properties file for corp1-001. The database associated with this node is btest1. Node that the registration.url points to corp0-000 while sync.url is it's own url.
 

The configuration for the third node corp2-002 is similar to the one above, corp1-001 replacing relevant paramters like group-id, external-id and database name etc.

The following is the Symmetric-DS configuration as an sql file, which has to be executed on the registration node (corp0-000, in this case).
 

You might want to follow the quick start guide available at Symmetric-DS website before attempting this configuration.