Log inserted/updated/deleted rows in all tables for a given database in SQL Server 2008

Question

Whats the best way to track/Log inserted/updated/deleted rows in all tables for a given database in SQL Server 2008?

Or is there a better "Audit" feature in SQL Server 2008?

Answer 1

Short answer is that there is no one single solution fits all. It depends on the system but and requirements but here are couple different approaches.

DML Triggers

Relatively easy to implement, because you have to write one that works well for one table and then apply it to other tables.

Downside is that it can get messy when you have a lot of tables and even more triggers. Managing 600 triggers for 200 tables (insert, update and delete trigger per table) is not an easy task. Also, it might cause a performance impact.

Creating audit triggers in SQL Server
Log changes to database table with trigger

Change Data Capture

Very easy to implement, natively supported but only in enterprise edition which can cost a lot of $ ;). Another disadvantage is that CDC is still not as evolved as it should be. For example, if you change your schema, history data is lost.

Transaction log analysis

Biggest advantage of this is that all you need to do is to put the database in full recovery mode and all info will be stored in transaction log However, if you want to do this correctly you’ll need a third party log reader because this is not natively supported.

Read the log file (*.LDF) in SQL Server 2008
SQL Server Transaction Log Explorer/Analyzer

If you want to implement this I’d recommend you try out some of the third party tools that exist out there. I worked with couple tools from ApexSQL but there are also good tools from Idera and Netwrix

ApexSQL Log – auditing by reading transaction log

ApexSQL Comply – uses traces in the background and then parses those traces and stores results in central database.

Disclaimer: I’m not affiliated with any of the companies mentioned above.