Auditing Your SQL Server - Part 1

Introduction

I constantly see postings in our forums and others regarding auditing SQL Server. I know there is lots of confusion as well as a variety of philosophies for how one goes about auditing, so I decided to drop my thoughts down and provide some guidance for others. Not that I'm correct or these are the best ways, but hey, I like to write and share my opinions.

So, what is auditing?

Well, there are two types of auditing to me. Server auditing and data auditing. I'll tackle server auditing in later articles and start my look at data auditing here. It's a complex topic and I'm expecting at least 3 articles on data auditing, but we'll see as I dive into this and get feedback from all of you out there.

To me, data auditing is the act of looking at the changes that occur within your database to your data. Someone changes a row, adds a new one, deletes one, etc. and you need to know what happened. This is typical in most systems that I've worked with in that a manager somewhere wants to know who changed a particular row. It may be rows in multiple tables, but the manager is looking at his screen and the order screen shows him something different than (what he remembers) it did yesterday. Without some type of system in place for tracking changes of data, this can be a HUGE pain in your rear when he comes traipsing down to your office.

So where do you start? Well, I'll start by examining a simple auditing system.

Simple Auditing

The easy and simple way to audit is to track the changes in a row. I know, I know, there are all kinds of holes in this approach, but bear with me a minute because I've seen this type of auditing (and implemented it) in a number of cases.

Let's setup a simple table, similar to one I built for a scheduling application.

create table ServerBackup
(  MyPK int                     -- PK for the table. The values aren't important here
   , ServerName varchar(80)     -- Name of the server
   , BackupFile varchar(200)    -- Name of the backup file
   , BackupDate datetime        -- Date of the backup file
   , BackupSize int             -- Size of the file in bytes
   , Modified datetime          -- date the record was modified
   , ModifiedBy varchar(80)     -- user who modified the record.
)

This table is part of a scheduling system and contains information that is manually entered by a person who "backs up" or archives a server resource. The application allows each person to perform the backup (a manual process) and then record the information about the file name, date, and size of the file. Some of this information is pre-filled, but the user verifies it.

The auditing in this case is handled by triggers such as the one below:

create trigger ServerBackup_tri on ServerBackup for Insert 
as
update Sb
 set Modified = getdate()
, ModifiedBy = suser_sname()
 from inserted i
inner join ServerBackup sb
on i.mypk = sb.mypk

There is a similar trigger for updates, but both of these triggers work by "stamping" the record with the last update based on the server time and the logged in user.

Pros and Cons

I'm sure lots of people are thinking this is a stupid way to audit records. And you may be right, but this method works well in our case for a few reasons. First, this is for internal auditing of a small application. Only about 6 people have access to this information and it is primarily for catching mistakes, like someone put the size at 6MB instead of 6GB. There are no external reporting requirements and the last information saved is "good enough" for this application.

This auditing also has very little impact on the server. There is nothing to install, not overhead for object creation, and it's simple and self documenting. Anyone who can look at this record can see the audit.

There are problems with this type of audit, however. Not in my case, but most times the tracking of changes for a record is required. For example, if there were multiple backups made, and the filename was changed, the old data is lost. Only the most recent data is in the row and there is no way to backtrack through the old data.

There is also the loss of auditing data itself. If Bob changes this row and puts in the right information and then Andy updates the row with incorrect information, we lose the fact that Bob entered anything since we cannot track this change.

Conclusions

I'm sure lots of you have other suggestions and I'd welcome hearing them, however keep in mind this is a very simple auditing system. It can be used in conjunction with other systems, like logging to a separate table, something I will tackle in the next article.

Steve Jones

©dkRanch.net February 2003

Return to Steve Jones Home