SQL Server Source (Change Tracking, Beta)

​

Choosing a capture method

​

What it captures

• Net changes only. A row updated several times between two polls arrives once, with its latest values. Intermediate states are not captured.
• No before-image. An update carries before = null. A delete carries the primary key columns only.
• The after-image is the row as the connector reads it, not as it was at commit time.

​

What is not captured

​

Column types

​

Compatible versions

​

Prerequisites

​

1. Enable Change Tracking on the database

ALTER DATABASE CURRENT
  SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON);

• Retention: at least 3 days, and always longer than the longest connector downtime you expect. A connector stopped longer than the retention cannot read what changed in between (see Retention gap).
• Keep AUTO_CLEANUP = ON, otherwise the internal change tables grow without bound.

​

2. Allow snapshot isolation

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

• the statement waits for every open transaction in the database to finish: run it in a quiet window;
• from then on, UPDATE and DELETE keep row versions in tempdb while a snapshot transaction may need them, and add 14 bytes to each updated row. Size tempdb accordingly.

​

3. Enable Change Tracking on each table

ALTER TABLE dbo.Orders ENABLE CHANGE_TRACKING;

​

Permissions

USE master;
CREATE LOGIN popsink_ct WITH PASSWORD = '<a strong password>';
-- Optional, for commit times. Use VIEW SERVER STATE on 2017 and 2019.
GRANT VIEW SERVER PERFORMANCE STATE TO popsink_ct;

USE [{{your_database}}];
CREATE USER popsink_ct FOR LOGIN popsink_ct;
GRANT SELECT, VIEW CHANGE TRACKING ON dbo.Orders TO popsink_ct;
-- Repeat for each captured table, or grant on a whole schema:
-- GRANT SELECT, VIEW CHANGE TRACKING ON SCHEMA::dbo TO popsink_ct;

CREATE USER popsink_ct WITH PASSWORD = '<a strong password>';
GRANT SELECT, VIEW CHANGE TRACKING ON dbo.Orders TO popsink_ct;
-- Optional, for commit times:
GRANT VIEW DATABASE STATE TO popsink_ct;

​

Checking the prerequisites

​

Configuration

​

Connecting through an SSH Tunnel

​

Initial load

​

Load on the source

• Every write to a captured table adds one row to that table’s internal change table, a cost Microsoft compares to maintaining one more index.
• Snapshot isolation adds the tempdb version store and 14 bytes per updated row, database-wide.
• Each poll runs one CHANGETABLE query per captured table, then reads the changed rows by primary key. Its cost follows the number of rows changed since the last poll, not the size of the table. A backlog is read in pages, so catching up after a pause does not slow down as it drains.

​

Retention gap

​

Reloading tables

curl -X POST "$POPSINK_API/connectors/{connector_id}/trigger-incremental-load" \
  -H "Authorization: Bearer $TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"data_collections": ["dbo.Orders", "dbo.Customers"]}'

​

Housekeeping

​

Check Change Tracking

-- Database: retention and cleanup
SELECT DB_NAME(database_id) AS database_name, is_auto_cleanup_on,
       retention_period, retention_period_units_desc
FROM sys.change_tracking_databases;

-- Tables tracked in the current database
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name
FROM sys.change_tracking_tables ct
JOIN sys.tables t ON t.object_id = ct.object_id;

-- Snapshot isolation (1 = allowed)
SELECT name, snapshot_isolation_state FROM sys.databases WHERE name = DB_NAME();

​

Raise the retention

ALTER DATABASE CURRENT
  SET CHANGE_TRACKING (CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

​

Disable Change Tracking

ALTER TABLE dbo.Orders DISABLE CHANGE_TRACKING;
ALTER DATABASE CURRENT SET CHANGE_TRACKING = OFF;