Using SQL Server 2008 Change DataYou've written a nice database application that allows the user to manipulate data in an SQL Server database. All is good, but the users would also like to see how the data in the database has changed over time. For instance, John wants to know how the customer records changed last week and Jane would like to know how those details were added into her latest sales order.
Technically speaking, keeping such a log inside your application is quite straight forward. If you have a properly architected layered application, you could add logging code into your data access layer (DAL), and then construct a user interface for this log. But, adding such capability to each and every operation and table is often time-consuming work. Wouldn't it be great if the database you use could help you in this common, but often times taxing work?
Enter SQL Server 2008 Change DataIf you are using Microsoft's latest SQL Server version, then the database can help. SQL Server 2008 Enterprise Edition (Developer Edition also works, as it's based on Enterprise) supports two features that are collectively called Change Data. More precisely, these features are named Change Tracking (CT) and Change Data Capture (CDC).
Both services have the same aim: to help developers easily collect information about changes in the database data. Of the two features, Change Tracking is a simpler one and is only able to record that something has changed in the database. It supports the familiar DML statements: INSERT, UPDATE and DELETE. Change Data Capture on the other hand is able to record that something changed just like Change Tracking, but also how the data changed.
Of course, being able to use the change tracking features requires some work on your part. First, you need to enable the change data collection features in your database. Second, you need to write code to retrieve the change data and process it. Additionally, you also need to understand the consequences of enabling change data, as both performance and database storage considerations must be kept in mind. For small databases, these overheads might not cause you to spend sleepless nights, but with larger databases you need to be more careful.
Using Change TrackingIf you are happy to live with the inherent overhead, using change data collection features is a good way to save the trouble of implementing these by hand. The easier of the two related features is Change Tracking, which is enabled for each database and table you want to track. Change Tracking can tell which rows in which table have changed. But on the other hand, Change Tracking data won't tell you how the data changed: you cannot use it to retrieve the older data back. You just know for instance that column N changed while doing an UPDATE, but no more.
But even with these limitations, Change Tracking can be useful. To get started with the feature, you first need a suitable SQL Server database that has been created at least with the compatibility level of 90 (this means SQL Server 2005 or later database, SQL Server 2008 databases by default have the compatibility level of 100). For testing purposes, you might even wish to create a completely new database. The next step is to enable Change Tracking. You can either do this from the database's properties in SQL Server Management Studio (see Figure 1) or by running the following T-SQL statement:
Click here for larger image
Figure 1. Change Tracking can be enabled from database's properties in SQL Server Management Studio.
ALTER DATABASE MyDatabase SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
After enabling the feature on a database level, you need to enable change tracking for each table you wish to monitor. This is done with the following SQL statement:
ALTER TABLE dbo.MyTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON)
Just as with the database level setting, you can also enable change tracking from the SQL Server Management Studio. The setting can be found from the properties dialog box for each table. Remember that in addition to typing these scripts by hand, you can let SQL Server Management Studio help you. Its Template Explorer pane contains numerous ready-made scripts that you can use with little modifications. This applies to change tracking and change data capture functions as well (Figure 2).
Figure 2. Management Studio's Template Explorer has ready-written scripts to help use change tracking functions.
When you enable change tracking and start adding, deleting or modifying records in the table for which you have enabled tracking, you can immediately use the built-in functions to read the logs. SQL Server 2008 provides several of these functions, most of which are provided as table- value functions, or TVFs. A table-value function is a regular function, but instead of returning a single value like a scalar function, these functions return a table's worth of data: zero or more rows.