SymmetricDS User Guide

SymmetricDS includes features that solve a particular set of synchronization problems.

SymmetricDS is written in Java 5 and requires a Java SE Runtime Environment (JRE) or Java SE Development Kit (JDK) version 5.0 or above.

Any database with trigger technology and a JDBC driver has the potential to run SymmetricDS. The database is abstracted through a Database Dialect in order to support specific features of each database. The following Database Dialects have been included with release 1.0:

While implementing a commercial Point of Sale (POS) system for a large retailer, the development team concluded that the software available for trickling back transactions to the general office did not meet the project needs. The list of problems in the requirements made finding the ideal solution difficult:

The team created a custom solution that met the requirements and made the project successful. From this initial challenge came the knowledge and experience that SymmetricDS benefits from today.

The software is released with a version number based on the Apache Portable Runtime Project version guidelines. In summary, the version is denoted as three integers in the format of MAJOR.MINOR.PATCH. Major versions are incompatible at the API level, and they can include any kind of change. Minor versions are compatible with older versions at the API and binary level, and they can intoduce new functions or remove old ones. Patch versions are perfectly compatible, and they are released to fix defects.

Install the SymmetricDS software and configure it with your database connection information.

Create the sample tables in the root node database, load the sample data, and load the sample configuration.

Create the sample tables in the client node database to prepare it for receiving data.

Verify both databases by logging in and listing the tables.

Start the SymmetricDS nodes and observe the logging output.

my.url=http://localhost:8080/sync

Open registration for the client node using the root node administration feature.

Send an initial load of data to the client node using the root node administration feature.

Modify data in the root database. The changes are propagated to the client database during pull synchronization.

Modify data in the client database. The changes are propagated to the root database during push synchronization.

A batch is used for tracking and sending data changes to nodes. The sending node creates a batch and the receiving node acknowledges it. A batch in error is retried during synchronization attempts, but only after data changes in other channels are allowed to be sent. Channels are categories assigned to tables for the purpose of independent synchronization and control. Batches for a channel are not created when a batch in the channel is in error status.

The receiving node keeps track of the batches it acknowledges and records statistics about loading the data. Duplicate batches are skipped by default, but this behavior can be changed with the incoming.batches.skip.duplicates runtime property.

The configuration is entered by the user into the data model to control the behavior of what data is sychronized to which nodes. To ease management of several databases, nodes are put into groups, and groups are linked together for synchronization. A trigger captures data for a table, which can include conditions and criteria for subsets. Triggers are grouped into channels for prioritization and control of data flow.

Figure 3.1. Configuration Data Model

At runtime, the configuration is used to capture data changes and route them to nodes. The data changes are placed together in a single unit called a batch that can be loaded by another node. Outgoing batches are delivered to nodes and acknowledged. Incoming batches are received and loaded. History is recorded for batch status changes and statistics.

Figure 3.2. Runtime Data Model

The following deployment options are possible:

