October 9, 2008 at 11:50 pm
Just to add my 2 cents. I also have been down this path using loops and what not to audit data into a generic set of tables. However, with the advent of the Service Broker, I now do all auditing asynchronously and I can handle any data type thrown at the database (including ntext, text, image, etc).
The first thing to do is setup the Audit database. This database will have one Procedure which will "receive" the incoming audit "messages" (i.e. incoming data) and will insert the incoming Xml into the generic auditing table(s).
The 2nd thing I did was I came up with a generic Trigger which could be scripted onto every table I wanted to audit. The trigger would selected all the data being updated / deleted (I didn't worry about inserts since having a CreatedDate column on all the rows would already give me that audit) and use the FOR XML clause to stuff the updated rows into Xml. In SQL 2005 the FOR XML clause has been greatly enhanced which reduced the amount of logic needed to generate a generic xml structure. Needless to say, inserting massing amounts of data into Xml is much faster than loops.
Once I had the Xml (2 xml variables, one for the "inserted" and optionally one for the "deleted" virtual tables), I passed the data off to a generic procedure which simply took the data and passed it off through the Service Broker messages I had setup. Since the SB is async, this did not slow down the OLTP data that needed to be inserted / deleted.
Please note that a SB implementation can be setup where a database can communicate with itself asynchronously instead of sending data off to another database or server instance altogether. I just choose to have a separate database so all of our application databases could be audited in a generic way without changing anything or complicated setup within each DB.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply