SymmetricDS User Guide

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:

See Appendix C, Database Notes, for compatibility notes and other details for your specific database.

The following is an overview of how SymmetricDS works.

1.2.1. A Node is Born

SymmetricDS is a Java-based application that hosts a synchronization engine which acts as an agent for data synchronization between a single database instance and other synchronization engines in a network.

The SymmetricDS engine is also referred to as a node . SymmetricDS is designed to be able to scale out to many thousands of nodes. The database connection is configured by providing a database connection string, database user, and database password in a properties file. SymmetricDS can synchronize any table that is accessible by the database connection, given that the database user has been assigned the appropriate database permissions.

Figure 1.1. Simple Configuration

A SymmetricDS node is assigned an external id and a node group id. The external id is a meaningful, user-assigned identifier that is used by SymmetricDS to understand which data is destined for a given node. The node group id is used to identify groupings or tiers of nodes. It defines where the node fits into the overall node network. For example, one node group might be named “corporate” and represent an enterprise or corporate database. Another node group might be named “local_office” and represent databases located in different offices across a country. The external id for a “local_office” could be an office number or some other identifying alphanumeric string. A node is uniquely identified in a network by a node id that is automatically generated from the external id. If local office number 1 had two office databases and two SymmetricDS nodes, they would probably have an external id of “1” and node ids of “1-1” and “1-2.”

SymmetricDS can be deployed in a number of ways. The most common option is to deploy it as a standalone process running as a service on your chosen server platform. When deployed in this manner SymmetricDS can act as either a client, a multi-tenant server or both depending on where the SymmetricDS database fits into the overall network of databases. Although it can run on the same server as its database, it is not required to do so. SymmetricDS can also be deployed as a web application in an application server such as Apache Tomcat, JBoss Application Server, IBM WebSphere, or others.

SymmetricDS was designed to be a simple, approachable, non-threatening tool for technology personnel. It can be thought of and dealt with as a web application, only instead of a browser as the client, other SymmetricDS engines are the clients. It has all the characteristics of a web application and can be tuned using the same principles that would be used to tune user facing web applications.

At a high level, SymmetricDS comes with a number of features that you are likely to need or want when doing data synchronization. A majority of these features were created as a direct result of real-world use of SymmetricDS in production settings.

The idea of SymmetricDS was born from a real-world need. Several of the original developers were, several years ago, implementing a commercial Point of Sale (POS) system for a large retailer. The development team came to the conclusion that the software available for trickling back transactions to corporate headquarters (frequently known as the 'central office' or 'general office') did not meet the project needs. The list of project requirements made finding the ideal solution difficult:

The team ultimately created a custom solution that met the requirements and led to a successful project. From this work came the knowledge and experience that SymmetricDS benefits from today.

There are several industry recognized techniques to capture changing data for replication, synchronization and integration in a relational database.

All three of these techniques have advantages and disadvantages, and all three are on the road map for SymmetricDS. At present time, SymmetricDS supports trigger-based data capture and basic lazy data capture. These two techniques were implemented first for a variety of reasons, not the least of which is that the majority of use cases that SymmetricDS targets can be solved using trigger-based and conditional replication in a way that allows for more database platforms to be supported using industry standard technologies. This fact allowed our developers' valuable time and energy to be invested in designing a product that is easy to install, configure and manage versus spending time reverse engineering proprietary and not well documented database log files.

Trigger-based data capture does introduce a measurable amount of overhead on database operations. The amount of overhead can vary greatly depending on the processing power and configuration of the database platform, and the usage of the database by applications. With nonstop advances in hardware and database technology, trigger-based data capture has become feasible for use cases that involve high data throughput or require scaling out.

Trigger-based data capture is easier to implement and support than log-based solutions. It uses well known database concepts and is very accessible to software and database developers and database administrators. It can usually be installed, configured, and managed by application development teams or database administrators and does not require deployment on the database server itself.

SymmetricDS is backed by JumpMind, Inc.

SymmetricDS is, and always will be, open source, which means free community support is available online, through the forums and the issue tracker. In a production environment, we have found that clients demand fast, more experienced help from the original architects and engineers — people who have the knowledge and experience to design, tune, troubleshoot, and shape future versions of the product.

To meet this demand, JumpMind provides Support Subscriptions designed to provide your organization with expert, dependable support from development to mission critical production support.

SymmetricDS 3 builds upon the existing SymmetricDS 2.x software base and incorporates a number of architectural changes and performance improvements. If you are brand new to SymmetricDS, you can safely skip this section. If you have used SymmetricDS 2.x in the past, this section summarizes the key differences you will encounter when moving to SymmetricDS 3.

One optimization that effects both routing and data extraction is a change to the routing process to reuse batches across nodes if all of the data in the batches is going to be the same. SymmetricDS will automatically reuse batches if the default router is being used and there are NO inbound routers that have sync_on_incoming_batch turned on. If the same data is being sent to all nodes then a great deal of processing, during both routing and extraction, can be avoided. This is especially useful when data is being delivered to thousands of nodes. As a result of this change, the primary key of OUTGOING_BATCH has changed. This means that during an upgrade the table will be rebuilt.

Another optimization that effects data transport is the change to load batches as soon as they have been delivered to a target node. In 2.x all batches for a synchronization run were delivered, and then data was loaded. When errors occurred early on and there were several big batches or hundreds of batches to deliver, this was inefficient because all the batches were transported before the loading started.

Yet another optimization allows SymmetricDS to scale better when it is initiating communication with nodes. The pulling and pushing of data now happens from a configurable, but fixed size thread pool so that multiple nodes can be pulled and pushed to concurrently. This means that now, a centralized node can reach out to many child nodes in an efficient manner where in the past, the child nodes were relied upon to initiate communication.

The 2.x series allowed multiple nodes to be hosted in one standalone SymmetricDS instance. This feature (called multiServerMode) was off by default. In SymmetricDS 3 this feature is now the preferred mode of operation. It formalizes where properties file are configured and allows multiple nodes to be hosted on one JVM which saves on system resources.

SymmetricDS 3 introduces a long anticipated feature: Conflict Detection and Resolution. Please see Section 3.8, “Planning Conflict Detection and Resolution” for more information.

Transformations are now friendlier. They allow columns to be implied. This means that when configuring transformations, not all of the columns have to be specified which makes transformations much more maintainable.

An architectural change to the data loader subsystem allows the data loader to now be pluggable by channel. This will allow more efficient data loaders to be built if necessary. It will also make it straight forward to load data into non-relational data stores.

Several properties and extension points have been deprecated or renamed. Please see Appendix E, Upgrading from 2.x for a list of deprecated features.

First, we will install two copies of the SymmetricDS software and configure it with your database connection information:

