Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Auditing Your SQL Server - Part 2

By Steve Jones, (first published: 2003/03/17)

Auditing Your SQL Server - Part 2 - Using a Mirror

Introduction

Auditing your SQL Server can be a tough task. The amount of activity and variety of events make this a challenging operation. This article continues my series on auditing by looking at a more advanced method of auditing changes to a table. Part 1 of this series examined simple auditing using in row fields for tracking the last time and user who made a change.

More Advanced Auditing

In the last article, we looked at simple auditing using additional fields in a row. This article takes this a step further by using a mirror table to track the changes to all fields. While the amount of detail captured in this level of auditing is much greater, you pay the price with additional overhead on the server during insert/update/delete operations as well as additional space needs for storing the audit data.

What is a mirror table? The easiest way to explain this is with an example. Let's start with the Northwind database and assume that we want to track the changes to the Orders table. I'll start with a clean Northwind database, so you can reset yours to follow along. The instnwnd.sql script in your /MSSQL/Install folder will clean your version. This table is a fairly standard orders table that looks like many I have seen. Let's create a mirror table:



I cheated by using the Object Browser in Query Analyzer to script this table out quickly. Each field in this table corresponds to the matching field in the original table. Now let's add a few fields. Specifically, let's add these rows to the end of the script:

	, modifiedby varchar(40)
	, modified datetime
	, action char(1)
These are similar fields to those used in part 1 to "mark" the time and user who made a change. These are an important part of performing an audit, allowing us to quickly and easily determine the order in which changes occur. this table is designed to "mirror" the Orders table and record all of the information that is in that table along with dates and times of change.

Auditing to a Mirror Table

The next step is to implement the auditing of actions on the Orders table. To do this, we'll again use a trigger that records the audit information for us. Specifically, we'll use 3 triggers, one for inserts, one for updates, one for deletes. For the sake of brevity, I'm only showing the insert trigger in the article. The update and delete triggers are available in the code download for this article.

The insert trigger, which I'll call Orders_tri, according to my naming convention, will duplicate the information inserted into Orders into Orders_audit. The code looks like:

create trigger Orders_tri on orders for insert
as
insert orders_audit
 select 
	*
	, suser_sname()
	, getdate()
	, 'I'
 from inserted i
 return

A pretty basic trigger, and one which will take all the rows inserted into this table and add them to the Orders_Audit table along with the login user name, the current server date and time, and mark these rows as "I" for inserts. The delete trigger functions in the same manner by inserting all the rows from "deleted" into the Orders_Audit table using a "D" to mark them. The update trigger is just like this trigger, only inserting the new data, but using a "U" to mark the update.

In practice, this looks like the following. Let's assume we've just implemented auditing (which you have if you've run the code download) and Orders_Audit is empty. We now add a new order:

	insert Orders
	 select 1,2,3,4,5

If we now check the Order_Audit table, we will see that there is a new row that exactly matches the new row in Orders with a few additional fields:

	select * from Orders where orderid = xxx
	select * from Orders_Audit

-------------------
OrderID    CustomerID ......                   

OrderID    CustomerID ......    ModifiedBy    Modified   Action


Suppose I now wish to modify the order because the quantity was incorrect. I issue the following:

	update orders
	  set quantity = 11
	  where orderid = 

A further check of Order_Audit shows that there are now two rows. One is the original insert and the other is the updated row.
	select * from Orders_Audit

-------------------
OrderID    CustomerID ......    ModifiedBy    Modified   Action


The results are abbreviated, but here we see the two rows. As a final test, let's remove the order with a "delete orders where orderid = xx". After issuing this statement, we will see an Order_Audit table that looks like:
	select * from Orders where orderid = xxx
	select * from Orders_Audit

-------------------
OrderID    CustomerID ......                   
---------  ----------
0 rows affected

OrderID    CustomerID ......    ModifiedBy    Modified   Action


I reformatted the results, but basically the order is removed from the system. Without some type of auditing, we would have no idea that there even was an order like this ever entered. However by tracing through the audit table, we can find out the history of this row.

Pros and Cons

So is this a good idea? the end all for your auditing needs? Probably not. This type of auditing imposes overhead, essentially an extra write for each updated row along with index overhead on the indexes (you do want indexes on the audit table). Essentially 2x the overhead for each transaction that modifies data.

It also increases the amount of storage that is required for each table. Sometimes exponentially as there will be a copy of each row for every modification. If your application were to issue:

update orders set quantity = 6 where orderid = 12
update orders set price = 12.5 where orderid = 12

then you will have two copies of this row, one for each update. An inefficiently written application (ever run into one of these?) can quickly overwhelm the auditing capabilities of the server.

On the positive side, this type of auditing can allow you to trace exactly what the history of each row may be. I have implemented this type of auditing in financial applications (for certain tables) where the audit trail must be extremely precise. and detailed. The tradeoffs are worth it because the liability can be very high.

Conclusions

A step above Part 1 and auditing just the date and time of a change. However this type of auditing forces you to pay a price in disk space and overhead. In Part 3, I'll examine a few ways to manage this audit data and keep control of your server.

As always, I welcome feedback on this article, including your suggestions, ideas, and criticisms.

Steve Jones
©dkRanch.net March 2003

Return to Steve Jones Home

Total article views: 22006 | Views in the last 30 days: 9
 
Related Articles
FORUM

Auditing Insert Update and Delete Operations

Audiing Insert Update and Delete Operations

FORUM

Update Audit

an audit on update columns

FORUM

SSIS Export to Flat File with a blank line b/w each Order

Anyone used MYOB for Orders/OrderDetails records? Need a line break between each OrderID?

FORUM

Audit script

Creating an update record audit script

FORUM

How do I do an update/insert to certain fields in a certain order?

Am trying to update/insert certain fields in a certain order

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones