sql-server-scripts/cdc/SQLServerCDCDemo.sql at master · mbentham/sql-server-scripts

518 lines (464 loc) · 18.5 KB
    Script:         SQL Server CDC Demo
    Description:    End-to-end tutorial covering Change Data Capture (CDC) setup,
                    querying changes with human-readable operation labels, PII
                    redaction in both live and CDC history tables (GDPR-relevant),
                    schema drift detection, and manual capture instance recycling
                    with history preservation. Designed as a self-contained learning
                    resource that creates and cleans up its own objects.
    Prerequisites:  - SQL Server 2016 SP1+ (for sys.sp_cdc_enable_db, CREATE OR ALTER)
                    - SQL Server Enterprise, Developer, or Standard edition
                      (CDC is available in Standard from SQL Server 2016 SP1+)
                    - SQL Server Agent must be running (CDC uses Agent jobs)
                    - sysadmin or db_owner on the target database
                    - The target database must NOT be master, model, msdb, or tempdb
    Usage Notes:    - This is a TUTORIAL script. Run each section sequentially
                      in a NON-PRODUCTION database to observe CDC behaviour.
                    - The script creates a demo table, enables CDC, performs DML,
                      then cleans up after itself.
                    - Between disabling and re-enabling a capture instance (the
                      schema drift section), any changes to the table will NOT
                      be captured. In production, perform this during a
                      maintenance window.
                    - The cleanup section at the end drops the demo table and
                      disables CDC on the database. Only run it when you are
                      finished with the tutorial.
    License:        MIT License - https://opensource.org/licenses/MIT
    Source:         https://github.com/mbentham/sql-server-scripts
-- ============================================================================
-- STOP: This script is a step-by-step tutorial. Do NOT execute it all at once.
-- Highlight and run each section (between GO statements) individually so you
-- can observe the results at each stage.
-- ============================================================================
RAISERROR('This script is a step-by-step tutorial. Do not run it all at once. Highlight and execute each section individually.', 16, 1);
-- ============================================================================
-- STEP 1: Create the demo table
-- ============================================================================
-- Creates a demo table to demonstrate CDC. Uses IF NOT EXISTS so
-- the script is safe to re-run.
IF OBJECT_ID(N'dbo.CDC_Demo_Customer', N'U') IS NULL
    CREATE TABLE [dbo].[CDC_Demo_Customer] (
        [CustomerID]        INT            IDENTITY(1,1) NOT NULL,
        [CustomerName]      NVARCHAR(256)  NOT NULL,
        [CustomerTypeID]    INT            NOT NULL,
        [CountryCode]       VARCHAR(2)     NOT NULL,
        [TaxIdentifier]     VARCHAR(14)    NULL,
        [PhoneNumber]       VARCHAR(14)    NULL,
        [ExternalReference] VARCHAR(50)    NULL,
        [CreatedDate]       DATETIME2(7)   NULL,
        [RegionID]          INT            NOT NULL,
        [ParentCustomerID]  INT            NULL,
        CONSTRAINT [PK_CDC_Demo_Customer] PRIMARY KEY CLUSTERED ([CustomerID] ASC)
    PRINT 'Demo table created.';
    PRINT 'Demo table already exists -- skipping creation.';
-- ============================================================================
-- STEP 2: Enable CDC on the database
-- ============================================================================
-- CDC must be enabled at the database level before any table can be tracked.
-- This is idempotent -- calling it when CDC is already enabled is a no-op.
IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = DB_NAME() AND is_cdc_enabled = 1)
    EXEC sys.sp_cdc_enable_db;
    PRINT 'CDC enabled on database [' + DB_NAME() + '].';
    PRINT 'CDC is already enabled on database [' + DB_NAME() + '] -- skipping.';