First, create the sample tables in the root node database, load the sample data, and load the sample configuration, by doing the following:

We have now created the corp database tables and populated them with our SymmetricDS configuration and sample data. Next, we will create the sample tables in the store node database to prepare it for receiving data.

Please verify both databases by logging in and listing the tables.

Database setup and configuration for the tutorial is now complete. Time to put SymmetricDS into action. We will now start both SymmetricDS nodes and observe the logging output.

When an unregistered node starts up, it will attempt to register with the node specified by the registration URL (which is our root node, in almost every case). The registration node centrally controls nodes on the network by allowing registration and returning configuration to a node once it has registered. In this tutorial, the registration node is the root node or 'corp' node, and it also participates in synchronization with other nodes.

So, we next need to open registration for the store node so that it may receive its initial load of data and so that it may receive and send data from and to the corp node. There are several ways to do this. We will use an administration feature available in SymmetricDS and issue a command on the corp node (since it is the node responsible for registration).

Next, we will send an initial load of data to our store, again using a node administration feature run on the corp node.

Next, we will make a change to the item data in the central office corp node database (we'll add a new item), and observe the data being pulled down to the store.

We will now simulate a sale at the store and observe how SymmetricDS pushes the sale transaction to the central office.

Now that we have pushed and pulled data, we will demonstrate how you can obtain information about what data has been batched and sent. A batch is used for tracking and sending one or more data changes to a given node. The sending node creates a batch and the receiving node receives and then acknowledges it.

In addition, in SymmetricDS tables are grouped into data "Channels" for, among many reasons, the purpose of allowing different types of data to synchronize even when other types of data might be in error. For example, if a batch for a given channel is in error, that batch will be retried with each synchronization for that channel until the batch is no longer in error. Only after the batch is no longer in error will additional batches for that channel be sent. In this way, the order of the data changes that have occurred for a given channel are guaranteed to be sent to the destination in the same order they occurred on the source. Batches on a channel without batch errors, however, will not be blocked by the existence of a batch in error on a different channel. In this way, data changes for one channel are not blocked by errors present in another channel.

Explore the outgoing batches by doing the following:

Understanding these three tables, along with a fourth table discussed in the next section, is key to diagnosing any synchronization issues you might encounter. As you work with SymmetricDS, either when experimenting or starting to use SymmetricDS on your own data, spend time monitoring these tables to better understand how SymmetricDS works. Exploring and solving any synchronization issues is discussed later in far greater depth in Section 6.1, “Solving Synchronization Issues”.

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.

Explore incoming batches by doing the following:

Our Quick Start Tutorial is finished. We have succesfully set up and performed synchronization between two databases. However, we did want to go back and discuss one of the first steps you did in the tutorial; namely, the step where you installed two copies of SymmetricDS when doing the tutorial. Feel free to skip this section until a later time if you wish.

In the example above, we placed one properties file in the engines directory of each installed SymmetricDS application. When SymmetricDS was started in the examples above, the application initialized, and then created a "SymmetricDS engine" based on the provided property file (again, each engine serves as a SymmetricDS node and is responsible for one particular database).

In reality, though, the SymmetricDS application is capable of starting more than one engine at a time. When SymmetricDS starts, it looks in the engines directory for any files that end in .properties. It will start a SymmetricDS engine for each and every property file found. The --engine command line prompt is an override for this and will cause SymmetricDS to only start the one engine as specified on the command line. In cases where a single SymmetricDS application is running multiple engines, this is known as a "multi-homed" SymmetricDS application, and the feature, in general, is known as "multi-homing".

Figure 2.3. Multi-Homed version of Tutorial

So, for our tutorial above, how could we have "multi-homed" the corp and store such that we only had to install a single copy of SymmetricDS? It's fairly simple. The following changes to the above would be needed:

A node is a single instance of SymmetricDS. It can be thought of as a proxy for a database which manages the synchronization of data to and/or from its database. For our example retail application, the following would be SymmetricDS nodes:

Each node of SymmetricDS can be either embedded in another application, run stand-alone, or even run in the background as a service. If desired, nodes can be clustered to help disperse load if they send and/or receive large volumes of data to or from a large number of nodes.

Individual nodes are easy to identify when planning your implementation. If a database exists in your domain that needs to send or receive data, there needs to be a corresponding SymmetricDS instance (a node) responsible for managing the synchronization for that database.

Nodes in SymmetricDS are organized into an overall node network, with connections based on what data needs to be synchronized where. The exact organization of your nodes will be very specific to your synchronization goals. As a starting point, lay out your nodes in diagram form and draw connections between nodes to represent cases in which data is to flow in some manner. Think in terms of what data is needed at which node, what data is in common to more than one node, etc. If it is helpful, you could also show data flow into and out of external systems. As you will discover later, SymmetricDS can publish data changes from a node as well using JMS.

Our retail example, as shown in Figure 3.1 , represents a tree hierarchy with a single central office node connected by lines to one or more children nodes (the POS workstations). Information flows from the central office node to an individual register and vice versa, but never flows between registers.

Figure 3.1. Two-Tier Retail Store Deployment Example

More complex organization can also be used. Consider, for example, if the same retail example is expanded to include store servers in each store to perform tasks such as opening the store for the day, reconciling registers, assigning employees, etc. One approach to this new configuration would be to create a three-tier hierarchy (see Figure 3.2 ). The highest tier, the centralized database, connects with each store server's database. The store servers, in turn, communicate with the individual point-of-sale workstations at the store. In this way data from each register could be accumulated at the store server, then sent on to the central office. Similarly, data from the central office can be staged in the store server and then sent on to each register, filtering the register's data based on which register it is.

Figure 3.2. Three-Tier, In-Store Server, Retail Store Deployment Example

One final example, show in Figure 3.3 , again extending our original two-tier retail use case, would be to organize stores by "region" in the world. This three tier architecture would introduce new regional servers (and corresponding regional databases) which would consolidate information specific to stores the regional server is responsible for. The tiers in this case are therefore the central office server, regional servers, and individual store registers.

Figure 3.3. Three-Tier, Regional Server, Retail Store Deployment Example

These are just three common examples of how one might organize nodes in SymmetricDS. While the examples above were for the retail industry, the organization, they could apply to a variety of application domains.

Once the organization of your SymmetricDS nodes has been chosen, you will need to group your nodes based on which nodes share common functionality. This is accomplished in SymmetricDS through the concept of a Node Group . Frequently, an individual tier in your network will represent one Node Group. Much of SymmetricDS' functionality is specified by Node Group and not an individual node. For example, when it comes time to decide where to route data captured by SymmetricDS, the routing is configured by Node Group .

For the examples above, we might define Node Groups of:

Considerable thought should be given to how you define the Node Groups. Groups should be created for each set of nodes that synchronize common tables in a similar manner. Also, give your Node Groups meaningful names, as they will appear in many, many places in your implementation of SymmetricDS.

Note that there are other mechanisms in SymmetricDS to route to individual nodes or smaller subsets of nodes within a Node Group, so do not choose Node Groups based on needing only subsets of data at specific nodes. For example, although you could, you would not want to create a Node Group for each store even though different tax rates need to be routed to each store. Each store needs to synchronize the same tables to the same groups, so 'store' would be a good choice for a Node Group.

Now that Node Groups have been chosen, the next step in planning is to document the individual links between Node Groups. These Node Group Links establish a source Node Group, a target Node Group, and a data event action , namely whether the data changes are pushed or pulled . The 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.

For our retail store example, there are two Node Group Links defined. For the first link, the "store" Node Group pushes data to the "corp" central office Node Group. The second defines a "corp" to "store" link as a pull. Thus, the store nodes will periodically pull data from the central office, but when it comes time to send data to the central office a store node will do a push.

When SymmetricDS captures data changes in the database, the changes are captured in the order in which they occur. In addition, that order is preserved when synchronizing the data to other nodes. Frequently, however, you will have cases where you have different "types" of data with differing priorities. Some data might, for example, need priority for synchronization despite the normal order of events. 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.

SymmetricDS supports this by allowing tables being synchronized to be grouped together into Channels of data. A number of controls to the synchronization behavior of SymmetricDS are controlled at the Channel level. For example, Channels provide a processing order when synchronizing, a limit on the amount of data that will be batched together, and isolation from errors in other channels. By categorizing data into channels and assigning them to TRIGGER s, the user gains more control and visibility into the flow of data. In addition, SymmetricDS allows for synchronization to be enabled, suspended, or scheduled by Channels as well. The frequency of synchronization can also be controlled at the channel level.

Choosing Channels is fairly straightforward and can be changed over time, if needed. Think about the differing "types" of data present in your application, the volume of data in the various types, etc. What data is considered must-have and can't be delayed due to a high volume load of another type of data? For example, you might place employee-related data, such as clocking in or out, on one channel, but sales transactions on another. We will define which tables belong to which channels in the next sections.

At this point, you have designed the node-related aspects of your implementation, namely choosing nodes, grouping the nodes based on functionality, defining which node groups send and receive data to which others (and by what method). You have defined data Channels based on the types and priority of data being synchronized. The largest remaining task prior to starting your implementation is to define and document what data changes are to be captured (by defining SymmetricDS Triggers ), to decide to which node(s) the data changes are to be routed to, and to decide which trigger applies to which router and under what conditions. We will also, in this section, discuss the concept of an initial load of data into a SymmetricDS node.

SymmetricDS also provides the abilty to transform synchronized data instead of simply synchronizing it. Your application might, for example require a particular column in your source data to be mapped to two different target tables with possibly different column names. Or, you might need to "merge" one or more columns of data from two indepdentent tables into one table on the target. Or, you may want to set default column values on a target table based on a particular event on the source database. All of these operations, and many more, can be accomplished using SymmetricDS' transformation capabilities.

As you plan your SymmetricDS implementation, make notes of cases where a data transformation is needed. Include details such as when the transformation might occur (is it only on an insert, or a delete?), which tables or columns play a part, etc. Complete details of all the transformation features, including how to configure a transformation, are discussed in Section 4.8, “Transforming Data”.

As a final step to planning an implementation, consider for a moment cases in which the same data may be modified at nearly the same time at more than one node. For example, can data representing a customer be modified at both a central office and a store location? 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. Conflict resolution is the act of figuring out what to do when a conflict is detected. Both detection and resolution behaviour can be configured and customized in a number of ways. For example, a conflict can be "detected" based solely on a single column which has been modified to a different value, or a row can be considered in conflict if any data in the row has been changed from what was expected, even if the column that has been changed was still expected. There are also numerous ways to resolve the conflict, such as referencing a timestamp column and choosing whichever edit was "most recent" or perhaps causing the conflict to cause the channel to go into error until a manual resolution takes place. A set of conflict detection / resolution rules is configured for a given node group link, but you can set the rules to be for a given channel or for a given table in a channel.

For the purpose of planning your implementation, make a list of all tables that could have data being modified at more than one node at the same time. For each table, think through what should happen in each case if such an event occurs. If the tables on a given channel all have the same set of conflict resolution and detection rules, then you might be able to configure the rules for the channel instead of a series of table-level detections and resolutions. Complete details on how to configure conflict resolution and detection are discussed further in Section 4.10, “Conflict Detection and Resolution”.

To get a SymmetricDS node running, it needs to be given an identity and it needs to know how to connect to the database it will be synchronizing. The preferred way to configure a SymmetricDS engine is to create a properties file in the engines directory. The SymmetricDS server will create an engine for each properties file found in the engines directory. When started up, SymmetricDS reads the synchronization configuration and state from the database. If the configuration tables are missing, they are created automatically (auto creation can be disabled). Basic configuration is described by inserting into the following tables (the complete data model is defined in Appendix A, Data Model).

During start up, triggers are verified against the database, and database triggers are installed on tables that require data changes to be captured. The Route, Pull and Push Jobs begin running to synchronize changes with other nodes.

Each node requires properties that allow it to connect to a database and register with a parent node. Properties are configured in a file named xxxxx.properties that 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. Note that you can use the variable $(hostName) to represent the host name of the machine when defining these properties (for example, external.id=$(hostName) ).

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 where the database connection pool should be created using a JDBC driver, set the following properties:

A node, a single instance of SymmetricDS, is defined in the NODE table. Two other tables play a direct role in defining a node, as well The first is NODE_IDENTITY. The only row in this table is inserted in the database when the node first registers with a parent node. In the case of a root node, the row is entered by the user. The row is used by a node instance to determine its node identity.

The following SQL statements set up 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)
values
  ('00000', 'corp', '00000', 1);

