SymmetricDS 3.13 User Guide

SymmetricDS is written in Java and requires a Java Runtime Environment (JRE) Standard Edition (SE) or Java Development Kit (JDK) Standard Edition (SE) version 8.0 or above. Most major operating systems and databases are supported. See the list of supported databases in the Database Compatibility section. The minimum operating system requirements are:

The memory, disk, and CPU requirements increase with the number of connected clients and the amount of data being synchronized. The best way to size a server is to simulate synchronization in a lower environment and benchmark data loading. However, a rule of thumb for servers is one server-class CPU with 2 GB of memory for every 500 MB/hour of data transfer and 350 clients. Multiple servers can be used as a cluster behind a load balancer to achieve better performance and availability.

A node is responsible for synchronizing the data from a database or file system with other nodes in the network using HTTP. Nodes are assigned to one of the node Groups that are configured together as a unit. The node groups are linked together with Group Links to define either a push or pull communication. A pull causes one node to connect with other nodes and request changes that are waiting, while a push causes one node to connect with other nodes when it has changes to send.

Each node is connected to a database with a Java Database Connectivity (JDBC) driver using a connection URL, username, and password. While nodes can be separated across wide area networks, the database a node is connected to should be located nearby on a local area network for the best performance. Using its database connection, a node creates tables as a Data Model for configuration settings and runtime operations. The user populates configuration tables to define the synchronization and the runtime tables capture changes and track activity. The tables to sync can be located in any Catalog and Schema that are accessible from the connection, while the files to sync can be located in any directory that is accessible on the local server.

At startup, SymmetricDS looks for Node Properties Files and starts a node for each file it finds, which allows multiple nodes to run in the same instance and share resources. The property file for a node contains its external ID, node group, registration server URL, and database connection information. The external ID is the name for a node used to identify it from other nodes. One node is configured as the registration server where the master configuration is stored. When a node is started for the first time, it contacts the registration server using a registration process that sends its external ID and node group. In response, the node receives its configuration and a node password that must be sent as authentication during synchronization with other nodes.

Each subsystem in the node is responsible for part of the data movement and is controlled through configuration. Data flows through the system in the following steps:

SymmetricDS offers a rich set of features with flexible configuration for large scale deployment in a mixed environment with multiple systems.

SymmetricDS is a feature-rich data synchronization solution that focuses on ease of use, openness, and flexibility. The software encourages interoperability and accessibility for users and developers with the availability of source code, an application programming interface (API), and a data model supported by documentation. Configuration includes a powerful set of options to define node topology, communication direction, transformation of data, and integration with external systems. Through scripts and Java code, the user can also extend functionality with custom behavior. With a central database for setup and runtime information, the user has one place to configure, manage, and troubleshoot synchronization, with changes taking immediate effect across the network.

The trigger-based data capture system is easy to understand and widely supported by database systems. Table synchronization can be setup by users and application developers without requiring a database administrator to modify the server. Triggers are database objects written in a procedural language, so they are open for examination, and include flexible configuration options for conditions and customization. Some overhead is associated with triggers, but they perform well for applications of online transaction processing, and their benefits of flexibility and maintenance outweigh the cost for most scenarios.

Using an architecture based on web server technology, many simultaneous requests can be handled at a central server, with proven deployments in production supporting more than ten thousand client nodes. Large networks of nodes can be grouped into tiers for more control and efficiency, with each group synchronizing data to the next tier. Data loading is durable and reliable by tracking batches in transactions and retrying of faults for automatic recovery, making it a low maintenance system.

SymmetricDS is free software licensed under the GNU General Public License (GPL) version 3.0. See http://www.gnu.org/licenses/gpl.html for the full text of the license. This project includes software developed by JumpMind (http://www.jumpmind.com/) and a community of multiple contributors. SymmetricDS is licensed to JumpMind as the copyright holder under one or more Contributor License Agreements. SymmetricDS and the SymmetricDS logos are trademarks of JumpMind.

The SymmetricDS standalone ZIP file can be downloaded from Sourceforge. It is installed by unzipping to the installation location.

The sym command line utility starts a standalone instance of SymmetricDS using the built-in Jetty web server to handle requests. The web server can be configured by changing properties in the conf/symmetric-server.properties file.

The following example starts the server on the default port from the command line. SymmetricDS will automatically create a node for each Node Properties File configured in the engines directory.

To automatically start SymmetricDS when the machine boots, see Running as a Service.

SymmetricDS can be configured to start automatically when the system boots, running as a Windows service or Linux/Unix daemon. A wrapper process starts SymmetricDS and monitors it, so it can be restarted if it runs out of memory or exits unexpectedly. The wrapper writes standard output and standard error to the logs/wrapper.log file.

