CODEWITHSUNDEEP
c#sql-serverentity-frameworkauditing
knappster
knappster

Reputation: 401

How to audit all changes in a .net application with SQL Server

I have a .net application that uses EF along with mainly SQL Server 2008 but a couple of customers use 2005. In the database there are a few hundred tables. I need to audit a selection of these tables to record all data inserts/updates/deletes. I was thinking of having copies of these tables to record all the changes along with the datetime and username, then have a trigger on the actual table to insert the data changes to the relevant audit table. Is this the best way of going about this? Is a trigger the best way or should I use the EF instead? I was thinking of having just the one audit table but I reckon it could get out of hand very quickly.

Upvotes: 1

Views: 3236

Answers (3)

Zoran Causev
Zoran Causev

Reputation: 360

Also check this: http://doddleaudit.codeplex.com/

We're using it to audit db changes in our product. You can also modify it easily to suit your needs.

Upvotes: 1

Sascha
Sascha

Reputation: 10347

Auditing a SQL Server is not an easy task, especially when you need to support older versions of SQL Server.

  • Track changes with your DbContext. This will add overhead to your application and (as you already noted) will become a heck of a nightmare easily (key-value doesn't scale, structure changes for history tables need to be adjusted (how do you reflect a dropped column for example)

  • CDC - if your SQL server supports CDC this is a nice option and put a lower impact on your application

  • Third party options like Audit SQL Server You would need to investigate if they do what you need

Edit: CDC is not available in SQL 2005

Upvotes: 2

Laurent S.
Laurent S.

Reputation: 6947

Starting from SQL Server 2008, you got some built-in functionalities to do, I think, what you want.

Upvotes: 0

Related Questions