Menu
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!
990
To see schema changes in the server right click connection element in the object explorer and choose Reports > Standard Reports > Schema Changes History from context menu.
SQL Server provides two features that track changes to data in a database: change data capture and change tracking. These features enable applications to determine the DML changes (insert, update, and delete operations) that were made to user tables in a database.
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:
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.
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.
85
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)
25
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
