SQL Server 2008 Change Tracking

Question

We want to use Change Tracking to implement a two-way sync between a SQL Server 2008 Enterprise/Standard instance, and an Express 2008 instance.

When we read the remote changes, and then make the adjustments on the local server, how can we keep those statements from being change tracked? I foresee endless loops of one server tracking a change, then the other making the change and also tracking the change, the other server making the change, etc.

Disabling change tracking on that table while performing the sync operations could potentially miss changes from other processes on that table, so I don't think that's the answer.

Is there a way to disable change tracking on a per-statement or per-transaction basis?

EDIT: I discovered the WITH CHANGE_TRACKING_CONTEXT command, so I might be able to use that to specify when the changes are performed by the sync code so that the sync code itself won't pick those up and use them.

Answer 1

Change Tracking isn't really meant to be used as bi-directional replication. You should figure out some way to determine the instance where the change was actually made, then your "replication" code should be able to ensure that the changed rows on the replicated server do not wrap back to the original server again.