A single SymmetricDS node may be deployed across a series of servers to cooperate as a cluster. A node can be clustered to provide load balancing and high availability.

When using clustering, a hardware load balancer is typically used, but a software load balancer, such as a reverse proxy, can also be used.

For clustering SymmetricDS nodes running, the recommended approach is to configure the load balancer to use sticky sessions and ensure the staging directory for all nodes in the cluster are using a shared network drive. Sticky sessions are needed to support reservation requests, which allows for nodes to connect and obtain a reservation before connecting again and pushing their changes. The shared staging directory is needed to support extract in background of the initial load, which is extracted by one node, but served by different nodes in the cluster. If the start.initial.load.extract.job property is disabled, then shared staging is not required, but the performance of the initial load may be degraded.

Also, the sync.url (discussed in Node Properties File) SymmetricDS property should be set to the URL of the load balancer.

If the cluster will be running any of the SymmetricDS jobs, then the cluster.lock.enabled property should be set to true. By setting this property to true, SymmetricDS will use a row in the LOCK table as a semaphore to make sure that only one instance at a time runs a job. When a lock is acquired, a row is updated in the lock table with the time of the lock and the server id of the locking job. The lock time is set back to null when the job is finished running. Another instance of SymmetricDS cannot acquire a lock until the locking instance (according to the server id) releases the lock. If an instance is terminated while the lock is still held, an instance with the same server id is allowed to reacquire the lock. If the locking instance remains down, the lock can be broken after a period of time, specified by the cluster.lock.timeout.ms property, has expired. Note that if the job is still running and the lock expires, two jobs could be running at the same time which could cause database deadlocks.

By default, the locking server id is the hostname of the server. If two clustered instances are running on the same server, then the cluster.server.id property may be set to indicate the name that the instance should use for its server id.

When deploying SymmetricDS to an application server like Tomcat or JBoss, no special session clustering needs to be configured for the application server.

It is recommended that SymmetricDS is installed as a standalone service, however there are two other deployment options.

Each node that is deployed to a server is represented by a properties file that allows it to connect to a database and register with a parent node. Properties are configured in a file named xxxxx.properties. It is placed in the engines directory of the SymmetricDS install. The file is usually named according to the engine.name, but it is not a requirement.

To give a node its identity, the following properties are required. Any other properties found in conf/symmetric.properties can be overridden for a specific engine in an engine’s properties file. If the properties are changed in conf/symmetric.properties they will take effect across all engines deployed to the server.

See Startup Parameters, for additional parameters that can be specified in the engine properties file.

Section Add Node talks about creating additional nodes.

Load only nodes can be configured for nodes that are only designed to load data into the database. This prevents any triggers or runtime tables from being installed on this database but still allow data to be replicated to it.

In SymmetricDS, configuration rules are applied to groups of nodes versus individual nodes. A group is a categorization of nodes with similar synchronization needs. For example, in a synchronization scenario where a corporate office database is synchronized with field office databases, two node groups would be created, one for the corporate office database (Corporate), and one for the field office databases (Field_office). In the corporate group, there would be a single node and database. In the field_office group, there would be many nodes and databases, one for each field office. Configuration rules/elements are applied to the node group versus the individual nodes in order to simplify the configuration setup (no need to configure each individual field office node, just how the field office nodes sync with the corporate office node).

Group links define at a high level how data moves throughout your synchronization scenario. The group link defines which node groups will synchronize data to other node groups and within that exchange, which node group will initiate the conversation for that exchange.

Routers ride on top of group links. While a group link specifies that data should be moved from nodes in a source node group to nodes in a target node group, routers define more specifically which captured data from a source node should be sent to which specific nodes in a target node group, all within the context of the node group link.

Once group links and routers are defined, configuration must be completed to specify which data (tables, file systems, etc.) should be synchronized over those links and routers. The next step in defining which specific data in the database is moved is to define logical groupings for that data. Channels define those logical groupings. As an example, a set of tables that hold customer data might be logically grouped together in a Customer channel. Sales, returns, tenders, etc. (transaction data) might be logically grouped into a transaction channel. A default channel is automatically created that all tables will fall into unless other channels are created and specified. The default channel is called 'default'.

Channels can be disabled, suspended, or scheduled as needed.

SymmetricDS captures synchronization data using database triggers. SymmetricDS' Triggers are defined in the TRIGGER table. Each record is used by SymmetricDS when generating database triggers. Database triggers are only generated when a trigger is associated with a ROUTER whose source_node_group_id matches the node group id of the current node.

