1

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.

knappster
  • 401
  • 9
  • 23
  • did you consider using the SQL server profiler to record all operations on these tables? – Siraf Apr 15 '13 at 07:51
  • 3
    Please note: the SQL server profiler is meant to analyze problems and not to be used as a audit tool. Despite the fact it puts a significant load on the server it will be hard to actually use the results as a reliable base for any audits – Sascha Apr 15 '13 at 07:56
  • Storing it in a single audit table means either a) Storing all audit information as e.g. XML, which may then limit your ability to search the audit, or b) Lots of practically anonymous columns e.g. `IntColumn1`, `IntColumn2`, etc, into which the data is inserted, but for which you now need to know the mapping from each table to these columns, or c) A super-wide table containing all possible columns from all audited tables. Personally, none of these appeal that much. – Damien_The_Unbeliever Apr 15 '13 at 08:14

3 Answers3

2

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

Sascha
  • 10,231
  • 4
  • 41
  • 65
1

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.

Zoran Causev
  • 360
  • 1
  • 6
0

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

Laurent S.
  • 6,816
  • 2
  • 28
  • 40