Post 10: Handling Schema Evolution Mid-Migration: Managing Live DDL Changes over Change Data Capture (CDC)

You are in week three of a live replication catch-up phase, syncing a 40TB database footprint. Suddenly, an automated CI/CD pipeline pushes an ALTER TABLE ADD COLUMN statement to production. Does your migration pipeline survive, or does it silently corrupt your target tables?

When executing long-term cloud database migrations under active production load, thinking that you can enforce a blanket schema freeze for several weeks is a fantasy. Feature development continues, and schema evolution is inevitable.

Following our setup of the live replication pipeline architecture, the next major challenge is ensuring Schema Evolution Parity. If you are utilizing log-based Change Data Capture (CDC) via Azure Database Migration Service (DMS), Data Definition Language (DDL) changes alter the binary tracking boundaries.

Here is the exact engineering playbook we used to handle mid-migration DDL events, the structural mechanics of how CDC captures them, and how to avoid pipeline breaking errors.

The Mechanics: How CDC Reacts to DDL Changes

By default, when you execute a DDL command (like adding a column) on a source SQL Server table tracked by CDC, the underlying engine does not automatically alter the existing CDC capture instance table structure.

Get Nitish Anand Srivastava’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Instead:

1. The logging engine continues tracking transactions based on the old column layout schema.
2. Any newly added columns return a NULL state inside the transaction log stream for old transactions, and new entries in the new column are completely ignored by the active capture instance.
3. This creates an immediate structural mismatch between your active on-premise tables and the target Azure SQL Hyperscale tables, which will cause Azure DMS to throw serialization errors or silently drop data frames.

To bridge this gap without rebuilding your baseline seeding from scratch, you must implement a Dual-Capture Instance Switch.

The Engineering Playbook for a Live DDL Shift

If a schema modification must go through mid-flight, execute this precise four-step operational sequence to update the replication stream:

Step 1: Apply the Schema Change to the Target First

Before altering the source, manually apply the exact same DDL change to your target Azure SQL Hyperscale instance to prevent the DMS ingestion engine from hitting schema validation mismatches.

-- Execute on TARGET Azure SQL Hyperscale Instance
ALTER TABLE dbo.Orders 
ADD OperationalFlags VARCHAR(50) NULL;

Step 2: Apply the Schema Change to the Source

Apply the modification to your active production environment. Ensure the new column is created as NULL or possesses a valid default constraint.

-- Execute on SOURCE On-Premise SQL Server
ALTER TABLE dbo.Orders 
ADD OperationalFlags VARCHAR(50) NULL;

Step 3: Instantiate a Secondary Capture Instance

SQL Server allows up to two concurrent capture instances for a single source table. This allows us to spin up a new tracking engine that understands the updated schema layout without stopping the old one:

-- Create a secondary tracking instance on the source table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'Orders',
    @role_name = NULL,
    @capture_instance = N'dbo_Orders_v2', -- New version suffix
    @supports_net_changes = 1;
GO

Step 4: Update the Target Replication Stream Mapping

Once the secondary instance (v2) begins logging, identify its baseline Log Sequence Number (LSN). You will then update your active Azure DMS task parameters via the Azure CLI or Resource Manager portal to transition tracking boundaries seamlessly to the new capture instance.

Telemetry Script: Auditing Active Capture Schemas

To prevent hidden column drift from breaking your ingestion pipelines, run this metadata audit query on your source instance daily to identify misaligned definitions:

-- Audit column attributes inside active CDC capture instances
SELECT 
    Capture_Instance = c.capture_instance,
    Source_Table = OBJECT_NAME(c.source_object_id),
    Column_Name = col.name,
    Column_Type = type_name(col.user_type_id),
    Column_Ordinal = col.column_id
FROM sys.cdc_change_tables c
INNER JOIN sys.columns col ON c.source_object_id = col.object_id
ORDER BY Source_Table, Capture_Instance, Column_Ordinal;

Three Schema Pitfalls to Avoid at Enterprise Scale

1. The Online Index Rebuild Trap: Running an on-premise ALTER INDEX ... REBUILD WITH (ONLINE = ON) on a 5TB partition triggers massive internal log tracking tables generation. This significantly spike your transaction log (.ldf) size and introduces severe capture lag. The Strategy: Defer non-critical index maintenance optimization routines until after the cutover window.
2. DataType Narrowing Rules: Changing a column type from INT to BIGINT handles cleanly, but attempting to narrow definitions (e.g., VARCHAR(100) to VARCHAR(50)) will cause internal buffer exceptions inside DMS.
3. Dropping Tracked Columns: Avoid executing ALTER TABLE DROP COLUMN during an active replication stream. Instead, logically deprecate columns at the application layer and physically remove them from the schema structure after the target system goes live.

Schema evolution shouldn’t be treated as an unexpected emergency. By incorporating the dual-capture instance framework into your operational patterns, you can confidently run schema updates without interrupting your 40TB data flow.

How does your infrastructure team handle schema updates during active replication? Do you script manual dual-instance target cuts, or do you strictly mandate configuration lockouts? Let’s share notes in the comments.

This is Post 10 of our 20-part architectural series detailing cloud database transformations. In Post 11, we will wrap up our Data Replication phase by reviewing the exact human-centric Change Management processes and application freeze cadences required to stabilize our pipelines for cutover validation.