Switching Capture Modes

This guide is SQL Server-specific. Postgres has one capture mode (WAL logical replication) and cannot be switched.

When you’d switch

The two SQL Server capture modes serve different environments:

	CDC mode	tlog mode
How it works	Reads CDC change tables (`cdc.*_CT`)	Reads `sys.fn_dblog` directly
Requires	CDC enabled on the database and each table	`VIEW DATABASE STATE` permission only
Overhead	Lower — CDC is a native SQL Server feature	Higher on busy databases

Common reasons to switch:

tlog → CDC: Your DBA finally enabled CDC after you launched in tlog mode. CDC is lower overhead and the recommended long-term choice.
CDC → tlog: You’re migrating to a managed SQL Server instance (e.g. Azure SQL Managed Instance) where CDC isn’t available, or you need zero-schema-change read access.

How to switch

Update cdc_mode in your pipeline YAML:
```
source:
  connection: my-sqlserver
  tables: [dbo.orders]
  properties:
    cdc_mode: cdc
```
```
source:
  connection: my-sqlserver
  tables: [dbo.orders]
  properties:
    cdc_mode: tlog
```
Apply the updated config:
```
nanosync apply --file pipelines.yaml
```

Nanosync detects the mode change and handles the transition automatically. You’ll see this in the logs:

INF capture mode change detected  pipeline=orders-to-bigquery  old=tlog  new=cdc
INF pipeline pausing for mode switch
INF re-snapshot required           tables=dbo.orders
INF snapshotting                   table=dbo.orders
INF cdc streaming                  lsn=0x0000C3A100000001

What happens to in-flight data

Nanosync completes writing all buffered events from the old capture mode before switching. Once the buffer is flushed and committed to the sink, the pipeline pauses and records the current LSN.

Depending on the direction of the switch, a re-snapshot may or may not be needed:

Switch direction	Re-snapshot?	Reason
tlog → CDC	Yes, if CDC wasn’t active when the pipeline started	The CDC change tables only contain changes from when CDC was enabled. Historical tlog data is not replayed through CDC tables.
CDC → tlog	No	tlog reads the transaction log directly, which contains the full history back to the log retention window. Nanosync resumes from the last CDC LSN.

No events are lost and no events are duplicated in either direction. The handoff is coordinated at an exact LSN boundary.

Switching tlog to CDC — prerequisites

Before applying cdc_mode: cdc, CDC must be enabled on both the database and the specific tables you’re replicating. If it isn’t, the pipeline will fail at startup with:

ERR CDC not enabled on table  table=dbo.orders
    hint: run sp_cdc_enable_table for each replicated table

Enable CDC on the database and tables:

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'orders',
  @role_name     = NULL;

See SQL Server source setup for the full permission requirements.

Switching CDC to tlog — prerequisites

tlog mode requires only VIEW DATABASE STATE:

GRANT VIEW DATABASE STATE TO nanosync;

No other schema changes are needed on the source. CDC can remain enabled — nanosync will simply stop reading the CDC change tables and switch to sys.fn_dblog.

Verifying the switch

After applying, confirm the pipeline resumed in the new mode:

nanosync pipeline status orders-to-bigquery

NAME                  STATUS      CAPTURE MODE   LAG    EV/S
orders-to-bigquery    ● live CDC   cdc            8ms    3,201

The CAPTURE MODE column shows the active mode. If the switch failed, the pipeline will show a failed status with an error message explaining why.