SymmetricDS Pro 3.6 User Guide

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 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 stand alone 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.

Changes are captured at a SymmetricDS enabled database by database triggers that are installed automatically by SymmetricDS based on configuration settings that you specify. The database triggers record data changes in the DATA table. The triggers are designed to be as noninvasive and as lightweight as possible. After SymmetricDS triggers are installed, changes are captured for any Data Manipulation Language (DML) statements performed by external applications. Note that no additional libraries or changes are needed by the applications that use the database and SymmetricDS does not have to be online for data to be captured.

Database tables that need to be replicated are configured in a series of SymmetricDS configuration tables. The configuration for the entire network of nodes is typically managed at a central node in the network, known as the registration server node. The registration server node is almost always the same node as the root node in a tree topology. When configuring “leaf” nodes, one of the start-up parameters is the URL of the registration server node. If the “leaf” node has not yet registered, it contacts the registration server and requests to join the network. Upon acceptance, the node downloads its configuration. After a node is registered, SymmetricDS can also provide an initial load of data before synchronization starts.

SymmetricDS will install or update its database triggers at start-up time and on a regular basis when a scheduled sync triggers job runs (by default, each night at midnight). The sync triggers job detects changes to your database structure or trigger configuration when deciding whether a trigger needs to be rebuilt. Optionally, the sync triggers job can be turned off and the database triggers DDL script can be generated and run by a DBA.

After changed data is inserted by the database trigger into the DATA table, it is batched and assigned to a node by the router job. Routing data refers to choosing the nodes in the SymmetricDS network to which the data should be sent. By default, data is routed to other nodes based on the node group. Optionally, characteristics of the data or of the target nodes can also be used for routing. A batch of data is a group of data changes that are transported and loaded together at the target node in a single database transaction. Batches are recorded in the OUTGOING_BATCH . Batches are node specific. DATA and OUTGOING_BATCH are linked by DATA_EVENT . The delivery status of a batch is maintained in OUTGOING_BATCH . After the data has been delivered to a remote node the batch status is changed to ‘OK.’

Data is delivered to remote nodes over HTTP or HTTPS. It can be delivered in one of two ways depending on the type of transport link that is configured between node groups. A node group can be configured to push changes to other nodes in a group or pull changes from other nodes in a group. Pushing is initiated from the push job at the source node. If there are batches that are waiting to be transported, the pushing node will reserve a connection to each target node using an HTTP HEAD request. If the reservation request is accepted, then the source node will fully extract the data for the batch. Data is extracted to a memory buffer in CSV format until a configurable threshold is reached. If the threshold is reached, the data is flushed to a file and the extraction of data continues to that file. After the batch has been extracted, it is transported using an HTTP PUT to the target node. The next batch is then extracted and sent. This is repeated until the maximum number of batches have been sent for each channel or there are no more batches available to send. After all the batches have been sent for one push, the target returns a list of the batch statuses.

Pull requests are initiated by the pull job from at the target node. A pull request uses an HTTP GET. The same extraction process that happens for a "push" also happens during a "pull."

After data has been extracted and transported, the data is loaded at the target node. Similar to the extract process, while data is being received the data loader will cache the CSV in a memory buffer until a threshold is reached. If the threshold is reached the data is flushed to a file and the receiving of data continues. After all of the data in a batch is available locally, a database connection is retrieved from the connection pool and the events that had occurred at the source database are played back against the target database.

Data is always delivered to a remote node in the order it was recorded for a specific channel. A channel is a user defined grouping of tables that are dependent on each other. Data that is captured for tables belonging to a channel is always synchronized together. Each trigger must be assigned a channel id as part of the trigger definition process. The channel id is recorded on SYM_DATA and SYM_OUTGOING_BATCH. If a batch fails to load, then no more data is sent for that channel until the failure has been addressed. Data on other channels will continue to be synchronized, however.

If a remote node is offline, the data remains recorded at the source database until the node comes back online. Optionally, a timeout can be set where a node is removed from the network. Change data is purged from the data capture tables by SymmetricDS after it has been sent and a configurable purge retention period has been reached. Unsent change data for a disabled node is also purged.

The default behavior of SymmetricDS in the case of data integrity errors is to attempt to repair the data. If an insert statement is run and there is already a row that exists, SymmetricDS will fall back and try to update the existing row. Likewise, if an update that was successful on a source node is run and no rows are found to update on the destination, then SymmetricDS will fall back to an insert on the destination. If a delete is run and no rows were deleted, the condition is simply logged. This behavior can be modified by tweaking the settings for conflict detection and resolution.

SymmetricDS was designed to use standard web technologies so it can be scaled to many clients across different types of databases. It can synchronize data to and from as many client nodes as the deployed database and web infrastructure will support. When a two-tier database and web infrastructure is maxed out, a SymmetricDS network can be designed to use N-tiers to allow for even greater scalability. At this point we have covered what SymmetricDS is and how it does its job of replicating data to many databases using standard, well understood technologies.

