SQL Audit, Extended Events, or other recommendations?

Question

Hello, we have User created Tables (within SQL 2016 instance) which contain Columns such as [Created_Date], [Created_ByUser, and [Updated_Date], [Updated_ByUser] which the Developing Application team is aiming along the lines for auditing for each particular record/row.

Any thoughts on ways to do this more efficiently/easier via perhaps SQL Audit, Extended Events or other recommendations? (Also which would be least impact of negative Performance and maintenance from DBA perspective)

Thanks in advance.

Answer 1

Hi，techresearch7777777

There are several ways to track the DDL of a database, among which the main consideration is how to filter the useful records. Of course, using triggers can add filtering conditions in the SQL, but it will affect the performance of the production database.

Refer to

Extended Events：https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-of-31-event-session-ddl-events/

CREATE EVENT NOTIFICATION

CREATE DATABASE AUDIT SPECIFICATION

CDC：https://www.mssqltips.com/sqlservertip/4096/understanding-how-dml-and-ddl-changes-impact-change-data-capture-in-sql-server/

Regards

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.