Geek Answers Handbook

Change Tracking Recommendations on Sql Server 2008

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

Besides using the old-fashioned way to have copies of the table or storing the field name, field value, changed, changed to, etc., I considered using Sql Server 2008 change tracking.
Found How-To article on msdn on the same

Has anyone used (or performed a POC) Sql Server 2008 Change Tracking and found it to be worth it. Also, if possible, indicate what you wanted and what you found / concluded. Any advice on this is welcome.

[Change]
It was a week for which there was no answer!

+7
sql sql-server-2008 audit change-tracking audit-trail
source share
2 answers

I used CDC on a previously created data warehouse. This is very good and allows you to write triggers for all of your tables, but I would list 2 areas of caution:

  • By default, there is an SQL agent job that processes tables every X days (I don’t remember the default value), so you need to either override this or send data elsewhere.

  • Scenarios that delete and recreate tables (sometimes this is done by the MSSQL management studio) can cause your CDC tables to be deleted and data lost, so be careful.

+3
source share

SQL change tracking cannot be used for audit purposes. (well, maybe he can, but not intended for this).

SQL DBA here, where I work, tell me that the transaction log is already an audit log.

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

+1
source share

More articles:


All Articles