Using SymmetricDS to replicate your Database Structure
SymmetricDS is an open source database replication tool that can be used to keep heterogeneous databases in sync.
For a good overview of SymmetricDS, check out this introductory article.
Before you can begin replicating data, before you can perform an initial load, you need to create the database structure.
Sometimes this can be easy. If you are syncing between the same type of databases (i.e. MySQL to MySQL, Oracle to Oracle) then you might already have a script to create the database structure. If not, most databases have native tools that can export the structure of the database to a script. If this is the case, you can use the same mechanism to create your target databases as you did your source.
If the target database is not the same type of database as the source then you might want to use SymmetricDS to create your target databases.
SymmetricDS has a setting that when enabled, can create the target schema for you prior to an initial load.
This feature is enabled using the initial.load.create.first parameter when using using automatic initial loads. The feature can also be leveraged when loading data via settings on the SYM_TABLE_RELOAD_REQUEST table.
When used, a “create table” batch will be generated for each table that is being synchronized.
When a “create table” batch is transferred to the target, an XML representation of the database table is sent to the target.
On the target database, SymmetricDS uses the detected target database type to generate DDL that will create the represented table on that database.
Note that SymmetricDS only creates database table structures. It does not create other database artifacts like custom triggers or procedures.
SymmetricDS does its best to maintain data types and default values, but sometimes concepts do not translate from one database type to another.
Custom data types are one example of a concept that might not translate from one type of database to another.
Sometimes default values will be represented in a way that can’t be ported.
The following are properties that can be used to customize the behavior of the table creation.
There are times that SymmetricDS’s create tables feature will get you 95% the way there but the resulting create DDL needs to be tweaked for your use case.
If this is the case then you might want to use the dbexport tool that is included with SymmetricDS. With it you can create a SQL script for a target database type. After creating the SQL script you can test it, tweak it and add to it. The script can be automatically delivered and run by putting the contents of the script in the initial.load.before.sql parameter. When set, the script will be run prior to starting an initial load. Of course if you are using this feature you would have initial.load.create.first turned off.
Altering Tables
Creating the database structure using the aforementioned techniques is a one time event that occurs prior to doing an initial load. If the structure of a table changes at the source, a manual action needs to be taken to keep the target databases in sync. This can be done by queuing up another create table event. The easiest way to do this is by inserting a request into the SYM_TABLE_RELOAD_REQUEST table for the altered table. Again an XML representation is delivered to the target. When the target SymmetricDS node receives the XML it uses it to generate the appropriate DDL alters to the target table to get it in sync with the source.
You would only want to use this technique if your tables were originally created by SymmetricDS. If they were not and you want to use the SymmetricDS infrastructure to alter your target tables you could leverage SQL events and send explicit DDL to be run on your target databases.
Summary
There are many more situations to consider when setting up database replication. Figuring out how to replicate the database structure is just one of them and it happens to be the first!
