# SymmetricDS 3.16 Tutorials

version 3.16.0

Copyright © 2007-2025 JumpMind, Inc

Version 3.16.0

Permission to use, copy, modify, and distribute this SymmetricDS Tutorials Guide for any purpose and without fee is hereby granted in perpetuity, provided that the above copyright notice and this paragraph appear in all copies.

## 1. Demo

This guide explains how to quickly setup a demonstration of SymmetricDS to help you understand what a working system looks like.

Three nodes, which represent three embedded databases, will be installed. One node will represent a centralized corporate database and the other two nodes will represent two individual retail store databases.

### 1.1. Installation

For information on how to install SymmetricDS, please refer to the Installation section of the SymmetricDS User Guide.

#### 1.1.1. Creating and Starting

Once SymmetricDS has been installed, we will need to populate the database with the demo configuration and sym tables. To do this, execute the following steps:

- Open a command prompt and navigate to the `samples` subdirectory of your SymmetricDS installation.

- Copy the `corp-000.properties`, `store-001.properties` and `store-002.properties` files from the `samples/` folder to the `engines/` folder. Each properties file in the engines directory represents a SymmetricDS node.

      cp corp-000.properties store-001.properties store-002.properties ../engines

- Create the sample tables for items, prices, and sales, in the root database by executing the following command:

      ../bin/dbimport --engine corp-000 --format XML --alter-case create_sample.xml

- Create the SymmetricDS tables.

      ../bin/symadmin --engine corp-000 create-sym-tables

- Load the SymmetricDS configuration and sample data into the root node database.

      ../bin/dbimport --engine corp-000 insert_sample.sql

Start the SymmetricDS instance. All three nodes run in the same instance. From the command prompt, navigate to the SymmetricDS home directory and run the following command.

    bin/sym

| SymmetricDS is normally deployed as a service. For more information about deploying SymmetricDS as a service please refer to the SymmetricDS User Guide.

The log output will go to both the command line and the `logs/symmetric.log`. Log output for each node can be identified by the name of the node in brackets.

    INFO [corp-000] [AbstractSymmetricEngine] [symmetric-engine-startup-0] Started SymmetricDS

- Watch the logging output of the store nodes to see it successfully register with the corp node. The stores are configured to attempt registration at a random time interval up to every minute.

#### 1.1.2. Initial Load

An initial load is the process of seeding tables at a target node with data from a source node. Instead of capturing data, data is selected from the source table using a SQL statement and then it is streamed to the client.

The store nodes were pre-configured to do an initial load after registration. However, it is worth noting that Initial Loads can be sent using the following commands:

    bin/symadmin --engine corp-000 reload-node 001

    bin/symadmin --engine corp-000 reload-node 002

#### 1.1.3. What’s Created

Please verify the databases by logging in and listing the tables. You can verify using the following command:

    bin/dbsql --engine corp-000

From here, you can:

- Find the item tables that sync from root to client (that is, from corp to store): item and item_selling_price.

- Find the sales tables that sync from store to corp: sale_transaction and sale_return_line_item.

- Validate the corp item tables have sample data.

### 1.2. Pulling Data

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.

#### 1.2.1. Create Data

Create data in corp for all stores to pull.

Open an interactive SQL session with the corp database and add a new item for sale, with different prices at store 001 and store 002.

    insert into item (item_id, name) values (110000055, 'Soft Drink');
    insert into item_selling_price (item_id, store_id, price)
            values (110000055, '001', 0.65);
    insert into item_selling_price (item_id, store_id, price)
            values (110000055, '002', 1.00);

Watch the logging output of both nodes to see the data transfer. The store is configured to push data to the corp node every minute.

#### 1.2.2. Verify Outgoing Batches

Open an interactive SQL session with the corp node database and run the following query to see that the batch sent (status of `OK`).

    select * from sym_outgoing_batch order by batch_id desc

#### 1.2.3. Verify Incoming Batches

Open an interactive SQL session with the store node database and run the following query to see that the batch was received (status of `OK`).

    select * from sym_incoming_batch order by batch_id desc

#### 1.2.4. Verify SQL

Verify the change directly in a store database.

    select * from item_selling_price

| Make sure you have selected either store-001 or store-002 to run your query against.

### 1.3. Pushing Data

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

#### 1.3.1. Create Data

Create data in a store to be pushed to corp.

Open an interactive SQL session with the store node database and add a new sale to the store node database.

    insert into sale_transaction (tran_id, store_id, workstation, day, seq)
            values (1000, '001', '3', '2014-03-21', 100);

    insert into sale_return_line_item (tran_id, item_id, price, quantity)
            values (1000, 110000055, 0.65, 1);

Watch the logging output of both nodes to see the data transfer. The store is configured to push data to the corp node every minute.

#### 1.3.2. Verify Outgoing Batches

Open an interactive SQL session with the store node database and run the following query to see that the batch sent (status of `OK`).

    select * from sym_outgoing_batch order by batch_id desc

#### 1.3.3. Verify Incoming Batches

