Compartir a través de

SQL Audit Buffering and Error Handling

  • Artículo

I've had several questions about how exactly the buffering and error handling works in SQL Audit and thought it would help to give some more detail.

For starters, let's break down the event firing workflow into the following stages:

1. Permission Check/Audit Check
2. Filling out the event
3. Distribute event to Audit Extended Event sessions
4. Audit Extended Event sessions record event
    a. Creating handle to file/windows log session.

Steps 1 - 3 always happen synchronously in the user thread and any error will cause the transaction to abort. Such an error will not cause the server to shutdown because the transaction is aborted. The goal of the shutdown on error setting is to prevent operations that manipulate data without being audited, so continuing when the transaction is aborted is not in conflict with this setting.

Whether step 4 is synchronous or not depends on the sessions QUEUE_DELAY setting (0 means synchronous, 1000 or above means it will happen asynchronously). The default is 1000 and is asynchronous, meaning as soon as the event is in all the appropriate event sessions buffers in step 3, the user thread returns to processing.  The Audit Extended Event sessions wait until their buffers are full or QUEUE_DELAY has been exceeded and then write the event to their target. If one of the audits is synchronous (QUEUE_DELAY=0), the user thread blocks until that audit session has written the event - the event is written immediately.

If an error happens here then the sessions ON_ERROR setting takes affect and may shutdown the server. If it is set to "CONTINUE" the event is lost but as long as the failure didn't happen in substep 4a, later events will continue to try to write. If as part of writing the event a new file is needed, such as when MAX_SIZE limit is hit, and creating that new file fails, then further attempts will not be made. The session will be taken offline awaiting corrective actions by an administrator.

Comments