Implementing Audit Trail for your application

I recently came across Davy’s post where he talks about implementing auditing. Davy talks about 3 requirements which I guess are quite common for audit trail:

1) We have to be able to easily retrieve what user X changed in the database on day Y or during a certain time frame.
2) We need to know what record X looked like at point Y in time, and be able to show a historic list of changes for a specific record.
3) We need to configure the duration for which auditing data must be kept in the database on a per-table (or per-schema) level

In this post I would like to talk about the way we did it for one of our recent projects. We had prepared a generic sort of table which would hold the audit data. This table mainly consists of AuditID, EntityID, EntityTypeID, UserID, AuditTime, AuditLevel & AuditData. AuditData is a TEXT column (we support SQL 2000) holding XML structure, for those changes made to an entity by a given user in form of old and new values, at a level specified. This would cater to the first requirement as it’s quite easy to search for activities of a given user.

Second requirement is little tough, as with this model we don’t allow direct queries against record attributes. But this can be achieved by 2 variants. First simple solution could be you have to search by entity type & a specific entity in turn for a date range & browse through records to see changes done on them. Second option is take this AuditData out and restructure its layout with help of OLAP cubes to produce meaningful quick search.

Third requirement is very much there for us in order to control size of this massive table but we for simplicity purge entire database at a given time interval & do not make it adjustable for each entity. Though I feel providing that flexibility shouldn’t be difficult considering the design we have laid out.

A final thing I wanted to highlight is about generating the XML data itself. It’s always better to do such processing in .NET code & if you have been using DataSets you get old values extraction feature out of box. We are yet to see a significant performance degradation due to this design but as a contingency plan I was thinking of making this XML conversion & insertion into AuditDB an asynchronous activity with help of Queue like data structure.

Look forward to read your thoughts on above.

Whoever saves one life, saves the world entire

Title of this post happens to be from one of my all time favourite movies – Schindler’s List, but post is about my newly created cloud application using Azure Platform. My cloud application – EmergencyBloodBank is finally up. Here’s the direct link to application. This application was created was Cloud App Contest.

There were 2 major driving factors for this application – one to create an application covering all offerings of Azure platform that can help community members to boot start their Azure efforts and second was coming up with scenario that effectively leverages cloud services displaying no elements of over engineering (which can make solution look artificial). My initial thought was to create a shared calendar service or streaming live video but there were few existing compelling solutions already there. So, I decided to focus on healthcare segment and immediately could see the impact on blood bank field. An initial search on internet landed me here and that was it, I was all set to create a communication platform for sharing blood related requirements that leverages cloud infrastructure and services.

First step was to create a site with 99.999% uptime which is a necessary requirement when talking about blood bank sort of service. Site is built on ASP.NET 3.5 with C# as programming language, and this is where one can post their blood related requests. Next was using Live Services for authentication. How many times you have turned away from an excellent site due to its lengthy login form? I have and I never wanted the end users of this site to do that. So using Live Services was a natural fit with a large user base – apprx. 500 million users. So challenge was how we make these requests reach potential donors? Pull model is not effective in this case (imagine the load it would put on site or a web service) and .NET Service Bus provides the push model I was looking for. So I went ahead with it and created a WPF client which gets the relayed messages from Azure worker role via .NET Services Bus. Few things that I opted out of due to time constraints were providing a SMS service, porting WPF client to Windows mobile, & integration with web 2.0 applications like FaceBook, Twitter, etc. Finally task was to store the user details so they don’t have to key them everytime they visit site & also audit all blood requests they place. SQL Azure with its SaaS offering was a natural fit because the data here is nothing more than a KB per request, so one can easily forgo buying the product and use the available SaaS model (add to that almost zero percent of installation & maintenance effort). This would help maintain the site cost within 100$ per month providing a unique high available solution. Below is the architecture diagram for the application

You can access the acutal solution document here.

Let me know your thoughts if I could have done things differently. I plan to cover the entire architecture, design & development process in one of upcoming talks.

Till then, Happy Clouding .

(P.S. My Application has been selected as winner of the Azure Cloud App Contest. Thanks to everybody who voted and provided their valuable feedback).