For our applications we have legal requirements to keep a full audit trail, including data retrieval. Also, we need to be able to replay the trace to re-generate result-sets to be reviewed by external auditors. Our idea is to run a server-side trace and use profiler to replay, but we have a number of issues
1. Replaying per database; we need to trace activity for all databases, but may want to replay actions for 1 database only. We also noticed that creating 1 audit table containing the full trace and then creating views on top of that filtering by database ID doesn't work, because Profiler doesn't let you replay from a view, you need a physical table.
2. Replaying cross server; from an application point of view, we may access multiple databases deployed on different servers. From an auditor point of view, all application activity needs to be replayed, which means replaying accross servers. We will have two traces that somehow have to be merged and replayed correctly. Is this feasible???
3. Going back in time; auditors want to replay, starting from a certain point back in time. We then need to restore the latest backup prior to that date and start replaying the trace from there. However, Profiler does not let you start the replay from somewhere in the middle of the trace, you have to start at the beginning.
4. Filtering; how do we determine what activity needs be traced in order to be able to do a 'functional' replay, from an application point of view. We do not need any other system stuff in the trace, unless of course it affects the results. What is 'safe' to filter out?
Any comments on this?
quote:
It also depends on several factors of what you are wanting to audit. If it is basic access on tables for INSERT, UPDATE, and DELETE then a trigger is sufficient. If for SELECT then either use Profiler or setup running traces to capture it. Another method which I use is to use procedures and no direct access whatsoever. Then I can write the procedures access to the audit table (this way I can know exactly what they ran).Then it boils down to what type of audit. Is it change audit? Query audit? Access audit?
I do a little of each in various ways. What is your audit for and I can offer better suggestions.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)