October 17, 2011 at 3:01 am
Hi,
We are currently using a trigger based solution to track both DDL and DML changes. However we are working in a VLDB environment and I think we could improve performance by using the new auditing features in SQL Server 2008 as we would not be duplicating effort on the source disk by writing both a record to the affecting table and also the audit table because we would take the data directly from the log which is on a different physical disk. Also it would mean that we could hold audit data on a separate server that we would have better control over permissions using windows secuirity.
However, we currently track the hostname, userid, and application name within the triggers for both data level changes and schema ones. I am not seeing an obvious way of tracking this at the data level within the data auditing functionality. We have tried parsing out the data from the security log and joining that to the data held in the audit table with mixed success rates.
I'm sure somebody must have come across this before?
January 18, 2012 at 11:49 am
I've looked into a number of auditing solutions (DDL, not DML) and they all seem to have their drawbacks. For SQL Server Audit, the lack of the client-side information you're looking for was a big one. You'll see the same problem with Event Notifications. The best part about these solutions (IMO) is that they're asynchronous, but that also means they can't capture this info.
Since you've already developed a trigger-based solution, have you looked into incorporating that with Service Broker to log the audit records to a remote database? That would let you capture the client data you need, move the additional I/O off of your source system, and you could limit access to that audit database as needed.
Colleen
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply