SymmetricDS 3.6 Quick Start Guide

Download the symmetric-ds-3.x.x-server.zip file from http://www.symmetricds.org/

Create two directories to represent your two "machines". One will hold the corp installation of SymmetricDS and one to hold the store installation. For example, you could name the directories sym-corp and sym-store001, and we'll assume you used these names below (but feel free to update the steps below with your directory names as needed). Unzip the above zip file into both directories. This will create a symmetric-ds-3.x.x directory, which corresponds to the version you downloaded.

Properties files are use to store the minimal configuration information needed to start SymmetricDS. Copy the corp sample properties file to the corp engines directory, and the store one to the store engines directory. If you used the suggested directory names above, you would do the following copies:

samples/corp-000.properties to sym-corp/symmetric-ds-3.x.x/engines/

and

samples/store-001.properties to sym-store001/symmetric-ds-3.x.x/engines/

Browse both properties files and explore the various settings. For exampl, notice that the root node is given a group id of corp, and that the store node is given a group id of store. Notice also that the root node is given an external id of 000, and the store node is given an external id of 001.

Set the following properties in both properties files now present in the engines directories to specify how to connect to your particular database (the values below are just examples):

# The class name for the JDBC Driver
	db.driver=com.mysql.jdbc.Driver
	
	# The JDBC URL used to connect to the database
	db.url=jdbc:mysql://localhost/sample
	
	# The user to login as who can create and update tables
	db.user=symmetric
	
	# The password for the user to login as
	db.password=secret

Next, set the following property in the store-001.properties file to specify where the root node can be contacted:

# The HTTP URL of the root node to contact for registration
	registration.url=http://localhost:8080/sync/corp-000

http://{hostname}:{port}/sync/{engine.name}

Open a command prompt and navigate to the samples subdirectory of your corp SymmetricDS installation (for example, navigate to sym-corp/symmetric-ds-3.x.x/samples)

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 create_sample.xml

Note that the warning messages from the command are safe to ignore.

Another quick comment about properties files. At startup, SymmetricDS looks for one or more properties files in the engines directory. Since we have specified a --engine parameter on the command line, it will look only for the specific file listed, namely corp-000.properties. Technically, the --engine corp-000 part is optional in our particular tutorial example. Since there's only one properties file in the engines directory, SymmetricDS would just default to using that one file, after all. By including it, though, it will reduce errors while running the tutorial, because if you run the command from the wrong SymmetricDS installation, SymmetricDS will complain about the missing engines property file you specified.

Next, create the SymmetricDS-specific tables in the corp node database. These tables will contain the configuration for synchronization. The following command uses the auto-creation feature to create all the necessary SymmetricDS system tables.

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

Finally, load the sample item and transaction data and SymmetricDS configuration into the root node database by executing:

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

Open a command prompt and navigate to the samples subdirectory of your store #001 SymmetricDS installation (for example, navigate to sym-store001/symmetric-ds-3.x.x/samples)

Create the empty, sample tables in the client database by executing:

../bin/dbimport --engine store-001 --format XML create_sample.xml

Note that the warning messages from the command are safe to ignore. Also, feel free to review the create_sample.xml file to see what it contains.

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.

Find the SymmetricDS system tables, which have a prefix of "sym_", such as sym_channel, sym_trigger, sym_router, and sym_trigger_router.

Validate the corp item tables have sample data.

If they are not already open, open two command prompts and navigate to the samples directory of each installed SymmetricDS application (for example, navigate to sym-corp/symmetric-ds-3.x.x/samples and sym-store001/symmetric-ds-3.x.x/samples).

From the corp samples directory, start the corp SymmetricDS by executing:

../bin/sym --engine corp-000 --port 8080

Upon startup for the first time, the corp node creates all the triggers that were configured by the sample configuration. It listens on port 8080 for synchronization and registration requests for the corp-000 engine.

From the store001 samples directory, start the store SymmetricDS by executing:

../bin/sym --engine store-001 --port 9090

This command starts the store node server for the first time and uses the auto-creation feature to create the SymmetricDS system tables. It begins polling the corp node to try to register (it knows where to contact the corp node via the registration URL you configured in the previous steps). Since registration is not yet open, the store node receives an authorization failure (HTTP response of 403). We discuss registration next.

Leave the corp and store SymmetricDS applications that you started in the previous step running, and open a command prompt and navigate to corp's samples subdirectory of your corp SymmetricDS installation.

