Sources

Native CDC

Capture SQL Server changes from SQL Server's own CDC change tables, with nothing installed on the database host and no raw log access.

Native CDC reads the change tables that SQL Server's change data capture fills under the cdc schema. Use it when the host allows no raw log access, such as Amazon RDS or Azure SQL Managed Instance, or when the database is encrypted with TDE and the login can't be sysadmin or db_owner, which Polling needs.

How it works

SQL Server's CDC capture job reads the transaction log and writes each change of a CDC-enabled table to that table's change table, cdc.<capture_instance>_CT. The agent reads those change tables over its ordinary SQL connection, so lag follows the capture job's scan interval.

How Native CDC affects the log

SQL Flow holds no log in this mode. SQL Server's own CDC capture job keeps log records until it has scanned them, and its cleanup job deletes change rows older than its retention period. SQL Flow doesn't create capture instances or CDC jobs in this mode, and never changes the ones your DBA created.

A pipeline can resume only while the change rows it needs are still in the change tables. If the cleanup job removed them, for example during a long pause, the pipeline stops with a retention gap and the table needs a reload. Set the cleanup job's retention longer than the longest pause you expect, with sys.sp_cdc_change_job @job_type = N'cleanup', @retention = <minutes>.

Requirements

Requirement	Details
SQL Server version	SQL Server 2016 SP2 or later, in an edition that supports CDC.
Host	Nothing to install on the database host.
CDC	Enabled on the database (`sys.sp_cdc_enable_db`), with a capture instance on every selected table (`sys.sp_cdc_enable_table`). The CDC capture job runs, which needs SQL Server Agent.
Keys	Every selected table has a primary key.
Recovery model	Any.
`max text repl size`	`-1` (or `2147483647`) when a selected table has a LOB column.
Text columns	`char`, `varchar` and `text` columns use the database's code page.
Login	`SELECT` on every column of the selected tables and on the `cdc` schema, membership in a capture instance's gating role when it was created with one (`@role_name`), and `SELECT` on `sys.system_internals_partition_columns`. `db_owner` covers all of them.
Table repair and resync	Permission to run `sys.sp_repltrans`, which `db_owner` has. Only key repair and table resync need it.
Network	The agent reaches SQL Server on its SQL port (1433 by default).

Setup

As a sysadmin, enable CDC on the database. Replace <database_name>.
```
USE [<database_name>];
EXEC sys.sp_cdc_enable_db;
```

Create a capture instance for each selected table. Replace <schema_name> and <table_name>.

EXEC sys.sp_cdc_enable_table
    @source_schema = N'<schema_name>',
    @source_name = N'<table_name>',
    @role_name = NULL,
    @supports_net_changes = 0;

Confirm that the capture and cleanup jobs exist and that SQL Server Agent is running.
```
EXEC sys.sp_cdc_help_jobs;
```
If a selected table has a LOB column, have a DBA with ALTER SETTINGS set max text repl size. SQL Flow never changes a server option.
```
EXEC sys.sp_configure N'max text repl size', -1; RECONFIGURE;
```

Create the capture login and user, and grant read access. Replace <login_name> and <password>. If a capture instance was created with a gating role (@role_name), also add the user to that role.

USE master;
CREATE LOGIN [<login_name>] WITH PASSWORD = N'<password>';
USE [<database_name>];
CREATE USER [<login_name>] FOR LOGIN [<login_name>];
GRANT SELECT ON SCHEMA::[<schema_name>] TO [<login_name>];
GRANT SELECT ON SCHEMA::cdc TO [<login_name>];
GRANT SELECT ON sys.system_internals_partition_columns TO [<login_name>];

In the pipeline wizard, choose SQL Server on the Source step, select Native CDC under Capture mode, and enter the connection.
Select Test connection & list tables. Every start checks CDC on the database, the login's permissions, computed columns, text code pages and max text repl size, and names every unmet requirement at once, with the GRANT that fixes a missing permission. A selected table without a capture instance is named when the capture first reads it.

Encrypted data

TDE. Supported. The capture job reads the log through the engine, which decrypts it.
Always Encrypted columns. Refused. A selected table with an Always Encrypted column is refused with always_encrypted_column.
Connections. The agent always encrypts its SQL connection. With Encryption at Require (default), it validates the server certificate; Prefer skips that check, for an on-premises server with a self-signed certificate. A Managed Instance host name under .database.windows.net is always validated.

Pros and cons

Pros

