SQL Server

Overview

The SQL Server connector reads from the change tables that SQL Server maintains when change data capture is on. Workflows react to committed changes without triggers on your tables, and every event shows up in the trace of the run it started.

Teams use it to post journal entries to NetSuite when a ledger row lands, to keep Salesforce accounts in step with an ERP and to ask for approval in Microsoft Teams before a large credit note goes out.

Supported versions

• SQL Server 2016 SP1 to 2022, Standard or Enterprise edition

• Azure SQL Database, Azure SQL Managed Instance and Amazon RDS for SQL Server

• Connections over TLS, with optional SSH tunnel or Azure Private Link

How CDC works

The SQL Server Agent capture job copies changes from the transaction log into change tables. Tessel reads those tables in log sequence order with cdc.fn_cdc_get_all_changes, checks each row against the schema and remembers the last LSN it delivered, so a restart picks up where it stopped.

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Invoices',
  @role_name     = N'tessel_cdc';

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Invoices',
  @role_name     = N'tessel_cdc';

EXEC sys.sp_cdc_enable_db;

EXEC sys.sp_cdc_enable_table
  @source_schema = N'dbo',
  @source_name   = N'Invoices',
  @role_name     = N'tessel_cdc';

Retention

The CDC cleanup job keeps three days of changes by default. If Tessel is paused for longer than that, it snapshots the affected tables again and marks the gap in the trace. Raise the retention with sys.sp_cdc_change_job if you pause connectors often.

Permissions needed

A db_owner turns on CDC once. After that, Tessel's login only needs read access and membership in the gating role named when each table was enabled. Write access is only required if a workflow writes back.

• db_datareader on the database

• Membership in the gating role, for example tessel_cdc

• INSERT, UPDATE and EXECUTE only where you write back