Open registration for the store node server by executing:

../bin/symadmin --engine corp-000 open-registration store 001

The registration is now opened for a node group called "store" with an external identifier of "001". This information matches the settings in store-001.properties for the store node. In SymmetricDS, each node is assigned to a node group and is given an external ID that makes sense for the application. In this tutorial, we have retail stores that run SymmetricDS, so we named our node group representing stores as "store" and we used numeric identifiers for external ids starting with "001" ("000" is used to represent the corp node). More information about node groups will be covered in the next chapter.

Watch the logging output of the store node to see it successfully register with the corp node. The store is configured to attempt registration at a random time interval up to every minute. Once registered, the corp and store nodes are enabled for synchronization!

Open a command prompt and navigate to the corp samples subdirectory of the corp SymmetricDS installation. (Note that, in general, most system commands are issued using the corp server directly. All configuration, for example, is entered at the corp and synchronized to any clients.)

Send an initial load of data to the store node server by executing:

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

With this command, the server node queues up an initial load for the store node that will be sent the next time the store performs its pull. The initial load includes data for each table that is configured for synchronization (assuming its initial load order is a non-negative number, as discussed in later chapters).

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

Open an interactive SQL session with the corp database.

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

Once the statements are committed, the data change is captured by SymmetricDS and queued for the store node to pull.

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

Since item_selling_price is configured with a column match router in this tutorial, specific pricing data changes will be sent (or "routed", in SymmetricDS terms) only to nodes whose store_id matches the node's external ID. Verify that the new data arrives in the store database using another interactive SQL session. In this case, the first pricing row will be routed to store 001 only, and the second row would be routed to store 002 (which doesn't exist currently, so in this case the data change is recorded but routed nowhere and therefore discarded.)

Open an interactive SQL session with the store node database.

Add a new sale to the store node database:

insert into "sale_transaction" ("tran_id", "store_id", "workstation", "day", "seq") values (1000, '001', '3', '2007-11-01', 100);

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

Once the statements are committed, the data change is captured and queued for the store node to push.

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.

Open an interactive SQL session with either the corp or store database.

Verify that the data change you made was captured:

select * from sym_data order by data_id desc;

Each row represents a row of data that was changed. Data Ids are sequentially increasing, so one of the most recent (highest) data ids should be related to your data insert SQLs. The event_type is "I" for insert, "U" for update", or "D" for delete. For insert and update, the captured data values are listed in row_data. For update and delete, the primary key values are listed in pk_data.

Verify that the data change was included in a batch, using the data_id from the previous step:

select * from sym_data_event where data_id = ?;

Batches are created based on the needed routing to nodes as part of a background job, called the Route Job. As part of the Route Job, the data change is assigned to a batch using a batch_id which is used to track and synchronize the data. The links between batches and data are managed by this sym_data_event table.

Verify that the data change was batched, sent to the destination, and acknowledged, using the batch_id from the previous step:

select * from sym_outgoing_batch where batch_id = ?;

Batches initially have a status of "NE" when they are new and not yet sent to a node. Once a receiving node acknowledges the batch, the batch status is changed to a status of "OK" for success or "ER" for error (failure). If the batch failed, the error_flag on the batch is also sent to 1, since the status of a batch that failed can change as it's being retried.

Open an interactive SQL session with either the corp or store database.

Verify that the batch was received and acknowledged, using a batch_id from the previous section:

select * from sym_incoming_batch where batch_id = ?;

A batch represents a collection of changes loaded by the node. The sending node that created the batch is recorded, and the batch's status is either "OK" for success or "ER" for error.

Install a single copy of the SymmetricDS software instead of two copies. You no longer need a directory to represent the two machines.

Instead of copying a single property file from samples to each separate engines directory, copy both files to just the one engines directory.

All commands in the tutorial are run from the one single samples directory.

When you start SymmetricDS, you will no longer specify a specific engine, as you want both engines to start. The command, still run from the samples directory, would now be:

../bin/sym --port 8080

Note that we are no longer using port 9090, by the way. SymmetricDS now listens on port 8080 for traffic relevant to both the store and corp engines.

Other than starting the server, all other commands you executed will still have the --engine specification, since you are addressing the command itself to a specific node (engine) of SymmetricDS to open registration, set up the corp server to issue an initial load to store, etc.