Microsoft SQL Server Source: CDC Capture Instances

With this method, Artie reads the change tables that SQL Server CDC maintains for each tracked table. This is the default replication method. SQL Server documents and supports this interface.

What you’ll need

SQL Server 2012 and later, or Azure SQL Managed Instance, or Amazon RDS for SQL Server
SQL Server Agent running, so the CDC capture and cleanup jobs run. On Amazon RDS, Agent is managed for you.
A service account with SELECT on the database
CDC enabled on the database and on each table you replicate
The primary key columns included in each table’s capture instance

Setup

Create a service account

USE [ANALYTICS];
CREATE LOGIN artie WITH PASSWORD = 'use-a-strong-password';
CREATE USER artie FOR LOGIN artie;
ALTER LOGIN artie ENABLE;
GRANT SELECT ON DATABASE::ANALYTICS TO artie;
GO

Enable CDC on the database

USE [ANALYTICS];
EXEC sys.sp_cdc_enable_db;
GO

On Amazon RDS, run EXEC msdb.dbo.rds_cdc_enable_db 'ANALYTICS'; instead.If CDC isn’t enabled on the database, the pipeline stops with database "ANALYTICS" is not enabled for SQL Server CDC.

Set the retention period

The CDC cleanup job deletes changes older than the retention period. The default is 3 days. Set it to cover the longest time your pipeline could be paused or down. We recommend 7 days.

USE [ANALYTICS];
EXEC sys.sp_cdc_change_job @job_type = N'cleanup', @retention = 10080; -- minutes, 7 days
GO

If the pipeline is stopped for longer than the retention period, the changes it hadn’t read yet are gone from SQL Server. When it starts again, Artie continues from the oldest change still in the change table and logs Persisted SQL Server CDC offset is behind current min LSN; resetting to min LSN. Changes in between aren’t replicated. Trigger a backfill of the affected tables to fix the destination.

Enable CDC on each table

USE [ANALYTICS];
EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'orders',
  @role_name = NULL;
GO

To restrict who can read the change data, pass a database role as @role_name and add the artie user to it.The capture instance must include the table’s primary key columns. If it doesn’t, the pipeline stops with CDC capture instance "dbo_orders" for table dbo.orders does not capture primary key column "id".

Enter the connection in Artie

In Source, enter the Host, Port (default 1433), Username, Password and database. Under Replication method, select Capture instances.If you use an Always On availability group, enter the listener as the host. See High availability and failover.

Advanced

How Artie reads changes

Every 2.5 seconds, Artie checks sys.fn_cdc_get_max_lsn(). If it has moved, Artie reads each capture instance with cdc.fn_cdc_get_all_changes_<capture_instance> and the all update old option, 10,000 rows at a time, until it catches up. It reads up to 10 capture instances at once.
Each update arrives as one message with the row’s values before and after the change. Each change carries its commit time from sys.fn_cdc_map_lsn_to_time.
Artie saves its position (LSN, sequence value and operation per capture instance) only after every change it read has been written to its Kafka buffer. If the pipeline restarts, it replays from the saved position, so some changes can arrive twice. The destination merges on the primary key, so the result is the same.

Schema changes

A capture instance keeps the columns the table had when it was created. To capture a new column, create a second capture instance, then drop the old one.

USE [ANALYTICS];
ALTER TABLE dbo.customers ADD phone_number VARCHAR(32);

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name = N'customers',
  @role_name = NULL,
  @capture_instance = N'dbo_customers_v2';
GO

Artie looks up capture instances when the pipeline starts. Redeploy the pipeline after you create the new capture instance. Artie then reads the old capture instance up to the point where the new one starts, and continues from the new one.Keep the old capture instance until the pipeline has switched over. Then drop it:

EXEC sys.sp_cdc_disable_table
  @source_schema = N'dbo',
  @source_name = N'customers',
  @capture_instance = N'dbo_customers';
GO

Until you redeploy, a new column isn’t replicated.

Unique index override

By default, sys.sp_cdc_enable_table identifies rows by the table’s primary key. To use a different unique index, set @index_name.

USE [ANALYTICS];
CREATE UNIQUE INDEX cdc_index ON dbo.orders (id, prefix);

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

Capture job not running

SQL Server Agent’s capture job copies changes from the transaction log into the change tables. If the job stops, sys.fn_cdc_get_max_lsn() stops moving and Artie has no new changes to read. The pipeline stays running and reports no errors.No changes are lost while the job is stopped: SQL Server keeps them in the log. To check the job:

USE [ANALYTICS];
EXEC sys.sp_cdc_help_jobs;
SELECT * FROM sys.dm_cdc_log_scan_sessions ORDER BY start_time DESC;

Start the job with EXEC sys.sp_cdc_start_job @job_type = N'capture';.

Limitations

TRUNCATE TABLE isn’t allowed on CDC-tracked tables. Use DELETE.
Columns added after a capture instance was created aren’t replicated until you rotate the capture instance and redeploy. See Schema changes.
If the pipeline is down longer than the CDC retention period, the changes SQL Server cleaned up are lost. Backfill the affected tables.

Getting Started

Pipelines

Connectors

Monitoring

Artie Dashboard

Microsoft SQL Server Source: CDC Capture Instances

What you’ll need

Setup

Advanced

Limitations

​What you’ll need

​Setup

​Advanced

​Limitations

What you’ll need

Setup

Advanced

Limitations