Change Data Capture (cdc) System Tables aren't being created
bobkaine
Posts: 6
We're using CDC for audit purposes.
When I create a new deployment package by right clicking the database in SSMS and select 'Publish [dbname] for Deployment', the package created doesn't include the CDC system tables.
As you can see above, it's attempting to create the CDC function, but since the function references the CDC tables that weren't created it fails.
How do I "fix" this?
When I create a new deployment package by right clicking the database in SSMS and select 'Publish [dbname] for Deployment', the package created doesn't include the CDC system tables.
2013-10-28 13:30:37 -04:00 DEBUG Creating [cdc].[fn_cdc_get_all_changes_dbo_TchApplicationRole] 2013-10-28 13:30:37 -04:00 ERROR Error while executing job: Create failed Invalid object name 'cdc.dbo_TchApplicationRole_CT'.
As you can see above, it's attempting to create the CDC function, but since the function references the CDC tables that weren't created it fails.
How do I "fix" this?
Comments
The problem here is that our SQL Compare engine (which underpins DM and several other tools we provide) doesn't currently support handling System level objects.
Because of this, the relevant objects will need to be created by some other method before you deploy.
If you don't have many target DB's to deploy to, and they are likely to stay in place once initially configured, then it's perhaps easiest to manually create the system objects CDC requires.
If you're likely to deploy to more systems, then it would be more useful to include a "PreDeploy.ps1" powershell script in your package that would check for, and create if not present, the required objects by executing a query via SQLCMD.
Redgate Software
I was kind of suspecting something similar.
I'll determine which approach you suggested will work the best and proceed from there.
Thank you.