January 13, 2016 at 12:00 pm
On my current project we have the requirement that every query that gets executed against the database by a user must be logged, as far as who executed it and what the query was comprised of.
Initially, my approach was to use SQL Server Audit. This took care of queries executed directly against the database, however, it left a huge gap when it came to our application, which is where the bulk of the applicable queries originate. Our app uses EntityFramework, which calls sp_executesql, parameterizing just about every piece of the query that is not a constant.
The problem we found with this is that while SQL Server Audit captures the call to sp_executesql, it does not capture the actual parameters that were passed to it, making this completely useless to us.
We changed course and started using Server Side Tracing. Unfortunately, this now meant we had a ton of information that was getting dumped to a trace file that we just didn't care about. An SSIS package was created to ETL the trace files, filtering out the data we don't need. This is now starting to get extremely unwieldy.
Does anyone have a decent alternative that would work for us? Is SSA in later versions of SQL Server more robust, or at least more capable of capturing the actual values of the parameters passed during a stored proc's execution?
Thanks in advance. Let me know if you have any questions.
January 13, 2016 at 4:12 pm
You're stuck.
Trace is probably the best answer, at least in 2008. If you moved to 2012 or greater I'd also suggest looking at moving to Extended Events. It doesn't make the data you're managing less, it'll be the same if not more. However, it puts far less load on the server than trace does. Now, you can move to this in 2008, but there are limits around it that you might hit with what you're monitoring. I'd probably stick with trace, as painful as it is to deal with all that data.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply