How does the QUEUE_DELAY option for Server Audits work

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

The value set for queue delay is usually a trade off between security and performance. if server fail occurs, results that are in the buffer but not yet flushed to the target are lost. If the queue delay is set to zero, synchronous writes occur as events happen.This avoids the chance of losing events on server failure but can impact server performance.