SQL Server CDC with Fivetran: Change Data Capture, Change Tracking and the Retention Trap

Postgres gets all the attention in change-data-capture write-ups, but in most enterprises the system that actually runs the business is SQL Server — an ERP, a claims platform, a 15-year-old order management system with 3,000 tables and a DBA who has opinions. Fivetran replicates it well, but SQL Server is the source where "just point the connector at it" fails most often, because the ingestion method is a decision you make on the database, before Fivetran is involved at all.

This tutorial covers the three SQL Server read methods Fivetran supports, how to pick between them, the exact database-side setup, and the failure modes that show up in week three rather than day one.

The three methods, and why the choice matters

Method	Captures deletes	Captures before-images	Source overhead	Needs Agent/Enterprise features
CDC (sys.sp_cdc_enable_table)	Yes	Yes	Change tables + capture job	SQL Server Agent must run
Change Tracking	Yes (as a flag)	No	Light, version-based	Agent not required
Table scan / teleport	No (without workarounds)	No	Full or key-based reads	None

The short rule: CDC is the default for anything you will model, Change Tracking is the fallback when CDC is politically or operationally blocked, and table scan is for small, append-only or reference tables where a periodic full read is cheaper than any capture mechanism.

The distinction people get burned by is before-images. CDC writes both the old and new version of a changed row into a change table; Change Tracking only tells you that row with key 42 changed, and Fivetran then has to go back and read the current row from the base table. That has two consequences: Change Tracking produces extra read load against your live tables, and if a row changes twice between syncs you only ever see the final state. For analytics that is usually fine. For audit or history-mode Type 2 dimensions, it is not — you will silently lose intermediate versions.

Azure SQL Database, Azure SQL Managed Instance, Amazon RDS for SQL Server and self-managed instances all support CDC, but the enabling procedure differs — RDS uses msdb.dbo.rds_cdc_enable_db, not the plain sys.sp_cdc_enable_db. Check which platform you are on before you write the runbook.

Step 1: database-side setup for CDC

Run as a user with sysadmin (or db_owner plus the RDS wrapper on Amazon). Enable at the database level once:

-- Self-managed / Azure MI
EXEC sys.sp_cdc_enable_db;

-- Amazon RDS for SQL Server
EXEC msdb.dbo.rds_cdc_enable_db 'SalesDb';

Then enable per table, explicitly listing the columns you want. Do not enable CDC on the whole schema reflexively — every captured column is written twice, once to the base table and once to the change table.

EXEC sys.sp_cdc_enable_table
  @source_schema       = N'dbo',
  @source_name         = N'Orders',
  @role_name           = N'fivetran_cdc_role',   -- gating role, not NULL
  @supports_net_changes = 1,
  @captured_column_list = N'OrderId, CustomerId, Status, Amount, UpdatedAt';

@supports_net_changes = 1 requires a primary key or a unique index, which you want anyway — Fivetran needs a primary key to merge rows into the destination. Tables without one get appended rather than merged, and that difference is how a 40-million-row table becomes a 400-million-row one.

Create a least-privilege login for the connector:

CREATE LOGIN fivetran_svc WITH PASSWORD = '<strong-secret>';
USE SalesDb;
CREATE USER fivetran_svc FOR LOGIN fivetran_svc;

GRANT SELECT ON SCHEMA::dbo TO fivetran_svc;          -- or per-table grants
GRANT SELECT ON SCHEMA::cdc TO fivetran_svc;
GRANT VIEW DATABASE STATE TO fivetran_svc;
GRANT VIEW CHANGE TRACKING ON SCHEMA::dbo TO fivetran_svc;  -- if using CT
EXEC sp_addrolemember 'fivetran_cdc_role', 'fivetran_svc';

Verify the capture mechanism is actually alive before you leave the database:

SELECT name, is_cdc_enabled, is_change_tracking_on FROM sys.databases;
SELECT * FROM sys.dm_cdc_errors;
EXEC sys.sp_cdc_help_jobs;   -- confirms capture and cleanup jobs exist

If sp_cdc_help_jobs returns nothing, SQL Server Agent is not running the capture job, and CDC will look enabled while recording nothing. That is the single most common "Fivetran isn't picking up changes" ticket on this source.

Step 2: the retention trap

The CDC cleanup job defaults to 72 hours of retention. If the connector is paused for a long weekend, or a huge initial sync overruns, or a network change breaks the connection on a Friday, cleanup deletes change rows the connector never read. Fivetran detects the gap and the recovery is a re-sync of the affected tables — expensive in MAR and in warehouse credits.

Extend it deliberately:

EXEC sys.sp_cdc_change_job
  @job_type   = N'cleanup',
  @retention  = 10080;   -- minutes = 7 days

