SymmetricDS Setup - SQL Server 2008 to MySQL

Question

I need to replicate just a couple of tables from a Microsoft SQL Server 2008 database to a MySQL 5.7 database (one-way only). I've been struggling to find a simple solution.

I've ultimately decided to try SymmetricDS, but the setup is confusing: Assuming I've got SQL Server running on Computer1, and MySQL on Computer2, and I just need Table1 and Table2, how am I supposed to set this up?

As I understand already, I need to add four additional tables to the Master Node (SQL Server in my case), do I add these tables directly to the database I need to copy? This may prove problematic as this database is managed by a very temperamental third-party application and we really don't want to touch it, should we replicate the specific data we need into a local copy on the SQL Server and then "sym" that entire db over to MySQL?

I got SSIS talking my MySQL instance, if it's possible to set that up with a trigger of some kind to automatically run the procedure that would probably work as an alternative.

However, to add, I'm pretty sure I understand the process of creating the .properties files, what I don't fully follow is the Configuration settings, or more specifically, how to set these up properly so the connection is only one-way (the SQL Server should be essentially read-only).

Answer 1

It seem that the official documents are not clear enough to help you. The steps are below:

• You need 1( or 2) intermediate node(s)(Linux or Windows) which have connection between Source and Destination databases and install symmetricDs on it.

• You need to build the schema(database structure) on the destination(Mysql) node by symmetricds script or by insert yourself.

• You need to create 2 users on 2 nodes who have access at read and write to database

• You need to create the engine file for source and destination in the intermediate(s) node similar like this:

For mssql.properties

 engine.name=MSSQL
 db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://[sqlserver:port]/[DatabaseName];useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880
 db.user=[username]
 db.password=[password]
registration.url=
sync.url=http://localhost:8080/sync/MSSQL
group.id=source
external.id=0000000
job.purge.period.time.ms=7200000
job.routing.period.time.ms=5000
 job.push.period.time.ms=10000
 job.pull.period.time.ms=10000
 initial.load.create.first=true

For mysql.properties

engine.name=mysql
db.driver=com.mysql.jdbc.Driver
db.url=jdbc:mysql://<IP>/databasename?tinyInt1isBit=false
registration.url=http://localhost:8080/sync/MSSQL
group.id=destination
external.id=0000002
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000
jobs.synchronized.enable=true

• On the intermediate Node, You will need to CREATE SYMMETRICDS CONFIGURATION TABLES with following bin\symadmin --engine source create-sym-tables
• On the intermediate Node, You need sql file containing the initial table configurations to insert to source engine node with at least 4 parameter below to make trigger and synchronization:

insert into sym_node_group (node_group_id, description) values ('MSSQL', 'SQL Servers Group');
insert into sym_node_group (node_group_id, description) values ('MYSQL', 'MYSQL server');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('MSSQL', 'MYSQL', 'P');
insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('MSSQL_2_MYSQL', 'MSSQL', 'MYSQL', 'default',current_timestamp, current_timestamp);
 
insert into sym_channel (channel_id, processing_order, max_batch_size, enabled, description) values ('main_channel', 1, 100000, 1, 'channel for replication');
 
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table1','table1','main_channel',current_timestamp,current_timestamp);
 
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table1','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);
insert into sym_trigger (trigger_id,source_table_name,channel_id,last_update_time,create_time) values ('table2','table2','main_channel',current_timestamp,current_timestamp);
 
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('table2','MSSQL_2_MYSQL', 100, current_timestamp, current_timestamp);

• And the last step is run the script for source engine node and destination engine node.

  If you want the connection is only one-way just