Need a Change? Try Katmai!

In the past, when we wrote applications that required the tracking of changing data, we typically implemented this through a series of Triggers and/or Timestamp columns. We could then write application code to copy changed data from one location to another using, for instance, sophisticated ETL tools such as Informatica or Microsoft's own Integration Services. This would not only be time-consuming but may be expensive too. Now in SQL Server 2008, Change Tracking and Change Data Capture can be enabled automatically at the Database and Table level.

These features allow SQL Server to track changes efficiently through its own internal system tables without the need for coding Triggers or defining extra columns. So what's the difference between Change Tracking and Change Data Capture? Well, if you are interested in what rows and columns have changed in a table and only require the current values to synchronize with another database then Change Tracking will suffice. Change Tracking uses a synchronous process to record the occurrence of changes in special system tables. Using Change Tracking functions such as CHANGETABLE, allow an application to determine changes at the row or column level and to detect change conflicts using a 2-way synchronization model, if necessary, between database tables. However, the historical changes are not recorded change by change. For instance, the fact that a row or column has changed multiple times will be tracked but the actual data value changes will not be captured. That is where Change Data Capture (CDC) takes over. CDC will capture all the data changes which make it ideal for enhancing ETL processes that would have had to be done manually before. The trade off is disk space; CDC uses extra storage to capture that changed data, which may amount to a significant amount of storage for very active tables. However, the processing that is automatically performed by SQL Server, at least, is asynchronous so should have minimal impact on performance.

Change Tracking and Change Data Capture are more cool features that Microsoft are giving away with Katmai. The Informaticas of the world have to go that extra mile to justify their license fees. Good for the consumer? Yes, as long as the competition can stay in business, that is.

Cheers
Brian