Change Data Capture (cdc) System Tables aren't being created

Automated deployment for web applications and databases

Moderators: Mike Upton, justin.caldicott, Sean.newham, csmith, chirayu, DavidSimner, david.conlin

Change Data Capture (cdc) System Tables aren't being created

Postby bobkaine » Mon Oct 28, 2013 6:51 pm

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.

Code: Select all
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?
bobkaine
 
Posts: 4
Joined: Mon Oct 28, 2013 6:35 pm

Postby james.billings » Thu Oct 31, 2013 1:47 pm

Hi,

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby bobkaine » Thu Oct 31, 2013 2:01 pm

Thanks for the quick response.

I was kind of suspecting something similar.

I'll determine which approach you suggested will work the best and proceed from there.

Thank you.
bobkaine
 
Posts: 4
Joined: Mon Oct 28, 2013 6:35 pm


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 1 guest