insert into SYM_NODE_IDENTITY values ('00000');

The second table, NODE_SECURITY has rows created for each child node that registers with the node, assuming auto-registration is enabled. If auto registration is not enabled, you must create a row in NODE and NODE_SECURITY for the node to be able to register. You can also, with this table, manually cause a node to re-register or do a re-initial load by setting the corresponding columns in the table itself. Registration is discussed in more detail in Section 4.7, “Opening Registration”.

Node Groups are straightforward to configure and are defined in the NODE_GROUP table. The following SQL statements would create node groups for "corp" and "store" based on our retail store example.

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');

Similarly, Node Group links are established using a data event action of 'P' for Push and 'W' for Pull ("wait"). 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');

A node group link can be configured to use the same node group as the source and the target. This configuration allows a node group to sync with every other node in its group.

A third type of link action of 'R' for 'Route Only' exists if you want to associate a router with a link that will not move the data. This action type might be useful when using an XML publishing router or an audit table changes router.

By categorizing data into channels and assigning them to TRIGGERs, the user gains more control and visibility into the flow of data. In addition, SymmetricDS allows for synchronization to be enabled, suspended, or scheduled by channels as well. The frequency of synchronization and order that data gets synchronized is also controlled at the channel level.

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, max_batch_to_send, 
   extract_period_millis, batch_algorithm, enabled, description)
values
  ('item', 10, 1000, 10,  0, 'default', 1, 'Item and pricing data');

insert into SYM_CHANNEL 
  (channel_id, processing_order, max_batch_size, max_batch_to_send, 
   extract_period_millis, batch_algorithm, enabled, description)
values
  ('sale_transaction', 1, 1000, 10,  60000, 'transactional', 1, 
   'retail sale transactions from register');

Batching is the grouping of data, by channel, to be transferred and committed at the client together. There are three different out-of-the-box batching algorithms which may be configured in the batch_algorithm column on channel.

If a channel contains only tables that will be synchronized in one direction and and data is routed to all the nodes in the target node groups, then batching on the channel can be optimized to share batches across nodes. This is an important feature when data needs to be routed to thousands of nodes. When this mode is detected, you will see batches created in OUTGOING_BATCH with the common_flag set to 1.

There are also several size-related parameters that can be set by channel. They include:

Based on your particular synchronization requirements, you can also specify whether old, new, and primary key data should be read and included during routing for a given channel. These are controlled by the columns use_old_data_to_route, use_row_data_to_route, and use_pk_data_to_route, respectively. By default, they are all 1 (true).

Finally, if data on a particular channel contains big lobs, you can set the column contains_big_lob to 1 (true) to provide SymmetricDS the hint that the channel contains big lobs. Some databases have shortcuts that SymmetricDS can take advantage of if it knows that the lob columns in DATA aren't going to contain large lobs. The definition of how large a 'big' lob is varies from database to database.

In order to synchronize data, you must define at least one trigger, at least one router, and provide at least one link between the two (known as a trigger-router).

insert into SYM_TRIGGER 
    (trigger_id,source_table_name,channel_id,last_update_time,create_time)
  values
    ('item', 'item', 'item', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, 
    create_time, last_update_time)
values
  ('corp-2-store','corp', 'store', current_timestamp, current_timestamp);

insert into SYM_TRIGGER_ROUTER 
  (trigger_id, router_id, initial_load_order,  create_time, last_update_time)
values
  ('item', 'corp-2-store', 1, current_timestamp, current_timestamp);

insert into SYM_ROUTER 
(router_id, source_node_group_id, target_node_group_id, router_type, 
 router_expression, create_time, last_update_time)
values
('corp-2-store-ok','corp', 'store', 'column', 
 'STATUS=READY TO SEND', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-status','corp', 'store', 'column', 
    'STATUS!=:OLD_STATUS', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-id','corp', 'store', 'column', 
    'STORE_ID=:EXTERNAL_ID', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-redirect','corp', 'store', 'column', 
    'STORE_ID=:REDIRECT_NODE', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-multiple-matches','corp', 'store', 'column', 
   'STORE_ID=ALL or STORE_ID=:EXTERNAL_ID', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-multiple-matches','corp', 'store', 'column', 
   'STORE_ID=NULL or STORE_ID=:EXTERNAL_ID', current_timestamp, current_timestamp);

insert into SYM_ROUTER 
(router_id, source_node_group_id, target_node_group_id, router_type, 
 router_expression, create_time, last_update_time)
values
('corp-2-store-ok','corp', 'store', 'lookuptable', 
 'LOOKUP_TABLE=STORE
KEY_COLUMN=BRAND_ID
LOOKUP_KEY_COLUMN=BRAND_ID
EXTERNAL_ID_COLUMN=STORE_ID', current_timestamp, current_timestamp);

select c.node_id from sym_node c where 
  c.node_group_id=:NODE_GROUP_ID and c.sync_enabled=1 and ...

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store','corp', 'store', 'subselect', 
    'c.external_id in (select STORE_ID from order where order_id=:ORDER_ID)', 
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-bsh','corp', 'store', 'bsh', 
    'targetNodes.add(STORE_ID + "-" + WORKSTATION_NUMBER);', 
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-bsh','corp', 'store', 'bsh', 
    'STORE_ID + "-" + WORKSTATION_NUMBER', 
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-flag-changed','corp', 'store', 'bsh', 
    'FLAG != null && !FLAG.equals(OLD_FLAG)', 
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-trimmed-station','corp', 'store', 'bsh', 
    'for (org.jumpmind.symmetric.model.Node node : nodes) {
         if (STATION != null && node.getExternalId().equals(STATION.trim())) {
             targetNodes.add(node.getNodeId());
         }
      }', 
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    create_time, last_update_time)
values
  ('audit_at_corp','corp', 'local', 'audit', current_timestamp, current_timestamp);

insert into SYM_TRIGGER 
  (trigger_id,source_table_name,channel_id,external_select,
    last_update_time,create_time)
values
  ('orderlineitem', 'orderlineitem', 'orderlineitem','select STORE_ID 
    from order where order_id=$(curTriggerValue).$(curColumnPrefix)order_id',
    current_timestamp, current_timestamp);

insert into SYM_ROUTER 
  (router_id, source_node_group_id, target_node_group_id, router_type, 
    router_expression, create_time, last_update_time)
values
  ('corp-2-store-ext','corp', 'store', 'column', 
    'EXTERNAL_DATA=:EXTERNAL_ID', current_timestamp, current_timestamp);

insert into sym_trigger (TRIGGER_ID,SOURCE_CATALOG_NAME,
  SOURCE_SCHEMA_NAME,SOURCE_TABLE_NAME,CHANNEL_ID,
  SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,
  SYNC_ON_INCOMING_BATCH,NAME_FOR_UPDATE_TRIGGER,
  NAME_FOR_INSERT_TRIGGER,NAME_FOR_DELETE_TRIGGER,
  SYNC_ON_UPDATE_CONDITION,SYNC_ON_INSERT_CONDITION,
  SYNC_ON_DELETE_CONDITION,EXTERNAL_SELECT,
  TX_ID_EXPRESSION,EXCLUDED_COLUMN_NAMES,
  CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME) 
  values ('SALE_TRANSACTION_DEAD',null,null,
  'SALE_TRANSACTION','transaction',
  0,0,0,0,null,null,null,null,null,null,null,null,null,
  current_timestamp,'demo',current_timestamp);

