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.

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.

SQL Audit Part 8: Data Archiving Script

The archiving of the records is a simple affair.  All that we need to make sure of is that we don't archiving something twice, or that we don't delete something we haven't archived.  For high volume tables, this can get interesting, especially if you want to remove the audit records as soon as they are written. (That is, every time that the archive script is run.)


So, to get around that we mark all of the records we want to copy first, then we copy all the marked records, then delete the audit records that are marked.  Makes it nice and neat. While it creates an extra query, it is a safegard that is worth it.  While playing with audits you can never be to careful.


So that about wraps up the project, more or less.  The last part in the series details where you can find the source code (complete) and some other odds and ends.

SQL Audit Part 7: Audit Table Maintenance Script

Now we have the audit started and working, what is needed now is to manage it.  By manage, I mean that when we change the source table structure, we don't want to have to manually alter the tables that store the audit data.

Rather than recreate slightly modified code to manage the the tables, I coded the creation script so that it will do both.  Yes, I know that this is cheating (I planned the development that way) but coding is meant to be done smartly, not just with brute force but also finesse.  So, to manage both in the one script, there are a few principals to use.

1. Check before create.

Before you do what you need to, check that it hasn't already been done.  In some cases this will mean that if it is there you do nothing, in other cases you will delete it so that you can recreate it, and in more you update instead of delete.  That is a decision that you can make based on your code, and what your wanting to achieve.

2. Understand both requirements.

Updating code and creating code can be combined, but there are some cases where this is not advisable.  You need to understand both how the creation needs to work and how the update needs to work to be able to know if the functions can be combined, and then the best way to achieve that.

3. Make your code manageable.

This is more of a generic coding principal, but it still applies.  When you combine 2 functionalities into the one piece of code, it makes it that much more complex. The best way to manage the complexity it so make sure that your code is readable, and to make sure that another can come along and understand it with without you pointing out whats what.  On this one I am continually learning, but I think I am getting better.

Then, with that in mind, I can just create a cursor to loop though the tables that have been started, and re-run the creation stored procedure over it, and were done.

SQL Audit Part 6: Creating The Trigger

So, now we have the database tables created for the audit and the archive, now we need to create the trigger to perform the audit. Now the code for this, based on what I have done to date, is what you would expect.

Set @sql = '';
Set @sql = @sql + N'IF EXISTS (SELECT name FROM sysobjects
WHERE name = ''adt_tgr_'+@auditTableName+''' AND type = ''TR'')
DROP TRIGGER adt_tgr_'+@auditTableName
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
exec sp_executesql @sql;

However there is this part that is the check for the existing trigger. We cant drop a trigger that doesn't exist, but where it does exist we want to drop it so that we recreate it. It is pretty basic, but gets the job done, and also shows the use of the sp_executesql in the source database.

With this in place, the audit is now running.  Any insert, update or delete command on the source database table will create the audit record in the audit table.  But there are still 2 things missing from the original brief, to be able to maintain the structures of the audit and archive tables at the database level, as well as the ability to archive the audit records after a set time so that we don't keep records to the database we don't need.  And these will be covered in the next 2 posts.

SQL Audit Part 5: Altering the Tables - Source Defined Fields

So, we have now got a manageable setup that will allow us to add the audit required fields to the audit and archive tables. Now we need to replicate the source table structure in those tables. The basics are the same with the audit fields, but we get the structure from the source table.

CREATE TABLE #adtSourceTable (
FieldName varchar(255),
FieldType varchar(255),
FieldSize int
);
SET @sql = N'Insert into #adtSourceTable
Select COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from '+@databaseName+'.information_schema.columns
where TABLE_NAME like '''+@TableName+'''';
EXEC sp_executesql @sql;

If you look above you will see how we get the list of fields and their definition.  We look at a table called information_schema.columns to get the information that we need.  Now this table will get more fields than we actually need, so we only get the fields that we do need, namely the name, type and size or length.

The rest of this part of the code is almost an exact mirror of the code that creates the audit fields in both of the tables.

SQL Audit Part 4: Altering the Tables - Audit Defined Fields

So, we have our tables created with an ID field, now we need to add to those tables the basic fields to store information about the audit of each record, such as action, date time, status etc.  Now, for ease of altering the set of required fields, I have placed the field names and specifications into another table.  When I need to add a new field, I can just add a new record to the table.


