quote:
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
These are my thoughts on it, which I have not tested since I don't have all the details nor doI have this type of setup. Others may want to throw in here, please feel free.
quote:
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.
Create a table with the exact same struture as the current and copy the items over to be used in Profiler. If should be okay with this unless there is an item noting step items.
quote:
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???
This I don't think can be done since Profiler is run only against one server at a time. However if your app is connecting to the other servers via a linked server then all activity can be recorded at a single server source and should be able to be played back from the single server.
quote:
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.
Same thing as answer 1.
quote:
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?
You only need to capture the events from the Appliction itself since triggers and constriants will react the same if not changed. Depending on hos you setup you applications connection to SQL I try to use the AppName parameter in ADO connections to make them more visible. But all you need are those event from the Applciation itself. Let profiler decide how to handle them. For consistency and data integrity you should not filter out any items from the replay. One change can potentially alter the entire outcome.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)