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 …
I am not actively blogging about Sync Technologies. Please see Sync Team Blog for more updated content.