SET @sql = N'IF EXISTS(SELECT * FROM '+@databaseName+'.INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='''+@dbSchema+''' AND CONSTRAINT_NAME=''DF_'+@auditTableName+'_'+@FieldName+''' AND TABLE_NAME='''+@auditTableName+''')
BEGIN
alter table '+@databaseName+'.'+@dbSchema+'.'+@auditTableName+' drop constraint DF_'+@auditTableName+'_'+@FieldName+';
END';
SET @sql = 'EXEC '+cast(@databaseName as nvarchar(255))+'.sys.sp_executesql N'''+REPLACE(@sql,'''','''''')+'''';
EXEC sp_executesql @sql;


One of the issues I needed to address was adding constraints to the audit table in the source database.  To do that, I found out that the sp_executesql command actually was an object in each database, so to call the instance of the stored procedure in another database you can use <database>.sys.sp_executesql <nvarchar execute to>

Yes, I know that for some, this isn't earth shattering, but for me who was doing this to improve my SQL skills, it was a revelation.  Even helped me to re-factor my earlier code to the manner that it is now (more reliable and better performing).  I used the same thoughts on getting the table list to see if the table existed in the database.

So, the above code will remove a constraint on a table in a different database.  A very handy concept on cross database management.

Again, I will try to find somewhere to upload all of the code, but for now I am just pointing out the smart (in my opinion) aspects of each functionality.

SQL Audit Part 3: Creating the Tables

The first thing I needed to do was to determine that the source table does indeed exist.  Now, assuming that @databaseName and @tableName are inputs to the stored procedure, look at the following code:


CREATE TABLE #adtMyTableListCheck ( 
dbName varchar(255), 
dbSchema varchar(255),
tblName varchar(255), 
tblType varchar(255)
);

SET @sql = N'Insert into #adtMyTableListCheck 
Select * From '+@databaseName+'.INFORMATION_SCHEMA.TABLES 
where TABLE_NAME like '''+@tablename+'''';

EXEC sp_executesql @sql; 
SELECT TOP 1 @dbSchema = IsNull(dbSchema,''), @dbTableType = IsNull(tblType,'')  FROM #adtMyTableListCheck;
DROP TABLE #adtMyTableListCheck;


First we create a temp table to store our results into.  Then we search the database in question for the table we are after.  If an object is found, we get the type and Schema of the object (the object owner).  The object could be view or a table, so later on we check that using the table type.  But, you might ask why do we get the object owner? By using the object owner in the queries it allows the queries execution plan to be cached.  Now, for the queries that we are talking about it probably .002 of a second at most, but if you do that 100 times a second, you start to get some savings.  If you do that 1000 times a second even more, and so on.

That is about all that I need to explain from the start part the overall flow of the stored procedure, which is:

• Gather inputs, sort out table names to use etc.
• Determine existence of the source table
• Insert a record into the audited table list
• Make sure that the database tables for the audit and archive tables have the required audit fields
• Make sure that the tables have the correct fields for the source table
• Create the trigger for the audit

So, we have created the audit and archive tables, as well as checked that the table exists, now we need to create the fields required for the database audit.

SQL Audit Part 2: Overall Description of the Flow of Data

Now that we have set the ground rules for our little discussion, let me take some time to explain how I wanted the data to flow.

The image above shows you what I aim to do.  On inserting, deleting or updating data in the Source Table, I want to store that change in the Source Database (the Audit Table in the Source Database above).  Then, for "old" audit entries, I want to move them off the source database and onto an archive database (in this case called the Audit Controller Database) so that they don't take up space in the source database and could in theory be saved to disk to be kept for prosperity.  "Old" can be a variable time, in my mind calculated in days, some source tables would have a small "old" value, whilst others would have a large one, all in the same application.

All of this would be managed by triggers (to audit the data and store it in the audit table) and stored procedures to create the audit table and archive tables, to create the triggers, to maintain the triggers and tables, and to archive the audit table data to the controllers database.

SQL Audit Part 1: The Aims

It has been a long time since my last post, but I think some people will think it is worth the wait.  Over the past few months, when I have had the time, I have worked on improving my SQL skills and understanding of databases.  Now, if your like me and you hate just reading things with no hands on practice, you will understand that I gave myself a little project definition to help me in my learning's.  I decided to write a SQL audit project.

The requirements of the project were as follows:

• Managed by stored procedures;
• Database level auditing (for speed);
• Always accessible via SQL (that is, I wanted a store somewhere where I would review the audited information without having to involve a DBA);
• I wanted the ability to archive the data after a set time.

I am just finishing up some final refactoring and logging parts, but I think I have something that will achieve this.

Some disclaimers before I continue I think are in order.  I am not a SQL DBA, just a developer wanting to improve his database skills.  The code I have written has not been thoroughly tested, nor would I recommend using it for a production environment without some form of testing in your Staging or User Acceptance Testing environments for speed and to make sure it doesn't break existing systems.  I have written this in Microsoft SQL Server 2008 (Express Edition), so there may be more features in other versions (such as Standard or Enterprise) that would break the code in this project, but again I have not tested this against other editions so am unaware of any issues with them.  Whilst you are free to use this for yourself, you do so at your own risk.

Now that that is out of the way, let me detail the parts of the "series" that I am going to write:

• The Aims (This post)
• Overall Description of the Flow of Data
• Creating the Tables
• Altering the Tables - Audit Defined Fields
• Altering the Tables - Source Defined Fields
• Creating The Trigger
• Audit Table Maintenance Script
• Data Archiving Script
• Wrapping It Up

I will try to find a location to upload the various files to create and review the various parts of what I have done, just looking into which source repository I should use, Codeplex or code.google.com.

OK, now without further ado let me begin!