Solved

.NET Audit Log Design

Posted on 2014-11-27
9
225 Views
Last Modified: 2014-12-14
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!
0
Comment
Question by:dev775
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40468545
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?
0
 

Author Comment

by:dev775
ID: 40468559
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.
0
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40468571
Ok, then I will suggest you to get into SQL Server Audit starting by reading this good article in MSDN.
You can create an audit and set it to write in a log file and what you want to audit may be performed by this command example:
CREATE DATABASE AUDIT SPECIFICATION PCI_Txn_Database_Spec
FOR SERVER AUDIT PCI_Audit
  ADD (DATABASE_OBJECT_PERMISSION_CHANGE_GROUP),
  ADD (SELECT ON dbo.Customer BY dbo),
  ADD (INSERT ON dbo.Customer BY dbo),
  ADD (UPDATE ON dbo.Customer BY dbo),
  ADD (DELETE ON dbo.Customer BY dbo),
  ADD (EXECUTE ON dbo.usp_SubmitPO BY public)

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 11

Expert Comment

by:melmers
ID: 40468581
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.
0
 

Author Comment

by:dev775
ID: 40469318
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?
0
 
LVL 10

Expert Comment

by:Walter Padrón
ID: 40469648
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
0
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 40470138
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40470647
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.
0
 

Author Closing Comment

by:dev775
ID: 40499291
Thank you for your help!  --Rob
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question