Thanks for this suggestion.

Change Data Capture is now supported quite well by SQL Change Automation projects which has plugins in both Visual Studio and SSMS. Configuring this does take some careful implementation as I’ll describe here.

When setting up the project, I recommend setting the ‘comparison’ option for “Add object existence checks” to true. This can be done in the graphic project wizard in the SSMS plugin, and this means that any commands regarding the CDC schema, etc in your migration script will be written in an idempotent way that won’t cause errors. (Note: I didn’t see CDC system tables included in the baseline script, which is good.)

It is also helpful to add a pre-deployment script to the project to ensure that cdc is enabled. This will enable project verification against a shadow database and build databases.

If you wish to manage change tracking in your migration scripts, you can do this as well by adding in commands to do so in your migration scripts, 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

If teams are also SQL Prompt users, then snippets of reusable code can make adding in these code blocks easier.

I will follow up on this by adding some additional documentation to SQL Change Automation for this scenario for others who may want to implement this.

Thanks again.