Over the weekend I had a terrible experience with the external hard drive I use.  The drive completely crapped out leaving me no options other than banging it on my head.  This drive held everything in my day to day work at Magenic, so a lot of content.  And I know, the irony of the data guy being burned by not having a recent backup is truly fabulous.  Fortunately, I started using Live Mesh several months ago and had all project folders from Visual Studio and SSMS pointed there.  As painful as it was to lose several weeks worth of client documents and some presentation slide decks the code survives.  At the end of the day that makes Live Mesh my favorite Microsoft product and I would strongly recommend it to anyone that cares about keeping code or other content.

I’m so out of touch with Twitter than I’m not completely sure that I used the proper wording for the title…at any rate James Ashley has shamed several of us in the Atlanta office into taking the Twitter plunge.  You can follow me at http://twitter.com/WhitneyWeaver.

I would like to thank everyone that came out to the Atlanta Code Camp and stopped in to hear me talk about Change Data Capture.  The slides and code samples from the session are available at the end of this post.  Enjoy!

Paul Nielsen posted about a rather provocative statement a few days back regarding SQL in the cloud.  The post itself raised my eyebrows and led to some conversation with my Magenic co-workers.  In referencing the post I started keeping up with the rather lengthy debate in the comments.  A fascinating read if you have a moment.

As I said to one of my co-workers, Paul knows a hell of a lot more than I do but I have to disagree with his timelines.  The bandwidth penetration in our country is but on of the massive hurdles that need to be removed before cloud data storage is a real option.

When SQL Server 2008 launched several people focused on the Change Data Capture feature.  I was one of them as the functionality provided allowed me to leave triggers and all their associated craziness behind (for the most part).  Another similar feature that seemed to fly under the radar was Change Tracking.  At first, you might see these two features and roll your eyes…another duplication of functionality by Microsoft.  Not really.  The two features answer very different questions when it comes to changing data in a database application.

Change Data Capture

This feature has been discussed pretty thoroughly on the the blog circuit, including this one.  You can see my original post here for a full description of Change Data Capture.

Change Tracking

As the name implies, Change Tracking is more interested in tracking the fact that a change occurred, not so much in storing said change.  This makes the feature more suited for applications that only need to synchronize or replicate data.  Disconnected systems are an example of the application types that will benefit from Change Tracking.

Change Tracking is enabled initially at the database level via ALTER DATABASE syntax:

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

Both the CHANGE_RETENTION and AUTO_CLEANUP options are configurable to your needs.

Next, we must enable Change Tracking for each table that we want tracked.  This is accomplished through ALTER TABLE syntax:

ALTER TABLE Sales.OrderDetail
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

When TRACK_COLUMNS_UPDATED option is set to ON the Database Engine stores each column from the table that took part in the DML action.  This can improve the synchronization activities of your application can specify only the update columns.  Because the setting adds extra storage overhead it is off by default.

If at some point we no longer wish to track changes for an object or database we simply reverse our steps with the following ALTER statements:

ALTER TABLE Sales.OrderDetail
DISABLE CHANGE_TRACKING;

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = OFF;

Key Differences Between Change Data Capture and Change Tracking

The implementation of the two features are quite different in some areas and need to be fully understood before employing them in your database.  The table below lists what I consider the more important differences. 

If you do your research and find the correct style of processing for your application you will greatly enjoy these two new features.  I will be using Change Data Capture for my current client and will post a follow up blog about our experiences.