When determining whether a data change has occurred or not, by default the triggers will record a change even if the data was updated to the same value(s) they were originally. For example, a data change will be captured if an update of one column in a row updated the value to the same value it already was. There is a global property, trigger.update.capture.changed.data.only.enabled (false by default), that allows you to override this behavior. When set to true, SymmetricDS will only capture a change if the data has truly changed (i.e., when the new column data is not equal to the old column data).

The TRIGGER_ROUTER table is used to define which specific combinations of triggers and routers are needed for your configuration. The relationship between triggers and routers is many-to-many, so this table serves as the join table to define which combinations are valid, as well as to define settings available at the trigger-router level of granularity.

Three important controls can be configured for a specific Trigger / Router combination: Enabled, Initial Loads and Ping Back. The parameters for these can be found in the Trigger / Router mapping table, TRIGGER_ROUTER .

In addition to supporting database synchronization, SymmetricDS also supports File Synchronization. Similar to database synchronization which allows configuring [Table Triggers], SymmetricDS also supports setting up File Triggers. A file trigger is equivalent to specifying a directory structure or path that should be "watched" for files that need to be synchronized.

The FILE_TRIGGER_ROUTER table is used to define which specific combinations of file triggers and routers are needed for your configuration. The relationship between file triggers and routers is many-to-many, so this table serves as the join table to define which combinations are valid, as well as to define settings available at the trigger-router level of granularity.

Conflict detection is the act of determining if an insert, update or delete is in "conflict" due to the target data row not being consistent with the data at the source prior to the insert/update/delete. Without any overriding configuration, the system uses a detection of USE_CHANGED_DATA and a resolution of NEWER_WINS by default.

Conflict detection and resolution strategies are configured in the CONFLICT table. They are configured at minimum for a specific NODE_GROUP_LINK . The configuration can also be specific to a CHANNEL and/or table.

Conflict detection is configured in the detect_type and detect_expression columns of CONFLICT. The value for detect_expression depends on the detect_type.

Transforms allow you to manipulate data on a source node or on a target node, as the data is being loaded or extracted.

The source trigger creates the synchronization data, while the transformation configuration decides what to do with the synchronization data as it is either being extracted from the source or loaded into the target. You have the flexibility of defining different transformation behavior depending on whether the source change that triggered the synchronization was an Insert, Update, or Delete. In the case of Delete, you even have options on what exactly to do on the target side, be it a delete of a row, setting columns to specific values, or absolutely nothing at all.

SymmetricDS stores its transformation configuration in two configuration tables, TRANSFORM_TABLE and TRANSFORM_COLUMN. Defining a transformation involves configuration in both tables, with the first table defining which source and destination tables are involved, and the second defining the columns involved in the transformation and the behavior of the data for those columns. We will explain the various options available in both tables and the various pre-defined transformation types.

To define a transformation, you will first define the source table and target table that applies to a particular transformation. The source and target tables, along with a unique identifier (the transform_id column) are defined in TRANSFORM_TABLE . In addition, you will specify the source_node_group_id and target_node_group_id to which the transform will apply, along with whether the transform should occur on the Extract step or the Load step (transform_point).

Load Filters are a way to take a specific action when a row of data is loaded by SymmetricDS at a destination database node.

Extensions are custom code written to a plug-in interface, which allows them to run inside the engine and change its default behavior. Saving extension code in the configuration has the advantage of dynamically running without deployment or restarting. Configured extensions are available to other nodes and move between environments when configuration is exported and imported.

In SymmetricDS, jobs are tasks that are scheduled to run by a job manager. These jobs do the majority of synchronization work for SymmetricDS. In addition to the built in jobs, you can create custom jobs in Configuration to run for specified node groups.

This is an example BSH job:

This is the same job as a Java job. This might perform a little better, but you’ll need to have a JDK (not just JRE) available on your nodes for this to compile.

This is an example of a SQL job that updates the heartbeat_time of the current node. Available tokens are: :NODE_ID and :NODE_GROUP_ID.

Parameters can be used to help tune and configure your SymmetricDS configuration. Parameters can be set for an individual node or for all nodes in your network.

See Parameter List for a complete list of parameters.

A mail server can be configured for sending email notifications.

A monitor watches some part of the system for a problem, checking to see if the monitored value exceeds a threshold. (To be notified immediately of new monitor events, configure a notification.)

A notification sends a message to the user when a monitor event records a system problem. First configure a monitor to watch the system and record events with a specific severity level. Then, configure a notification to match the severity level and write to the log or send an email.