Seven days costs disk and buys you a holiday weekend. Pair it with an actual alert on change-table lag rather than hoping:

SELECT  ct.capture_instance,
        DATEDIFF(minute, sys.fn_cdc_map_lsn_to_time(MIN(ct.start_lsn)), SYSUTCDATETIME()) AS oldest_change_age_min
FROM    cdc.change_tables ct
GROUP BY ct.capture_instance;

Wire that into the same alerting path as your pipeline observability dashboard, and set the threshold at roughly half your retention window so you have time to act.

Step 3: configure the connection in Fivetran

In the connector setup choose SQL Server and the update method matching what you enabled. Then, before the first sync:

• Select tables explicitly. A 3,000-table ERP with schema change handling on Allow all will replicate every audit and staging table it finds. Set the connection to ALLOW_COLUMNS or BLOCK_ALL and enable tables deliberately — see the schema drift playbook.
• Point at a replica where possible. An Always On readable secondary works for Change Tracking and table reads. CDC change tables are, however, populated on the primary and readable from the secondary, so confirm the capture instance is configured on the primary and the connector's login exists on both nodes after failover.
• Plan the private path. SQL Server is almost never internet-facing; this will need PrivateLink, a VPN or an SSH tunnel. Compare the options before kickoff, because firewall approvals are the long-lead item.
• Watch the datatype map. DATETIME2, DATETIMEOFFSET, MONEY, UNIQUEIDENTIFIER, HIERARCHYID, GEOGRAPHY and XML all land differently. Legacy DATETIME has ~3ms rounding, so timestamps will not always compare equal to the source. Agree what happens with NVARCHAR(MAX) and binary columns up front, and block them if they are large and useless to analytics — they are pure cost.

Step 4: survive the initial sync

ERP fact tables are big, and SQL Server initial syncs read the base table while the change tables accumulate. Two rules keep this calm:

1. Sync in waves. Enable the dimension and reference tables first, confirm they land and model correctly, then add the large fact tables one at a time. A failed 20-hour sync of everything restarts everything.
2. Hold retention open wider than the expected sync time, times two. If the backfill is estimated at three days, do not run with a three-day cleanup window.

The mechanics of large backfills — chunking, pausing, resuming — are covered in surviving the initial sync; everything there applies here, with the SQL Server twist that your retention clock is running the whole time.

Step 5: validate before anyone builds a dashboard

Do not accept "sync succeeded" as proof. Run three checks per table on the first day and then weekly for a month:

-- Source
SELECT COUNT(*) AS row_count, MAX(UpdatedAt) AS max_updated FROM dbo.Orders;

-- Destination (Snowflake example)
SELECT COUNT(*) AS row_count, MAX(UPDATED_AT) AS max_updated
FROM RAW.SALESDB_DBO.ORDERS
WHERE NOT _FIVETRAN_DELETED;

Then a checksum on a numeric column (SUM(Amount)) and a spot-check on deletes: delete a test row in a staging copy and confirm _FIVETRAN_DELETED flips rather than the row vanishing. If deletes never appear, you are on Change Tracking or table scan without realising it — see handling deletes in Fivetran.

Common failures, and what they actually mean

• "Sync is succeeding but data is stale." SQL Server Agent stopped, or the capture job is disabled. Check sys.sp_cdc_help_jobs and sys.dm_cdc_errors first, not the Fivetran logs.
• "Connector asked for a re-sync out of nowhere." The LSN it held was cleaned up. Retention, every time.
• "Row counts drift upward in the destination." A table without a primary key is appending. Add a unique index or exclude it.
• "MAR spiked after a release." A deployment rewrote a column across every row — a backfill UPDATE, an index rebuild with a data change, or a tool that touches rows it did not need to. One UPDATE dbo.Orders SET Status = Status re-bills the entire table. Look at the connection-level MAR view and go talk to the release team.
• "Schema change broke downstream models." Capture instances are tied to a column list. Adding a column to the table does not add it to CDC — you create a second capture instance and migrate. This is the one genuine operational tax of SQL Server CDC, and it needs to be in your change-management checklist, not discovered by an analyst.

A realistic timeline

For a single mid-size SQL Server source, a competent team lands this in two to four weeks: a few days of DBA negotiation and CDC enablement, a week of network approvals running in parallel, a few days of wave-by-wave backfill, and a week of validation and modelling. The parts that overrun are never the connector config — they are the retention decision, the private network path and the table without a primary key that nobody mentioned.

SyncSpur does this work regularly against ERP and line-of-business SQL Server estates: we scope the capture method with your DBAs, size the retention and backfill, and hand back validated raw schemas with a runbook. If you have a SQL Server source that needs to reach a warehouse without destabilising the application, get in touch, or read how we approach data integration using Fivetran and migration to the Fivetran platform.