Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working through how SQL Server Audits work and would like some clarity on how the QUEUE_DELAY options works. BOL defines the options as:

Determines the time, in milliseconds, that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The minimum settable query delay value is 1000 (1 second), which is the default. The maximum is 2,147,483,647 (2,147,483.647 seconds or 24 days, 20 hours, 31 minutes, 23.647 seconds). Specifying an invalid number will raise the error MSG_INVALID_QUEUE_DELAY.

Where are these actions queued tempdb, a system table, etc? Can I query that location to see how many items are in the queue? If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

share|improve this question

3 Answers 3

up vote 5 down vote accepted

SQL Server Audit leverages the new Extended Events architecture introduced in 2008.

If you look at the following DMVs, you can see the related structures appear when the audit is started.

select * from sys.dm_server_audit_status
select * from sys.dm_xe_session_events
select * from sys.dm_xe_session_targets

Note that audits have their own set of special targets, which cannot be used directly through Extended Events. If you start generating some audit events, you'll see the events show up in the actual target, but unlike a ring buffer target, they don't appear in the target_data column in the DMV for security reasons.

Having said that, it's a bit unclear (and apparently undocumented) how the Audit session targets work internally. Based on the fact that Audit is built on Extended Events, and given the behaviour options for when auditing fails, I assume the Audit targets are simply modified ring buffer targets that don't expose the event data back to SQL Server. While this explanation makes the most sense to me, I'm honestly not 100% sure. Hopefully someone with more internals knowlege can comment.

Can I query that location to see how many items are in the queue?

Not as far as I can tell; again, likely for security reasons.

If the ON_FAILURE option is set to shut down the server, will these items be logged to the audit once the server is restarted and the location can be accessed again?

There are many scenarios wherein the event could not be recorded. If my assumption about hitting a memory buffer is correct, the failed event(s) will be dropped on the floor as a shutdown will release all SQL Server memory. Anything that didn't make it to disk will be lost.

share|improve this answer

SQL Server will queue up the writes from the captured data and only write it to the logs when the amount of time specified has passed.

share|improve this answer
    
I understand that part. I have updated the question to better reflect what I am trying to understand. Thank you for helping get a better handle on this. –  jgardner04 Aug 15 '12 at 13:35

I'm interested in what your situation is. We write software, LOGbinder SQL, that translates SQL audit logs and sends them to a log management/SIEM systems. We normally recommend using a file share on another server where our software runs to reduce impact to the SQL server to the bare minimum. It would be good to test what happens if that server is unavailable for a time. This speaks to using a local folder on the SQL server. We don't recommend using the Windows event log for performance reasons.

share|improve this answer
    
I am simply trying to get a better picture of what is happening under the covers with SQL Server auditing. I don't have a specific situation that I am working through. –  jgardner04 Aug 19 '12 at 18:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.