In practice, much of the data in a typical synchronization requires synchronization in just one direction. For example, a retail store sends its sales transactions to a central office, and the central office sends its stock items and pricing to the store. Other 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 then sent back to the store. SymmetricDS supports bi-directional or two-way table synchronization and avoids getting into update loops by only recording data changes outside of synchronization.

SymmetricDS supports the concept of channels of data. Data synchronization is defined at the table (or table subset) level, and 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 changes to the item tables to an item channel and inventory tables' changes to an inventory channel, the changes are processed independently so inventory can get through despite the large amount of item data.

After a change to the database is recorded, the SymmetricDS nodes interested in the change are notified. Change notification is configured to perform either 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. If the network configuration protects a node with a firewall, a pull configuration could allow the node to receive data changes that might otherwise be blocked using push. The frequency of the change notification is configurable and defaults to once per minute.

By default, SymmetricDS uses web-based HTTP or HTTPS in a style called Representation State Transfer (REST). It 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.

Using SymmetricDS, data can be filtered as it is recorded, extracted, and loaded.

Many databases provide a unique transaction identifier associated with the rows that are committed together as a transaction. SymmetricDS stores the transaction identifier, along with the data that changed, so it can play back the transaction exactly as it occurred originally. This means the target database maintains the same transactional integrity as its source. Support for transaction identification for supported databases is documented in the appendix of this guide.

Administration functions are exposed through Java Management Extensions (JMX) and 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 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.

Quite a few users of SymmetricDS have found that they have a need to not only synchronize database tables to remote locations, but they also have a set of files that should be synchronized. As of version 3.5 SymmetricDS now support file synchronization.

Please see Section 3.5, “File Triggers / File Synchronization” for more information.

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

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

Two lobs-related settings are also available on TRIGGER :

Occasionally, you may find that you need to capture and save away a piece of data present in another table when a trigger is firing. This data is typically needed for the purposes of determining where to 'route' the data to once routing takes place. Each trigger definition contains an optional external_select field which can be used to specify the data to be captured. Once captured, this data is available during routing in DATA 's external_data field. For these cases, place a SQL select statement which returns the data item you need for routing in external_select . An example of the use of external select can be found in Section 3.6.7, “Utilizing External Select when Routing” .

Occasionally the decision of what data to load initially results in additional triggers. These triggers, known as Dead Triggers , are configured such that they do not capture any data changes. A "dead" Trigger is one that does not capture data changes. In other words, the sync_on_insert , sync_on_update , and sync_on_delete properties for the Trigger are all set to false. However, since the Trigger is specified, it will be included in the initial load of data for target Nodes.

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

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

 
            insert into sym_trigger (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); 

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 changes 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 4.3.5, “Sync Triggers Job”.

SymmetricDS not only supports the synchronization of database tables, but it also supports the synchronization of files and folders from one node to another.

File synchronization features include:

Like database synchronization, file synchronization is configured in a series of database tables. The configuration was designed to be similar to database synchronization in order to maintain consistency and to give database synchronization users a sense of familiarity.

For database synchronization, SymmetricDS uses TRIGGER to configure which tables will capture data for synchronization and ROUTER to designate which nodes will be the source of data changes and which nodes will receive the data changes. TRIGGER_ROUTER links triggers to routers.

Likewise, for file synchronization, SymmetricDS uses FILE_TRIGGER to designate which base directories will be monitored. Each entry in FILE_TRIGGER designates one base directory to monitor for changes on the source system. The columns on FILE_TRIGGER provide additional settings for choosing specific files in the base directory that will be monitored, and whether to recurse into subdirectories, etc. File triggers are linked to routers by FILE_TRIGGER_ROUTER. The file trigger router not only links the source and the target node groups, but it also optionally provides the ability to override the base directory name at the target. FILE_TRIGGER_ROUTER also provides a flag that indicates if the target node should be seeded with the files from the source node during SymmetricDS's initial load process.

Not only is file synchronization configured similar to database synchronization, but it also operates in a very similar way. The file system is monitored for changes via a background job that tracks the file system changes (this parallels the use of triggers to monitor for changes when synchronizing database changes). When a change is detected it is written to the FILE_SNAPSHOT table. The file snapshot table represents the most recent known state of the monitored files. The file snapshot table has a SymmetricDS database trigger automatically installed on it so that when it is updated the changes are captured by SymmetricDS on an internal channel named filesync.

The changes to FILE_SNAPSHOT are then routed and batched by a file-synchronization-specific router that delegates to the configured router based on the FILE_TRIGGER_ROUTER configuration. The file sync router can make routing decisions based on the column data of the snapshot table, columns which contain attributes of the file like the name, path, size, and last modified time. Both old and new file snapshot data are also available. The router can, for example, parse the path or name of the file and use it as the node id to route to.

Batches of file snapshot changes are stored on the filesync channel in OUTGOING_BATCH. The existing SymmetricDS pull and push jobs ignore the filesync channel. Instead, they are processed by file-synchronization-specific push and pull jobs.

When transferring data, the file sync push and pull jobs build a zip file dynamically based on the batched snapshot data. The zip file contains a directory per batch. The directory name is the batch_id. A sync.bsh Bean Shell script is generated and placed in the root of each batch directory. The Bean Shell script contains the commands to copy or delete files at their file destination from an extracted zip in the staging directory on the target node. The zip file is downloaded in the case of a pull, or, in the case of a push, is uploaded as an HTTP multi-part attachment. Outgoing zip files are written and transferred from the outgoing staging directory. Incoming zip files are staged in the filesync_incoming staging directory by source node id. The filesync_incoming/{node_id} staging directory is cleared out before each subsequent delivery of files.

The acknowledgement of a batch happens the same way it is acknowledged in database synchronization. The client responds with an acknowledgement as part of the response during a file push or pull.

There are two types of Bean Shell scripts that can be leveraged to customize file synchronization behavior: before_copy_script and after_copy_script.

Each of these scripts have access to local variables that can be read or set to affect the behavior of copying files.

The simplest router is a router that sends all the data that is captured by its associated triggers to all the nodes that belong to the target node group defined in the router. A router is defined as a row in the ROUTER table. It is then linked to triggers in the TRIGGER_ROUTER table.

The following SQL statement defines a router that will send data from the 'corp' group to the 'store' group.

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

The following SQL statement maps the 'corp-2-store' router to the item trigger.

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

Sometimes requirements may exist that require data to be routed based on the current value or the old value of a column in the table that is being routed. Column routers are configured by setting the router_type column on the ROUTER table to column and setting the router_expression column to an equality expression that represents the expected value of the column.

The first part of the expression is always the column name. The column name should always be defined in upper case. The upper case column name prefixed by OLD_ can be used for a comparison being done with the old column data value.

The second part of the expression can be a constant value, a token that represents another column, or a token that represents some other SymmetricDS concept. Token values always begin with a colon (:).

Consider a table that needs to be routed to all nodes in the target group only when a status column is set to 'READY TO SEND.' The following SQL statement will insert a column router to accomplish that.

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

Consider a table that needs to be routed to all nodes in the target group only when a status column changes values. The following SQL statement will insert a column router to accomplish that. Note the use of OLD_STATUS, where the OLD_ prefix gives access to the old column value.

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

Consider a table that needs to be routed to only nodes in the target group whose STORE_ID column matches the external id of a node. The following SQL statement will insert a column router to accomplish that.

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

Attributes on a NODE that can be referenced with tokens include:

Captured EXTERNAL_DATA is also available for routing as a virtual column.

Consider a table that needs to be routed to a redirect node defined by its external id in the REGISTRATION_REDIRECT table. The following SQL statement will insert a column router to accomplish that.

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

More than one column may be configured in a router_expression. When more than one column is configured, all matches are added to the list of nodes to route to. The following is an example where the STORE_ID column may contain the STORE_ID to route to or the constant of ALL which indicates that all nodes should receive the update.

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

The NULL keyword may be used to check if a column is null. If the column is null, then data will be routed to all nodes who qualify for the update. This following is an example where the STORE_ID column is used to route to a set of nodes who have a STORE_ID equal to their EXTERNAL_ID, or to all nodes if the STORE_ID is null.

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

A lookup table may contain the id of the node where data needs to be routed. This could be an existing table or an ancillary table that is added specifically for the purpose of routing data. Lookup table routers are configured by setting the router_type column on the ROUTER table to lookuptable and setting a list of configuration parameters in the router_expression column.

Each of the following configuration parameters are required.

Note that the lookup table will be read into memory and cached for the duration of a routing pass for a single channel.

Consider a table that needs to be routed to a specific store, but the data in the changing table only contains brand information. In this case, the STORE table may be used as a lookup table.

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

Sometimes routing decisions need to be made based on data that is not in the current row being synchronized. A 'subselect' router can be used in these cases. A 'subselect' is configured with a router_expression that is a SQL select statement which returns a result set of the node ids that need routed to. Column tokens can be used in the SQL expression and will be replaced with row column data. The overhead of using this router type is high because the 'subselect' statement runs for each row that is routed. It should not be used for tables that have a lot of rows that are updated. It also has the disadvantage that if the data being relied on to determine the node id has been deleted before routing takes place, then no results would be returned and routing would not happen.

The router_expression you specify is appended to the following SQL statement in order to select the node ids:

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

As you can see, you have access to information about the node currently under consideration for routing through the 'c' alias, for example c.external_id . There are two node-related tokens you can use in your expression:

Column names representing data for the row in question are prefixed with a colon as well, for example: :EMPLOYEE_ID , or :OLD_EMPLOYEE_ID . Here, the OLD_ prefix indicates the value before the change in cases where the old data has been captured.

For an example, consider the case where an Order table and an OrderLineItem table need to be routed to a specific store. The Order table has a column named order_id and STORE_ID. A store node has an external_id that is equal to the STORE_ID on the Order table. OrderLineItem, however, only has a foreign key to its Order of order_id. To route OrderLineItems to the same nodes that the Order will be routed to, we need to reference the master Order record.

There are two possible ways to solve this in SymmetricDS. One is to configure a 'subselect' router_type on the ROUTER table, shown below (The other possible approach is to use an external_select to capture the data via a trigger for use in a column match router, demonstrated in Section 3.6.7, “Utilizing External Select when Routing” ).

Our solution utilizing subselect compares the external id of the current node with the store id from the Order table where the order id matches the order id of the current row being routed:

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

As a final note, please note in this example that the parent row in Order must still exist at the moment of routing for the child rows (OrderLineItem) to route, since the select statement is run when routing is occurring, not when the change data is first captured.

When more flexibility is needed in the logic to choose the nodes to route to, then the a scripted router may be used. The currently available scripting language is Bean Shell. Bean Shell is a Java-like scripting language. Documentation for the Bean Shell scripting language can be found at http://www.beanshell.org .

The router_type for a Bean Shell scripted router is 'bsh'. The router_expression is a valid Bean Shell script that:

Also bound to the script evaluation is a list of nodes . The list of nodes is a list of eligible org.jumpmind.symmetric.model.Node objects. The current data column values and the old data column values are bound to the script evaluation as Java object representations of the column data. The columns are bound using the uppercase names of the columns. Old values are bound to uppercase representations that are prefixed with 'OLD_'.

If you need access to any of the SymmetricDS services, then the instance of org.jumpmind.symmetric.ISymmetricEngine is accessible via the bound engine variable.

In the following example, the node_id is a combination of STORE_ID and WORKSTATION_NUMBER, both of which are columns on the table that is being routed.

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

The same could also be accomplished by simply returning the node id. The last line of a bsh script is always the return value.

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

The following example will synchronize to all nodes if the FLAG column has changed, otherwise no nodes will be synchronized. Note that here we make use of OLD_, which provides access to the old column value.

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

The next example shows a script that iterates over each eligible node and checks to see if the trimmed value of the column named STATION equals the external_id.

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

This router audits captured data by recording the change in an audit table that the router creates and keeps up to date (as long as auto.config.database is set to true.) The router creates a table named the same as the table for which data was captured with the suffix of _AUDIT. It will contain all of the same columns as the original table with the same data types only each column is nullable with no default values.

Three extra "AUDIT" columns are added to the table:

The following is an example of an audit router

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

The audit router captures data for a group link. For the audit router to work it must be associated with a node_group_link with an action of type 'R'. The 'R' stands for 'only routes to'. In the above example, we refer to a 'corp to local' group link. Here, local is a new node_group created for the audit router. No nodes belong to the 'local' node_group. If a trigger linked to an audit router fires on the corp node, a new audit table will be created at the corp node with the new data inserted.

There may be times when you wish to route based on a piece of data that exists in a table other than the one being routed. The approach, first discussed in Section 3.6.4, “Subselect Router” , is to utilize an external_select to save away data in external_data , which can then be referenced during routing.

Reconsider subselect's Order / OrderLineItem example (found in Section 3.6.4, “Subselect Router” ), where routing for the line item is accomplished by linking to the "header" Order row. As an alternate way of solving the problem, we will now use External Select combined with a column match router.

In this version of the solution, the STORE_ID is captured from the Order table in the EXTERNAL_DATA column when the trigger fires. The router is configured to route based on the captured EXTERNAL_DATA to all nodes whose external id matches the captured external data.

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

The following variables can be used with the external select:

The advantage of this approach over the 'subselect' approach is that it guards against the (somewhat unlikely) possibility that the master Order table row might have been deleted before routing has taken place. This external select solution also is a bit more efficient than the 'subselect' approach, although the triggers produced do run the extra external_select SQL inline with application database updates.

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:

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

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

Three additional configuration settings are also defined at the source-target table level: the order of the transformations, the behavior when deleting, and whether an update should always be attempted first. More specifically,

For each transformation defined in TRANSFORM_TABLE , the columns to be transformed (and how they are transformed) are defined in TRANSFORM_COLUMN . This column-level table typically has several rows for each transformation id, each of which defines the source column name, the target column name, as well as the following details:

There are several pre-defined transform types available in SymmetricDS. Additional ones can be defined by creating and configuring an extension point which implements the IColumnTransform interface. The pre-defined transform types include the following (the transform_type entry is shown in parentheses):

SymmetricDS stores its load filter configuration in a single table called LOAD_FILTER . The load filter table allows you to specify the following:

As part of the bean shell load filters, SymmetricDS provides certain variables for use in the bean shell script. Those variables include:

The following is an example of a load filter that watches a table named TABLE_TO_WATCH being loaded from the Server Node Group to the Client Node Group for inserts or updates, and performs an initial load on a table named "TABLE_TO_RELOAD" for KEY_FIELD on the reload table equal to a column named KEY_FIELD on the TABLE_TO_WATCH table.

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

At this point, an example would probably be useful. Picture the case where you have 100 retail stores (each containing one database, and each a member of the "store" node group) and a central office database (external id of corp, and a member of the "corp" node group ). You wish to pilot two new trigger and routers for a new feature on your point-of-sale software (one which moves data from corp to store, and one which moves data from store to corp), but you only want the triggers to be installed on 10 specific stores that represent your "pilot" stores. In this case, the simplest approach would be to define a grouplet with, say, a grouplet id of "pilot". We'd use a grouplet link policy of "inclusive", and list each of the 10 external ids in the GROUPLET_LINK table.

For the trigger-router meant to send data from corp to store, we'd create an entry in TRIGGER_ROUTER_GROUPLET for our grouplet id of "pilot", and we'd specify "T" (target) as the applies-when setting. In this way, the source node list is not filtered, but the target node list used during routing will filter the potential target nodes to just our pilot stores. For the trigger-router meant to send data from a pilot store back to corp, we would have the grouplet apply when the node is in the source node list (i.e., applies_when will be "S"). This will cause the trigger to only be created for stores in the pilot list and not other stores.

An important thing to mention in this example: Since your grouplet only included the store nodes, you can't simply specify "both" for the applies when setting. For the corp-to-store trigger, for example, if you had said "both", no trigger would have been installed in corp since the grouplet nodes represent all possible source nodes as well as target nodes, and "corp" is not in the list! The same is true for the store to corp trigger-router as well. You could, however, use "both" as the applies when if you had included the "corp" external id in with the list of the 10 pilot store external ids.

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.

An initial load is the process of seeding tables at a target node with data from its parent node. When a node connects and data is extracted, after it is registered and if an initial load was requested, each table that is configured to synchronize to the target node group will be given a reload event in the order defined by the end user. A SQL statement is run against each table to get the data load that will be streamed to the target node. The selected data is filtered through the configured router for the table being loaded. If the data set is going to be large, then SQL criteria can optionally be provided to pare down the data that is selected out of the database.

An initial load cannot occur until after a node is registered. An initial load is requested by setting the initial_load_enabled column on NODE_SECURITY to 1 on the row for the target node in the parent node's database. You can configure SymmetricDS to automatically perform an initial load when a node registers by setting the parameter auto.reload to true. Regardless of how the initial load is initiated, the next time the source node routes data, reload batches will be inserted. At the same time reload batches are inserted, all previously pending batches for the node are marked as successfully sent.

SymmetricDS recognizes that an initial load has completed when the initial_load_time column on the target node is set to a non-null value.

An initial load is accomplished by inserting reload batches in a defined order according to the initial_load_order column on TRIGGER_ROUTER . If the initial_load_order column contains a negative value the associated table will NOT be loaded. If the initial_load_order column contains the same value for multiple tables, SymmetricDS will attempt to order the tables according to foreign key constraints. If there are cyclical constraints, then foreign keys might need to be turned off or the initial load will need to be manually configured based on knowledge of how the data is structured.

Initial load data is always queried from the source database table. All data is passed through the configured router to filter out data that might not be targeted at a node.

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:

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 transaction 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. 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).

After data is captured in the DATA table, it is routed to specific nodes in batches by the Route Job . It is a single background task that inserts into DATA_EVENT and OUTGOING_BATCH .

The job processes each enabled channel, one at a time, collecting a list of data ids from DATA which have not been routed (see Section 4.3.1.1, “Data Gaps” for much more detail about this step), up to a limit specified by the channel configuration ( max_data_to_route , on CHANNEL ). The data is then batched based on the batch_algorithm defined for the channel. Note that, for the default and transactional algorithm, there may actually be more than max_data_to_route included depending on the transaction boundaries. The mapping of data to specific nodes, organized into batches, is then recorded in OUTGOING_BATCH with a status of "RT" in each case (representing the fact that the Route Job is still running). Once the routing algorithms and batching are completed, the batches are organized with their corresponding data ids and saved in DATA_EVENT . Once DATA_EVENT is updated, the rows in OUTGOING_BATCH are updated to a status of New "NE".

The route job will respect the max_batch_size on OUTGOING_BATCH . If the max batch size is reached before the end of a database tranaction and the batch algorithm is set to something other than nontransactional the batch may exceed the specified max size.

The route job delegates to a router defined by the router_type and configured by the router_expression in the ROUTER table. Each router that has a source_node_group_id that matches the current node's source node group id and is linked to the TRIGGER that captured the data gets an opportunity to choose a list of nodes the data should be sent to. Data can only be routed to nodes that belong to the router's target_node_group_id .

After database-change data is routed, it awaits transport to the target nodes. Transport can occur when a client node is configured to pull data or when the host node is configured to push data. These events are controlled by the push and the pull jobs . When the start.pull.job SymmetricDS property is set to true , the frequency that data is pulled is controlled by the job.pull.period.time.ms . When the start.push.job SymmetricDS property is set to true , the frequency that data is pushed is controlled by the job.push.period.time.ms .

