Sync Services and SQL Server 2008 Change Tracking…a perfect match?

Just saw Aaron Bertrand's post from PASS on SQL Server 2008 Change Tracking vs Change Data Capture (change data capture (or CDC) is in the current CTP; change tracking is not). I'm not at PASS this week myself, but home while the house is being re-roofed. His post seems to confirm something I'd suspected all along.

Change tracking seems to go hand-in-hand with Sync Services for ADO.NET. I've been following Sync Services for a while; it's a set of libraries for controlling and implementing synchronization for disconnected database applications. Its current shipping vehicle is the Visual Studio Orcas Beta 2 release, along with SQL Server Compact Edition version 3.5. The fact that SQL Server 2008 Change Tracking provides a mechanism to keep track of which user (the sync OriginatorID) made a change (CDC doesn't) and also seems to provide automatic change table management for DELETEs (CHANGE_RETENTION) and a mechanism to "get the set of changes that have occured from a baseline" (the "sync_last_received_anchor" in sync services) makes Change Tracking line right up with what Sync Services requires.

Although Aaron mentions "offline stores like Outlook in cache mode", SQLCE is an exciting offline store because its currently deployed in places like Windows Media Player, Zune, Media Center PC, and more. SQLCE runs on mobile devices and desktops. Sync Services isn't available for mobile devices yet, but is said to be "in progress".

When you set up a Sync Service app, you currenly must make changes to the database (triggers, "tombstone tables for deletes", and such) referred to by the "ServerProvider", in order to track the information Sync needs. The Sync "ServiceProvider" architecture layers over/shares concepts with the ADO.NET provider model. But you don't have to make changes for the SQLCE 3.5-side (SQLCE is the only current "ClientProvider" that Sync Services supports) because, "support for sync is built in". Well…maybe it's built in to the server too, with SQL Server 2008. Bet we'll see (at least one) demo with Sync Services when the Change Tracking feature ships.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.