Avatar of dev775
dev775
 asked on

.NET Audit Log Design

I am looking for advice on a high level design approach to build an audit trail for a financial application.  Need to track every change made to every financial record.  Our development team has spent much time and effort using SQL triggers to detect record-level changes.  This turned out to be a major disaster as it caused significant performance problems.  How are audit mechanisms effectively implemented in large .NET applications?  Thanks in advance!
Programming TheoryMicrosoft SQL Server.NET Programming

Avatar of undefined
Last Comment
dev775

8/22/2022 - Mon
Vitor Montalvão

Which SQL Server version are you working with?
What you want to audit/track? i.e., do you want to record the columns changed and keep track to the previous value and the  new value or you just want to record who changed the data (no matter which) and when?
dev775

ASKER
Our software currently supports SQL 2008 or 2012.  What do we want to keep track of... a) any change to a data field; b) when a record was accessed (even if only to view); c) date/time of change; d) who made the change; & d) from what workstation the change was made.  We need to have a sequential record of changes and yes, we need a history of the actual values that were changed.  For example if a customer phone number is changed we would want to record previous value and new value.
ASKER CERTIFIED SOLUTION
Vitor Montalvão

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
melmers

Hello,

i would think when you use the Event Sourcing pattern for your application it could,
be archiveid what you want. as Reference you could read http://msdn.microsoft.com/en-us/library/dn589792.aspx or http://martinfowler.com/eaaDev/EventSourcing.html 

But it comes to the cost that your application has an higher performance request and you need
to design this explicit and can't use ORM Mapper out of the box.

When you have an application with this pattern you can audit or rollback to any state of change
if you only use this pattern for database access. If you use other strategies like ORM or direct SQL for
some task then your data can be corrupted and not rollback complete because the event store has not
record all changes.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
dev775

ASKER
Thanks for the helpful comments.  Regarding the SQL Server Audit, what is the likely cost on performance from the standpoint of the client application user?  Will it be possible to store the audit data in another SQL DB?
Walter Padrón

Auditing is a common example of a CROSS-CUTTING CONCERN. Because of the SRP principle, i suggest that you didn't implement it at the dataaccess layer/database level and instead at the aplication domain.

Using a DECORATOR pattern you can easily add such behaviour to your project
http://en.wikipedia.org/wiki/Decorator_pattern

Best regards

PD: I forget to add that this design makes your system very TESTABLE
Vitor Montalvão

In the article I sent in my previous post you can see a table comparing the performance impact with the use of trace. Auditing will always has impact no matter the solution you chose but my recommendation is to use the native SQL Server Audit since has better performance than traces and so will impact less.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

The only way to do what you want outside of 3rd party products is SQL Server Audit.

Be aware, though, that this is not an easy thing to learn and implement.  You'll need plenty of time for defining, testing and verifying the audit setup, and verifying that all accesses are being logged as expected.

Also, once Audit is in place on for an instance, it's a prerequisite for that instance: if there's an Audit error during SQL start up, the instance can't start up at all.
dev775

ASKER
Thank you for your help!  --Rob