Choosing a Capture Mode

SQL Server exposes changes through three mechanisms that nanosync calls capture modes. This guide explains what each mode does, when to use it, and how to configure it.

This guide is SQL Server specific. PostgreSQL has only one capture mechanism — logical replication via the pgoutput WAL plugin — so there is no mode selection for Postgres sources.

The three modes

cdc — SQL Server CDC change tables (default)

Nanosync reads from SQL Server’s built-in CDC change tables, which the SQL Server Agent populates by scanning the transaction log. Nanosync polls these tables using LSN watermarking to track position.

Requirements: CDC must be enabled on the database and on each replicated table. The database recovery model must be FULL or BULK_LOGGED. The SQL Server Agent must be running.

This is the recommended mode for almost every deployment. See the SQL Server source reference for the full setup procedure.

tlog — direct transaction log read

Nanosync reads changes directly from the SQL Server transaction log via sys.fn_dblog. No CDC feature is required on the source database — only VIEW DATABASE STATE permission.

The tradeoff: nanosync holds all events for each open transaction in memory until the transaction commits. Very large or long-running transactions can exhaust memory.

snapshot_only — full table read, no ongoing CDC

Nanosync performs a complete read of each configured table and writes all rows to the sink, then exits. No change stream is established. This is a one-shot migration mode, not a replication mode.

Decision guide

Work through these questions in order:

1. Is this a one-time migration with no need for ongoing changes? → Use snapshot_only. Once the initial load is complete, the pipeline exits. You do not need to maintain a persistent connection or manage a capture process.

2. Is the database on a managed service?

• Azure SQL Database → cdc only. Azure SQL does not expose the transaction log to sys.fn_dblog.
• Amazon RDS SQL Server → cdc only. RDS restricts direct log access.
• Self-managed SQL Server → either mode is available.

3. Can you enable CDC on the source database?

• Yes → use cdc. It is more stable under large transactions and integrates with SQL Server’s built-in operational tooling.
• No (SQL Server Express, shared instance without db_owner, corporate policy) → use tlog.

4. Does the workload have very large or long-running transactions?

• cdc mode writes completed change rows to the CDC change tables — nanosync reads them after commit. Memory use is bounded.
• tlog mode buffers all events for each open transaction in memory until commit. Transactions exceeding max_xact_memory (default 256 MiB) cause the pipeline to error. If your workload includes multi-GB transactions, prefer cdc.

Comparison

	cdc mode	tlog mode	snapshot_only
Requires CDC enabled on DB	Yes	No	No
SQL Server Agent required	Yes	No	No
Source overhead	Low	Very low	None
Nanosync memory use	Low	Higher — buffers open transactions	Low
Large transactions	Handles well	Risk of OOM (see max_xact_memory)	N/A
Azure SQL Database	Yes	No	Yes
Amazon RDS SQL Server	Yes	No	Yes
SQL Server Express	No	Yes	Yes
Ongoing replication	Yes	Yes	No — one-shot only
Recommended default	Yes	Restricted environments only	Migration only

Configuration

Set the mode in the source properties block of the pipeline:

connections:
  - name: prod-sqlserver
    type: sqlserver
    dsn: "sqlserver://nanosync:${env:SQL_PASSWORD}@host:1433?database=mydb"

pipelines:
  - name: orders-pipeline
    source:
      connection: prod-sqlserver
      tables:
        - dbo.orders
        - dbo.order_items
      properties:
        cdc_mode: cdc          # cdc | tlog | snapshot_only

The cdc_mode property defaults to cdc if omitted.

For tlog mode, you will typically also want to tune the memory limit:

properties:
  cdc_mode: tlog
  max_xact_memory: "536870912"   # 512 MiB — increase if large transactions are expected
  log_batch_size:  "10000"

For snapshot_only, no additional properties are required. The pipeline exits after the backfill completes:

properties:
  cdc_mode: snapshot_only
  chunk_size: "10000"

Mode-specific limitations

cdc mode:

• TRUNCATE is not captured as a streaming event — nanosync detects it via a full table scan on the next poll.
• The SQL Server Agent must be running continuously. If the Agent is stopped long enough for the transaction log to be truncated past the last CDC watermark, rows are lost and a re-snapshot is required.
• The CDC cleanup job retention window (@retention, default 3 days) must exceed the maximum expected nanosync downtime. Increase it if pipelines might be offline for extended periods.

tlog mode:

• Not available on Azure SQL Database or Amazon RDS SQL Server.
• Not available on SQL Server Express.
• TRUNCATE operations are not captured.
• Memory use grows linearly with the number of events in open transactions. Set max_xact_memory conservatively and monitor ns_tlog_open_xact_bytes in Prometheus.

snapshot_only mode:

• No LSN position is maintained after the snapshot. If you later want ongoing CDC, delete the pipeline and create a new one with cdc or tlog mode — it will re-snapshot and then begin streaming.