CDC support in SQL Server

Provisioning CDC enabled virtual databases 

Read: Provisioning a SQL Server VDB.

Viewing or updating CDC your configuration

For standalone VDBs, the CDC configuration selected during provisioning is saved and used for subsequent Refresh and Rewind operations. This configuration can also be viewed or updated after the VDB is provisioned in the Source tab under the Configuration section of the VDB.

For AG VDBs, the CDC configuration cannot be modified after provisioning. Once the VDB is provisioned, the CDC state remains fixed for the entire lifecycle of the VDB. All lifecycle operations such as Refresh, Enable, and Rewind will continue to use the CDC configuration that was selected at the time of provisioning.

Exporting CDC enabled physical databases 

1. In the Configuration page of the wizard, open the Advanced section and Select the ‘Enable’ option under ‘Change Data Capture (CDC)
   

   

2. CDC capture and cleanup jobs have to be added and CDC metadata has to be upgraded (if exporting is done from a lower database version to SQL2016 and above), manually on exported databases.

CDC capture and cleanup jobs

CDC capture and cleanup jobs must be added manually on the exported database.

In an Availability Group (AG) setup, these jobs can only be created on the primary replica. After a failover, you must ensure that these jobs are added again on the new primary replica if they are not already present.

These jobs cannot be created in advance on secondary replicas, as the database on the secondary node is not writable. The required commands can only be executed after the replica becomes primary and writable.

For reference, see the sample post start hook script to add CDC jobs.

CDC metadata upgrade

CDC metadata must be upgraded manually on exported databases if the export is performed from a lower SQL Server version to SQL Server 2016 or later.

General notes/troubleshooting 

• Make sure the SQL Server instance on which the VDB is being provisioned or exported, supports CDC.

• Make sure ‘SQL Server Agent’ for the instance is running otherwise adding CDC capture and cleanup jobs will fail.

• When to Enable or Disable CDC on a VDB

  • Retain CDC metadata: If the source database being ingested into Delphix is already CDC-enabled and you want the provisioned VDB to retain the corresponding CDC metadata, then CDC should be enabled on the VDB.

  • Start with a clean state: If you prefer the VDB to start without any inherited CDC metadata—for example, to begin with a clean state—you can disable CDC on the VDB after provisioning.

  • READ_ONLY dSource snapshots: Enabling or disabling CDC during VDB provisioning is not supported when the snapshot was taken from a READ_ONLY source database. This applies to both the provisioning wizard CDC toggle and to the CDC option in export workflows.

  Version specific behavior:
  

• Continuous Data Engine versions before 21.0.0.0: Disabling CDC did not delete the CDC metadata. Residual CDC artifacts could remain on the VDB and would require either hooks or manual cleanup after provisioning.

  On Continuous Data Engines version before 21.0.0.0, if the preference is to not enable CDC on the VDB, this command is to be issued after Provisioning operation:

  sys.sp_cdc_disable_db

  
  This is best accomplished with a Configure Clone hook. Here is a sample hook: 

# Use if you wish to trouble shoot and debug the code.
# Set-PSDebug -Trace 2; 
$VDB_HOST = $env:VDB_INSTANCE_HOST 
$VDB_PORT = $env:VDB_INSTANCE_PORT 
$VDB_INSTANCE = $env:VDB_INSTANCE_NAME 
$VDB_NAME = $env:VDB_DATABASE_NAME 
$CONNECT_STRING = "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT" 
$script:ErrorActionPreference = "Continue" 
function die { 
    Write-Error "Error: $($args[0])" 
    # run exit handler, if defined 
    if (Get-Command -type Function -name atExit 2> $null) { 
        atExit 
    } 
    exit 1 
} 
function verifySuccess { 
    if (!$?) { 
        die "$($args[0])" 
    } 
} 
## sqlcmd 
sqlcmd -l 30 -b -S "$VDB_HOST\$VDB_INSTANCE,$VDB_PORT" -d $VDB_NAME -W -E -s ";" -q "sys.sp_cdc_disable_db"  -o "C:\Users\delphix\DelphixPSLog_$((Get-Date).ToString('MM-dd-yyyy_hh-mm-ss')).log"

Alternatively, run the command manually after the provision job (Refresh, for example) and then perform a snapshot on the VDB to preserve the setting. (The Configure Clone hook runs the script after VDB is running, just prior to the snapshot sub-job that runs after the provision).

At this point the VDB will no longer have the " cdc " schema resources and CDC itself is disabled.

You can check CDC status from SQL Server Management Service(SSMS), SQLCMD or a suitable SQL management tool and run the following query:.

In this example the database name is "my_vdb"

select is_cdc_enabled from sys.databases where name like 'my_vdb';

Sample output:

is_cdc_enabled
1

• Continuous Data Engine version 21.0.0.0 and later: The provisioning scripts include a disable sequence, so disabling CDC will properly remove the metadata, requiring no user intervention.