Working with Databases Using Change Data Capture (CDC)
Published 23 December 2020
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.
Please note that SQL Change Automation does not script out system functions related to Change Data Capture into the baseline script. Those system functions will automatically be created by SQL Server itself when you enable Change Data Capture in a database.
Add a pre-deployment script to enable Change Data Capture for the database
If you are using a clone for baseline and Change Data Capture has already been deployed to your production environment, this step should not be necessary.
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.