From time to time we are approached with the question of how to do initial loads of data in two directions with SymmetricDS, not just one direction. This feature is available in SymmetricDS and isn’t hard to configure. An understanding of how initial loads work in general, combined with a few settings, is all that is needed. This article will walk you through a tutorial on initial loads in general and will then discuss the configuration needed to do a ‘reverse’ initial load.
For this article, we will use a scenario which is quite common – retail stores. The scenario involves a corporate (‘corp’) database which synchronizes with several ‘store’ databases. In this common setting, item and pricing data is synced down to the stores, for example, while sales transactions are synced up to the corporate office database. Typically, we’d assign the single corporate database a node group, called ‘corp’, and we’d assign the store nodes to a node group called ‘store’. New stores, when coming online for the first time, would “register” their SymmetricDS node with the registration node (the corporate node), which sets up the configuration needed to synchronize data between the corp and store nodes.
Standard Initial Loads
Before we discuss reverse initial loads, let’s explore the standard initial load case (or non-reversed initial loads, if you will). Picture the case where a new store location is being opened for the first time. You typically would want to seed the store’s database with a set of data, such as items and pricing, as the store is registering with the corporate node. This seeding process is called an ‘initial load’ of data (another phrase used for this is a “reload” of data). Initial load data is, by default, queried from the registration server database tables (corp, in this case), and all data is passed through your configured routers to filter out data that might not be targeted to a given node (a brand new store, in this case).
To define which tables are to be included in an initial load, you specify a value known as an ‘initial load order’ (an integer) when configuring your trigger / router setup in SymmetricDS. If the initial load order number is non-negative, then data from that table is sent to the client when an initial load is requested. You can also specify subsets of data to be loaded by using initial_load_select (see Initial Loads, in the SymmetricDS User’s Guide for more details and some additional features).
There are a couple of ways in which you can initiate or “kick off” an initial load. One way is by setting the initial_load_enabled column on the node_security table, in the corp database, to a value of 1 for the node. The next time the corp node routes data, a series of reload batches will be created at the corp node, which are then synced to the store node to achieve the initial load. If you are using SymmetricDS Pro, you can also cause an initial load to occur by right-clicking the node in the Manage / Nodes view and choose Reloadlem. Finally, you can also cause an initial load to occur automatically immediately after registration by setting SymmetricDS parameter called auto.reload to true.
Reverse Initial Loads
The above scenario works great if all of your data is already in the corporate database and none is in the store’s database. You register the new store, receive an initial load of data (items and prices), and begin ringing sales.
Picture the case, however, where your store has existed for some time, has accumulated sales data, and is just now being included in a SymmetricDS synchronization configuration. In this case, we would most likely want the store’s existing sales data to be synced or “reverse initial loaded” to the corp database upon registration so that the corp node has the complete sales data.
You can configure SymmetricDS to do this ‘reverse initial load’, although it is restricted in one way when compared to a normal initial load: if configured, the reverse initial load must occur automatically at registration time and can’t be run manually after registration has occurred.
To configure SymmetricDS to do a reverse initial load, the first step is the same as before, in that you need to specify ‘initial load order’ values for the tables you wish to load from the store to corp. Again, set the load order to a negative number for tables you don’t wish to reverse load.
Next, you need to set a parameter, auto.reload.reverse, to true but only for (and this is key!) the node group representing the store’s. This is easy to accomplish in SymmetricDS Pro by going to Configure / Parameters, selecting the Target Node to your store node group, then setting the parameter to true. If you aren’t using Pro, you can insert a parameter in the parameter table on corp, using “ALL” for the external_id column and the node group name in the node_group_id column, as in:
INSERT INTO sym_parameter (external_id,node_group_id,param_key,param_value)
VALUES ('ALL','store','auto.reload.reverse','true');
When registration is taking place for a given node, this parameter will cause the store node to create a series of batches at the store and sync those batches to the corp database, thereby causing the appropriate tables to be reverse initial loaded into corp.
So, why is it called “reverse” anyways?
One final thought. One question that might come to mind is why exactly is this second type of initial load called a ‘reverse’ initial load? After all, in both cases, data is flowing in the natural direction defined by the node group links (in other words, item data naturally flows from corp to store, and sales data naturally flows from store to corp, and the natural flow reflects the direction that the data is initial loaded, yet the loads in one case are called ‘reverse’ loads)? The terminology of ‘reverse’ makes more sense if you realize the definition of the initial load is relative to the registration node itself. For standard initial loads, data flows from the registration server to the client nodes. For reverse loads, data flows to the registration server from the client nodes, hence the use of the term “reverse”.