-- ============================================================================
-- STEP 3: Enable CDC on the Demo table
-- ============================================================================
-- Creates a capture instance that tracks all columns. The CDC history table
-- will be named cdc.<capture_instance>_CT (e.g. cdc.dbo_CDC_Demo_Customer_CT).
-- Parameters:
--   @source_schema       - Schema of the tracked table
--   @source_name         - Name of the tracked table
--   @role_name           - Database role with SELECT on the CT table
--   @supports_net_changes - 0 = store every individual change
--                           1 = also support net change queries (requires PK/unique index)
--   @capture_instance    - Name for this capture instance (defaults to schema_table)
IF NOT EXISTS (
    SELECT 1
    FROM cdc.change_tables
    WHERE capture_instance = N'dbo_CDC_Demo_Customer'
    EXEC sys.sp_cdc_enable_table
        @source_schema     = N'dbo',
        @source_name       = N'CDC_Demo_Customer',
        @role_name         = N'CDCHistoryReader',
        @supports_net_changes = 0,
        @capture_instance  = N'dbo_CDC_Demo_Customer';
    PRINT 'CDC enabled on dbo.CDC_Demo_Customer with capture instance [dbo_CDC_Demo_Customer].';
    PRINT 'Capture instance [dbo_CDC_Demo_Customer] already exists -- skipping.';
-- ============================================================================
-- STEP 4: Insert sample data
-- ============================================================================
INSERT INTO dbo.CDC_Demo_Customer
    (CustomerName, CustomerTypeID, CountryCode, TaxIdentifier, RegionID)
    (N'Contoso Ltd', 1, 'GB', '12345678', 1);
INSERT INTO dbo.CDC_Demo_Customer
    (CustomerName, CustomerTypeID, CountryCode, TaxIdentifier, RegionID)
    (N'Northwind Traders', 2, 'FR', '01010101', 0);
-- View the current state of the table
SELECT * FROM dbo.CDC_Demo_Customer;
-- ============================================================================
-- STEP 5: Perform an UPDATE and a DELETE
-- ============================================================================
-- These DML operations will be captured by CDC automatically.
-- Update one record
UPDATE dbo.CDC_Demo_Customer
SET    TaxIdentifier = 'SC00001'
WHERE  CustomerName = N'Northwind Traders';
-- View the table after the update
SELECT * FROM dbo.CDC_Demo_Customer;
-- Delete a record
DELETE FROM dbo.CDC_Demo_Customer
WHERE  TaxIdentifier = '12345678';
-- View the table after the delete
SELECT * FROM dbo.CDC_Demo_Customer;
-- ============================================================================
-- STEP 6: Query the CDC history table
-- ============================================================================
-- The CDC history table (cdc.dbo_CDC_Demo_Customer_CT) stores every captured change.
-- Key CDC metadata columns:
--   __$start_lsn   - Log Sequence Number when the change was committed
--   __$end_lsn     - Reserved (usually NULL)
--   __$seqval       - Sequence value to order changes within a transaction
--   __$operation    - 1 = Delete, 2 = Insert, 3 = Update (before), 4 = Update (after)
--   __$update_mask  - Bitmask indicating which columns changed
--   __$command_id   - Ordinal of the command within the transaction
     sys.fn_cdc_map_lsn_to_time(__$start_lsn)  AS ChangeTime
    ,sys.fn_cdc_map_lsn_to_time(__$end_lsn)    AS EndTime
    ,__$seqval
    ,CASE __$operation
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Update (Before)'
        WHEN 4 THEN 'Update (After)'
     END                                         AS Operation
    ,__$update_mask
    ,__$command_id
    ,CustomerID
    ,CustomerName
    ,CustomerTypeID
    ,CountryCode
    ,TaxIdentifier
    ,PhoneNumber
    ,ExternalReference
    ,CreatedDate
    ,RegionID
    ,ParentCustomerID
FROM cdc.dbo_CDC_Demo_Customer_CT
ORDER BY __$start_lsn, __$seqval, __$command_id;
-- ============================================================================
-- STEP 7: PII redaction (GDPR right to erasure)
-- ============================================================================
-- When a data subject requests erasure, PII must be redacted in BOTH the live
-- table AND the CDC history table. CDC history tables are ordinary tables and
-- can be updated directly.
-- IMPORTANT: This demonstrates the concept. In production, build a stored
-- procedure that redacts all PII columns across all relevant tables and
-- capture instances in a single transaction.
-- Redact PII in the live table for CustomerID 2
-- (CustomerID 1 was deleted in Step 5)
UPDATE dbo.CDC_Demo_Customer
SET    CustomerName = N'[REDACTED]'
WHERE  CustomerID = 2;
-- Redact PII in the CDC history table for the same customer
UPDATE cdc.dbo_CDC_Demo_Customer_CT
SET    CustomerName = N'[REDACTED]'
WHERE  CustomerID = 2;
-- Verify the live table
SELECT * FROM dbo.CDC_Demo_Customer;
-- Verify the CDC history table -- PII is now redacted for customer 2
     sys.fn_cdc_map_lsn_to_time(__$start_lsn)  AS ChangeTime
    ,sys.fn_cdc_map_lsn_to_time(__$end_lsn)    AS EndTime
    ,__$seqval
    ,CASE __$operation
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Update (Before)'
        WHEN 4 THEN 'Update (After)'
     END                                         AS Operation
    ,__$update_mask
    ,__$command_id
    ,CustomerID
    ,CustomerName
    ,CustomerTypeID
    ,CountryCode
    ,TaxIdentifier
    ,PhoneNumber
    ,ExternalReference
    ,CreatedDate
    ,RegionID
    ,ParentCustomerID
FROM cdc.dbo_CDC_Demo_Customer_CT
ORDER BY __$start_lsn, __$seqval, __$command_id;
-- ============================================================================
-- STEP 8: Schema drift detection
-- ============================================================================
-- When columns are added to (or removed from) a tracked table, CDC does NOT
-- automatically update the capture instance. Changes to untracked columns
-- are silently lost.
-- The query below compares every user-table column against the columns
-- tracked by its capture instance. Any row with [IsTracked] = 0 indicates
-- a column that exists on the source table but is NOT being captured.
-- TIP: Schedule this query as a SQL Agent job or monitoring check to detect
-- schema drift before it causes data loss.
-- First, add a column to demonstrate drift
IF COL_LENGTH(N'dbo.CDC_Demo_Customer', N'IsActive') IS NULL
    ALTER TABLE dbo.CDC_Demo_Customer
        ADD [IsActive] BIT NULL;
    PRINT 'Column [IsActive] added to dbo.CDC_Demo_Customer.';
    PRINT 'Column [IsActive] already exists -- skipping.';
-- Make a change that touches the new column
UPDATE dbo.CDC_Demo_Customer SET [IsActive] = 1;
-- Detect untracked columns across all CDC-enabled tables
     s.[name]                                           AS SchemaName
    ,t.[name]                                           AS TableName
    ,ct.capture_instance                                AS CaptureInstance
    ,c.[name]                                           AS ColumnName
    ,ty.[name]                                          AS ColumnType
        WHEN cc.column_name IS NOT NULL THEN 1
        ELSE 0
     END                                                AS IsTracked
FROM sys.schemas        AS s
INNER JOIN sys.tables   AS t  ON s.[schema_id]  = t.[schema_id]
INNER JOIN sys.columns  AS c  ON t.[object_id]  = c.[object_id]
INNER JOIN sys.types    AS ty ON c.system_type_id = ty.system_type_id
                              AND c.user_type_id  = ty.user_type_id
LEFT  JOIN cdc.change_tables   AS ct ON t.[object_id] = ct.source_object_id
LEFT  JOIN cdc.captured_columns AS cc ON ct.[object_id] = cc.[object_id]
                                      AND c.[name]       = cc.column_name
                                      AND ty.[name]      = cc.column_type
WHERE t.[type]      = 'U'
  AND s.[name]     <> 'cdc'
  AND t.is_ms_shipped = 0
  AND ct.capture_instance IS NOT NULL   -- only show CDC-enabled tables
ORDER BY s.[name], t.[name], c.column_id;
-- ============================================================================
-- STEP 9: Capture instance recycling with history preservation
-- ============================================================================
-- To track the new column, we must:
--   1. Back up the existing CDC history to a temporary table
--   2. Disable the old capture instance
--   3. Re-enable CDC with the updated column set
--   4. Restore the old history into the new CT table
-- WARNING: Between steps 2 and 3, changes to the table are NOT captured.
-- In production, perform this during a maintenance window.
SET XACT_ABORT ON;
    -- 9a. Back up existing CDC history
    IF OBJECT_ID(N'dbo.dbo_CDC_Demo_Customer_CT_Backup', N'U') IS NOT NULL
        DROP TABLE dbo.dbo_CDC_Demo_Customer_CT_Backup;
    SELECT *
    INTO dbo.dbo_CDC_Demo_Customer_CT_Backup
    FROM cdc.dbo_CDC_Demo_Customer_CT;
    PRINT 'CDC history backed up to dbo.dbo_CDC_Demo_Customer_CT_Backup.';
    -- 9b. Disable the old capture instance
    EXEC sys.sp_cdc_disable_table
        @source_schema   = N'dbo',
        @source_name     = N'CDC_Demo_Customer',
        @capture_instance = N'dbo_CDC_Demo_Customer';
    PRINT 'Old capture instance [dbo_CDC_Demo_Customer] disabled.';
    -- 9c. Re-enable CDC with the full column set (including IsActive)
    EXEC sys.sp_cdc_enable_table
        @source_schema       = N'dbo',
        @source_name         = N'CDC_Demo_Customer',
        @role_name           = N'CDCHistoryReader',
        @supports_net_changes = 0,
        @capture_instance    = N'dbo_CDC_Demo_Customer';
    PRINT 'New capture instance [dbo_CDC_Demo_Customer] created with updated columns.';
    -- 9d. Restore old history into the new CT table
    --     The new CT table has the [IsActive] column, which will be NULL for
    --     historical rows (the column did not exist when those changes occurred).
    INSERT INTO cdc.dbo_CDC_Demo_Customer_CT (
         __$start_lsn
        ,__$end_lsn
        ,__$seqval
        ,__$operation
        ,__$update_mask
        ,__$command_id
        ,CustomerID
        ,CustomerName
        ,CustomerTypeID
        ,CountryCode
        ,TaxIdentifier
        ,PhoneNumber
        ,ExternalReference
        ,CreatedDate
        ,RegionID
        ,ParentCustomerID
         __$start_lsn
        ,__$end_lsn
        ,__$seqval
        ,__$operation
        ,__$update_mask
        ,__$command_id
        ,CustomerID
        ,CustomerName
        ,CustomerTypeID
        ,CountryCode
        ,TaxIdentifier
        ,PhoneNumber
        ,ExternalReference
        ,CreatedDate
        ,RegionID
        ,ParentCustomerID
    FROM dbo.dbo_CDC_Demo_Customer_CT_Backup;
    PRINT 'Historical CDC rows restored into new capture instance.';
    -- 9e. Clean up the backup table
    DROP TABLE dbo.dbo_CDC_Demo_Customer_CT_Backup;
    PRINT 'Backup table dropped. Capture instance recycling complete.';
BEGIN CATCH
    -- If anything fails, report the error and leave the backup table for investigation
    PRINT 'ERROR during capture instance recycling:';
    PRINT '  Message:  ' + ERROR_MESSAGE();
    PRINT '  Severity: ' + CAST(ERROR_SEVERITY() AS NVARCHAR(10));
    PRINT '  State:    ' + CAST(ERROR_STATE() AS NVARCHAR(10));
    PRINT '';
    PRINT 'The backup table dbo.dbo_CDC_Demo_Customer_CT_Backup has been preserved for recovery.';
-- ============================================================================
-- STEP 10: Verify the recycled capture instance
-- ============================================================================
-- The history table now contains both the restored historical rows (without
-- the IsActive column value) and any new changes going forward.
-- View the full history including the new IsActive column
     sys.fn_cdc_map_lsn_to_time(__$start_lsn)  AS ChangeTime
    ,sys.fn_cdc_map_lsn_to_time(__$end_lsn)    AS EndTime
    ,__$seqval
    ,CASE __$operation
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Update (Before)'
        WHEN 4 THEN 'Update (After)'
     END                                         AS Operation
    ,__$update_mask
    ,__$command_id
    ,CustomerID
    ,CustomerName
    ,CustomerTypeID
    ,CountryCode
    ,TaxIdentifier
    ,PhoneNumber
    ,ExternalReference
    ,CreatedDate
    ,RegionID
    ,ParentCustomerID
    ,[IsActive]
FROM cdc.dbo_CDC_Demo_Customer_CT
ORDER BY __$start_lsn, __$seqval, __$command_id;
-- Make a change to the new column to prove it is now tracked
UPDATE dbo.CDC_Demo_Customer SET [IsActive] = 0;
-- View the history again -- the IsActive change should now appear
     sys.fn_cdc_map_lsn_to_time(__$start_lsn)  AS ChangeTime
    ,sys.fn_cdc_map_lsn_to_time(__$end_lsn)    AS EndTime
    ,__$seqval
    ,CASE __$operation
        WHEN 1 THEN 'Delete'
        WHEN 2 THEN 'Insert'
        WHEN 3 THEN 'Update (Before)'
        WHEN 4 THEN 'Update (After)'
     END                                         AS Operation
    ,__$update_mask
    ,__$command_id
    ,CustomerID
    ,CustomerName
    ,CustomerTypeID
    ,CountryCode
    ,TaxIdentifier
    ,PhoneNumber
    ,ExternalReference
    ,CreatedDate
    ,RegionID
    ,ParentCustomerID
    ,[IsActive]
FROM cdc.dbo_CDC_Demo_Customer_CT
ORDER BY __$start_lsn, __$seqval, __$command_id;
-- ============================================================================
-- STEP 11: Cleanup (optional -- only run when you are finished with the demo)
-- ============================================================================
-- Removes all objects created by this tutorial. CDC must be disabled on the
-- table before dropping it, and disabled on the database after all tracked
-- tables are removed.
-- Disable CDC on the table
IF EXISTS (SELECT 1 FROM cdc.change_tables WHERE capture_instance = N'dbo_CDC_Demo_Customer')
    EXEC sys.sp_cdc_disable_table
        @source_schema    = N'dbo',
        @source_name      = N'CDC_Demo_Customer',
        @capture_instance = N'dbo_CDC_Demo_Customer';
    PRINT 'CDC disabled on dbo.CDC_Demo_Customer.';
-- Drop the demo table
IF OBJECT_ID(N'dbo.CDC_Demo_Customer', N'U') IS NOT NULL
    DROP TABLE dbo.CDC_Demo_Customer;
    PRINT 'Demo table dropped.';
-- Disable CDC on the database
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = DB_NAME() AND is_cdc_enabled = 1)
    EXEC sys.sp_cdc_disable_db;
    PRINT 'CDC disabled on database [' + DB_NAME() + '].';
Provide feedback

Saved searches

Use saved searches to filter your results more quickly

FilesExpand file tree

SQLServerCDCDemo.sql

Latest commit

History

SQLServerCDCDemo.sql

File metadata and controls