Data is extracted by channel from the source database's DATA table at an interval controlled by the extract_period_millis column on the CHANNEL table. The last_extract_time is always recorded, by channel, on the NODE_CHANNEL_CTL table for the host node's id. When the Pull and Push Job run, if the extract period has not passed according to the last extract time, then the channel will be skipped for this run. If the extract_period_millis is set to zero, data extraction will happen every time the jobs run.

The maximum number of batches to extract per synchronization is controlled by max_batch_to_send on the CHANNEL table. There is also a setting that controls the max number of bytes to send in one synchronization. If SymmetricDS has extracted the more than the number of bytes configured by the transport.max.bytes.to.sync parameter, then it will finish extracting the current batch and finish synchronization so the client has a chance to process and acknowlege the "big" batch. This may happen before the configured max number of batches has been reached.

Both the push and pull jobs can be configured to push and pull multiple nodes in parallel. In order to take advantage of this the pull.thread.per.server.count or push.thread.per.server.count should be adjusted (from their default value of 10) to the number to the number of concurrent push/pulls you want to occur per period on each SymmetricDS instance. Push and pull activity is recorded in the NODE_COMMUNICATION table. This table is also used to lock push and pull activity across multiple servers in a cluster.

SymmetricDS also provides the ability to configure windows of time when synchronization is allowed. This is done using the NODE_GROUP_CHANNEL_WND table. A list of allowed time windows can be specified for a node group and a channel. If one or more windows exist, then data will only be extracted and transported if the time of day falls within the window of time specified. The configured times are always for the target node's local time. If the start_time is greater than the end_time , then the window crosses over to the next day.

All data loading may be disabled by setting the dataloader.enable property to false. This has the effect of not allowing incoming synchronizations, while allowing outgoing synchronizations. All data extractions may be disabled by setting the dataextractor.enable property to false. These properties can be controlled by inserting into the root server's PARAMETER table. These properties affect every channel with the exception of the 'config' channel.

Node communication over HTTP is represented in the following figure.

Figure 4.1. Node Communication

The File Sync Push and Pull jobs (introduced in version 3.5) are responsible for synchronizing file changes. These jobs work with batches on the filesync channel and create ZIP files of changed files to be sent and applied on other nodes. The parameters job.file.sync.push.period.time.ms and job.file.sync.pull.period.time.ms control how often the jobs runs, which default to every 60 seconds. See also Section 4.3, “Jobs” and Section 3.5.2, “Operation”.

The File System Tracker job (introduced in version 3.5) is responsible for monitoring and recording the events of files being created, modified, or deleted. It records the current state of files to the FILE_SNAPSHOT table. The parameter job.file.sync.tracker.cron controls how often the job runs, which defaults to every 5 minutes. See also Section 4.3, “Jobs” and Section 3.5, “File Triggers / File Synchronization”.

SymmetricDS examines the current configuration, corresponding database triggers, and the underlying tables to determine if database triggers need created or updated. The change activity is recorded on the TRIGGER_HIST table with a reason for the change. The following reasons for a change are possible:

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

The process of examining triggers and rebuilding them is automatically run during startup and each night by the SyncTriggersJob. The user can also manually run the process at any time by invoking the syncTriggers() method over JMX.

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.

The first step in analyzing the cause of a failed batch is to locate information about the data in the batch, starting with OUTGOING_BATCH To locate batches in error, use:

select * from sym_outgoing_batch where error_flag=1;

Several useful pieces of information are available from this query:

To get a full picture of the batch, you can query for information representing the complete list of all data changes associated with the failed batch by joining DATA and DATA_EVENT, such as:

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

where XXXXXX is the batch id of the failing batch.

This query returns a wealth of information about each data change in a batch, including:

More importantly, if you narrow your query to just the failed data id you can determine the exact data change that is causing the failure:

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

where XXXXXX is the batch id and YYYYY is the node id of the batch that is failing.

The queries above usually yield enough information to be able to determine why a particular batch is failing. Common reasons a batch might be failing include:

Once you have decided upon the cause of the issue, you'll have to decide the best course of action to fix the issue. If, for example, the problem is due to a database schema mismatch, one possible solution would be to alter the destination database in such a way that the SQL error no longer occurs. Whatever approach you take to remedy the issue, once you have made the change, on the next push or pull SymmetricDS will retry the batch and the channel's data will start flowing again.

If you have instead decided that the batch itself is wrong, or does not need synchronized, or you wish to remove a particular data change from a batch, you do have the option of changing the data associated with the batch directly.

Now that you've read the warning, if you still want to change the batch data itself, you do have several options, including:

Analysis using an incoming batch is different than that of outgoing batches. For incoming batches, you will rely on two tables, INCOMING_BATCH and INCOMING_ERROR. The first step in analyzing the cause of an incoming failed batch is to locate information about the batch, starting with INCOMING_BATCH To locate batches in error, use:

select * from sym_incoming_batch where error_flag=1;

Several useful pieces of information are available from this query:

For incoming batches, we do not have data and data event entries in the database we can query. We do, however, have a table, INCOMING_ERROR, which provides some information about the batch.

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

