Track data changes
May include but is not limited to: change tracking, database audit specification, CHANGETABLE
There are two tools in SQL Server serving the purpose of auditing data changes. The first is Change Tracking, which you can find in every version, the other is Change Data Tracking, can be found in Enterprise Edition.
The difference in a nutshell: using CDC, you can see what’s changed and how, in an asynchronous manner (there’s a latency involved). With CT, you can see that there’s a change occurred, but can’t see the changes themselves, and do so in a synchronous manner.
You can imagine the benefits and drawbacks: CDC uses much more resources, while providing more information. Change Tracking doesn’t provide historical data, while still indicate the type of change made. Now let’s see both techniques in detail.
The first thing to do with CT is to enable it. You must define that you need this functionality at the database level. You’d write a syntax like this:
ALTER DATABASE MyDataBase
SET CHANGE_TRACKING = ON
After this is done, you need to enable it on the table level, with the following syntax:
ALTER TABLE MyTable
After you’ve enabled CT on the table level, you need to retrieve the initial data version. You can do so by calling the CHANGE_TRACKING_CURRENT_VERSION() method. Then you can pass the retrieved value into the CHANGETABLE function, which you can query in a SELECT statement. Something like this:
@syncver = CHANGE_TRACKING_CURRENT_VERSION()
SELECT * FROM
CHANGETABLE(CHANGES Scehma.Table, @syncver);
Instead of always assigning the current version number, you can specify explicit version numbers. When you need other values than just the primary key of the table (by default, you only get that), it is a good idea to join the table based on the primary key.
Change Data Capture
Another useful technique to keep notified about changes in a database is CDC. CDC is a more powerful solution, available in SQL Server Enterprise, Evaluation or Developer edition. In order to use it, you need to enable it, both for tables and databases.
The line above invokes the stored procedure which enables CDC in the database level. After this, you need to turn it on for the tables, too:
EXEC sys.sp_cdc_enable_table @source_name=”myTable” @schema_name=”mySchema”
Now that you have your tables enabled, you can query them for changes. A good candidate for this is the cdc.fn_cdc_get_all_changes stored procedure, which returns all changes (a shock).
When you work with CDC, you better familiarize yourself with the concept of LSNs. It is the abbreviate of Log Sequence Number. CDC stores information in logs, and these numbers are the keys to access those logs. For example, get the cdc.fn_cdc_get_net_changes procedure, which returns net changes, between the boundaries you specify, in LSN values.
I must admit that I’m not quite interested in these tools, nor I understand the concept behind them, but this is part of the exam, too. Dig up MSDN and BOL for the details.