Freigeben über

Change Tracking for the Masses

  • Artikel

I admit that writing your own change tracking logic for each table you want to synchronize is a little bit annoying and suboptimal experience at best. Not just that, adding triggers affects the performance of DML operation negatively. You also need to store information about deleted rows (tombstones) which we typically do in a separate table. At some point, you would need to clean up the oldest tombstones that have been sent to your existing sync clients since there is no point in keeping them anymore. All of this adds up to the complexity of your system.
 
While nobody claims that build sync application is any easy task, there is nothing stopping us from reducing the complexity especially for common operations like change tracking.
 
Introducing SQL Server Change Tracking
 
The good news is that better solutions are in the pipeline. In the latest CTP5 of SQL Server 2008 there is a cool new feature called SQL Server Change Tracking. This feature is all what you wish for from a sync-friendly store: A lightweight, reliable change tracking infrastructure! With SQL Server Change tracking, you can achieve the following:

  • Enable change tracking for a given table without making any schema changes to your data table. The tracking is happening deep inside the engine that is extremely fast and won’t degrade the performance of your DML compared to triggers.
  • Configure automatic cleanup of the tracking information
  • Track changes at column level
  • Enumerate changes starting from a given version. SQL Server Change Tracking record changes per transaction scope which works better than timestamp based approach. 

Looks promising, right?! I personally believe that SQL Change Tracking is the best feature coming up in SQL Server for data sync and replication scenarios. But for full disclosure, I am biased toward anything sync :) there are loads of new features in SQL Server 2008.
 
You might be wondering about what it means to “record changes per transaction scope” and why is that better than using @@DBTS or min_active_rowversion(). Well, I owe you a detailed explanation; it just needs a separate post. Stay Tuned …
 

Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews. See Examples: Romantic Hotels in Myrtle Beach , Top 10 Hotels in Seattle ,Best Hotels in Miami, Best Hotels in San Francisco , Hotels with in-room jacuzzi and Best Hotels in Los Angeles . 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.

 

SqlChangeTracking.jpg

Comments

  • Anonymous
    January 24, 2008
    PingBack from http://msdnrss.thecoderblogs.com/2008/01/24/change-tracking-for-the-masses/

  • Anonymous
    January 24, 2008
    This is GREAT news!  I'm really looking forward to the SQL 2008 / Visual Studio 2008 launch event.  I've been playing around with Sync Services in VS 2008 and loving it (also built a custom clientsyncprovider).  I was however fearful of the schema changes.  Not that they are anything major, just outside my normal development, so could create issues later on. I'm curious how Change Tracking handles schema changes?  Also, will you still be able to use BuilderAdapters with change tracking? Last question, answer if you can, any news on a SQL Express client provider?  I build one for VistaDB, but I don't feel it's as good as the SQL CE one you guys have, however, I can't really use SQL CE at this point.

  • Anonymous
    January 28, 2008
    The comment has been removed

  • Anonymous
    February 19, 2008
    I fear this feature will not be inlcuded in the Express Edition. Is this assumption correct?

  • Anonymous
    February 23, 2008
    I am not sure mic030. I am not part of making the decision to which feature goes to what edition. My guess though, it may make it... let's wait and see. Rafik