Lets start taking at look at some of the NFRs and lets start with Auditing. In some industries, auditing is a very important activity. When we want to audit operations we are looking for a few key things -
- Who did what action and when did they do it?
- What effect did the above action have – or what changed?
In some instances, we are concerned about the various actions. We are interested in auditing who looked at some records, who printed some reports, who accessed which part of the application and so forth. This essentially, is a track of actions that a user performs. In a very simple manner, if every user click/input is treated as an action – we are essentially interested in every thing that the user does. Simon has a series of posts regarding auditing which looks at it from the perspective of an Entity-Framework implementation.
But sometimes, the user makes some changes – edits data, deletes some information, approves some items. In such cases, we are also interested to know WHAT data changed. What was the oldisalue and what is the new value that is was changed to. This can get quite messy if an action updates many tables and does a lot of calculations. If we import data and expose the same via services, it adds on to the woes of auditing!!!
ASP.NET MVC has added on a nifty feature called action filters. These are attributes that we add to a method which then calls the OnActionExecuting (before executing the specified action method) and OnActionExecuted (after the execution). With this we can write a generic audit process that specifies – User X did Action Y at time T and to audit any action, we just need to add the attribute to it. If we generate an ‘action-guid’, we can now start tracking the various actions that users across our system are performing in our application. This is nice, but is not the total solution.
While the ASP.NET MVC helps in tracking all actions that users perform (or actions that external systems perform – the Controller methods are exposed to the outside world. So any action filter on these methods will also be applicable to external services), it does not tell us what has changed.
This is where the SQL 2008 change-data-capture or CDC comes into play. The CDC can track all the old values and the new values and it does so in the background for us. In order for this to be useful, we need to somehow pass on the ‘action-guid’ so that we can related all the data changed to the operation that was performed by the user. This can now give us comprehensive reporting on who did what action and as a result of that action, what data was changed. That is a good set of information from an auditing perspective.
Note also that SQL 2008 introduces an AUDIT object which we can use to track who did what – who executed a proc, who selected data from a table or who updated/deleted records from a table. If you are using the EntityFramework, you can add-on auditing to the object context like what Simon describes. So depending on what set of technologies we are working on, we can use these features to make auditing easier and part of the underlying engine in our applications with minimal effort.