Multiple source nodes to one target node data replication

[Maneshii]

I'm new to SymmetricDS. It's bit confusing for me how to configure this setup on SymmetricDS. Our requirement is like this.

We have 4 MSSQL databases (DB1, DB2, DB3 and DB4). All 4 should be pointed to one target db (DB5). So I created 4 source nodes for each source databases (DB1, DB2, DB3 and DB4) and one target node for the DB5 in the same SymmetricDS installation. All the changed data and initial load in 4 source nodes should be routed to DB5 only, not the other way round. Hope this requirement is clear.

Could you please help me on configurations of the setup? I need to clarify below doubts I have. Could you please assist me on the following points if those are the correct/recommended way of configuring this requirement?

1. I created 4 properties files in engines folder for each source db and one for the target db. All 5 engines are in the same SymmetricDS installation.

2. I kept one source db as the root node and added its sync url to the registration url of other 3 source nodes properties files.

3. I executed create-sym-tables command for each node and started the node using sudo ./bin/sym_service.bat start but it failed. Then sym.bat worked.

4. Updated sym_node_group, sym_node_group_link, sym_router, sym_channel for each source dbs.

5. Updated tables on SYM_TRIGGER and SYM_TRIGGER_ROUTER and executed initial load for each node.
   sudo ./bin/symadmin.bat --engine corp-<dbname> reload-node 001

6. Then restarted the service using,
   sudo ./bin/sym.bat

7. Only the data in DB1 node has been routed to the target. Shouldn't it route all 4 source db data to the target db? Am I missing any configuration or step?

Thank you.

[Jake Van Meter]

The Target needs to be the registration node. Put the Sync url of the Target node in all of the source node's engine files. This will allow them to all see the target. You can optionally set Reverse initial load to true, so once those Sources register with the Target, they will send an initial load automatically to the Target without having to be manually triggered. You can use the documentation found in the link below to look into Reverse initial load more:
https://www.symmetricds.org/doc/3.14/html/user-guide.html

[Eric Long]

The reload-node command is between a node and the root node -- forward load from root to node, or reverse load from node to root. You can send loads between a source and target node using the sym_table_reload_request table. We can work on updating the reload-node command to allow a source and target node.

[Maneshii]

Hi Jake & Eric,

I created corp-000 as the target node and store-<dbname> as the source nodes. I added the sync.url of the target node as the registration.url of source nodes engine files as you have suggested. Then, </dbname>

1. Executed create-sym-tables for each engine.
2. Set rev_initial_load_enabled=1 in SYM_NODE_SECURITY in each source db.
3. Updated sym_channel, sym_trigger and sym_trigger_router for each source db.
4. Inserted following 4 records into SYM_TABLE_RELOAD_REQUEST table in target db for each source nodes.

insert into SYM_TABLE_RELOAD_REQUEST (target_node_id, source_node_id, trigger_id, router_id, create_time, create_table, last_update_time) values ('000',  <dbname>, 'ALL', 'ALL', current_timestamp, 1, current_timestamp);

1. Then started the service.

sudo bin/sym_service.bat stop
sudo bin/sym_service.bat uninstall
sudo bin/sym_service.bat install
sudo ./bin/sym.bat

I noticed that all the source nodes has sent the create table scripts for their tables. But I see only one source db triggered the initial load for its table.
I have added following parameters to all the source engine files.

initial.load.create.first=true
db.validation.query=select 1
create.table.without.indexes=true
create.table.without.foreign.keys=true

Could you please help me find what I have missed here?

Thanks.

[Eric Long]

I don't think step 3 is needed (updating sym_channel, sym_trigger, sym_trigger_router on each source db) because each store node will register with corp-000 and receive a copy of all SYM tables. You don't need step 4 with sym_table_reload_request because you are using the reverse initial load (sym_node_security.rev_initial_load_enabled=1).

Other that that, the steps sound correct. Look in the logs/symmetric.log to see if there are any errors. If you can post logs, we can take a look.

[Maneshii]

Any update on this?

[Maneshii]

Any update on this?

[Maneshii]

Any clue on this?

Thanks

[Philip Marzullo]

Can you attach your symmetric.log file from all of the source nodes that do not send data to the target?

[Maneshii]

I attached the symmetric.log herewith.
Steps done :
1. Executed create-sym-tables for each engine.
2. Set rev_initial_load_enabled=1 for each node on SYM_NODE_SECURITY in source db.
3. Updated sym_channel, sym_trigger and sym_trigger_router in source db.
4. Then started the service.

sudo bin/sym_service.bat stop
sudo bin/sym_service.bat uninstall
sudo bin/sym_service.bat install
sudo ./bin/sym.bat

[Maneshii]

Any update on this?

Thanks

[Maneshii]

Is there any update?

[Maneshii]

Is there any update?

[Evan Miller]

I searched through your log file and it looks like 3 of your 4 source nodes successfully sent loads to the corp-000 node:

Line  8690: 2023-11-30 05:09:13,954 INFO [store-dbd] [DataExtractorService] [store-dbd-push-default-2] Initial load ended for node 000
Line  9147: 2023-11-30 05:09:19,814 INFO [store-dbc] [DataExtractorService] [store-dbc-push-default-2] Initial load ended for node 000
Line 10741: 2023-11-30 05:09:30,754 INFO [store-dbb] [DataExtractorService] [store-dbb-push-default-3] Initial load ended for node 000

The store-dba node is encountering the following error when sending data to the corp-000 node:

org.h2.jdbc.JdbcSQLException: Schema "information_schema" not found;

Can you check whether the corp-000 node's H2 database contains the information_schema?