Enable CDC in Microsoft SQL Server

Prerequisites:

• Let’s assume we have a SQL Server instance named MSSQL running SQL Server 2012 or later.
• Let’s also assume that we have a database named SalesDB in the Full recovery model.

Enabling CDC:

• Open SQL Server Management Studio and connect to the MSSQL instance.
• Right-click the SalesDB database, and select "Properties."
• Select the “Options” page and set the “CDC” option to “True.”
• Close the database properties dialog.

Creating a CDC Capture Instance:

• In Object Explorer, right-click the “Replication” folder, and select “New Capture Instance.”
• In the “New Capture Instance Wizard,” select the SalesDB database and provide a name for the capture instance, such as SalesDB_CaptureInstance.
• Follow the remaining steps in the wizard to complete the creation of the CDC capture instance.

Creating a CDC Enabled Table:

• Open a new query window and run the following script to enable CDC for a table named Orders in the SalesDB database:

EXEC sys.sp_cdc_enable_table
@source_schema = ‘dbo’,
@source_name = ‘Orders’,
@role_name = NULL;

Once CDC is enabled, you can use the cdc.fn_cdc_get_all_changes_Orders function to retrieve changes made to the Orders table. This function returns a result set that can be used to track changes made to the data in real-time.

For example, you can use the following script to retrieve all changes made to the Orders table in the last hour:

SELECT *
FROM cdc.fn_cdc_get_all_changes_Orders (
GETDATE() — 1,
GETDATE(),
‘all’
)