What the database owns

You put "EnableCDC": true on one table and deploy. Somewhere in that request is a second one you didn’t make: turn Change Data Capture on for the whole database. That switch creates a cdc schema, two SQL Server Agent jobs, retention and cleanup for every table that will ever be captured, and a change table’s worth of storage per table. It’s a decision about the database.

A deploy tool that quietly made it for you, because one table’s JSON asked, would be doing exactly what this course is about not doing. So SchemaSmith doesn’t. It deploys the table, skips the feature, and tells you what to run. Then it gives you a place in the package to run it yourself.

The downgrade is the refusal

        CDC skipped: not enabled on this database (EXEC sys.sp_cdc_enable_db to allow it - downgraded): [dbo].[Payment]
        Change Tracking skipped: not enabled on this database (ALTER DATABASE ... SET CHANGE_TRACKING = ON to allow it - downgraded): [dbo].[Customer]

The tables deploy. The features don’t. The run exits 1 with outcome Incomplete, and the summary lists both downgrades. Each line names the statement that would allow it, because the fix is one statement away. It’s just not the deploy’s statement to run.

The Setup slot

The fix is a folder whose QuenchSlot is Setup:

"ScriptFolders": [ { "FolderPath": "Setup", "QuenchSlot": "Setup" } ]

Setup scripts run before the first object and before any table is touched, so a deploy can turn the database switch on and the table feature on in one pass:

    Quenching .\Package\Templates\Main\Setup\01-change-tracking.sql
    Quenching .\Package\Templates\Main\Setup\02-cdc.sql
        Enable CDC on [dbo].[Payment]
        Enable Change Tracking on [dbo].[Customer]

The switch now lives in your package, where review can see it, not in a deploy side effect. One rule: Setup runs on every deploy. It isn’t a run-once migration, so every script checks first:

IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE database_id = DB_ID() AND is_cdc_enabled = 1)
  EXEC sys.sp_cdc_enable_db;

Two capture instances

A capture instance records a fixed list of columns. Widen a column and the list is the same, so nothing happens to capture. Add a column and the list changes, so SchemaSmith rotates:

        CDC ROTATED on [dbo].[Payment]: new capture instance dbo_Payment_2 now captures the new column set. The previous instance dbo_Payment STILL HOLDS ITS HISTORY and was NOT dropped -- drain it, then drop it with EXEC sys.sp_cdc_disable_table @capture_instance = N'dbo_Payment'. Until then the next column change on this table WILL FAIL: SQL Server allows only two capture instances.

The capitals are deliberate. The old instance holds changes your downstream readers haven’t consumed yet, and only you know when they have. SQL Server allows two instances per table, so a third column change before you drain is refused:

CDC capture-instance limit reached on: [dbo].[Payment]. … Nothing has been changed. Drain the older instance on each listed table and drop it …, then re-run.

A change the change table can’t hold

This is the subtle one. Narrow Memo from VARCHAR(40) to VARCHAR(10), on a table whose rows all fit. The table could take it. But SQL Server applies a column’s type change to every change table that captures the column, and the change table has a captured value 29 characters long. That change fails inside the engine, and when it does, the capture job stops for every table in the database.

So the deploy checks the change tables first:

CDC capture would stop on [dbo].[Payment]: the change to column [Memo] (VARCHAR(10) …) cannot be applied to change table [cdc].[dbo_Payment_2_CT] of capture instance dbo_Payment_2, which holds values the new type does not take (the longest is 29 characters). … Nothing has been changed.

In the lab that 29-character value was written by the real capture job. The sandbox runs SQL Server Agent, so what you’re protecting is genuinely captured history.

Leaving it out is never destructive

Take EnableCDC out of a table’s JSON and the next deploy leaves capture exactly as it is, so CDC someone enabled outside the package survives. Turning it off drops the capture instances and their history, so it takes an explicit "EnableCDC": false. That’s the doctrine of the next module, arriving early: what you don’t declare isn’t in the comparison, so no deploy of yours can lose it.

Check yourself: A teammate adds a column to a CDC-tracked table on Monday, and the deploy logs the CDC ROTATED line. On Wednesday they add another column and the deploy fails with 'capture-instance limit reached', exit 2. They ask you to make the deploy just drop the old instance automatically. What do you tell them?

That dropping it is a data decision, not a schema one. The old instance holds the changes captured before Monday’s rotation, and whatever reads them, an ETL job or a replication feed, may not have consumed them yet. Dropping it automatically would discard that history without anyone checking.

The refusal names the fix: confirm the readers have drained the old instance, drop it with sys.sp_cdc_disable_table, and re-run. Nothing has been changed means Wednesday’s deploy left the table exactly as it was, so there’s nothing to clean up before the retry.

Some switches belong to the whole furnace, not to the piece on the anvil. A table can ask for capture, but it can’t light the database’s fire for every other table in the shop. So the deploy says what it needs, and the Setup slot is where you decide to give it, in writing, in review.

Then the guards take over: rotate, never discard; refuse the third instance; refuse the type change that would stop every capture in the database. Each one says Nothing has been changed, and means it.

Got a capture setup a deploy wouldn’t leave alone? Email me at forgebarrett@schemasmith.com. More’s coming from the forge.

Until then, may your capture jobs keep running, and your history wait until you’re ready to let it go.

— Forge

Mark this lesson complete