Using SQL Server 2008 Integrated Change Tracking to Optimize Data Synchronization

  • Article

I thought I would take the chance to talk to you a little bit about a major enhancements we have made with synchronization in SQL Server 2008. The feature I am referrring to is called SQL Server 2008 Integrated Change Tracking.

The idea behind this feature is that it enables SQL Server to track data changes to your database rather than relying on you to create a change tracking technique.

Why is this feature so great? Well in my experience I have found that DBA's are less then enthusiastic when you tell them that in order to track changes, they will need to add triggers to each of the tables. Then they will either need to add an additional tracking column to that table (or to a separately linked table) and will need to create one tombstone table per table to track deletes. Oh, and by the way these triggers will need to be fairly complicated because you really need to deal with all of the isolation issues that come along with proper change tracking... and cross your fingers that these changes do not affect your existing applications. Not a fan of that idea? I am not surprised.

Well with SQL Server 2008, you can now simply turn on an option to enable change tracking. Then you tell SQL Server which tables you want it to monitor. From that point SQL Server will start monitoring these changes and store them in a separate change tracking table which you have access to. If you want to get the changes since a certain point in time you simply join the ChangeTable to your base table and presto you have all of the inserts, updates and deletes. 

As an added bonus, this feature has been integrated into the existing Visual Studio SP1 beta that is now available for download. Guy Burstein has a great summary of this feature and I am going to steal one of his screen shots below.

Sync Services for ADO.Net Visual Studio 2008 Service Pack 1 Beta VS2008 SP1

As you can see from the screen shot, Visual Studio will detect if you are using SQL Server 2008 and let you choose to automatically enable change tracking for the tables you have selected to synchronize.

After you have selected change tracking, Visual Studio can go out and make the changes to your database for you. Of course, you still have the option to use the previous method of Change Tracking using triggers with SQL Server 2008. Visual Studio will still make those changes to your database to support change tracking, however given the performance of integrated change tracking and the added simplicity I think there will be very few times when the previous technique will be used. 

 

Liam Cavanagh