SQL Server Change Tracking vs min_active_rowversion()
In a bit old post I wrote about min_active_rowversion() as the best way to get the new anchor showing you that @@DBTS has its flaws and thus should be avoided. Min_active_rowversion() was introduced in SP2 for SQL Server 2005. If you have code that still uses @@DBTS, you are running the risk of missing changes from the server, do yourself a favor and change it now.
Despite the a fact that min_active_rowversion() addressed a major problem for sync developers, it did not solve it. Check out the diagram below:
T1 made a change to R1 row which received a timestamp of 50. The transaction did not commit and when you call min_active_rowversion() you will end up with the value of 50. That’s quite what we expect; sync should then synchronize up to 50-1 = 49 anchor. So what if we have few more transactions, like T2, that started after T1 and committed soon thereafter. If T1 did not commit, then min_active_rowversion() will remain at 50 until T1 commits. This could be a problem in many situations. You see, min_active_rowversion() did address the problem but did not solve it. Still much better that @@DBTS.
If we dig deeper into what the problem really is, we find that timestamp value is assigned at the statement execution time and not at transaction commit time. Had the row version be assigned at the transaction commit time, we would have a real solution for this problem. Fortunately, that’s exactly what SQL Server Change Tracking does for you in SQL Server 2008. Each transaction gets a version at the commit time and all the rows affected by the transaction will get that version. Check out the diagram below:
SQL Change Tracking will assign version at transaction commit time and all the rows affected by the transaction will have the same version. Now, when you try to sync before T1 or T2 starts, you should get 49, whereas after T2 commits, you will receive a value of 50. You see, now you are not stuck waiting for that long transaction to commits to sync changes that happened in subsequent transactions. Pretty cool, huh! …
SQL Server Change Tracking is a reliable way to track changes and designed from the ground up to address the needs of data synchronization applications!
Update: Just to let you know, I left Microsoft to start a new company, Raveable Hotel Reviews . See examples: Top 10 Hotels in Myrtle Beach, Best Hotels in New York City, Romantic Hotels in Seattle , Hotels with a kitchen or kitchenette and Top 10 Hotels in San Francisco . 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.
