Chapter 2. Quick Start

Table of Contents

2.1. Installing SymmetricDS
2.2. Creating and Populating Your Databases
2.3. Starting SymmetricDS
2.4. Registering a Node
2.5. Sending an Initial Load
2.6. Pulling Data
2.7. Pushing Data
2.8. Verifying Outgoing Batches
2.9. Verifying Incoming Batches

Now that an overview of SymmetricDS has been discussed, a quick working example of SymmetricDS is in order. This section contains a hands-on tutorial that demonstrates how to synchronize a sample database between a client and server engine deployed in a single instance of SymmetricDS. This example models a retail business that has a central office database (called "server") and multiple retail store databases (called "client"). For the tutorial, we will have only one "client", as shown in Figure 2.1.

Simplified Two Tiered Retail Store Tutorial Example

Figure 2.1. Simplified Two Tiered Retail Store Tutorial Example


The server engine captures item data changes for the client, such as item number, description, and price. The client engine captures sale transaction data changes for the server, such as time of sale and items sold. The sample configuration has the client always initiating communication with the server. The client will attach to the server on a periodic basis to pull data from the server. The client will also push captured changes for the server to the server when changes are available.

This tutorial will walk you through:

  1. Installing multiple engines in a single installation of SymmetricDS,

  2. Creating separate databases for the server and client,

  3. Creating sample tables for client and server and sample data for the server,

  4. Starting SymmetricDS and registering the client with the server,

  5. Sending an initial load to the client,

  6. Causing a data push and data pull operation, and

  7. Verifying information about the batches that were sent and received.

2.1. Installing SymmetricDS

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

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

  2. Unzip the file in any directory you choose. This will create a symmetric-ds-3.x.x directory, which corresponds to the version you downloaded.

  3. Copy the following properties files for the server (central office) and client (store) nodes into the engines directory of the SymmetricDS install and edit them to configure the database you want to use.

    • samples/server.properties

    • samples/client.properties

  4. Set the following properties in both files to specify how to connect to the database:

    # 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
  5. Next, set the following property in the client.properties file to specify where the server node can be contacted:

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

    Tip

    Note that the URL for a engine is the following format:

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

    The engine.name portion of the URL comes from a node's properties file.

    For the tutorial, the client database starts out empty, and the node is not registered. Registration is the process where the node receives its configuration and stores it in its database. The configuration describes which database tables to synchronize and to which nodes. When an unregistered node starts up, it will register with the node specified by the registration URL. The registration node centrally controls nodes on the network by allowing registration and returning configuration. In this tutorial, the registration node is the server node, which also participates in synchronization with other nodes.

2.2. Creating and Populating Your Databases

Important

You must first create the databases for your server and client nodes using the administration tools provided by your database vendor. Make sure the name of the databases you create match the settings in the properties files.

See Appendix C, Database Notes for compatibility with your specific database.

First, create the sample tables in the server node database, load the sample data, and load the sample configuration.

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

  2. Create the sample tables in the server database by executing the following command:

    ../bin/dbimport --engine server --format XML create_sample.xml

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

  3. Next, create the SymmetricDS tables in the server 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 server create-sym-tables

  4. Finally, load the sample data and configuration into the server node database by executing:

    ../bin/dbimport --engine server insert_sample.sql

We have now created the server database tables and populated them with sample data. Next, we create the sample tables in the client node database to prepare it for receiving data.

  • Create the sample tables in the client database by executing:

    ../bin/dbimport --engine client --format XML create_sample.xml

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

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

  1. Find the item tables that sync from server to client: item and item_selling_price.

  2. Find the sales tables that sync from client to server: sale_transaction and sale_return_line_item.

  3. Find the SymmetricDS system tables, which have a prefix of "sym_".

  4. Validate the server item tables have sample data.

