SQL Server Source

Prerequisites, permissions, and full configuration reference for the Nanosync SQL Server source connector.

Nanosync supports two change-capture modes for SQL Server:

Mode	Mechanism	Requirement
`cdc` (default)	SQL Server CDC change tables + LSN watermarking	CDC enabled on database and tables; `FULL` or `BULK_LOGGED` recovery model
`tlog`	`sys.fn_dblog` transaction log direct read	`VIEW DATABASE STATE` only — no CDC setup required

Mode: `cdc` (default)

Prerequisites

SQL Server 2016 or later
Database recovery model: FULL or BULK_LOGGED
CDC feature enabled on the database and each replicated table

Setup

Enable CDC on the database
```
USE mydb;
EXEC sys.sp_cdc_enable_db;
```

Enable CDC per table

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

Repeat for each table you want to replicate.

Create the login and grant permissions

CREATE LOGIN nanosync WITH PASSWORD = 'secret';
USE mydb;
CREATE USER nanosync FOR LOGIN nanosync;
EXEC sp_addrolemember 'db_datareader', 'nanosync';
GRANT SELECT ON SCHEMA::cdc TO nanosync;
GRANT EXECUTE ON sys.sp_cdc_help_change_data_capture TO nanosync;

Verify CDC is working
```
SELECT * FROM cdc.change_tables;
```

Configuration

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
        poll_interval: "5s"

Mode: `tlog`

Reads changes directly from SQL Server transaction log files — no CDC feature required on the source database.

Prerequisites

SQL Server 2016 or later
Database recovery model: FULL or BULK_LOGGED (not SIMPLE)
VIEW DATABASE STATE permission

Setup

Create the login

CREATE LOGIN nanosync WITH PASSWORD = 'secret';
USE mydb;
CREATE USER nanosync FOR LOGIN nanosync;

Grant permissions

EXEC sp_addrolemember 'db_datareader', 'nanosync';
GRANT VIEW DATABASE STATE TO nanosync;

Configuration

pipelines:
  - name: orders-tlog
    source:
      connection: prod-sqlserver
      tables:
        - dbo.orders
      properties:
        cdc_mode:       tlog
        poll_interval:  "200ms"
        log_batch_size: "10000"

tlog mode holds all events for an open transaction in memory until commit. Large transactions exceeding max_xact_memory (default 256 MiB) will cause the pipeline to error. For large-transaction workloads, prefer cdc mode.

Connection configuration

sqlserver://[user]:[password]@[host]:[port]?database=[db]&[options]

DSN option	Example	Description
`database`	`mydb`	Database name
`encrypt`	`true`	Enable TLS encryption
`TrustServerCertificate`	`false`	Skip certificate verification (dev/test only)
`MultiSubnetFailover`	`true`	Enable for Always On Availability Groups
`connection timeout`	`30`	Connection timeout in seconds

Always On Availability Group:

dsn: "sqlserver://nanosync:${env:SQL_PASSWORD}@ag-listener:1433?database=mydb&MultiSubnetFailover=true&encrypt=true"

Source properties

Connection

Property	Default	Description
`cdc_mode`	`cdc`	Change-capture mode: `cdc` or `tlog`
`poll_interval`	`5s` (cdc) / `200ms` (tlog)	How often to poll for new changes
`max_reconnect_attempts`	`5`	Maximum consecutive reconnect retries before the pipeline errors

Snapshot

Property	Default	Description
`chunk_size`	`10000`	Rows fetched per page during the initial backfill

CDC mode properties

Property	Default	Description
`schema_refresh_interval`	`5m`	TTL for the internal schema cache

tlog mode properties

Property	Default	Description
`log_batch_size`	`10000`	Maximum `fn_dblog` rows fetched per query
`max_xact_memory`	`268435456` (256 MiB)	In-memory byte limit across all open transactions
`max_xact_age`	`4h`	Duration after which a long-running transaction triggers a `WARN` log
`lob_fetch_timeout`	`5s`	Per-read timeout for off-row LOB columns

Output

Property	Default	Description
`track_history`	`false`	Include temporal table history-table inserts in the change stream
`split_updates`	`false`	Emit `UPDATE` as `DELETE(before) + INSERT(after)` pairs — improves BigQuery and Iceberg compatibility

Supported operations

Operation	`cdc` mode	`tlog` mode
`INSERT`	Yes	Yes
`UPDATE`	Yes — before + after	Yes — before + after
`DELETE`	Yes	Yes
`TRUNCATE`	Detected via full scan	Not captured
DDL changes	Schema drift detection only	Schema drift detection only

Limitations

TRUNCATE is not captured in tlog mode. In cdc mode it is detected via a full table scan, not a streaming event.
tlog mode holds all events for an open transaction in memory — large transactions exceeding max_xact_memory (default 256 MiB) cause the pipeline to error.
SQL Server Express edition does not support per-table CDC. Use tlog mode on Express.
Azure SQL Database: tlog mode is not available. Use cdc mode only.
DDL changes are detected but not replayed automatically. The pipeline pauses on breaking changes.

Monitoring

nanosync metrics pipeline orders-pipeline

Metric	Description
`ns_pipeline_replication_lag_seconds`	End-to-end source-to-sink latency
`ns_cdc_events_total`	CDC events read from change tables or tlog
`ns_snapshot_rows_total`	Rows written during the initial snapshot
`ns_tlog_read_lag_seconds`	Age of oldest unprocessed tlog record (tlog mode only)

Troubleshooting

sp_cdc_enable_db fails with “Database is not eligible for CDC”

The database must use FULL or BULK_LOGGED recovery model:

ALTER DATABASE mydb SET RECOVERY FULL;

No changes appear after CDC is enabled

The SQL Server Agent must be running:

SELECT name, enabled FROM msdb.dbo.sysjobs WHERE name LIKE 'cdc.%';

tlog mode: “transaction exceeded max_xact_memory”

Increase max_xact_memory or switch to cdc mode.

Always On AG: changes stop after failover

Point the DSN at the AG listener and set MultiSubnetFailover=true. The connector reconnects automatically and resumes from the last committed LSN.

SQL Server Source

Mode: cdc (default)

Prerequisites

Setup

Configuration

Mode: tlog

Prerequisites

Setup

Configuration

Connection configuration

Source properties

Connection

Snapshot

CDC mode properties

tlog mode properties

Output

Supported operations

Limitations

Monitoring

Troubleshooting

Mode: `cdc` (default)

Mode: `tlog`