In order
to track changes on the table, we need to enable the Change Data Capture feature
on the database. We can enable the Change Data Capture feature using the
following Transact SQL command, as shown below. [Refer Fig 1.2]

Until
this point, the only schema that exists on the database is dbo. Once we enable Change
Data Capture, a new schema with a bunch of objects will be created.

USE [MyDataBase]
GO
EXEC sys.sp_cdc_enable_db_change_data_capture
GO

Fig 1.2

The
following CDC tables are created under the CDC schema, as shown below. [Refer
Fig 1.3]

In order
to track changes on the table, we need to enable the Change Data Capture feature
on the table as well. Lets enable the Change Data Capture feature using the
following Transact SQL command, as shown below. [Fig 1.5]

By
reading the result, we can easily understand that SQL Server Agent is a must to
do the capture and cleanup. We can see these jobs are actually created as SQL
Server Scheduled jobs. [Refer Fig 1.6, 1.7]

Fig 1.6

Fig 1.7

Now lets
execute the following Transact SQL to see if any data has been inserted on any
of the CDC tables.

You can see
that the CDC schema stores metadata information about which tables and columns
are being tracked by Change Data Capture. It also stores information about what
Index the table that has been tracked has.

Step 4

We can check
to see if Change Data Capture is enabled on a database by using the following
transact SQL statement.

Conclusion

This article
illustrated how to enable the new SQL Server Feature Change Data Capture on a
database. In addition, it illustrated how to enable Change Data Capture on a
table and how to keep track of Data Definition Language changes on a table. It
also explained the CDC schema and changes happening in the objects of the CDC
schema.