SQL Server DML auditing - Triggers vs SQL Server Audit Feature (vs temporal tables vs change data capture)

Question

I am looking to implement an auditing process on an instance I have on SQL Server 2017. However, I am not finding a complete conclusion on which auditing feature would be best to use for a DB with a lot of transactions. I only need to audit the DML statements and not the values (e.g. I would need to know about an UPDATE statement and who performed it and on which table but I do not need to know what the value of the column was before the update).

From my research I have excluded traces and extended events due to their performance impact.

I have also excluded change tracking since it cannot be used with partial containment and I have some DBs which are set to partial containment.

I am currently mostly between using SQL Audit feature (i.e. using server and database audit specifications) and triggers. I do understand that both the above methods will cause some overhead and that the performance impact depends on how much activity I am auditing, but given the same activity being audited (e.g. selects, inserts and updates on 10 tables), which one will cause the least performance impact between these two? I am planning to use an audit file (locally or on a separate fileshare) in case I use the SQL Audit feature (and not in the windows log file).

From my research I have also concluded that triggers have to be maintained with every schema change as well, so if SQL Audit feature does not cause a massive difference in performance (compared with triggers) I will go with it.

Also I am not sure whether change data capture or temporal tables will be a better solution compared to SQL Audit feature/triggers.

Any experience with these auditing methods and their performance impact would be greatly appreciated as information about this is highly limited.

Answer 1

CDC and temporal doesn't say who did the modification.

Audit has lower overhead than triggers. Audit uses extended events under the covers.

Audit might not give you which rows where read or modified (when parameterized sql is submitted), but that wasn't a prerequisite. I. e, I'd look at Server Audit first.