where XXXXXX is the batch id and YYYYY is the node id of the failing batch.

This query returns a wealth of information about each data change in a batch, including:

For batches in error, from the incoming side you'll also have to decide the best course of action to fix the issue. Incoming batch errors that are in conflict can by fixed by taking advantage of two columns in INCOMING_ERROR which are examined each time batches are processed. The first column, resolve_data if filled in will be used in place of row_data. The second column, resolve_ignore if set will cause this particular data item to be ignored and batch processing to continue. This is the same two columns used when a manual conflict resolution strategy is chosen, as discussed in Section 3.7.1, “Conflict Detection and Resolution”.

SymmetricDS allows tables to be synchronized bi-directionally. Note that an outgoing synchronization does not process changes during an incoming synchronization on the same node unless the trigger was created with the sync_on_incoming_batch flag set. If the sync_on_incoming_batch flag is set, then update loops are prevented by a feature that is available in most database dialects. More specifically, during an incoming synchronization the source node_id is put into a database session variable that is available to the database trigger. Data events are not generated if the target node_id on an outgoing synchronization is equal to the source node_id.

By default, only the columns that changed will be updated in the target system.

Conflict resolution strategies can be configured for specific links and/or sets of tables.

There may be scenarios where data needs to flow through multiple tiers of nodes that are organized in a tree-like network with each tier requiring a different subset of data. For example, you may have a system where the lowest tier may be a computer or device located in a store. Those devices may connect to a server located physically at that store. Then the store server may communicate with a corporate server for example. In this case, the three tiers would be device, store, and corporate. Each tier is typically represented by a node group. Each node in the tier would belong to the node group representing that tier.

A node can only pull and push data to other nodes that are represented in the node's NODE table and in cases where that node's sync_enabled column is set to 1. Because of this, a tree-like hierarchy of nodes can be created by having only a subset of nodes belonging to the same node group represented at the different branches of the tree.

If auto registration is turned off, then this setup must occur manually by opening registration for the desired nodes at the desired parent node and by configuring each node's registration.url to be the parent node's URL. The parent node is always tracked by the setting of the parent's node_id in the created_at_node_id column of the new node. When a node registers and downloads its configuration it is always provided the configuration for nodes that might register with the node itself based on the Node Group Links defined in the parent node.

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

A Java application with the SymmetricDS Java Archive (JAR) library on its classpath can use the SymmetricWebServer to start the 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();
    }

}

This example starts the SymmetricDS server on port 8080. The configuration properties file, my-application.properties, is packaged in the application to provide properties that override the SymmetricDS default values. The second parameter to the constructor points to the web directory. The default location is ../web. In this example the web directory is located at conf/web_dir. The web.xml is expected to be found at conf/web_dir/WEB-INF/web.xml.

The sym command line utility starts a standalone web server with SymmetricDS pre-deployed. The standalone server uses an embedded instance of the Jetty application server to handle web requests. The web server can be configured using command line options or the web server can be configured by changing properties in the conf/symmetric-server.properties file.

The following example starts the SymmetricDS server on port 8080 with the startup properties found in the root.properties file.

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

Even though the port and properties settings can be passed in on the command line, the preferred configuration approach is to put each hosted node's properties file in the engines directory and to modify port settings and enable secure mode using the conf/symmetric-server.properties.

It is also suggested that SymmetricDS be configured to run as a service according to the instructions for your platform as documented in the following section.

To install the service, run the following command as Administrator:

bin\sym_service.bat install

Most configuration changes do not require the service to be re-installed. To un-install the service, run the following command as Administrator:

bin\sym_service.bat uninstall

To start and stop the service manually, run the following commands as Administrator:

bin\sym_service.bat start
bin\sym_service.bat stop

An init script is written to the system /etc/init.d directory. Symbolic links are created for starting on run levels 2, 3, and 5 and stopping on run levels 0, 1, and 6. To install the script, running the following command as root:

bin/sym_service install

Most configuration changes do not require the service to be re-installed. To un-install the service, run the following command as root:

bin/sym_service uninstall

To start and stop the service manually, run the following commands:

bin/sym_service start
bin/sym_service stop

The "sym" launch command uses Jetty as an embedded web server. Using command line options, the web server can be told to listen for HTTP, HTTPS, or both.

sym --port 8080 --server

sym --secure-port 8443 --secure-server

sym --port 8080 --secure-port 8443 --mixed-server

If you deploy SymmetricDS to Apache Tomcat, it can be secured by editing the TOMCAT_HOME/conf/server.xml configuration file. There is already a line that can be uncommented and changed to the following:

<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" />

When SymmetricDS connects to a URL with HTTPS, Java checks the validity of the certificate using the built-in trusted keystore located at JRE_HOME/lib/security/cacerts. The "sym" launcher command overrides the trusted keystore to use its own trusted keystore instead, which is located at security/cacerts. This keystore contains the certificate aliased as "sym" for use in testing and easing deployments. The trusted keystore can be overridden by specifying the javax.net.ssl.trustStore system property.

