Table of Contents
SymmetricDS is asynchronous data replication software supporting multiple subscribers and bi-directional synchronization. It uses web and database technologies to replicate tables between relational databases in near real time. The software was designed to scale for a large number of databases, work across low-bandwidth connections, and withstand periods of network outage.
The software is installed either standalone, as a web application in a Java application server or can be embedded into another Java application. A single installation of SymmetricDS is called a Node. A Node is initialized by a properties file and is configured by inserting configuration data into a series of database tables. It then creates database triggers on the application tables specified so that database events are captured for delivery to other SymmetricDS nodes.
SymmetricDS supports synchronization across different database platforms through the concept of database dialects. A database dialect is an abstraction layer that SymmetricDS interacts with to insulated the main synchronization logic from database specific implementation details.
After a change to the database is recorded, the nodes interested in the change are notified. Change notification is configured to perform a push (trickle-back) or a pull (trickle-poll) of data. When several nodes target their changes to a central node, it is efficient to push the changes instead of waiting for the central node to pull from each source node. When network configuration protects a node with a firewall, a pull configuration allows the node to receive data changes that might otherwise be blocked using push. The frequency of the change notification is configured by default to one minute.
Some data may synchronize in one direction. For example, a retail store sends its sales transactions to a central office, and the central office sends its stock items to the store. Some data may synchronize in both directions. For example, the retail store sends the central office an inventory document, and the central office updates the document status, which is sent back to the store. SymmetricDS supports bi-directional synchronization and avoids getting into update loops by only recording data changes outside of synchronization.
Data synchronization is defined at the table (or table subset) level. Each managed table can be assigned to a channel that helps control the flow of data. A channel is a category of data that can be enabled, prioritized and synchronized independently of other channels. For example, in a retail environment, users may be waiting for inventory documents to update while a promotional sale event updates a large number of items. If processed in order, the item updates would delay the inventory updates even though the data is unrelated. By assigning item table changes to the "item" channel and inventory table changes to the "inventory" channel, the changes are processed independently so inventory can get through.
Many databases provide a unique transaction identifier associated with the rows that are committed together. SymmetricDS stores the transaction ID along with the data that changed so it can play back the transaction exactly the way it happened. This means the target database maintains the same integrity as its source. Support for transaction ID is documented in the appendix of this guide.
Data can be filtered as it is recorded, extracted, and loaded.
Data routing and filtering is specified using SQL expressions in the trigger configuration. The SQL expressions are configured in the node_select (for real time routing) and initial_load_select (for initial data loads) columns of the Section 3.1.8, “Trigger Table”. The SQL expressions are used to create the SQL triggers that SymmetricDS installs to capture data changes. Using this technique data can be routed to a specific node or group of nodes.
As data changes are loaded in the target database, a class implementing
can change the data in a column or route it somewhere else. One possible
use might be to route credit card data to a secure database and blank it
out as it loads into a centralized sales database. The filter can also prevent
data from reaching the database altogether, effectively replacing the default
Columns can be excluded from synchronization so they are never recorded
when the table is changed. As data changes are loaded into the target database, a class
can altogether remove a column from the synchronization. For example, an
employee table may be synchronized to a retail store database, but the
employee's password is only synchronized on the initial insert.
As data changes are extracted from the source
database, a class implementing the
interface is called to filter data or route it somewhere else. By default, SymmetricDS provides
a handler that transforms and streams data as CSV. Optionally, an alternate implementation
may be provided to take some other action on the extracted data.
By default, SymmetricDS uses web-based HTTP in a style called Representation
State Transfer (REST) that is lightweight and easy to manage. A series of
filters are also provided to enforce authentication and to restrict the number
of simultaneous synchronization streams. The
interface allows other transports to be implemented. (The unit tests for
SymmetricDS take advantage of this by using an
that makes it easy to run automated tests locally.)
Administration functions are exposed through Java Management Extensions (JMX) that can be accessed from the Java JConsole or through an application server. Functions include opening registration, reloading data, purging old data, and viewing batches. A number of configuration and runtime properties are available to be viewed as well.
SymmetricDS also provides functionality to send a SQL events through the same synchronization mechanism that is used to send data. The data payload can be any SQL statement. The event is processed and acknowledged just like any other event type.
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 this release:
MySQL version 5.0.2 and above
Oracle version 8.1.7 and above
PostgreSQL version 8.2.5 and above
Sql Server 2005
Apache Derby 10.3.2.1 and above
IBM DB2 9.5
Firebird 2.0 and above
See the appendix Database Notes for compatibility notes and other details for your specific database.
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:
Sending and receiving data with 2000 stores during peak holiday loads.
Supporting one database platform at the store and another at general office.
Synchronizing some data in one direction, and other data in both directions.
Filtering out sensitive data and re-routing it to a protected database.
Preparing the store database with an initial load of data from general office.
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.