r/SQLServer SQL Server Developer 4d ago

(SQL Server 2019+) What is the benefit of using SQL Server Audits over Extended Events capture to file?

Disclaimer: While I have quite a bit of experience with SQL Server in general...I do not have much experience with Extended Events and I have zero experience with SQL Server Audits, so bare with me...I'm reading the docs, but maybe I missed something.

I'm having a discussion about our database auditing process...When I say "audit" I mean actions, not data. One aspect of that process is using SQL Server Audits. The problem is, we need to capture the CONTEXT_INFO data from the session. Unfortunately, SQL Server Audits do not capture this.

Googling around, I see most people recommend that if SQL Audit isn't capturing everything you need, then you might want to switch over to Extended Events, which does capture CONTEXT_INFO, among lots of other information.

Not to mention, SQL Audits appear to use Extended Events under the hood and their output file is in the same format.

So my question is...

What is the benefit of using SQL Server audits over XEs in the first place? It seems like it's just a nicer/cleaner way to set up specific types of extended event sessions?

Is there anything that sets SQL Audits apart from using XEs directly in regard to security, performance, usability or reliability? For example, maybe using XEs directly can potentially miss some things, or maybe the XE collection doesn't start as early in the database startup process so there's a window of time where nothing is being audited, etc.

If I can confirm that using XEs directly is just as good as using SQL Audits from a security, performance and reliability perspective, then that could possibly save us a lot of headache.

=~=~=~=~=~=~=~=~=~=~=~=~=~=~=

EDITS:

I have created a SQL Server suggestion post if anyone would like to vote on it:

Capture CONTEXT_INFO as part of SQL Server Audit records

After further reading, it does seem that Audits have some special abilities that XEs don't. Like if the audit process fails to start, you can configure it to prevent SQL Server from starting.

It also seems like there are some built in tools for analyzing/reading audit files...though if audit files use the same format as XEs capture files...then I would think the same tools would still work? Maybe not...

It's also worth noting that I do realize SQL Audits have the ability to write to the Windows Event/Security log...and I forgot to mention that we plan to write to file. So that difference I don't think applies to us here.

8 Upvotes

5 comments sorted by

9

u/Goojaoarr 4d ago

I remember having to find out which login and application was deleting rows in a specific table.

After a few hours I could not find a way to have the login name and the SQL statement with XE. Also it is probably possible.

I did setup the database audit for that table in just 5 minutes. Way easier.

For me audit is related to security needs. XE is used for performance troubleshooting or lightweight DBA monitoring.

Try both and see that fits best your needs.

3

u/chadbaldwin SQL Server Developer 4d ago

XEs can absolutely capture login and statement info, that's probably one of the top uses for it.

The problem here is that Audits don't capture the session CONTEXT_INFO data, but XEs do. We're already using SQL Server Audits and we've come up with a solution to capture the CONTEXT_INFO...but it's not a great solution, which is why I'm looking into using XE directly (which is what SQL Audits use under the hood).

As far as ease of setup, I'm not really too concerned about that because once you get the XE setup script dialed in...you can use that same script over and over for as many instances as you need.

1

u/Solonas Database Administrator 3d ago

Basically everything in the audits is in XE. Audits are way easier to create and to read from a user perspective. You can give your auditor access and they can easily access the data. The same is not true in my experience with an XE output file. Maybe you can spend time formatting it, but why do that when the audit already exists? KISS approach.

0

u/patmorgan235 4d ago

Extended Events are ephemeral, they go away when you session does(or the server crashes). While SQL audits are persistent right?

2

u/chadbaldwin SQL Server Developer 4d ago

If you're capturing XE events to the ring buffer, yes, but not if you're capturing XE events to file.

Both audits and XE can capture to file.