Change Data Capture for auditing SQL Server | Ahmad Yaseen | Sqlshack.com

 

Change Data Capture for auditing SQL Server

March 18, 2019 by Ahmad Yaseen

This article on Change Data Capture will provide an overview, notes on installation, architecture, enabling and disabling, auditing DML statements and limitations

Before reading this article, I recommend that you to go through the previous articles of this series (see the TOC at the bottom), to understand the concept of the SQL Server Audit, the reasons behind auditing the SQL Server instances and databases, and the different methods that can be used to audit the SQL Server databases. In this particular article, we will discuss how to perform SQL Server database tables audit using Change Data Capture.

Overview

Change Data Capture, also known as CDC, introduced the first time in SQL Server 2008 version, as a helpful feature to track and capture the changes that are performed on the SQL Server database tables, with no additional programming efforts. Before SQL Server 2016, Change Data Capture could be enabled on a SQL Server database only under the SQL Server Enterprise edition, which is not required starting from SQL Server 2016.

Change Data Capture tracks the INSERT, UPDATE and DELETE operations on the database table, and records detailed information about these changes in a mirrored table, with the same columns structure of the source tables, and additional columns to record the description of these changes. SQL Server writes one record for each INSERT statement showing the inserted values, on record for each DELETE statement showing the deleted data and two records for each UPDATE statement, the first one showing the data before the change and the second one showing the data after performing the change.