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

Writing Audit Logs To Azure Event Hubs

Ronit Reger announces that Azure SQL Database auditing logs can now go to Azure Log Analytics or Azure Event Hubs: Azure Log Analytics plays a central role in monitoring and management of your Azure environment. It enables collecting telemetry and other data from a variety of sources across Azure, and provides a query language and analytics […]

Read More

Switching Object Schemas

Steve Jones show you a quick way of switching a database object’s schema: I haven’t had the need to move an object from one schema to another in years. Really since SQL Server 2000. I wrote about deleting a user that owns a schema recently, but that’s often a first step. The next thing I might need […]

Read More

Categories

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