topic Re: multiple sources to same destination using CDC in Qlik Replicate

multiple sources to same destination using CDC

rteruya — Thu, 13 Feb 2025 15:24:23 GMT

Good morning everyone!

I have different databases with the same tables and I was wondering if I can use qlik replicate to have a single destination table showing live data.

example:

server1.customer_table (source end point connection created using MS SQL Server CDC)
server2.customer_table (source end point connection created using MS SQL Server CDC)
server3.customer_table (source end point connection created using MS SQL Server CDC)

I want to see if I can get
target_server.customer_table getting live data from 3 tables mentioned above.

Thanks in advance for your help!

Re: multiple sources to same destination using CDC

khchoy — Thu, 13 Feb 2025 23:48:56 GMT

The important point is whether or not there can be duplicate key values when merging records from three tables.

If the PK value or the columns configured in the PK contain values that can distinguish each server, there will be no problem.

task1 for capture from server1
Server1.customer_table -> target_server.customer_table

task2 for capture from server2
Server2.customer_table -> target_server.customer_table

task3 for capture from server3
Server3.customer_table -> target_server.customer_table

However, if the PK values of records generated from each server can be duplicated, a distinguishing column must be added to the target table.
And the added column (column name, e.g. From_Server) must be set to a specific value, that is, a value that can distinguish the source. For example, a record generated from server1 is set to the value of 'server1'. Adding columns or setting specific values can be done in "table settings" -> "transform".

table layout
source customer_table (PK-col,col1,col2,...coln)
targer customer_table (From_Server,PK-col,col1,col2,...coln)

Re: multiple sources to same destination using CDC

john_wang — Fri, 14 Feb 2025 08:19:11 GMT

Hello @rteruya ,

Welcome to Qlik Community forum and thanks for reaching out here!

Thank you for your input, KwangHo @khchoy . If the three source databases undergo frequent changes, there is a possibility that the three tasks may impact each other due to lock contention.

To mitigate the risk, I suggest replicating the tables from the three source databases into three corresponding target tables. This approach will reduce dependencies between the tasks. Additionally, you can create a VIEW in the target database to present a unified, real-time view of the 3 tables data. With this structure, the tasks design, management and maintenance is much easier.

Hope this helps.

John.