12

We have audit trail implemented using triggers on our web application. Those triggers log field level updates. So, in a table, if you have 5 column values changed, we have 5 records in the audit trail one for each column with old value and the new value.

Recently we have upgraded to SQL Server 2008. I have been thinking of using the new change data capture feature as it gives a very neat row level update with very less efforts and it's super-easy to implement. Just wanted to get some opinions from people who have been using change tracking for any caveat or any other real-world useful info on this.

Any advice would be helpful.

Edit :- http://technet.microsoft.com/en-us/magazine/2008.11.sql.aspx?pr=blog

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ashish Gupta
  • 14,869
  • 20
  • 75
  • 134
  • This provides some good suggestions :-http://stackoverflow.com/questions/2684293/change-data-capture-or-change-tracking-same-as-traditional-audit-trail-table – Ashish Gupta Apr 08 '12 at 04:40
  • 1
    If you need to track that the same column was changed twice in quick succession, you might want to look at CDC rather than Change Tracking. [MSDN Comparison](http://msdn.microsoft.com/en-us/library/cc280519.aspx) – Damien_The_Unbeliever Apr 08 '12 at 07:36
  • 2
    I've been looking at it myself. The obvious issue (from an audit perspective) is there's no way to trace the user who causes each transaction. I wanted it for a [slightly other purpose](http://stackoverflow.com/questions/9836527/preventing-update-loops-for-multiple-databases-using-cdc), and still haven't used it in anger. – Damien_The_Unbeliever Apr 08 '12 at 16:04

2 Answers2

8

There are total four solutions found in SQL Server, following is the detail:

  • SQL Server Change Tracking [CTC]
  • SQL Server Change Data Capture [CDC]
  • SQL Server Audit Trail with Triggers [Generic - Manual]
  • SQL Server Audit

SQL Server Change Tracking [CTC]

Pros

  • Works on all SQL Server editions
  • DML Triggers and additional tables are not required, as single temp table is created by CTC named CHANGETABLE
  • Minimal disk space costs
  • Packaged functions available to query the data
  • Configurable retention policy and Auto clean-up of CHANGETABLE, Also auto clean-up can be turned off when needed
  • No need to access LDF file of that particular database
  • Purging/truncation is possible when CTC enabled

Cons

  • Need to enable on both database and each required table
  • Synchronous tracking mechanism
  • Tracking data of all tables is stored in single temp table named CHANGETABLE
  • Cannot get historical data also not give exact detail about previous and new data in tracking columns
  • Composite keys support issue
  • Requires primary key
  • Only maintains current state of the data w.r.t operation I = Insert, U = Update, D = Delete
  • SQL Server user group 'sysadmin' can only enable CTC

SQL Server Change Data Capture [CDC]​

Pros

  • This features provide a table-by-table solution, after enabling on database level.
  • Efficient & fast​, asynchronous tracking mechanism ​
  • Can get historical data, also able to give exact detail about previous and new data in shadow table
  • DML triggers are not required, as new table is created by CDC with prefix 'dbo_' and suffix '_CT' for each table.
  • All CDC related tables & functions will be referred with 'cdc' schema.
  • All CDC related stored procedures will be referred with 'sys' or 'cdc' schema

Cons

  • Information about the user who made the change isn't captured, for that you may need to create some extra columns existing each table desired to be tracked, the columns are like:

    CreatedAt datetime default (getdate()), CreatedBy nvarchar(100) default (suser_sname()),​ UpdatedAt datetime default (getdate()),​ UpdatedBy nvarchar(100) default (suser_sname())​

  • SQL Server Agent should be enabled for CDC, also CDC can't work properly, when the Database Engine service or the SQL Server Agent service is running under the NETWORK SERVICE account
  • Need to enable on both database and each required table
  • Works only on Enterprise, Developer and DataCenter editions
  • Need to access LDF file of that particular database
  • SQL Server Agent should be enabled to capture the data
  • Purging/truncation is not possible on main table when CDC enabled, Auto clean-up on shadow table can't be turned off once enabled​
  • Extra space is required for transaction log (LDF file)
  • Db's user group 'db_owner' or server's role 'sysadmin' can only enable CDC
  • There are limitations when tracking changes in columns containing XML, Sparse, Timestamp, CLOB and BLOB data types
  • When the caller does not have permission to view the source data, the function returns error 229

Audit Trail with Triggers​ [Generic Solution]

Pros

  • Can get historical data, also able to give exact detail about previous and new data in shadow table
  • Well controlled w.r.t selection of columns, operations [I=Insert, U=Update, D=Delete]
  • Process can be made automated due to complexity, Audit Trail Process can be govern with some routine or service.
  • Purging/truncation as well as auto clean-up can also be automated

Cons

  • Exact copy of table with some extra columns is required as Shadow Tables
  • Complex process w.r.t development, data management and purging
  • Performance hit when data becomes huge in shadow tables, Purging is required
  • Takes much time to automate the process considering all the important aspects of reliability, security and performance. Which in native solution, DB Engine provides by default.
emkays
  • 444
  • 9
  • 15
7

I use CDC in my WPF app. Works very well but I've discovered three problems:

  • You need to back-up change tables quite often (I use MERGE statement to add records to historical tables). Because record stays in change table only for about 2-3 days as I found out. Don't forget to backup cdc.lsn_time_mapping table.
  • You can't truncate tables with CDC enabled.
  • There is a problem with disabling cdc and reenabling (should be solved in new service pack as MS said). I've got this problem only once so it's not so annoying.

    http://blogs.technet.com/b/claudia_silva/archive/2010/06/04/cdc-cdc-hangs-when-i-try-to-disable-it.aspx

Anyway, CDC is very useful mechanism which helps me track all changes on database.

devarc
  • 1,157
  • 1
  • 7
  • 11
  • 2
    I am guessing the there is a job which cleans up the change tables. Is that correct? If yes, could we just "disable" that job? Also, how do you track which application user has changed data? – Ashish Gupta Apr 12 '12 at 15:08
  • 1
    I never tried to, because I have to disable cdc every day to update some data without logging, but according to this article you can disable job without any problems: http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/1a073ef0-9a49-488f-a6c5-7b4b1c71f4c0/ – devarc Apr 13 '12 at 07:11
  • 3
    I track users in my application. I'm using EF and "OnPropertyChanged" method to track changes in objects and logging user. It's not "clean" solution but I don't know if there is any better. – devarc Apr 13 '12 at 07:16
  • For the first, CDC cleans up data quite often, and the time periods you can modify, Hope this link will help you for further understanding ---- https://technet.microsoft.com/en-us/library/cc645885%28v=sql.105%29.aspx – tharo May 08 '15 at 08:36