2

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.

uson1x
  • 407
  • 3
  • 16
  • 1
    this scenario should work. local machine (sovetskaya) should be Pushing data to the server (corp) and when there's data that should be synchronized from the server it will be Waiting for the local machine to reach for it. if I understand correctly, local to server data synchronization is not working. are there any logs on the local machine? – Boris Pavlović Sep 30 '20 at 09:10
  • 1
    Thank you, Boris, for confirming that it should be supported. That gave me impulse to dig in further. Turned out the missing parts were: enabling "initial reverse load" and creating DB table manually. I will post my findings as an answer. – uson1x Oct 02 '20 at 14:55

1 Answers1

2

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

uson1x
  • 407
  • 3
  • 16