SQL Server

The sqlserver connector reads changes out of Microsoft SQL Server three different ways — from the CDC capture tables, from the transaction log over SQL, or by parsing the .ldf log file directly. Which one you pick is mostly a question of what the database administrator is willing to enable.

Overview

Data Sync replicates SQL Server tables in the usual two phases: a parallel, chunked snapshot of the tables you select, followed by continuous CDC that tails whatever change source you configured. Inserts, updates and deletes all arrive as discrete change events with a before-image and an after-image, and the log position that produced each one is carried through to the sink so the checkpoint means something.

The capture mechanism is a property of the connection, not of the sync — the cdc_mode field. All three modes produce the same event shape downstream, so switching between them does not change what your sink sees. What changes is the privilege you need, the SQL Server features you have to turn on, and how fast the connector can read.

Choosing a capture mode

Start with cdc. It is the default, it is the least privileged of the three, and it is the only one that does not require server-wide rights or host access. The trade is that somebody has to enable the CDC feature on the database and keep SQL Server Agent running, because the capture job is what populates the tables Data Sync reads.

The two tlog modes exist for databases where the CDC feature is unavailable — an edition that does not ship it, a managed instance that will not expose it, or a DBA who will not enable the capture jobs. Both read the transaction log itself, so nothing has to be enabled per table; both need the database out of SIMPLE recovery so that the log chain survives long enough to be read.

Of the two, tlog_ldf is the fastest, because it bypasses the query layer entirely and decodes log segments in parallel. It is also the most demanding to deploy: the Data Sync runner process needs filesystem read access to the log file, which in practice means co-locating the runner with the SQL Server instance. If the runner is not on that host, use tlog_fndb instead.

Before you connect

Preflight is a hard gate. When a sync starts, the connector checks the server configuration before it reads a single row, and the sync will not start until every check passes. The failure message names the specific problem, but it is much less annoying to satisfy the checks up front. Run the statements below against the database you are replicating.

If you are using tlog_fndb or tlog_ldf

The recovery model must not be SIMPLE. Under SIMPLE, SQL Server truncates the log at every checkpoint and the change history Data Sync needs is gone before it can be read.

-- Take the database out of SIMPLE recovery.
ALTER DATABASE [your_database] SET RECOVERY FULL;

-- Then take a full backup: the log chain does not start until you do.
BACKUP DATABASE [your_database] TO DISK = '/var/opt/mssql/backup/your_database.bak';

Both tlog modes also read server-scoped log metadata, which requires a server-level grant on the login Data Sync connects as.

GRANT VIEW SERVER STATE TO [pulse_login];

If you are using cdc

Enable the CDC feature on the database. This needs sysadmin or db_owner, and it creates the cdc schema plus the capture and cleanup jobs.

EXEC sys.sp_cdc_enable_db;

Then enable capture on each table you intend to replicate. CDC is opt-in per table; a table that is not enabled produces no change events, and preflight will tell you which ones are missing.

EXEC sys.sp_cdc_enable_table
@source_schema = 'dbo',
@source_name   = 'orders',
@role_name     = NULL;

Finally, SQL Server Agent must be running. The capture job is an Agent job — if Agent is stopped, the capture tables stop being populated and Data Sync has nothing to read even though everything looks configured. Preflight checks the Agent status explicitly and fails if it is not Running.

Required in every mode

The initial backfill reads under snapshot isolation so that it sees one consistent point in time without holding locks against your production workload. Snapshot isolation is off by default on SQL Server and has to be enabled at the database level.

ALTER DATABASE [your_database] SET ALLOW_SNAPSHOT_ISOLATION ON;

Version floor

The minimum supported version is SQL Server 2012. One thing to know if you are on an older release: the wizard's table listing uses STRING_AGG, which needs SQL Server 2017 or later. On 2012 through 2016 the connector replicates normally, but the console cannot enumerate tables for you in step 3 of the sync wizard.

Connection fields

Set once, on the connection, in step 2 of the add-a-connection wizard. Every sync that uses this connection inherits them — including cdc_mode.

cdc_mode sits with the main fields because it decides which preflight checks apply; max_reconnect_attempts and lob_fetch_timeout are under Advanced settings. Credentials come next, in step 3.

Sync fields

Set per sync, so two syncs against the same connection can poll at different rates or use different chunk sizes.

Splitting updates

By default an UPDATE at the source becomes one update event carrying a before-image and an after-image, and the sink applies it as a single keyed write. With split_updates enabled, the connector instead emits two events: a DELETE of the before-image followed by an INSERT of the after-image.

Turn this on when the sink cannot express a key-changing update. If a source update changes a primary-key column, a plain upsert leaves the old row behind — the sink writes the new key and never learns that the old one is gone. Splitting the update makes the removal explicit, so the sink deletes the old key and inserts the new one.

It costs you an extra event per update, and it means an update is no longer atomic at the sink: there is a window in which neither row is present. On a keyed, upsert-class sink with a stable primary key, leave it off.

Type mapping

Source types are mapped to Arrow types on the runner, and the sink then maps Arrow to its own DDL. This is the source half.

Other behaviour

In cdc mode, the connector reads only the columns that CDC itself captured, and only those that are not computed. A column added to the table after sys.sp_cdc_enable_table ran is not in the capture instance, so it is not in the change stream — re-enable capture on the table to pick it up. Computed columns are excluded because SQL Server does not store their values in the capture tables.

Large object values stored off-row are not present in the change record itself. When the connector encounters one, it issues a separate bounded read to fetch the value, governed by lob_fetch_timeout on the connection. If that read exceeds the timeout the event fails rather than silently arriving truncated; raise the timeout if you replicate very large VARBINARY(MAX) or NVARCHAR(MAX) values over a slow link.