When SymmetricDS is run as a secure server with the "sym" launcher, it accepts incoming requests using the key installed in the keystore located at security/keystore. The default key is provided for convenience of testing, but should be re-generated for security.

To generate new keys and install a server certificate, use the following steps:

To use the preconfigured bulk data loaders, you set the data_loader_type on a channel to one of the following:

Tables that should be data loaded should be configured to use this channel. Many times, a reload channel will be set to bulk load to increase the performance of an initial load.

The MongoDB data loader maps relational database rows to MongoDB documents in collections. To use the preconfigured MongoDB data loader, you set the data_loader_type to MongoDB on a channel. Tables that should be synchronized to MongoDB should be configured to use this channel. In order to point it to a MongoDB instance set the following properties in the engines properties file.

mongodb.username=xxxx
mongodb.password=xxxx
mongodb.host=xxxx
mongodb.port=xxxx
mongodb.default.databasename=default
          

By default, the catalog or schema passed by SymmetricDS will be used for the MongoDB database name. The table passed by SymmetricDS will be used as the MongoDB collection name. If the catalog or schema are not set, the default database name property is used as the database name.

The _id of the MongoDB document will be the primary key of the database record. If the table has a composite primary key, then the _id will be an embedded document that has name value pairs of the composite key. The body of the document will be name value pairs of the table column name and table row value.

SymmetricDS uses the MongoDB Java Driver to upsert documents.

SymmetricDS transforms can be used to transform the data. If a complex mapping is required that is not supported by transforms, then the IDBObjectMapper can be implemented and a new MongoDataLoaderFactory can be wired up as an extension point.

Parameter values can be specified in code using a parameter filter. Note that there can be only one parameter filter per engine instance. The IParameterFilter replaces the deprecated IRuntimeConfig from prior releases.

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

}

Data can be filtered or manipulated before it is loaded into the target database. A filter can change the data in a column, save it somewhere else or do something else with the data entirely. It can also specify by the return value of the function call that the data loader should continue on and load the data (by returning true) or ignore it (by returning false). One possible use of the filter, for example, might be to route credit card data to a secure database and blank it out as it loads into a less-restricted reporting database.

A DataContext is passed to each of the callback methods. A new context is created for each synchronization. The context provides a mechanism to share data during the load of a batch between different rows of data that are committed in a single database transaction.

The filter also provides callback methods for the batch lifecycle. The DatabaseWriterFilterAdapter may be used if not all methods are required.

A class implementing the IDatabaseWriterFilter interface is injected onto the DataLoaderService in order to receive callbacks when data is inserted, updated, or deleted.

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

The filter class should be specified in conf/symmetric-extensions.xml as follows.

<?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>

Implement this extension point to override how errors are handled. You can use this extension point to ignore rows that produce foreign key errors.

Implement this extension point to provide a different implementation of the org.jumpmind.symmetric.io.data.IDataWriter that is used by the SymmetricDS data loader. Data loaders are configured for a channel. After this extension point is registered it can be activated for a CHANNEL by indicating the data loader name in the data_loader_type column.

SymmetricDS has two out of the box extensions of IDataLoaderFactory already implemented in its PostgresBulkDataLoaderFactory and OracleBulkDataLoaderFactory classes. These extension points implement bulk data loading capabilities for Oracle, Postgres and Greenplum dialects. See Appendix C. Database Notes for details.

Another possible use of this extension point is to route data to a NOSQL data sink.

Implement this extension point to receive callback events when a batch is acknowledged. The callback for this listener happens at the point of extraction.

Implement this extension point to listen in and take action before or after a reload is requested for a Node. The callback for this listener happens at the point of extraction.

This extension point is used to select an appropriate URL based on the URI provided in the sync_url column of sym_node.

To use this extension point configure the sync_url for a node with the protocol of ext://beanName. The beanName is the name you give the extension point in the extension xml file.

This extension point allows custom column transformations to be created. There are a handful of out-of-the-box implementations. If any of these do not meet the column transformation needs of the application, then a custom transform can be created and registered. It can be activated by referencing the column transform's name transform_type column of TRANSFORM_COLUMN

This extension point allows SymmetricDS users to implement their own algorithms for how node ids and passwords are generated or selected during the registration process. There may be only one node creator per SymmetricDS instance (Please note that the node creator extension has replaced the node generator extension).

Implement this extension point to get status callbacks during trigger creation.

Implement this extension point and set the name of the Spring bean on the batch_algorithm column of the Channel table to use. This extension point gives fine grained control over how a channel is batched.

Implement this extension point and set the name of the Spring bean on the router_type column of the Router table to use. This extension point gives the ability to programmatically decide which nodes data should be routed to.

Implement this extension point to get callbacks during the heartbeat job.

Implement this extension point to get callbacks for offline events on client nodes.

Implement this extension point to get callbacks for offline events detected on a server node during monitoring of client nodes.

Implement this extension point to intercept the saving and rendering of the node password.