In each deployment, you configure which services are available. The possible services to map for deployment include PushServlet, PullServlet, AckServlet, and RegistrationServlet. If synchronization is configured for "pull" action, the PullServlet and AckServlet must be mapped. If using "push" action, the PushServlet must be mapped. If the instance is used as the registration server, the RegistrationServlet must mapped.

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"
	version="2.5">

	<display-name>sync</display-name>

	<context-param>
		<param-name>contextConfigLocation</param-name>
		<!-- Optionally specify other spring xml files that are loaded in the same context -->
		<param-value>classpath:additional-spring.xml</param-value>
	</context-param>

	<filter>
		<filter-name>SymmetricFilter</filter-name>
		<filter-class>
			org.jumpmind.symmetric.web.SymmetricFilter
		</filter-class>
	</filter>

	<filter-mapping>
		<filter-name>SymmetricFilter</filter-name>
		<servlet-name>/*</servlet-name>
	</filter-mapping>
	
	<listener>
		<listener-class>
			org.jumpmind.symmetric.SymmetricEngineContextLoaderListener
		</listener-class>
	</listener>
	
	<servlet>
		<servlet-name>SymmetricServlet</servlet-name>
		<servlet-class>
			org.jumpmind.symmetric.web.SymmetricServlet
		</servlet-class>
		<load-on-startup>1</load-on-startup>
	</servlet>

	<servlet-mapping>
		<servlet-name>SymmetricServlet</servlet-name>
		<url-pattern>/*</url-pattern>
	</servlet-mapping>

</web-app>

/symmetric/bin/sym --properties root.properties --port 8080 --server

import org.jumpmind.symmetric.SymmetricEngine;

public class StartSymmetricDSEngine {

    public static void main(String[] args) throws Exception {
        String workingDirectory = System.getProperty("user.dir");

        SymmetricEngine engine = new SymmetricEngine("classpath://my-application.properties", "file://"
                + workingDirectory + "/my-environment.properties");

        // this will create the database, sync triggers, start jobs running
        engine.start();
        
        ....
        
        // this will stop the engine
        engine.stop();
    }   

}

As of 1.4 most system parameters are accessed using the ParameterService. The ParameterService looks for parameters in a hierarchy of properties files and in a database table. Parameters are re-queried from their source at a configured interval and can also be refreshed on demand by using the JMX API. The following table defines the way parameters may be defined.

Also see the appendix on Startup Parameters to see all the possible properties and their defaults.

Each Node requires properties that will connect it to the database and register it with a parent Node. To give a Node its identity, the following properties are used:

When a new Node is first started, it is has no information about synchronizing. It contacts the registration server in order to join the network and receive its configuration. The configuration for all Nodes is stored on the registration server, and the URL must be specified in the following property:

For a deployment to an application server, it is common for database connection pools to be found in the Java naming directory (JNDI). In this case, set the following property:

For a deployment where the database connection pool should be created using a JDBC driver, set the following properties:

Each Node must belong to a Node Group, a collection of one or more Nodes. A common use of Node Groups is to describe a level in a hierarchy of data synchronization. For example, at a retail store chain, there might be a few Nodes that belong to "corp", which sync with hundreds of Nodes that belong to "store", which sync with thousands of Nodes that belong to "register".

The following SQL statements would create Node Groups for "corp" and "store".

insert into SYM_NODE_GROUP 
  (node_group_id, description)
values
  ('store', 'A retail store node');

insert into SYM_NODE_GROUP 
  (node_group_id, description)
values
  ('corp', 'A corporate node');

To establish synchronization between Nodes, two Node Groups are linked together. The direction of synchronization is determined by specifying a source and target Node Group. If synchronization should occur in both directions, then two links are created in opposite directions. The target Node Group receives data changes by either push or pull methods. A push method causes the source Node Group to connect to the target, while a pull method causes it to wait for the target to connect to it.

The following SQL statements links the "corp" and "store" Node Groups for synchronization. It configures the "store" Nodes to push their data changes to the "corp" Nodes, and the "corp" Nodes to send changes to "store" Nodes by waiting for a pull.

insert into SYM_NODE_GROUP_LINK
  (source_node_group, target_node_group, data_event_action)
values
  ('store', 'corp', 'P');

insert into SYM_NODE_GROUP_LINK
  (source_node_group, target_node_group, data_event_action)
values
  ('corp', 'store', 'W');

Each instance of SymmetricDS is a Node that can be uniquely identified. The Node has a unique identifier used by the system, and the user provides an external identifier for context in the local system. For most common use, the two identifiers are the same. The registration process generates and sends the identity and password to the Node, along with its synchronization configuration. The top-level registration server must have its identity provided by the user since it has no parent to contact.

The following SQL statements setup a top-level registration server as a Node identified as "00000" in the "corp" Node Group.

insert into SYM_NODE 
  (node_id, node_group_id, external_id, sync_enabled, symmetric_version)
values
  ('00000', 'corp', '00000', 1, '1.4.0');

insert into SYM_NODE_IDENTITY values ('00000');

Data changes in the database are captured in the order that they occur, which is preserved when synchronizing to other Nodes. Some data may need priority for synchronization despite the normal order of events. Channels provide a higher-level processing order of data, a limit on the amount of data, and isolation from errors in other channels. By categorizing data into channels and assigning them to Triggers, the user gains more control and visibility into the flow of data.

The following SQL statements setup channels for a retail store. An "item" channel includes data for items and their prices, while a "sale_transaction" channel includes data for ringing sales at a register.

insert into SYM_CHANNEL 
  (channel_id, processing_order, max_batch_size, enabled, description)
values
  ('item', 10, 100000, 1, 'Item and pricing data');

insert into SYM_CHANNEL 
  (channel_id, processing_order, max_batch_size, enabled, description)
values
  ('sale_transaction', 1, 100000, 1, 'retail sale transactions from register');

At the heart of SymmetricDS are Triggers that define what data to capture. Nodes in the source Node Group will capture changes for a table and send them to a target Node Group. Changes can include inserts, updates, or deletes to the table, and it is even possible to filter data by a conditional expression. An entry in Trigger results in a database trigger being installed on the table. Whenever the Trigger entry is updated, the last_updated_time should be updated to indicate that the database trigger should also be updated.

The following SQL statement defines a Trigger that will capture data for a table named "item" whenever data is inserted, updated, or deleted. Data will be captured on Nodes in the "corp" Node Group and sent to Nodes in the "store" Node Group.

insert into SYM_TRIGGER 
  (source_table_name, source_node_group_id, target_node_group_id, channel_id, 
   sync_on_insert, sync_on_update, sync_on_delete, 
   initial_load_order, last_updated_by, last_updated_time, create_time)
values
  ('item', 'corp', 'store', 'item', 
   1, 1, 1, 
   105, 'demo', current_timestamp, current_timestamp);

There are variables you can use when specifying the initial_load_select SQL for a Trigger that will be replaced at runtime:

Normally a Trigger is specified to capture data changes to a table and send them to a target Node Group. A dead Trigger is one that does not capture data changes. In other words, the sync_on_insert, sync_on_update, and sync_on_delete properties for the Trigger are all set to false. Because the Trigger is specified, it will be included in the initial load of data for target Nodes.

A dead Trigger might be used to load a read-only lookup table. It could be used to load a table that needs populated with example or default data. Another use is a recovery load of data for tables that have a single direction of synchronization. For example, a retail store records sales transaction that synchronize in one direction by trickling back to the central office. If the retail store needs to recover all the sales transactions, they can be sent are part of an initial load from the central office by setting up dead Triggers that "sync" in that direction.

The following SQL statement sets up a non-syncing dead Trigger that sends the sale_transaction table to the "store" Node Group from the "corp" Node Group during an initial load.

insert into SYM_TRIGGER 
  (source_table_name, source_node_group_id, target_node_group_id, channel_id, 
   sync_on_insert, sync_on_update, sync_on_delete, 
   initial_load_order, last_updated_by, last_updated_time, create_time)
values
  ('sale_transaction', 'corp', 'store', 'sale_transaction', 
   0, 0, 0, 
   105, 'demo', current_timestamp, current_timestamp);

SymmetricDS may be extended via a plug-in like architecture where extension point interfaces may be implemented by a custom class and registered with the synchronization engine. All supported extension points extend the IExtensionPoint interface. The currently available extension points are documented in the following sections.

When the synchronization engine starts up, a Spring post processor searches the Spring ApplicationContext for any registered classes which implement IExtensionPoint. An IExtensionPoint designates whether it should be auto registered or not. If the extension point is to be auto registered then the post processor registers the known interface with the appropriate service.

The INodeGroupExtensionPoint interface may be optionally implemented to designate that auto registered extension points should only be auto registered with specific node groups.

/**
 * Only apply this extension point to the 'root' node group.
 */
 public String[] getNodeGroupIdsToApplyTo() {
     return new String[] { "root" };
 }

SymmetricDS will look for Spring configured extensions in the application Classpath by importing any Spring XML configuration files found matching the following pattern: META-INF/services/symmetric-*-ext.xml.

public class MyParameterFilter 
    implements IParameterFilter, INodeGroupExtensionPoint {

    /**
     * Only apply this filter to stores
     */
    public String[] getNodeGroupIdsToApplyTo() {
        return new String[] { "store" };
    }

    public String filterParameter(String key, String value) {
        // look up a store number from an already existing properties file.
        if (key.equals(ParameterConstants.EXTERNAL_ID)) {
            return StoreProperties.getStoreProperties().
              getProperty(StoreProperties.STORE_NUMBER);
        } 
        return value;
    }

    public boolean isAutoRegister() {
        return true;
    }

}