insert into sym_router (ROUTER_ID,TARGET_CATALOG_NAME,TARGET_SCHEMA_NAME,
  TARGET_TABLE_NAME,SOURCE_NODE_GROUP_ID,TARGET_NODE_GROUP_ID,ROUTER_TYPE,
  ROUTER_EXPRESSION,SYNC_ON_UPDATE,SYNC_ON_INSERT,SYNC_ON_DELETE,
  CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME) 
  values ('CORP_2_STORE',null,null,null,
  'corp','store',null,null,1,1,1,
  current_timestamp,'demo',current_timestamp);
   
insert into sym_trigger_router (TRIGGER_ID,ROUTER_ID,INITIAL_LOAD_ORDER,
  INITIAL_LOAD_SELECT,CREATE_TIME,LAST_UPDATE_BY,LAST_UPDATE_TIME) 
  values ('SALE_TRANSACTION_DEAD','CORP_2_REGION',100,null,
   current_timestamp,'demo',current_timestamp);
   

Node registration is the act of setting up a new NODE and NODE_SECURITY so that when the new node is brought online it is allowed to join the system. Nodes are only allowed to register if rows exist for the node and the registration_enabled flag is set to 1. If the auto.registration SymmetricDS property is set to true, then when a node attempts to register, if registration has not already occurred, the node will automatically be registered.

SymmetricDS allows you to have multiple nodes with the same external_id. Out of the box, openRegistration will open a new registration if a registration already exists for a node with the same external_id. A new registration means a new node with a new node_id and the same external_id will be created. If you want to re-register the same node you can use the reOpenRegistration() JMX method which takes a node_id as an argument.

New as of SymmetricDS 2.4, SymmetricDS is now able to transform synchronized data by way of configuration (previously, for most cases a custom data loader would need to have been written). This transformation can take place on a source node or on a target node, as the data is being loaded or extracted. With this new feature you can, for example:

These transformations can take place either on the target or on the source, and as data is either being extracted or loaded. In either case, the transformation is initiated due to existence of a source synchronization trigger. The source trigger creates the synchronization data, while the transformation configuration decides what to do with the sychronization 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.

A few key concepts are important to keep in mind to understand how SymmetricDS performs transformations. The first concept is that of the "source operation" or "source DML type", which is the type of operation that occurred to generate the synchronization data in the first place (i.e., an insert, a delete, or an update). Your transformations can be configured to act differently based on the source DML type, if desired. When transforming, by default the DML action taken on the target matches that of the action taken on the row in the source (although this behavior can be altered through configuration if needed). If the source DML type is an Insert, for example, the resulting transformation DML(s) will be Insert(s).

Another important concept is the way in which transforms are applied. Each source operation may map to one or more transforms and result in one or more operations on the target tables. Each of these target operations are performed as independent operations in sequence and must be "complete" from a SQL perspective. In other words, you must define columns for the transformation that are sufficient to fill in any primary key or other required data in the target table if the source operation was an Insert, for example.

Finally, please note that the tranformation engine relies on a source trigger / router existing to supply the source data for the transformation. The transform configuration will never be used if the source table and target node group does not have a defined trigger / router combination for that source table and target node group.

New as of SymmetricDS 3.1, SymmetricDS is now capable of taking actions upon the load of certain data via configurable load filters. This new configurable option is in additon to the already existing option of writing a class that implements IDatabaseWriterFilter. A configurable load filter watches for specific data that is being loaded and then takes action based on the load of that data.

Specifying which data to action is done by specifying a souce and target node group (data extracted from this node group, and loaded into that node group), and a target catalog, schema and table name. You can decide to take action on rows that are inserted, updated and/or deleted, and can also further delineate which rows of the target table to take action on by specifying additional criteria in the bean shell script that is executed in response to the loaded data. As an example, old and new values for the row of data being loaded are available in the bean shell script, so you can action rows with a certain column value in old or new data.

The action taken is based on a bean shell script that you can provide as part of the configuration. Actions can be taken at different points in the load process including before write, after write, at batch complete, at batch commit and/or at batch rollback.

insert into sym_load_filter 
   (LOAD_FILTER_ID, LOAD_FILTER_TYPE, SOURCE_NODE_GROUP_ID, TARGET_NODE_GROUP_ID,
    TARGET_CATALOG_NAME, TARGET_SCHEMA_NAME, TARGET_TABLE_NAME, 
    FILTER_ON_UPDATE, FILTER_ON_INSERT, FILTER_ON_DELETE, 
    BEFORE_WRITE_SCRIPT, AFTER_WRITE_SCRIPT, BATCH_COMPLETE_SCRIPT, 
    BATCH_COMMIT_SCRIPT, BATCH_ROLLBACK_SCRIPT, HANDLE_ERROR_SCRIPT, 
    CREATE_TIME, LAST_UPDATE_BY, LAST_UPDATE_TIME,
    LOAD_FILTER_ORDER, FAIL_ON_ERROR)
values ('TABLE_TO_RELOAD','BSH','Client','Server',NULL,NULL,
    'TABLE_TO_WATCH',1,1,0,null,
    'engine.getDataService().reloadTable(context.getBatch().getSourceNodeId(), 
    table.getCatalog(), table.getSchema(),
    "TABLE_TO_RELOAD","KEY_FIELD=''" + KEY_FIELD + "''");'
    ,null,null,null,null,sysdate,'userid',sysdate,1,1);

Conflict detection and resolution is new as of SymmetricDS 3.0. 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. Conflict resolution is the act of figuring out what to do when a conflict is detected.

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 . Conflicts are detected while data is being loaded into a target system.

The choice of how to resolve a detected conflict is configured via the resolve_type column. Depending on the setting, two additional boolean settings may also be configured, namely resolve_row_only and resolve_changes_only, as discussed in the resolution settings below.

For each configured conflict, you also have the ability to control if and how much "resolved" data is sent back to the node who's data change is in conflict. This "ping back" behavior is specified by the setting of the ping_back column and can be one of the following values:

The SymmetricDS software allows for outgoing and incoming changes to be synchronized to/from other databases. The node that initiates a synchronization connection is the client, and the node receiving a connection is the host. Because synchronization is configurable to push or pull in either direction, the same node can act as either a client or a host in different circumstances.

The SymmetricDS software consists of a series of background jobs, managers, Servlets, and services wired together via dependency injection using the Spring Framework.

As a client, the node runs the router job, push job and pull job on a timer thread. The router job uses services to create batches that are targeted at certain nodes. The push job uses services to extract and stream data to another node (that is, it pushes data). The response from a push is a list of batch acknowlegements to indicate that data was loaded. The pull job uses services to load data that is streamed from another node (i.e., it pulls data). After loading data, a second connection is made to send a list of batch acknowlegements.

