high availability

  • Clustering SymmetricDS

    Setting up SymmetricDS for clustering can be done a few different ways and can help solve a few different use cases. This blog will address a few of the common setups we see while utilizing clustering.

    Overview

    In general clustering in SymmetricDS is achieved when multiple installations of SymmetricDS access the same database. This might be for high availability, performance or even both. This behavior though is not turned on by default and would result in a variety of errors if multiple nodes were configured to access the same database through the same connection url. The various jobs that run within SymmetricDS would end up running against the same set of runtime tables and cause collisions and conflicts while fighting for access. To solve this you must tell SymmetricDS that a node should run in clustered mode a locking table will be used to prevent nodes in the same cluster from running on top of each other.

    Setup

    The simplest setup for clustering involves setting the following parameter on all engines that are clustered (set on the engine file directly in the engines folder of your SymmetricDS installation).

    cluster.lock.enabled=true

    In addition to this property being added to the engine file all other initial properties should match. So the easiest setup for a cluster is to copy the engine file from one installation to another and add the cluster enabled property. However if you have a db.password that is encrypted (starts with enc:) you will also need to copy over the security folder files from the original installation to the new one as well so that the database password can be decrypted.

    For more advanced setup and configuration see the clustering section in the documentation.

    https://www.jumpmind.com/downloads/symmetricds/doc/3.12/html/user-guide.html#_clustering

     

    Use Case - Load Balancer

    The most common use case that involves SymmetricDS clustering is through a load balancer to help support multiple client nodes. This helps in scaling the central database for replicating to hundreds or even thousands of client nodes. It can also serve as a high availability use case so that replication to a central node continues even if a central SymmetricDS node goes down.

    The key to this setup is that all the nodes behind the load balancer use the same sync url. This sync url is the end point of the load balancer and the url that all client nodes will communicate on to access the central node. So be sure in this setup that the sync url is the same for all the nodes behind the load balancer.

     

     

    Use Case - No Load Balancer (multi-homed only)

    If all the databases being replicated are on the same network (not over a WAN) then all nodes/engines can run under a single installation of SymmetricDS (multi-homed). If this is the case you can cluster more installations of SymmetricDS without a load balancer. This provides both high availability if a server fails as well as some distribution of the workload.    

    The key to this setup is that all of the engines sync.url and registration.url values should use a host name of localhost. This ensures that a request between node 1 and node 2 on server A will remain on server A and will not need to be sent through a load balancer.

    The basic clustering principles still apply though. Cluster enabled parameter must be set to true in all engine property files and the engine files that represent the same node must all match. So engine 1 properties file will be identical in all their SymmetricDS servers below. This also means than the security folder from the original installation should also be used for all future clustered additions so that the db.password can be decrypted.

     

     

     

     

    Use Case - Dual Load Balancer

    It is also possible to use two load balancers or two load balancer endpoints as well in a clustered setup. Below is an example of a simple 2 node bidirectional replication setup that contains a load balancer on each side. This would provide high availability and scaling for two high traffic critical nodes. Again all other principles apply with setting the cluster enabled etc. This time the sync.url for the two node 1 engines will match and be that of the load balancer on the left. A similar setup will occur by providing the load balancer on the rights endpoint as the sync.url for the node 2 engine files.

     

     

  • Load Balancing SymmetricDS Pro

    For most of our large clients where SymmetricDS is playing a mission critical role in their organization, our customers take advantage of SymmetricDS Pro's out of the box capabilities to be load balanced. While SymmetricDS does a great job with hands off recovery in cases of hardware or infrastructure failure, most clients want as little downtime as possible. They simply don't want a single point of failure in their synchronization scenario. The good news is, with SymmetricDS Pro, load balancing is simple and straight forward.

    The Scenario

    In many of these scenarios, SymmetricDS Pro is synchronizing a large server database with thousands of client databases. Most people are ok with the client databases being at risk of downtime due to client infrastructure, but the server is a different story. We see most people load balance the server by running two or more instances of SymmetricDS Pro on separate hardware, pointed to the same server database. Traffic is directed to one of the load balanced servers via some type of load balancer (mostly hardware based appliances).

    Configuration Background

    In order to understand the setup, it's helpful to understand the configuration that determines how SymmetricDS Pro nodes communicate with each other. In a typical synchronization scenario, one or more of the SymmetricDS Pro nodes are designated as the registration or root servers. These servers listen on a given port and URL for clients to request registration to the synchronization scenario. The port and URL they listen on are configured by setting the sync.url property on the server. This property is initially set during SymmetricDS Pro server setup, and can also be found by clicking "Configure", "Parameters" from the Pro Management Console, or by looking at the SymmetricDS properties file that gets written in the "engines" subdirectory of your installation.

    In our scenario from above, the three Symmetric Pro Servers on the right are the registration servers.

    On the client side, the clients need to know the URL to connect to in order to attempt registration to the synchronization scenario. This is the URL from above on which the registration servers are listening. On the client side, this is called the registration url and is setup during the client install, and can also be found by clicking "Configure", "Parameters" from the Pro Management Console, or by looking at the SymmetricDS properties file for the client.

    Once the client attempts registration, the server sends the client it's sync.url property, so the client knows where to reach out to the server for subsequent synchronization requests and work. The registration URL is only used during the registration process, not for go forward synchronization. So, the client uses the registration url to know where to go to register, and then after that uses the sync.url sent from the server during the registration process for all go forward synchronization work.

    Load Balancing Configuration

    Knowing that background, setting up the load balanced setup from above is pretty simple. Here are the steps:

    • During Server setup, when specifying Communication Settings, instead of using the default sync URL, click the radio button that says "Define URL setting for load balancer or proxy." Then, type in the Hostname and Port for the load balancer itself. Behind the scenes, this sets the sync.url for the server to the hostname and port (URL) for the load balancer.
    • Set your load balancer to forward requests to any number of SymmetricDS Pro root servers, forwarding them to the server name and port on which SymmetricDS Pro is running on the individual servers.
    • During the client setup, when specifying the Registration URL, specify the URL from above that points to the load balancer. This will get written to the .properties file as the registration.url for the client.

    The Result

    The result of the configuration is as follows:

    • On the Servers, the Hostname and Port that you configured for the load balancer gets written to the server.properties file as the sync.url.
    • On the Clients, the Registration.URL that you specified gets written to the client .properties file.
    • When the Clients register, they point to the load balancer, which passes off the request to one of the three SymmetricDS Pro servers.
    • The server in turn sends its sync.url (which also points to the load balancer) back down to the client to tell it where to make future synchronization requests.
    • On subsequent synchronization requests from the client to the server, it looks at the sync.url that was given to it by the server (stored in sym_node table on the client). This url points to the load balancer which forwards the request to one of the actual Symmetric Servers.

    Next Up

    In the next couple of weeks, we'll add another couple of articles that discuss the following:

    • Now that I have SymmetricDS Pro load balanced, how do the servers distribute the work?
    • How do the SymmetricDS Pro servers stay out of each other's way, and know who's doing what?
    • We'll also show you how to use the open source apache http server as a load balancer.

    Stay tuned for more!

  • Using Wildcards to Sync Database Tables

    Quickly select and dynamically add tables to database synchronization using wildcard characters. This powerful feature can save time during configuration and even eliminate the need to make changes to configuration in the future. Since data capture triggers can be created from a schedule or on-demand programmatically, wildcard characters open up the possibility of new applications, such as using SymmetricDS database replication in Platform As A Service (PaaS) where new tables created by a client are automatically replicated between high availability servers. This article will demonstrate using wildcard characters to sync some tables while ignoring others.

    Replication Setup

    A common scenario is to sync a set of tables while excluding some tables from being synced. If tables are named with common conventions, a wildcard character can match a set of tables. For example, a set of customer tables might be prefixed with "CUSTOMER".

    create table customer ( id integer primary key, first_name varchar(50), last_name varchar(50) ); create table customer_address ( id integer primary key, customer_id integer, street varchar(50), city varchar(50), state varchar(2), zip varchar(10) );

    At the same time, the database might have tables that should be excluded from replication. For example, a set of employee tables might be prefixed with "EMPLOYEE".

    create table employee ( id integer primary key, first_name varchar(50), last_name varchar(50) ); create table employee_address ( id integer primary key, customer_id integer, street varchar(50), city varchar(50), state varchar(2), zip varchar(10) );

     

    Replicating Tables with a Wildcard

    Using the 2-tier profile in SymmetricDS Pro, the synchronization configuration is quickly setup. To sync customer tables between nodes, a new trigger is created using the Configure->Triggers screen. The wildcard character is an asterisks that means "match any characters." So, to match the set of customer tables, the Source Table is specified as "CUSTOMER*".

     

    From the Manage->Logging screen, or directly from the symmetric.log file, it will output the triggers that are created. The new triggers can also be verified from the Manage->Installed Triggers screen. Data capture triggers are created for CUSTOMER and CUSTOMER_ADDRESS tables, while ignoring the employee tables.

    Creating SYM_ON_I_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_U_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_D_FOR_CSTMR_DDRSS_SRVR trigger for CUSTOMER_ADDRESS Creating SYM_ON_I_FOR_CSTMR_SRVR trigger for CUSTOMER Creating SYM_ON_U_FOR_CSTMR_SRVR trigger for CUSTOMER Creating SYM_ON_D_FOR_CSTMR_SRVR trigger for CUSTOMER

     

    Dynamic Replication

    Now that the synchronization configuration is in place, when new tables are created that match the wildcard expression, they will be automatically included in the database replication. For example, a new customer table is created.

    create table customer_phone ( id integer primary key, customer_id integer, type char(1), phone_num varchar(50), phone_ext varchar(10) );

    Using the Manage->Installed Triggers screen, the "Sync Triggers" button will cause the new data capture triggers to be created immediately.

    The Sync Triggers process is scheduled as a job that runs every night, but the schedule can be changed to run at any frequency using a cron expression. The expression has fields for the second, minute, hour, day of month, month, and day of week that are separated by a space. The fields are numeric, although month and day of week can also be specified by their name. For example, a cron expression of "0 0/5 9-17 * * MON-FRI" would run the job every 5 minutes between 9 AM and 5 PM on Monday through Friday.

    Java Management Extensions (JMX) are available to programmatically execute the Sync Triggers process from external clients. An application could alter the database or add new tables, then call Sync Triggers over JMX to detect the changes and setup replication. SymmetricDS also includes a web frontend to access JMX operations using the MX4J HTTP Adapter. The Sync Triggers operation is found in the management bean named "org.jumpmind.symmetric.server:name=Node" under the "org.jumpmind.symmetric.server" domain.

     

    Conclusion

    When configuring synchronization, consider using a wildcard character to match the set of tables. If all tables in the catalog or schema are required to be replicated, a single Trigger entry with a wildcard is a quick and easy configuration. Adding a configuration for a specific table can be used to override any wildcard Trigger entries. SymmetricDS will use the most specific Trigger configuration. Replicating new tables automatically as they are created can be used to build unique solutions that require the system to assemble itself in response to change.