Hi all

I've to design an audit system on a new database schemas. The database is under SQLServer 2005, and the application use .NET2.0.

So I've try to find on google out how people usually design this kind of functionnality, but I haven't found a lot of (new) ideas so I ask you here maybe you've some experience on this ?

I think I'll use a message queue so that on the other side I can put a windows service that handle the audit, to make some filter and store it somewhere (same db, or another db on another server)

Right now I've see 2 possibilities, but each have of course some pros and cons: 1) In Sql server : triggers Trigger automatically generated by a script, that send each field changes to a message queue. Pros: * all changes are loggued, even changes done directly in the database. * No need to write anything in the business layer Cons: * As the business layer use for all request a single account to log on the database, how to retrieve the user that really make the modification ?. Is it possible to put some parameters globals to a connection, in order to retrieve this parameter from the trigger ? * Cannot disable the audit (in fact, it's possible : need to remove all triggers used to audit, but it's not a really well-designed solution I think. Or if a connection-specific parameter is possible ...)

2) In the business layer Using llblgen to intercept all save done.

Pros: * All information on the context are available * Can also log actions that aren't related to the data (ie mail send, etc.) * Can easily disable the audit completely (ie for system batch processes) Cons: * Need to put more code in the data layer * make things that imho are not really the job of the business layer * Changes done directly in the database are not loggued * Changes that are done with UpdateEntityDirectly of DeleteEntityDirectly cannot be audited ? * In some case: need to fetch the data first before update (in order to have the value before modification)

I've put in bold the blocking items, as you can see there is one in each solutions So I'm here to get some help so if you have any ideas ... you're welcome

As you show in your message, this is not a simple subject.

Personally, I like trigger based audits. In addition to working with your app, they work if someone makes a db change out side of your app.

There are a few problems (challenges) with them though. Frankly, this is one of the (few) advantages to doing all data access via SP's. It is a simple thing to add code to each SP to update and audit trail table.

First, as you stated how do capture the user that made the edit? In todays disconnected n-Tier apps this is a bit more difficult than in a 2-Tier app. One of the most popular ways is to add a field to each table, EditedBy or some such that your business layer correctly populates on all insert and updates. Your trigger can use this value to populate the audit trail. How this works on deletes is not as simple. Generally you can update the record's EditedBy first then delete it.

Another method is to use the Application Name preperty in the connection string, but populate it with the users name. Your triggers can read this value and use it in the audit records. The problem here is that this contintutes a change to the connection string and you loose connection pooling.

Another method is that on each connection you create a temp table named #user and insert the users name into this table. Your triggers can use this value. Also, they can check if this table doesn't exist it can user suser_username() instead.

The second challenge here is that many of the times what is being changed in the table is a FK, but capturing this to the audit trail may not be usefull information to the user. When you display the audit data the user is going to want to know what that value represents. You could join to the table that holds the keys, but perhaps this value changed too. This leads you to joining perhaps to the audit data from that table, which isn't a simple query. (BTDT). This is why using code gen to create your audit triggers might not be optimal. I have toyed with creating meta data that describes the lookup for the FK, but never got there.

This brings you to business layer auditing. The advantage here is that you can do your audits based on business entities rather than data in a table. It can be much more valuable data to your user. In addition you know what user is making the change.

Of course, the disadvantage to business layer audit is that no audits take place if data is changed outside of the app. Then again, you could have triggers in addition to business layer code, but that might just be too much overhead.

Alot of these decisions also tie into how you store your audit data. Do you have one audit table per data table? Do you have a single audit table? Or, do you do inline audits... this would be where you never do deletes, just inserts with a few additional fields on each table to track who/when. This makes your queries a little more complex, but gives you much simpler method to do point in time reporting. Of course, you could combine some of these methods also.

Hope this helps a little, but it probably doesn't answer your question.

BOb

I would break the audit task into two different subtasks but without using triggers.

First I think SQLSerer 2005 is shipped with dedicated functions for various auditing mechanism, so you should use them, and furthermore you get access to most of the DB api, which should in last resort be the entry point for your handles, if you really want to do that manually. But maybe SQL Server Profiler is what you're looking for.

Then, that's really for data access auditing. Maybe you're more looking for an application usage audit. In that case, I would go for the business layer audit, and I would personally clone the DotNetNuke eventlog system, which is multithreaded and very flexible. You may have a look there.

Thanks BOb, no it doesn't answer, but it give me some ideas that I hadn't think before. Jessynoo thanks I'll take a look to the DotNetNuke eventlog system (never heard before!)

I know the subject isn't really simple, but as I have the chance to build the system at the beginning I try to find the best (or the less worse ) solution

Thanks