Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

What edition of SQL Server 2008 does change tracking require? Change Data Capture requires Enterprise from what I've read, I'm hoping change tracking will run on standard.

TIA Dave

share|improve this question

3 Answers 3

To show the difference in audited information, and explain Comparing Change Data Capture and Change Tracking better, here are some examples:

I modified the records in a table, the changes are highlighted – the rows with AddressIDs 1, 5 and 2, in that order

enter image description here

Then I added a row AddressID = 32522, and deleted it afterwards The results are:

enter image description here

The values shown indicate the changes made – three updates on the rows with AddressID 1, 2, and 5 and deletion of the row with AddressID = 32522. There is no clear indication that the 32522 row was first inserted, but according to the Change Creation Version (ChCrVer) and Change Version (ChVer) values 5 and 6, there were 2 changes. The second one was a delete, but we don’t know what the first one was

Here is the information provided by Change Data Capture

enter image description here

__$operation = 1 is the deleted row, 2 inserted, 3 is the row that was updated before the

If a column was first updated from ’1970 Napa Street’ to ‘123 Street’ and then to ‘99 Daisy Street’, you can get all 3 transactions, or just the net (last) value

enter image description here

enter image description here

While Change Tracking shows only what was changed and whether the change was an insert, update, or delete, Change Data Capture shows the values inserted, deleted or updated for the modified rows. For updates, it shows both old and new values of the updated row

The feature doesn’t track the user who made the change. To do that, you have to create a new field where the user’s details are stored and updated after each change. The same goes for the time of the change and the machine used to make the change. The execution of the SELECT statements and object access are not tracked

share|improve this answer

Change Tracking is in every edition - the whole point of it is to support the occasionally-connected systems scenario - e.g. a handheld device. Change Data Capture is Enterprise only.

And here's a feature article I wrote for TechNet Magazine in the November 2008: SQL Server 2008: Tracking Changes in Your Enterprise Database, that gives some of the gotchas you might run into as a DBA on a system with either Change Tracking or Change Data Capture enabled, and an expanded comparison sheet between them. From talking to the tech writers in the SQL Books Online team, they know that the BOL needs to be upgraded a bit for these two features - the TN Mag article is a bit more 'accessible' right now.

Main one for Change Tracking is that you should really use snapshot isolation too -> tempdb load.

share|improve this answer

See this:

http://msdn.microsoft.com/en-us/library/bb522489.aspx (Change Data Capture)

and this:

http://msdn.microsoft.com/en-us/library/cc280462.aspx (Change Tracking)

and this:

http://msdn.microsoft.com/en-us/library/cc280519.aspx (Comparison)

and, finally, this seems to indicate that Change Tracking is supported by Express Edition:

http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/3452dd14-d4d1-48e0-9de4-a193619a72be

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.