Audit 2

After some feedback and opinions from some co-workers and others in the business, I reviewed my audit codebase.  Finally I got a few hrs to finish it, and this is the result.

The Goals

First of all, let me review what I was setting out to achieve.  Looking at a previous post (Audit Again) here is a summary of what I was wanting to do:

1. Implement a process log system
2. Remove the reliance on specific databases (that is the core app could be anywhere in relation to the archive and audited tables)
3. Refactor the code to make it more readable and also break it down into more re-usable parts
4. Use Visual Studio 2010 Database Projects to implement these changes

It isn't a large list of improvements, but a list that I thought was worth while.  So, now for the report card, how did i go?

Implement a Process Log system

When looking at the log entries that were being generated I got to thinking what would happen if 2 management processes (or more for that matter) were running at the same time? for example an audit archive and a re-compile?  They should (in theory) not affect one another, so should be able to run at the same time.  But the logging would be all over the place.  There could be an intermingling of log entries from both processes that would make debugging that bit harder.

This was very simple to implement.  I just updated the Log Entry table to have a BigInt in it (called process), and then created a table called process and linked the ID to the Process field in the log entry.  After that it was a straight forward process of updating the code to cater for it.

Decouple archive from main database

This one required more thought, but in the end was simple enough.  I wanted to have the main tables for the audit in one database, and then be able to have the audited tables and their immediate audit entries in a second database and the archived data in a third.  I am not saying that this is the way that I would use this, but it means that you can have the audit management tables and stored procedures in either database.  That way the archive database can just have the archive, and the application is maintained in the main database, or the audit tables can be in the main database, and the audit management and archive tables and code can be in another database.

To do this, I just created an entry in the configuration table to identify the location of the audit archive tables, then refactored the code to use that location instead of the database that it management code and tables are in.  And that was it.

Code Refactor

With a Visual Studio 2010 Database Project I was able to do this very easily.  It treats the database project just like any other, which means that when you change the name of a table or stored procedure, it will change all of the references to that database object within the project.  That said, I had other issues with Visual Studio that I will touch on below.

Visual Studio 2010 Database Project

And here we come to the part where I didn’t achieve what I intended to.  I had hoped that I would be able to use the Visual Studio Database Project to manage the code base.  This would have been better the long by increasing maintainability and improved deployment.  I wont rehash my previous blog (here) but it was great for maintaining the code, but it (in my mind at least) failed at deployment.  As a result I didn't use it for anything else other than changing the names of the tables and stored procedures.

Audit 2

So, that was how I went about what I aimed to do.  If you want the source code, please click here.  It is provided as is, and you use it at your own risk.  If you have any questions, please leave a comment.