2.3. Starting SymmetricDS

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

  • Start SymmetricDS by executing:

    ../bin/sym --port 8080 --server

    The server and client nodes both start up. The server node creates all the triggers that were configured by the sample configuration. It listens on port 8080 for synchronization and registration requests.

    The client node server starts up and uses the auto-creation feature to create the SymmetricDS system tables. It begins polling the server node in order to register. Since registration is not yet open, the client node receives an authorization failure (HTTP response of 403).

Tip

If you want to change the port number used by SymmetricDS, you need to also set the sync.url runtime property to match. The default value is:

sync.url=http://localhost:8080/sync

2.4. Registering a Node

Next, we need to open registration for the client node so that it may receive its initial load of data and so that it may receive and send data from and to the server node. There are several ways to do this. We will use the administration feature on the server node.

  1. Open registration for the client node server by executing:

    ../bin/symadmin --engine server open-registration store 1

    The registration is now opened for a node group called "store" with an external identifier of "1". This information matches the settings in client.properties for the client node. 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 "store" and we used numeric identifiers starting with "1". More information about node groups will be covered in the next chapter.

  2. Watch the logging output of the client node to see it successfully register with the server node. The client is configured to attempt registration once per minute. Once registered, the server and client are enabled for synchronization!

2.5. Sending an Initial Load

Next, we will send an initial load of data to our store (that is, the client node), again using the server node administration feature.

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

  2. Send an initial load of data to the client node server by executing:

    ../bin/symadmin --engine server reload-node 1

    With this command, the server node queues up an initial load for the client node that will be sent the next time the client performs its pull. The initial load includes data for each table that is configured for synchronization.

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to pull data from the server every minute.

2.6. Pulling Data

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

  1. Open an interactive SQL session with the server database.

  2. Add a new item for sale:

    insert into item_selling_price (price_id, price) values (55, 0.65);

    insert into item (item_id, price_id, name) values (110000055, 55, 'Soft Drink');

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

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to pull data from the server every minute.

  4. Verify that the new data arrives in the client database using another interactive SQL session.

2.7. Pushing Data

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

  1. Open an interactive SQL session with the client database.

  2. Add a new sale to the client database:

    insert into sale_transaction (tran_id, store, workstation, day, seq) values (1000, '1', '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 client node to push.

  3. Watch the logging output of both nodes to see the data transfer. The client is configured to push data to the server every minute.

2.8. Verifying Outgoing Batches

Now that we have pushed and pulled data, we will demonstrate how you can obtain information about what data has been batched and sent. A batch is used for tracking and sending data changes to nodes. The sending node creates a batch and the receiving node acknowledges it. A batch in error is retried during synchronization attempts, but only after data changes in other channels are allowed to be sent. Channels are categories assigned to tables for the purpose of independent synchronization and control. Batches for a channel are not created when a batch in the channel is in error status.

  1. Open an interactive SQL session with either the server or client database.

  2. 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. 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.

  3. Verify that the data change was routed to a node, using the data_id from the previous step:

    select * from sym_data_event where data_id = ?;

    When the batched flag is set, the data change is assigned to a batch using a batch_id that is used to track and synchronize the data. Batches are created and assigned during a push or pull synchronization.

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

    select * from sym_outgoing_batch where batch_id = ?;

    A batch represents a collection of changes to be sent to a node. The batch is created during a push or pull synchronization, when the status is set to "NE" for new. The receiving node acknowledges the batch with a status of "OK" for success or "ER" for error.

Understanding these three tables, along with a fourth table discussed in the next section, is key to diagnosing any synchronizaiton issues you might encounter. As you work with SymmetricDS, either when experimenting or starting to use SymmetricDS on your own data, spend time monitoring these tables to better understand how SymmetricDS works.

2.9. Verifying Incoming Batches

The receiving node keeps track of the batches it acknowledges and records statistics about loading the data. Duplicate batches are skipped by default, but this behavior can be changed with the incoming.batches.skip.duplicates runtime property.

  1. Open an interactive SQL session with either the server or client database.

  2. Verify that the batch was 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. The status is either "OK" for success or "ER" for error.