As a host, the node waits for incoming connections that pull, push, or acknowledge data changes. The push Servlet uses services to load data that is pushed from a client node. After loading data, it responds with a list of batch acknowledgements. The pull Servlet uses services to extract, and stream data back to the client node. The ack Servlet uses services to update the status of data that was loaded at a client node. The router job batches and routes data.

By default, data is extracted from the source database into memory until a threshold size is reached. If the threshold size is reached, data is streamed to a temporary file in the JVM's default temporary directory. Next, the data is streamed to the target node across the transport layer. The receiving node will cache the data in memory until the threshold size is reached, writing to a temporary file if necessary. At last, the data is loaded into the target database by the data loader. This step by step approach allows for extract time, transport time, and load time to all be measured independently. It also allows database resources to be used most optimally.

The transport manager handles the incoming and outgoing streams of data between nodes. The default transport is based on a simple implementation over HTTP. An internal transport is also provided. It is possible to add other implementations, such as a socket-based transport manager.

Node communication over HTTP is represented in the following figure.

Figure 5.1. Node Communication

The StandaloneSymmetricEngine is wrapper API that can be used to directly start the client services only. The SymmetricWebServer is a wrapper API that can be used to directly start both the client and host services inside a Jetty web container. The SymmetricLauncher provides command line tools to work with and start SymmetricDS.

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 Section 5.10, “Extension Points” 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.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd">
 
    <bean id="configuration-publishingFilter" 
      class="org.jumpmind.symmetric.integrate.XmlPublisherDatabaseWriterFilter">
        <property name="xmlTagNameToUseForGroup" value="sale"/>
        <property name="tableNamesToPublishAsGroup">
            <list>
               <value>SALE_TX</value>
               <value>SALE_LINE_ITEM</value>
               <value>SALE_TAX</value>
               <value>SALE_TOTAL</value>
            </list>            
        </property>
        <property name="groupByColumnNames">
            <list>
               <value>STORE_ID</value>
               <value>BUSINESS_DAY</value>
               <value>WORKSTATION_ID</value>
               <value>TRANSACTION_ID</value>
            </list>                      
        </property>
        <property name="publisher">
           <bean class="org.jumpmind.symmetric.integrate.SimpleJmsPublisher">
               <property name="jmsTemplate" ref="definedSpringJmsTemplate"/>
           </bean>
        </property>        
    </bean>     
</beans>

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 similiar to the following:

<?xml version="1.0" encoding="UTF-8"?>
<sale xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  id="0012010-01-220031234" nodeid="00001" time="1264187704155">
  <row entity="SALE_TX" dml="I">
    <data key="STORE_ID">001</data>
    <data key="BUSINESS_DAY">2010-01-22</data>
    <data key="WORKSTATION_ID">003</data>
    <data key="TRANSACTION_ID">1234</data>
    <data key="CASHIER_ID">010110</data>
  </row>        
  <row entity="SALE_LINE_ITEM" dml="I">
    <data key="STORE_ID">001</data>
    <data key="BUSINESS_DAY">2010-01-22</data>
    <data key="WORKSTATION_ID">003</data>
    <data key="TRANSACTION_ID">1234</data>
    <data key="SKU">9999999</data>  
    <data key="PRICE">10.00</data>
    <data key="DESC" xsi:nil="true"/>
  </row>        
  <row entity="SALE_LINE_ITEM" dml="I">
    <data key="STORE_ID">001</data>
    <data key="BUSINESS_DAY">2010-01-22</data>
    <data key="WORKSTATION_ID">003</data>
    <data key="TRANSACTION_ID">1234</data>
    <data key="SKU">9999999</data>  
    <data key="PRICE">10.00</data>
    <data key="DESC" xsi:nil="true"/>
  </row>
  <row entity="SALE_TAX" dml="I">
    <data key="STORE_ID">001</data>
    <data key="BUSINESS_DAY">2010-01-22</data>
    <data key="WORKSTATION_ID">003</data>
    <data key="TRANSACTION_ID">1234</data>
    <data key="AMOUNT">1.33</data>  
  </row>
  <row entity="SALE_TOTAL" dml="I">
    <data key="STORE_ID">001</data>
    <data key="BUSINESS_DAY">2010-01-22</data>
    <data key="WORKSTATION_ID">003</data>
    <data key="TRANSACTION_ID">1234</data>
    <data key="AMOUNT">21.33</data>  
  </row>
</sale>
            

To publish JMS messages during routing the same pattern is valid, with the exception that the extension point would be the XmlPublisherDataRouter and the router would be configured by setting the router_type of a ROUTER to the Spring bean name of the registered extension point. Of course, the router would need to be linked through TRIGGER_ROUTERs to each TRIGGER table that needs published.

An instance of SymmetricDS can be deployed in several ways:

The deployment model you choose depends on how much flexibility you need versus how easy you want it to be. Both Jetty and Tomcat are excellent, scalable web servers that compete with each other and have great performance. Most people choose either the Standalone or Web Archive with Tomcat 5.5 or 6. Deploying to Tomcat is a good middle-of-the-road decision that requires a little more work for more flexibility.

Next, we will go into a little more detail on the first three deployment options listed above.

5.4.1. Web Archive (WAR)

As a web application archive, a WAR is deployed to an application server, such as Tomcat, Jetty, or JBoss. The structure of the archive will have a web.xml file in the WEB-INF folder, an appropriately configured symmetric.properties file in the WEB-INF/classes folder, and the required JAR files in the WEB-INF/lib folder.

A war file can be generated using the standalone installation's symadmin utility and the create-war subcommand. The command requires the name of the war file to generate. It essentially packages up the web directory, the conf directory and includes an optional properties file. Note that if a properties file is included, it will be copied to WEB-INF/classes/symmetric.properties. This is the same location conf/symmetric.properties would have been copied to. The generated war distribution uses the same web.xml as the standalone deployment.

../bin/symadmin -p my-symmetric-ds.properties create-war /some/path/to/symmetric-ds.war

The web.base.servlet.path property in symmetric.properties can be set if the SymmetricServlet needs to coexist with other Servlets. By default, the value is blank. If you set it to, say, web.base.servlet.path=sync for exmaple, registration.url would be http://server:port/sync.

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

import org.jumpmind.symmetric.SymmetricWebServer;

public class StartSymmetricEngine {

