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.

The First One

So, I have re-named the data structures.  I used the database project template in Visual Studio 2010, and to be honest I wasn’t impressed. As a database management project tool, I found it almost impossible to commit the updated changes back to the database.

But first, let me look at what I did and what I liked and what I didn’t like at each step.  My first step was to use a clean install of the project and get that imported into the database project.  That worked pretty well.  It created one file for each database object (stored procedure, table etc.) which I thought was great.  It created a good structure which was pretty easy to understand (for a non DBA like me).

To me, this structure is very similar to the structure that you see in the MS SQL Studio, which is a good thing in my mind.

Then I did one of the items on my version 2 list for the project, to rename the database tables.  The refactoring was very much in line with the way code gets refactored in VS2010.  It was quick and easy, and every reference was renamed.  Needless to say I did like that.

Now to the parts I didn't like.  I wanted to set one of the tables to save the data within as it was part of the project, not the dynamic data of the system usage. I could not work out how to do this.  I would have thought that a database project would have been able to allocate some default data to the tables, if not mark some tables as having all their data from the project loaded into the implementation of the project.

This leads to my next problem.  I got the source data structure from a database.  I made a relatively simple if somewhat extensive change.  I then wanted to update the database with the changes and it would not update.  Kept on returning a user error or something.  I am going to be honest here.  This to me is the core of a database project.  Writing scripts is one thing.  The ability to use a GUI to manage the code is awesome.  The UX for updating the data structures is not.

As a result, at this time I am not going to use the database project for my changes.  At least until I am able to resolve the 2 issues that I have encountered so far.  So, at this stage I have renamed the database tables, and am about to tackle another of the items on the list.

Audit Again

So, I am reviewing the audit code that I have written.  Following the review of some posts by a friend, I am going to write the next version of the audit functionality that I finished a while back (a whole 2-4 weeks). So, using that as a basis, and ensuring that I maintain the aims set out in the previous version (for a refresher, go here).

Now, the aims for the next version are as follows:

• Process logging, not just a generic log table, but one that is based on each process so that the logging system can differentiate between 2 processes running at the same time.  Also increased logging for better debugging of issues.
• Allowing for the core parts of the audit to be in the line of business database, but still archiving to a separate database.  The audit should be configurable so that it can either run out of the archive database or the line of business database, or both (where the business has 2 audited databases there should only be one archive database).
• Rename the data objects so that they make logical sense.
• Further refractor the code so that there are as many re-usable parts as possible.
• Use the 2010 Visual Studio 2008 Database solution template to write the code in.

That is all I can think of as improvements for now.  If I think of anything else that I would like to add to the next version, I will let you know.