Search code examples
sql-servermariadbsymmetricds

One-way SymmetricDS sync between local machine and cloud instance

I'm trying to configure one-way data flow from Microsoft SQL Server running on a local machine to MariaDB running on a VPS in a cloud.

Local machine doesn't have a public static IP address, but the VPS does.

I managed to connect them, but no data is propagated.

Is such setup (machine with no static Public IP -> machine with static public IP) supported? Or both machines need to be able to connect to each other somehow? Am I missing something?

Here are my config files MariaDB VPS (corp/main server/target): engines/corp-000.properties

engine.name=corp-000

db.driver=org.mariadb.jdbc.Driver
db.url=jdbc:mariadb://localhost/LKDSDISP
db.user=<username>
db.password=<passwordhere>

registration.url=
sync.url=http://<ip>:31415/sync/corp-000

group.id=corp
external.id=000

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

MS SQL on a local machine (source) engines/sovetskaya-001.properties

engine.name=sovetskaya-001
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://localhost:1433/LKDSDISP;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880

db.user=<username>
db.password=<pwd>

registration.url=http://<mariadb-ip-here>:31415/sync/corp-000

sync.url=http://<local-machine-public-internet-ip-here>:31415/sync/sovetskaya-001
group.id=sovetskaya
external.id=001
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000

initial.load.create.first=true

My router/node/trigger configuration is like this:

insert into sym_node_group (node_group_id, description) values ('corp', 'Target MariaDB server');

insert into sym_node_group (node_group_id, description) values ('sovetskaya', 'Sovetskaya MS SQL Server source');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('sovetskaya', 'corp', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('corp', 'sovetskaya', 'W');

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('sovetskaya_2_corp', 'sovetskaya', 'corp', '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 ('item','item','main_channel',current_timestamp,current_timestamp);
    
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('item','sovetskaya_2_corp', 100, current_timestamp, current_timestamp);

The source table in MS SQL Server is called dbo.item.

Solution

  • Turned out I was missing three key parts:

    1. Creating the target database table on corp-000 manually. Thought SymmetricDS creates one automatically, but it didn't.
    2. Enabling "initial reverse load" in corp-000.properties (auto.reload.reverse=true). Just in case I enabled it in both target and source nodes properties.
    3. Inserting (auto.reload.reverse, true) into sym_parameter table (look below)

    My final properties files are: MariaDB VPS (corp/main server/target): engines/corp-000.properties

    engine.name=corp-000

db.driver=org.mariadb.jdbc.Driver
db.url=jdbc:mariadb://localhost/LKDSDISP
db.user=<username>
db.password=<passwordhere>

registration.url=
sync.url=http://<ip>:31415/sync/corp-000

group.id=corp
external.id=000

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
auto.reload.reverse=true

    MS SQL on a local machine (source) engines/sovetskaya-001.properties

    engine.name=sovetskaya-001
db.driver=net.sourceforge.jtds.jdbc.Driver
db.url=jdbc:jtds:sqlserver://localhost:1433/LKDSDISP;useCursors=true;bufferMaxMemory=10240;lobBuffer=5242880

db.user=<username>
db.password=<pwd>

registration.url=http://<mariadb-ip-here>:31415/sync/corp-000

sync.url=http://<local-machine-public-internet-ip-here>:31415/sync/sovetskaya-001
group.id=sovetskaya
external.id=001
job.routing.period.time.ms=5000
job.push.period.time.ms=10000
job.pull.period.time.ms=10000

initial.load.create.first=true
auto.reload.reverse=true

    My router/node/trigger configuration (run this in your Database console to add necessary configuration values into sym_* tables, which SymmetricDS uses for syncing configuration):

    insert into sym_node_group (node_group_id, description) values ('corp', 'Target MariaDB server');

insert into sym_node_group (node_group_id, description) values ('sovetskaya', 'Sovetskaya MS SQL Server source');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('sovetskaya', 'corp', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action) values ('corp', 'sovetskaya', 'W');

insert into sym_router 
(router_id,source_node_group_id,target_node_group_id,router_type,create_time,last_update_time)
values('sovetskaya_2_corp', 'sovetskaya', 'corp', '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 ('item','item','main_channel',current_timestamp,current_timestamp);
    
insert into sym_trigger_router (trigger_id,router_id,initial_load_order,last_update_time,create_time) values ('item','sovetskaya_2_corp', 100, current_timestamp, current_timestamp);

INSERT INTO sym_parameter (external_id,node_group_id,param_key,param_value) VALUES ('ALL','sovetskaya','auto.reload.reverse','true');

create table item (name varchar(50), count int);

    Then I run bin/sym on corp-000 as server:

    bin/sym -e corp-000 -S

    and on sovetskaya local PC as client:

    bin\sym -e sovetskaya -C