Open an interactive SQL session with the corp node database and run the following query to see that the batch was received (status of `OK`).

    select * from sym_incoming_batch order by batch_id desc

#### 1.3.4. Verify SQL

Verify the change directly in the corp database.

    select * from sale_transaction;

    select * from sale_return_line_item;

| Make sure you have selected corp-000 to run your query against.

### 1.4. Finish

| Congratulations! You have completed the Demo.

Continue to browse through the demo configuration to see how basic triggers, routers, group links and more are setup. When you are ready, uninstall the demo and begin your own configuration.

## 2. Quick Start

| To see a demonstration of a preconfigured SymmetricDS environment see the Demo tutorial.

This guide will walk you through the minimum steps required to setup data synchronization through a single installation of SymmetricDS.

Steps covered

1. Install

2. Connect Source Database

3. Configure

4. Connect Target Database

### 2.1. Install

Download and install SymmetricDS from the SymmetricDS.org website.

| For complete installation instructions click here

### 2.2. Connect Source Database

Create a property file named `xxxxx.properties` in the engines folder of your SymmetricDS installation. Replace the `xxxxx` with a name for your node (homeoffice, central, server, etc).

| A sample database is needed for each corresponding node that is created.

Properties

1. engine.name={an arbitrary name for the node}

       Example: engine.name=centralNode

2. group.id={this must match the name of a group inserted in step 3}

       Example: group.id=corp

3. external.id={id for the node}

       Example: external.id=000

4. sync.url=http://{hostname}:{port}/sync/{engine.name}

       Example: sync.url=http://localhost:31415/sync/centralNode

5. registration.url={leave this blank for the source node}

6. db.driver={JDBC driver class}

Database | Example Driver Classes
--- | ---
h2 | org.h2.Driver
mySQL | com.mysql.jdbc.Driver
Oracle | oracle.jdbc.driver.OracleDriver
PostgreSQL | org.postgresql.Driver
Apache | org.apache.derby.jdbc.EmbeddedDriver
HyperSQL | org.hsqldb.jdbcDriver
SourceForge | net.sourceforge.jtds.jdbc.Driver
IBM | com.ibm.db2.jcc.DB2Driver
SQLite | org.sqlite.JDBC
Firebird | org.firebirdsql.jdbc.FBDriver
Sybase | com.sybase.jdbc4.jdbc.SybDriver

7. db.url={JDBC url for your source database}

Database | Example JDBC Urls
--- | ---
h2 | jdbc:h2:file:target/h2/client
mySQL | jdbc:mysql://localhost/sampleroot?tinyInt1isBit=false&zeroDateTimeBehavior=convertToNull
Oracle | jdbc:oracle:thin:@127.0.0.1:1521:sampleroot
PostgreSQL | jdbc:postgresql://localhost/sampleroot
Apache | jdbc:derby:sampleroot;create=true
HyperSQL | jdbc:hsqldb:file:sampleroot;shutdown=true
SourceForge | jdbc:jtds:sqlserver://localhost:1433;sendStringParametersAsUnicode=false; useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
IBM | jdbc:db2://localhost/samproot
SQLite | jdbc:sqlite:target/sqlite/client
Firebird | jdbc:firebirdsql://host[:port]/client
Sybase | jdbc:sybase:Tds:localhost:5000/client

8. db.user={JDBC username}

       Example: db.user=root

9. db.password={JDBC password}

       Example: db.password=xxxxxx

| For complete instructions creating the master node click here

### 2.3. Configure

SymmetricDS configuration requires at a minimum each of the following entities.

1. Group

2. Group Links

3. Router

4. Trigger

5. Trigger Router

Use the following SQL templates to help you generate these entities replacing anything in red with your values (with the exception of the group link data_event_action, 'P', 'W').

Groups

    insert into SYM_NODE_GROUP
            (node_group_id, description)
            values ('corp', 'A corporate node');

    insert into SYM_NODE_GROUP
            (node_group_id, description)
            values ('store', 'A retail store node');

Group Links

    insert into SYM_NODE_GROUP_LINK
    (source_node_group_id, target_node_group_id, data_event_action)
          values ('store', 'corp', 'P');

    insert into SYM_NODE_GROUP_LINK
    (source_node_group_id, target_node_group_id, data_event_action)
          values ('corp', 'store', 'W');

Router

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

Trigger

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

Trigger Router

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

### 2.4. Connect Target Database

Create a property file named `xxxxx.properties` in the engines folder of your SymmetricDS installation. Replace the `xxxxx` with a name for your target node (ex. store).

Properties

1. engine.name={an arbitrary name for the node}

2. group.id={this must match the name of a group inserted in step 3, ex: store}

3. external.id={id for the node, ex: 001 }

4. sync.url=http://{hostname}:{port}/{webcontext}/sync/{engine.name}

5. registration.url={this should match the sync.url in your source property file}

6. db.driver={JDBC driver class}

7. db.url={JDBC url for your target database}

8. db.user={JDBC username}

9. db.password={JDBC password}

| For complete instructions creating the target node click here

| Congratulations! You have completed the Quick Start.