SQL Server 2008 change data capture vs triggers in audit trail
sql, sql-server-2008, triggers
Solution
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.
Problem
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