    public static void main(String[] args) throws Exception {
    
        SymmetricWebServer node = new SymmetricWebServer(
                                   "classpath://my-application.properties", "conf/web_dir");

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

}

SymmetricDS can be configured to start and run as a service in both Windows and *nix platforms.

bin\install_service.bat

bin\uninstall_service.bat

net start symmetricds
net stop symmetricds

cp bin/sym_service.initd /etc/init.d/sym_service

/sbin/service sym_service start
/sbin/service sym_service stop
/sbin/service sym_service status

/etc/init.d/sym_service start
/etc/init.d/sym_service stop
/etc/init.d/sym_service status
            

A single SymmetricDS node may be clustered across a series of instances, creating a web farm. A node might be clustered to provide load balancing and failover, for example.

When clustered, a hardware load balancer is typically used to round robin client requests to the cluster. The load balancer should be configured for stateless connections. Also, the sync.url (discussed in Section 4.1, “Node Properties”) 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 aquire 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 reaquire 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.

The db.user and db.password properties will accept encrypted text, which protects against casual observation. The text is prefixed with enc: to indicate that it is encrypted. To encrypt text, use the following command:

sym -e secret

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:

keytool -keystore keystore -storepass changeit -storetype jceks \
   -alias sym.secret -delete

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

keytool -keystore keystore -storepass changeit -storetype jceks \
   -alias sym.secret -genseckey -keyalg aes -keysize 128

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:

keytool -keystore keystore -storepass changeit -storetype jceks \
   -alias sym.secret -genseckey -keyalg idea -keysize 56 \
   -providerClass org.bouncycastle.jce.provider.BouncyCastleProvider \
   -providerPath ..\lib\bcprov-ext.jar

symadmin -providerClass org.bouncycastle.jce.provider.BouncyCastleProvider -e secret

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.

security.service.class.name=org.jumpmind.security.SecurityService

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

symadmin -p symmetric.properties -e secret

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.

<Connector port="8443" protocol="HTTP/1.1" SSLEnabled="true" 
  maxThreads="150" scheme="https" secure="true" 
  clientAuth="false" sslProtocol="TLS"
  keystoreFile="/symmetric-ds-1.x.x/security/keystore" />

SymmetricDS supports basic authentication for client and server nodes.

To configure a client node to use basic authentication when communicating with a server node, specify the following startup parameters:

The SymmetricDS Standalone Web Server also supports Basic Authentication. It can be enabled by passing the following arguments to the startup program

If the server node is deployed to Tomcat or another application server as a WAR or EAR file, then basic authentication is setup with the standard configuration in the WEB.xml file.

SymmetricDS has a pluggable architecture that can be extended. A Java class that implements the appropriate extension point interface, can implement custom logic and change the behavior of SymmetricDS to suit special needs. All supported extension points extend the IExtensionPoint interface. The available extension points are documented in the following sections.

When SymmetricDS starts up, the ExtensionPointManager searches a Spring Framework context for classes that implement the IExtensionPoint interface, then creates and registers the class with the appropriate SymmetricDS component.

If an extension point needs access to SymmetricDS services or needs to connect to the database it may implement the ISymmetricEngineAware interface in order to get a handle to the ISymmetricEngine.

The INodeGroupExtensionPoint interface may be optionally implemented to indicate that a registered extension point should only be registered with specific node groups.

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

Extensions are configured in the conf/symmetric-extensions.xml file.

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;
    }

}

public class MyFilter extends DatabaseWriterFilterAdapter {

    @Override
    public boolean beforeWrite(DataContext context, Table table, CsvData data) {
        if (table.getName().equalsIgnoreCase("CREDIT_CARD_TENDER")
                && data.getDataEventType().equals(DataEventType.INSERT)) {
            String[] parsedData = data.getParsedData(CsvData.ROW_DATA);
            // blank out credit card number
            parsedData[table.getColumnIndex("CREDIT_CARD_NUMBER")] = null;
        }
        return true;
    }
}

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:context="http://www.springframework.org/schema/context"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
           http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd">
 
    <bean id="myFilter" class="com.mydomain.MyFilter"/>
     
</beans>

By design, whenever SymmetricDS encounters an issue with a synchronization, the batch containing the error is marked as being in an error state, and all subsequent batches for that particular channel to that particular node are held and not synchronized until the error batch is resolved. SymmetricDS will retry the batch in error until the situation creating the error is resolved (or the data for the batch itself is changed).

Analyzing and resolving issues can take place on the outgoing or incoming side. The techniques for analysis are slightly different in the two cases, however, due to the fact that the node with outgoing batch data also has the data and data events associated with the batch in the database. On the incoming node, however, all that is available is the incoming batch header and data present in an incoming error table.

select * from sym_outgoing_batch where error_flag=1;

select * from sym_data where data_id in 
        (select data_id from sym_data_event where batch_id='XXXXXX');

select * from sym_data where data_id in 
        (select failed_data_id from sym_outgoing_batch where batch_id='XXXXX'
        and node_id='YYYYY');

select * from sym_incoming_batch where error_flag=1;

select * from sym_incoming_error 
            where batch_id='XXXXXX' and node_id='YYYYY';

A trigger row may be updated using SQL to change a synchronization definition. SymmetricDS will look for changes each night or whenever the Sync Triggers Job is run (see below). For example, a change to place the table price_changes into the price channel would be accomplished with the following statement:

update SYM_TRIGGER
set channel_id = 'price',
    last_update_by = 'jsmith',
    last_update_time = current_timestamp
where source_table_name = 'price_changes';

All configuration should be managed centrally at the registration node. If enabled, configuration changes will be synchronized out to client nodes. When trigger changes reach the client nodes the Sync Triggers Job will run automatically.

Centrally, the trigger changes will not take effect until the Sync Triggers Job runs. Instead of waiting for the Sync Triggers Job to run overnight after making a Trigger change, you can invoke the syncTriggers() method over JMX or simply restart the SymmetricDS server. A complete record of trigger changes is kept in the table TRIGGER_HIST, which was discussed in Section 5.2.3, “Sync Triggers Job”.

As you probably know by now, SymmetricDS stores its single configuration centrally and distributes it to all nodes. By default, a trigger-router is in effect for all nodes in the source node group or target node group. Triggers will be established on each node that is a member of the source node, and changes will be routed to all relevant nodes that are members of the target node group. If, for example, the router routes to "all" nodes, "all" means every node that is in the target node group. This is the default behavior of SymmetricDS.

Once in production, however, you will likely find you need or want to make configuration changes to triggers and routers as new features are rolled out to your network of SymmetricDS nodes. You may, for example, wish to "pilot" a new configuration, containing new synchronizations, only on specific nodes initially, and then increase the size of the pilot over time. SymmetricDS' does provide the ability to specify that only particular trigger-router combinations are applicableto particular nodes for this purpose. It does this by allowing you to define an arbitray collection of nodes, called a "grouplet", and then choosing which trigger-routers apply to the normal set of nodes (the default behavior) and which apply just to nodes in one or more "grouplets". This allows you, essentially, to filter the list of nodes that would otherwise be included as source nodes and/or target nodes. Through the use of grouplets, you can, for example, specify a subset of nodes on which a given trigger would be created. It also allows you to specify a subset of the normal set of nodes a change would be routed to. This behaviour is in addition to, and occurs before, any subsetting or filtering the router might otherwise do.

