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