Updating A Table Using Change Data Capture Without Downtime

Robert Blackburn takes us through the steps of updating a table which uses Change Data Capture without taking a downtime window:

Steps

  1. Stop jobs that process CDC (SSIS).

  2. Inside a transaction with isolation level serializable: Alter Table schema and create temporary CDC table

  3. Copy old CDC rows to new table excluding dup rows (based on [__$seqval])

  4. Disable old (original) CDC table (schema is outdated). Will drop table

Click through for the rest of the steps and an example script.

Related Posts

A Near-Zero Downtime Case Study

I have a post covering an example of making significant changes with near-zero downtime: This is where we start the decline phase in our story. Our temporary procedures existed as a bridge from the old procedures which took ClientID and new procedures which will take ProfileID. With our final versions of procedures, we replace @ClientID with @ProfileID in the input parameters […]

Read More

Sizing Azure SQL Database

Arun Sirpal takes us through finding the right size for Azure SQL Database: Do you want to identify the correct Service Tier and Compute Size ( was once known as performance level) for your Azure SQL Database? How would you go about it? Would you use the DTU (Database Transaction Unit) calculator? What about the […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031