In its simplest form, a grouplet is just an arbitrary collection of nodes. To define a grouplet, you start by creating a grouplet with a unique id, a description, and a link policy, as defined in GROUPLET. To defined which nodes are members of (or are not members of) a grouplet, you provide a list of external ids of the nodes in GROUPLET_LINK. How those external ids are used varies based on the grouplet link policy. The grouplet_link_policy can be either I or E, representing an "inclusive" list of nodes or an "exclusive" list of nodes, respectively. In the case of "inclusive", you'll be listing each external id to be included in the grouplet. In the case of exclusive, all nodes will be included in the grouplet except ones which have an external id in the list of external ids.

Once you have defined your grouplet and which nodes are members of a grouplet, you can tie a grouplet to a given trigger-router through the use of TRIGGER_ROUTER_GROUPLET. If a particular trigger-router does not appear in this table, SymmetricDS behaves as normal. If, however, an entry for a particular trigger-router appears in this table, the default behavior is overridden based on the grouplet_id and applies_when settings. The grouplet id provides the node list, and the applies_when indicates whether the grouplet nodes are to be used to filter the source node list, the target node list, or both (settings are "S", "T", and "B", respectively). Nodes that survive the filtering process on as a source will have a trigger defined, and nodes that survive the filtering process as a target are eligible nodes that can be routed to.

There may be times where you find you need to re-send or re-synchronize data when the change itself was not captured. This could be needed, for example, if the data changes occurred prior to SymmetricDS placing triggers on the data tables themselves, or if the data at the destination was accidentally deleted, or for some other reason. Two approaches are commonly taken to re-send the data, both of which are discussed below.

One possible approach would be to "touch" the rows in individual tables that need re-sent. By "touch", we mean to alter the row data in such a way that SymmetricDS detects a data change and therefore includes the data change in the batching and synchronizing steps. Note that you have to change the data in some meaningful way (e.g., update a time stamp); setting a column to its current value is not sufficient (by default, if there's not an actual data value change SymmetricDS won't treat the change as something which needs synched.

A second approach would be to take advantage of SymmetricDS built-in functionality by simulating a partial "initial load" of the data. The approach is to manually create "reload" events in DATA for the necessary tables, thereby resending the desired rows for the given tables. Again, foreign key constraints must be kept in mind when creating these reload events. These reload events are created in the source database itself, and the necessary table, trigger-router combination, and channel are included to indicate the direction of synchronization.

To create a reload event, you create a DATA row, using:

By way of example, take our retail hands-on tutorial covered in Chapter 2, Quick Start Tutorial. Let's say we need to re-send a particular sales transaction from the store to corp over again because we lost the data in corp due to an overzealous delete. For the tutorial, all transaction-related tables start with sale_, use the sale_transaction channel, and are routed using the store_corp_identity router. In addition, the trigger-routers have been set up with an initial load order based on the necessary foreign key relationships (i.e., transaction tables which are "parents" have a lower initial load order than those of their "children"). An insert statement that would create the necessary "reload" events (three in this case, one for each table) would be as follows (where MISSING_ID is changed to the needed transaciton id):

       
insert into sym_data (
    select null, t.source_table_name, 'R', 'tran_id=''MISSING-ID''', null, null,
            h.trigger_hist_id, t.channel_id, '1', null, null, current_timestamp
        from sym_trigger t inner join sym_trigger_router tr on
            t.trigger_id=tr.trigger_id inner join sym_trigger_hist h on
            h.trigger_hist_id=(select max(trigger_hist_id) from sym_trigger_hist
                where trigger_id=t.trigger_id) 
    where channel_id='sale_transaction' and
        tr.router_id like 'store_corp_identity' and 
        (t.source_table_name like 'sale_%') 
    order by tr.initial_load_order asc);
    

This insert statement generates three rows, one for each configured sale table. It uses the most recent trigger history id for the corresponding table. Finally, it takes advantage of the initial load order for each trigger-router to create the three rows in the correct order (the order corresponding to the order in which the tables would have been initial loaded).

The configuration of your system as defined in the sym_* tables may be modified at runtime. By default, any changes made to the sym_* tables (with the exception of sym_node) should be made at the registration server. The changes will be synchronized out to the leaf nodes by SymmetricDS triggers that are automatically created on the tables.

If this behavior is not desired, the feature can be turned off using a parameter. Custom triggers may be added to the sym_* tables when the auto syncing feature is disabled.

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.

Monitoring and administrative operations can be performed using Java Management Extensions (JMX). SymmetricDS uses MX4J to expose JMX attributes and operations that can be accessed from the built-in web console, Java's jconsole, or an application server. By default, the web management console can be opened from the following address:

http://localhost:31416/

In order to use JConsole, you must enable the JVM. You can edit the startup scripts to set the following system parameters.

          -Dcom.sun.management.jmxremote.port=31417 
          -Dcom.sun.management.jmxremote.authenticate=false 
          -Dcom.sun.management.jmxremote.ssl=false
          

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 then name defined by the engine.name property. The default value is SymmetricDS.

The management interfaces under SymmetricDS are organized as follows:

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 32,767 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,

  -Djava.io.tmpdir=/home/.symmetricds/tmp
        

Purging is the act of cleaning up captured data that is no longer needed in SymmetricDS's runtime tables. Data is purged through delete statements by the Purge Job. Only data that has been successfully synchronized will be purged. Purged tables include:

The purge job is enabled by the start.purge.job SymmetricDS property. The timing of the three purge jobs (incoming, outgoing, and data gaps) is controlled by a cron expression as specified by the following properties: job.purge.outgoing.cron, job.purge.incoming.cron, and job.purge.datagaps.cron. The default is 0 0 0 * * *, or once per day at midnight.

Two retention period properties indicate how much history SymmetricDS will retain before purging. The purge.retention.minutes property indicates the period of history to keep for synchronization tables. The default value is 5 days. The statistic.retention.minutes property indicates the period of history to keep for statistics. The default value is also 5 days.

The purge properties should be adjusted according to how much data is flowing through the system and the amount of storage space the database has. For an initial deployment it is recommended that the purge properties be kept at the defaults, since it is often helpful to be able to look at the captured data in order to triage problems and profile the synchronization patterns. When scaling up to more nodes, it is recomended that the purge parameters be scaled back to 24 hours or less.