SQL Server Source

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.

Enable snapshot isolation

Nanosync uses snapshot isolation during the initial backfill to read a consistent point-in-time view of each table. Without it, concurrent writes during the snapshot can produce duplicates or missing rows — especially on tables without primary keys.

ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION ON;

Start the SQL Server Agent and keep it running

CDC capture and cleanup jobs run inside the SQL Server Agent. If the Agent is down for an extended period, transaction logs accumulate and may get truncated before nanosync reads them — causing permanent data loss.

Verify the Agent is running:

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

Set the Agent service to restart automatically to prevent extended outages.

Configure CDC job retention

Two separate jobs control CDC behaviour. Set both:

Capture job — how often SQL Server scans the transaction log for new changes (@pollinginterval is in seconds):

USE mydb;
EXEC sys.sp_cdc_change_job @job_type = 'capture', @pollinginterval = 86399;
EXEC sp_cdc_stop_job 'capture';
EXEC sp_cdc_start_job 'capture';

Cleanup job — how long rows stay in CDC change tables before being deleted (@retention is in minutes; default is 4320 = 3 days). Increase this if pipelines might be down for extended periods:

EXEC sys.sp_cdc_change_job @job_type = 'cleanup', @retention = 14400;  -- 10 days

If nanosync is offline longer than the retention window, rows are deleted before they can be read — the pipeline must re-snapshot affected tables.

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;
GRANT VIEW DATABASE STATE TO nanosync;

-- Also add the user to master so server-level grants work
USE master;
CREATE USER nanosync FOR LOGIN nanosync;

Verify CDC is working

SELECT * FROM cdc.change_tables;

Create the login

CREATE LOGIN nanosync WITH PASSWORD = 'secret';

USE mydb;
CREATE USER nanosync FOR LOGIN nanosync;

-- Also add the user to master so server-level grants work
USE master;
CREATE USER nanosync FOR LOGIN nanosync;

Grant permissions on the source database

USE mydb;
EXEC sp_addrolemember 'db_datareader', 'nanosync';
-- db_owner is required to read tlog; db_denydatawriter prevents accidental writes
EXEC sp_addrolemember 'db_owner', 'nanosync';
EXEC sp_addrolemember 'db_denydatawriter', 'nanosync';
GRANT VIEW DATABASE STATE TO nanosync;

Grant server-level and master permissions

USE master;
GRANT VIEW SERVER STATE TO nanosync;
GRANT SELECT ON sys.fn_dblog TO nanosync;

Grant msdb permissions

Nanosync checks SQL Server Agent job state to detect log truncation risk.

USE msdb;
CREATE USER nanosync FOR LOGIN nanosync;
GRANT SELECT ON dbo.sysjobs TO nanosync;