Chapter 1. Introduction

Table of Contents

1.1. Introduction
1.1.1. What is SymmetricDS?
1.1.2. Notification Schemes
1.1.3. Two-Way Table Synchronization
1.1.4. Data Channels
1.1.5. Transaction Awareness
1.1.6. Data Filtering and Rerouting
1.1.7. HTTP Transport
1.1.8. Remote Management
1.2. Requirements
1.3. Background

1.1. Introduction

1.1.1. What is SymmetricDS?

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.

1.1.2. Notification Schemes

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.

1.1.3. Two-Way Table Synchronization

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.

1.1.4. Data Channels

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.

1.1.5. Transaction Awareness

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.

1.1.6. Data Filtering and Rerouting

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 IDataLoaderFilter 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 data loading.

  • 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 implementing IColumnFilter 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 IExtractorListener 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.

1.1.7. HTTP Transport

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 ITransportManager interface allows other transports to be implemented. (The unit tests for SymmetricDS take advantage of this by using an InternalTransportManager that makes it easy to run automated tests locally.)

1.1.8. Remote Management

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.

1.2. Requirements

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

  • HSQLDB 1.8

  • H2 1.1

  • Apache Derby 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.

1.3. Background

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.