How is Auditing Different From Change Data Capture in SQL Server?
CDC was created to help ETL scenarios by providing support for incremental data load. It uses an asynchronous capture mechanism that reads the transaction log and populates change capture tables with the row data and provides API's that provide access to that captured data in several ways. CDC is not to be used for auditing purposes.
These are some of the basic differences of CDC over SQL Server Audits..
- Audits cannot / must not have an option to altered by any mechanism.
- CDC can be purged based on the retention period.
- SQL Server Audits can also keep track of SELECT statements.
- Audits can also track Server changes (Login failures, DBCC commands execution etc) can also be tracked.