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!

Stored Proc vs Straight SQL

A quick one today.  Last night I was playing with some SQL I have been looking at, trying to finish it off so that I could wrap it into a stored proc for release.  It was taking about 15-25 seconds to complete, which given what I was doing was to be expected.  But after I put it into a stored proc that time dropped to 4-7 seconds.  Lesson of the day: when writing complex SQL, wrap it in a stored proc.