Two-way synchronization in Data Transmission Service (DTS) supports only direct synchronization between two instances. It does not support cascading synchronization — data synchronized from instance A to instance B is not propagated to instance C. Configuring a three-instance topology causes data loss.
How data loss happens
When DTS synchronizes data between two instances, it tags each transaction using transaction tables. This tagging prevents data loopback — it stops already-synced data from bouncing back to the source. In a three-instance topology, this same mechanism blocks data from reaching the third instance.
The following diagram illustrates the problem.
In the diagram:
-
When DTS syncs data from Database 1 to Database 2, the transaction is tagged. The tagged data records are not synchronized to Database 3 — causing data loss in Database 3.
-
When DTS syncs data from Database 3 to Database 2, Database 1 is similarly excluded — causing data loss in Database 1.
Solution
Do not configure a synchronization task that is similar to the three-instance synchronization scenario described above.