Error: CDC Cannot Be Enabled on a Table with a Clustered Columnstore Index

Issue

When enabling change data capture (CDC) on a SQL Server table, the following error appears:

F_JD20F2: DBMS error [ODBC Driver 17 for SQL Server][SQL Server]Change Data Capture cannot be enabled on a table with a clustered columnstore index.

Environment

• HVR 6
• Microsoft SQL Server (all editions)
• Tables with clustered columnstore indexes

Resolution

To resolve this issue, use one of the following workarounds:

• Drop the clustered columnstore index. You may replace it with a standard clustered index or a nonclustered columnstore index if performance is a concern.
• Copy the table structure and data to a shadow table that uses a supported index type. Enable CDC on the shadow table and set up HVR replication. This requires database development effort.
• If CDC isn’t an option and real-time sync isn’t required, use periodic full-table refreshes.
• Move affected tables to a separate channel configured only for refresh, leaving CDC-enabled tables in the standard sync configuration.

Cause

This issue occurs when you enable CDC on a table with a clustered columnstore index. Clustered columnstore indexes store table data in a columnar format, which prevents SQL Server from capturing the row-level changes CDC needs. SQL Server enforces this limitation and applies it to all replication tools, including Fivetran and HVR.

For more information, see the Microsoft documentation on CDC and columnstore indexes.

Additional notes

• CDC supports nonclustered columnstore indexes beginning with SQL Server 2016.
• Schemas such as Epic Clarity commonly include clustered columnstore indexes.
• We recommend reviewing indexing strategies before enabling CDC.
• For alternative configurations or index changes, contact Microsoft Support or your SQL Server DBA.