Most work done by SymmetricDS is initiated by jobs. Jobs are tasks that are started and scheduled by a job manager. Jobs are enabled by the start.<name>.job parameter.

Most jobs are enabled by default. The frequency at which a job runs is controlled by one of two parameters:

If a valid cron property exists in the configuration, then it will be used to schedule the job. Otherwise, the job manager will attempt to use the period.time.ms property.

The frequency of jobs can be configured in either the Node Properties File or in the PARAMETER table. When managed in PARAMETER table the frequency properties can be changed in the master node and when the updated settings sync to the other nodes in the system the job manager will restart the jobs at the new frequency settings.

SymmetricDS utilizes Spring’s CRON support, which includes seconds as the first parameter. This differs from the typical Unix-based implementation, where the first parameter is usually minutes. For example, */15 * * * * * means every 15 seconds, not every 15 minutes. See Spring’s documentation for more details.

SymmetricDS installs database triggers to capture changes in the DATA table. A record of the triggers that were installed and what columns are being captured is stored in the TRIGGER_HIST table. When data is captured in DATA it references the TRIGGER_HIST record that represented the trigger at the time data was captured.

This is necessary because if a trigger is rebuilt after columns are added or removed and data that was captured by the old trigger has not yet been delivered, we need a record of what columns were in play at the time the data had been captured.

The TRIGGER_HIST table records the reason a trigger was rebuilt. The following reasons are possible:

A configuration entry in Trigger without any history in Trigger Hist results in a new trigger being created (N). The Trigger Hist stores a hash of the underlying table, so any alteration to the table causes the trigger to be rebuilt (S). When the last_update_time is changed on the Trigger entry, the configuration change causes the trigger to be rebuilt ©. If an entry in Trigger Hist is missing the corresponding database trigger, the trigger is created (T).

Outgoing batches are delivered to the target node when the source node pushes or when the target node pulls.

A single push or pull connection is called a synchronization.

For one synchronization, each enabled channel will be processed. Channels are processed in the order defined by the Processing Order setting on the channel with two exceptions:

When outgoing batches are selected for a node and a channel, the maximum number of batches that are extracted per synchronization is controlled by the Max Batch To Send setting on the channel.

There is also a setting that controls the max number of bytes to send in one synchronization. If SymmetricDS has extracted more than the number of bytes configured by the transport.max.bytes.to.sync parameter, then it will finish extracting the current batch and then finish synchronization so the client has a chance to process and acknowledge the "big" batch. This may happen before the configured Max Batch To Send has been reached.

When extracting a batch, data is first extracted to the Staging Area and then sent across the network from the Staging Area. The staging area is used to minimize the amount of time a database connection is being used when streaming over slower networks. The use of the staging area can be turned off by setting the stream.to.file.enabled parameter.

Incoming batches are delivered to the target node when the source node pushes or when the target node pulls.

Incoming batches are written to the Staging Area first and then loaded. The use of the staging area can be turned off by setting the stream.to.file.enabled parameter.

SymmetricDS creates temporary extraction and data load files with the CSV payload of a synchronization when the value of the stream.to.file.threshold.bytes SymmetricDS property has been reached. Before reaching the threshold, files are streamed to/from memory. The default threshold value is 0 bytes. This feature may be turned off by setting the stream.to.file.enabled property to false.

SymmetricDS creates these temporary files in the directory specified by the java.io.tmpdir Java System property.

The location of the temporary directory may be changed by setting the Java System property passed into the Java program at startup. For example,

Both the Pull Job and the File Sync Pull Job can be configured to pull multiple nodes in parallel. In order to take advantage of this the pull.thread.per.server.count or file.pull.thread.per.server.count should be adjusted (from the default value of 1) to the number to the number of concurrent pulls you want to occur per period on each SymmetricDS instance.

Pull activity is recorded in the NODE_COMMUNICATION table. This table is also used as a semaphore to lock pull activity across multiple servers in a cluster.

Both the Push Job and the File Sync Push Job can be configured to push multiple nodes in parallel. In order to take advantage of this the push.thread.per.server.count or file.push.thread.per.server.count should be adjusted (from the default value of 1) to the number to the number of concurrent pushes you want to occur per period on each SymmetricDS instance.

Push activity is recorded in the NODE_COMMUNICATION table. This table is also used as a semaphore to lock push activity across multiple servers in a cluster.

