Metadata is the Hart and Sole of Peer Sync

  • Article

Occasionally, I receive questions about using SQL Change Tracking with PeerSyncProvider. People who examine SQL Change Tracking in preview version of SQL Server 2008 have become fans rather quickly and see the value of using it. Using change tracking with ServerSyncProvider scenarios is a piece of cake and will get easier with the VS2008 SP1. Now how can you do the same with PeerSyncProvider?
The short answer is that it is not so easy. Not as easy as with ServerSyncProvider. The reasons might not be so obvious and I see many attempts of writing the commands for PeerSyncProvider adapters to work against SQL Change Tracking. While it might work for very limited scenarios, it is largely incorrect.

Again, the question is why?

Well, let’s think a little bit about the following SyncAdapter commands for the PeerSyncProvider:

  • InsertMetadataCommand
  • UpdateMetadataCommand
  • DeleteMetadataCommand

Notice that these commands are intended to make changes to the tracking metadata. That’s rather interesting and clearly different from the commands you supply to for ServerSyncProvider. Now it is becoming clear that we have a problem since SQL Change Tracking metadata (i.e. CHANGETABLE) are all read-only!

General Purpose Tracking Infrastructure

Remember that SQL Change Tracking is a general purpose tracking mechanism. You enable change tracking for a table with the sole purpose to be able to ask “what have changed since given version?” If applications are allowed to make changes to the metadata then this question is not reliable and thus useless. Making change tracking metadata read only makes all the sense in the world for its general purpose nature. 

Direct Metadata Manipulation

An obvious question here is: Why make direct changes to the metadata? Changes to the base table will be reflected in the CHANGETABLE, wouldn’t that achieve the same effect?
Well, for peer to peer data synchronization… you need more control than that. Let’s take the following two cases:

  • Synchronizing Deletes

    Let’s consider the following example, three nodes A, B, C. The nodes are connected as shown in the diagram. Follow the following scenario:

    1. Make a change R on node A
    2. Let A sync with B, B now has R
    3. B deletes R
    4. Let B sync with C. B will send the deleted row metadata to C

As B received the metadata for the deleted row, it needs to persist is. R never make it to B thus a normal delete command won’t generate the metadata we desire as it simply won’t do anything.

As a side note, the delete metadata must be persisted on B. Since B could be connected to another node (A, for example) which have R and thus needs to pass on the delete during subsequent sync. In P2P environment, one cannot make assumption about the other nodes and must maintain metadata for every deletes.

At a first glance one can come up with a clever work around for this scenario which is insert R then delete it right away. SQL Change tracking should record that, right? That’s right but the workaround cannot survive two or more tables with PK-FK relations which is the reality of most databases. Thus this workaround is short lived.

  • Resolving Conflicts

    When conflicts are detected and a winner is chosen, one needs to update the metadata such that this winner will propagate across the topology of nodes. That said, the version of the row needs to be pumped. Now consider the case where the local row was selected as the winner. With SQL Change Tracking, there is no way to make changes to the version as the metadata is not editable.

My point in this post is to answer a common question and illustrate the underlying technical reasons behind the limitation. That being said, you can still leverage SQL Change Tracking for Peer Scenarios albeit you need to use a side table to store the changes to the metadata. I am sure some of the readers will find this as a worthwhile challenge to tackle. Happy Sync’ing!

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews . See examples: Top 10 Hotels in Myrtle Beach, Best Hotels in New York City, Romantic Hotels in Seattle , Hotels with a kitchen or kitchenette and Top 10 Hotels in San Francisco . Your feedback is welcome on twitter.com/raveable, raveable blog.

I am not actively blogging about Sync Technologies. Please see Sync Team Blog for more updated content.

syncdel.jpg