Share via

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

Comments

  • Anonymous
    June 26, 2008
    PingBack from http://wordnew.acne-reveiw.info/?p=3539

  • Anonymous
    October 01, 2010
    Based on your Blog i enabled Change tracking, lets take an Expample, i have 3 computer and all of them has same Databse aand tables, if i enable the change tracking how do i merge the data  between different computers and how frequently i can do.

  • Anonymous
    April 03, 2011
    Does this tool show you who made what changes to SQL Server? Change tracking doesn’t do us  any good unless we can find out who made the change. We use netwrix sql server change reporter for this, but I’m not sure if sql server 2008’s integrated change tracking can do it.

  • Anonymous
    August 10, 2011
    Anderson - Change Tracking only tells you that a row has changed.

  • Anonymous
    November 24, 2014
    Great article, it describe step by step procedure to track changes in SQL server 2008. Actually I tested an automated application from www.lepide.com/sql-server-audit that helps to track all changes happened in sql server and get instant alert as per the auditing requirement.