When a Monitor is configured, it is run periodically to check the current value of a system metric and compare it to a threshold value. Different monitor types can check the CPU usage, disk usage, memory usage, batch errors, outstanding batches, unrouted data, and number of data gaps. Custom monitor types can be created using Extensions that use the IMonitorType interface. When the value returned from the check meets or exceeds the threshold value, a MONITOR_EVENT is recorded. The MONITOR_EVENT table is synchronized on the "monitor" channel, which allows a central server to see events from remote nodes, but this behavior can be disabled by setting the monitor.events.capture.enabled parameter to false.

To be immediately notified of a monitor event, use Notifications to match on the severity level. Different notification type can send a message by writing to the log or sending an email. Custom notification types can be created using Extensions that use the INotificationType interface. In order to send email, the Mail Server should be configured.

The standalone SymmetricDS installation uses Log4J for logging. The configuration file is conf/log4j.xml. The log4j.xml file has hints as to what logging can be enabled for useful, finer-grained logging.

There is a command line option to turn on preconfigured debugging levels. When the --debug option is used the conf/debug-log4j.xml is used instead of log4j.xml.

SymmetricDS proxies all of its logging through SLF4J. When deploying to an application server or if Log4J is not being leveraged, then the general rules for for SLF4J logging apply.

The db.user and db.password properties will accept encrypted text, which protects against casual observation. This encryption can be done two different ways using the command: encrypt-text

The first option is to pass the text as an argument:

The second option is without an argument. In this case, it will prompt you to enter text:

The text is encrypted using a secret key named "sym.secret" that is retrieved from a keystore file. By default, the keystore is located in security/keystore. The location and filename of the keystore can be overridden by setting the "sym.keystore.file" system property. If the secret key is not found, the system will generate and install a secret key for use with Triple DES cipher.

Generate a new secret key for encryption using the keytool command that comes with the JRE. If there is an existing key in the keystore, first remove it:

Then generate a secret key, specifying a cipher algorithm and key size. Commonly used algorithms that are supported include aes, blowfish, desede, and rc4.

If using an alternative provider, place the provider JAR file in the SymmetricDS lib folder. The provider class name should be installed in the JRE security properties or specified on the command line. To install in the JRE, edit the JRE lib/security/java.security file and set a security.provider.i property for the provider class name. Or, the provider can be specified on the command line instead. Both keytool and sym accept command line arguments for the provider class name. For example, using the Bouncy Castle provider, the command line options would look like:

To customize the encryption, write a Java class that implements the ISecurityService or extends the default SecurityService, and place the class on the classpath in either lib or web/WEB-INF/lib folders. Then, in the symmetric.properties specify your class name for the security service.

Remember to specify your properties file when encrypting passwords, so it will use your custom ISecurityService.

By specifying the "https" protocol for a URL, SymmetricDS will communicate over Secure Sockets Layer (SSL) for an encrypted transport. The following properties need to be set with "https" in the URL:

For incoming HTTPS connections, SymmetricDS depends on the webserver where it is deployed, so the webserver must be configured for HTTPS. As a standalone deployment, the "sym" launcher command provides options for enabling HTTPS support.

Monitoring and administrative operations can be performed using Java Management Extensions (JMX). SymmetricDS exposes JMX attributes and operations that can be accessed from the jmx command, Java’s jconsole, or a third party tool.

In order to use jconsole, you must enable JMX remote management in the JVM. You can edit the startup scripts to set the following system parameters.

More details about enabling JMX for JConsole can be found here.

Using the Java jconsole command, SymmetricDS is listed as a local process named SymmetricLauncher. In jconsole, SymmetricDS appears under the MBeans tab under the name defined by the engine.name property. The default value is SymmetricDS.

The management interfaces under SymmetricDS are organized as follows:

With the proper configuration SymmetricDS can publish XML messages of captured data changes to JMS during routing or transactionally while data loading synchronized data into a target database. The following explains how to publish to JMS during synchronization to the target database.

The XmlPublisherDatabaseWriterFilter is a IDatabaseWriterFilter that may be configured to publish specific tables as an XML message to a JMS provider. See Extensions for information on how to configure an extension point. If the publish to JMS fails, the batch will be marked in error, the loaded data for the batch will be rolled back and the batch will be retried during the next synchronization run.

The following is an example extension point configuration that will publish four tables in XML with a root tag of 'sale'. Each XML message will be grouped by the batch and the column names identified by the groupByColumnNames property which have the same values.

The publisher property on the XmlPublisherDatabaseWriterFilter takes an interface of type IPublisher. The implementation demonstrated here is an implementation that publishes to JMS using Spring’s JMS template. Other implementations of IPublisher could easily publish the XML to other targets like an HTTP server, the file system or secure copy it to another server.

The above configuration will publish XML similar to the following: