Advice on Change Tracking in Sql Server 2008

Question

My client is looking for a way to do a full audit trails (full view of historical data on all tables) on the application.

Other than using the old fashioned way of having table copies or storing field name, field value, modified by, modified on etc, I was looking at using Sql Server 2008 change tracking.
Found a How-To article on msdn on the same

Has anyone used (or done a POC) Sql Server 2008 Change Tracking feature and found it to be worth it. Also if possible please specify what you wanted out of it and what you found/concluded. Any tips on the same are welcome.

[Edit]
Its been a week still no answer!

Answer 1

SQL Change Tracking cannot be used for audit purposes. (well maybe it can, but not meant for it).

The SQL DBA's here where I work, tell me the transaction log IS the audit trail already.

(Sorry I have no idea how to make that useful though)

Answer 2

I've used CDC in a data-warehouse set up before. It's very good and saves you having to write triggers for all your tables, but I would list 2 areas of caution:

1. By default, there is a SQL agent job that prunes the tables every X days (can't remember the default), so you need to either override this, or ship the data elsewhere.

2. Scripts that drop and recreate tables (which MSSQL Management studio sometimes does) can result in your CDC tables being dropped and the data lost, so take care.