SQL Change Automation gives you granular control over the TSQL executed against your target databases, which enables you to version control database code changes for objects even if you are using technologies like SQL Server replication, Change Tracking, or Change Data Capture. 

In this page, we share considerations for setting up a SQL Change Automation project when using Change Data Capture (CDC).

Recommended Project Setting: Object Existence Checks

When creating a new project for a database where you are using, or plan to use Change Data Capture, it is useful to enable the comparison option for 'Add object existence checks'. 

This can be done in the Project Setup wizard in the SSMS plugin on the "Edit comparison options" pop up.

Setting this property before 'baselining' an existing database with SQL Change Automation ensures that any items SQL Change Automation scripts related to Change Data Capture – such as creation of the CDC schema – are scripted in an idempotent way. 

If you ever wish to change this setting after the project has been created, you may alter it by editing the comparison and script generation options in the .sqlproj file. 

Add a pre-deployment script to enable Change Data Capture for the database

If you wish to include commands to manage Change Data Capture in your migration scripts – such as adding tables to change data capture, removing them, etc., you will need to create a pre-deployment script to enable the feature.

This pre-deployment script will ensure that your shadow and build databases are properly configured to validate scripts containing Change Data Capture commands.

We've provided a starter pre-deployment script here.

Add commands to manage CDC in your migration scripts as needed

While SQL Change Automation assists you in generating migration scripts for your database object definitions, it does not automatically detect if tables have been added to Change Data Capture.

However, SQL Change Automation allows you to easily add or edit migration scripts to add TSQL managing Change Data Capture to your project.

An example code snippet added to the project might look like this:

IF OBJECT_ID('cdc.dbo_NewTable_CT ') IS NULL
	EXEC sys.sp_cdc_enable_table @source_schema = N'dbo',
	@source_name = N'NewTable',
	@role_name = NULL;
GO

Verify your code locally

Whether you are using SQL Change Automation in SSMS or in Visual Studio, local verification will help you identify any problems in your project early.