Works where there is no raw log access, such as Amazon RDS.
Nothing to install on the database host.
Works on databases encrypted with TDE.
SQL Server does the log reading; SQL Flow only queries the change tables.

Cons

Lag follows the capture job's scan interval.
The capture job and the change tables add work and storage on the source.
Needs CDC enabled on the database and on every table, and SQL Server Agent running.
Tables without a primary key are refused.

Limitations

Typed xml. A column of xml typed by an XML schema collection is refused. Untyped xml is supported.
Primary keys. A table without a primary key is refused, even when it has a unique index.
Computed columns. A capture instance that includes a computed column is refused with native_cdc_computed_column, because CDC records computed values as NULL.
Text code pages. A char, varchar or text column whose collation uses a different code page than the database's is refused with native_capture_text_codepage_mismatch, because CDC can lose its non-ASCII characters. Use nchar, nvarchar or ntext, or matching code pages.
LOB columns. Tables with LOB columns need max text repl size at -1. SQL Server enforces it on your own writes to CDC-enabled tables.
CLR user-defined types. A column of a CLR user-defined type stops the capture by name when it first reads the table. hierarchyid, geography and geometry are supported.
sql_variant. Captured, but SQL Server and Azure SQL targets refuse a sql_variant column, because CDC returns each value without its base type. To replicate it to those targets, capture the table with DirectLog or Reconstruction.
Column sets. SQL Server can't enable CDC on a table with a column set (sys.sp_cdc_enable_table fails with error 22854). Capture such a table with Polling, or drop the column set; its sparse columns stay and are captured.
TRUNCATE TABLE. SQL Server refuses TRUNCATE TABLE on a table enabled for CDC.

Troubleshooting

CDC not enabled on the database

CDC enabled on database (EXEC sys.sp_cdc_enable_db)

Cause: CDC isn't enabled on the source database.

Fix: as a sysadmin, run EXEC sys.sp_cdc_enable_db; in the source database.

No capture instance for a tracked table

Native CDC capture is incomplete for tracked table '<table>': no capture instance exists. ...

Cause: a selected table has no capture instance.

Fix: run sys.sp_cdc_enable_table for the table, wait for the capture job to scan it, then reload the table.

Capture instance not yet queryable

Native CDC capture is not yet queryable for tracked table '<table>' (capture instance '<name>'): sys.fn_cdc_get_min_lsn returned zero. ...

Cause: the capture job hasn't initialized the new capture instance yet, or the login can't read every captured column or isn't in the capture instance's gating role.

Fix: confirm that SQL Server Agent and the capture job are running, and wait for the first scan. If the instance is initialized, grant the login SELECT on every captured column and add it to the gating role, if the instance has one. Then retry.

CDC retention gap on a tracked table

Native CDC retention gap for tracked table '<table>' (capture instance '<name>'): ...

Cause: the cleanup job deleted change rows the pipeline hadn't read, for example during a long pause.

Fix: reload the table. Raise the cleanup job's retention so it covers your longest pause.

Column type the capture can't read

NativeCdcUnsupportedProviderType: column '<column>' of '<table>' has type '<type>'; its retained provider bytes cannot be bounded before materialization. No checkpoint was advanced.

Cause: the capture instance includes a column of a CLR user-defined type.

Fix: remove the table from the pipeline.

Target refuses a sql_variant column

Table '<table>', column '<column>' (sql_variant): sql_variant remains deferred for this capture: only DirectLog and Reconstruction into a SQL Server or Azure SQL target carry each value's own base type exactly; Built-in CDC and Polling read it as a plain value, which would lose that type. To continue: ...

Cause: a selected table has a sql_variant column and the target is SQL Server or Azure SQL. The Prepare step shows this under the target schema check.

Fix: capture the table with DirectLog or Reconstruction, or deselect it on the Tables step.

Finite max text repl size for LOB tables

native_capture_lob_replication_limit_unmet: Native CDC selected LOB-capable tables [<tables>] require max text repl size (B) = -1 or 2147483647; ...

Cause: a selected table has a LOB column and max text repl size is finite. Your application's LOB writes longer than it fail with error 7139.

Fix: have a DBA run EXEC sys.sp_configure N'max text repl size', -1; RECONFIGURE;, or remove the tables from the pipeline.

If this was useful,
our newsletter will be too.

Get monthly insights on data engineering, AI, and building critical infrastructure - direct from the Spectral Core team and CEO Damir Bulic.