DirectLog

Capture every SQL Server change by reading the transaction log file through a log broker on the database host, with lag in milliseconds.

DirectLog reads the SQL Server transaction log file (.ldf) through a log broker on the database host and decodes every change itself. It is the wizard's default for SQL Server. Use it when you can install a log broker on the host and the database may have CDC enabled with the selected tables marked for capture.

How it works

The log broker on the SQL Server host serves the .ldf file's bytes to the agent over TLS, and the agent decodes the changes. Table metadata, and values stored off-row, come over the agent's ordinary SQL connection.

SQL Server logs full before and after images of an updated row only for a table marked for capture, so DirectLog requires every selected table to be marked with sys.sp_cdc_enable_table. SQL Flow doesn't use the change tables that CDC creates. If the database had no CDC jobs before marking, SQL Flow removes the capture and cleanup jobs that marking created.

How DirectLog holds the log

A marked table makes SQL Server keep its log records until they are marked as distributed, so log_reuse_wait_desc shows REPLICATION. SQL Flow moves that point forward only past changes the target has committed. Your log backups keep running as usual and truncate the log up to that point.

See Source log retention for the hold budget and its alerts.

Log backups SQL Flow can read

When a resumed pipeline needs log that SQL Server has already truncated, the agent reads your log backups through the log broker. This works only after the pipeline's initial load is complete, and only for backups that are:

• Written to disk, in a folder the broker serves (--allow-paths), one backup set per file and not striped over several files.
• Not encrypted, not taken to tape or a URL, and not written by a third-party backup tool.
• Uncompressed, or compressed with MS_XPRESS (the default algorithm) on SQL Server 2019 and later, or with ZSTD on SQL Server 2025. Other compressed backups, including every compressed backup from SQL Server 2016 SP2 and 2017, are refused with log_backup_compressed.
• Free of bulk-logged operations, not damaged, and not snapshot backups.

A log backup written to the NUL device can't be read, and a gap in the backup chain stops the resume.

Other log consumers

If the database already has a CDC capture job or a transactional replication publication, that consumer keeps the log hold and SQL Flow holds nothing. After Test connection & list tables, the Source step shows Secondary log consumer: with the reason. See Lag, spool and log retention.

Requirements

Setup

1. Put the database in FULL recovery and take a full backup. Replace <database_name> and <backup_path>.

   ALTER DATABASE [<database_name>] SET RECOVERY FULL;
   BACKUP DATABASE [<database_name>] TO DISK = N'<backup_path>';

2. Turn on row versioning and turn off delayed durability. Either snapshot setting is enough. Setting READ_COMMITTED_SNAPSHOT waits until it is the only connection in the database.

   ALTER DATABASE [<database_name>] SET ALLOW_SNAPSHOT_ISOLATION ON;
   -- or: ALTER DATABASE [<database_name>] SET READ_COMMITTED_SNAPSHOT ON;
   ALTER DATABASE [<database_name>] SET DELAYED_DURABILITY = DISABLED;

3. Confirm that the database has one log file, and note its folder for the broker.

   USE [<database_name>];
   SELECT name, physical_name FROM sys.database_files WHERE type = 1;

4. Create the capture login, add it to db_owner and grant the server permission. Replace <login_name> and <password>. Before SQL Server 2022, grant VIEW SERVER STATE instead.

   USE master;
   CREATE LOGIN [<login_name>] WITH PASSWORD = N'<password>';
   GRANT VIEW SERVER PERFORMANCE STATE TO [<login_name>];
   USE [<database_name>];
   CREATE USER [<login_name>] FOR LOGIN [<login_name>];
   ALTER ROLE db_owner ADD MEMBER [<login_name>];

5. As a sysadmin, enable CDC on the database.

   USE [<database_name>];
   EXEC sys.sp_cdc_enable_db;

6. If a selected table has a LOB column (varchar(max), nvarchar(max), varbinary(max), xml, text, ntext or image), have a DBA with ALTER SETTINGS set max text repl size. SQL Flow never changes a server option. RECONFIGURE also activates any other pending sp_configure change.

   EXEC sys.sp_configure N'max text repl size', -1; RECONFIGURE;

7. Install a log broker on the SQL Server host. In --allow-paths, include the folder that holds the .ldf file and the folders your log backups are written to, so a paused pipeline can resume from the backups. Approve the broker under Agents → Brokers.

8. In the pipeline wizard, choose SQL Server on the Source step and keep DirectLog under Capture mode. Enter the connection. The wizard finds the broker registered for the source host and shows the log file it serves.

9. Select Test connection & list tables. The test checks the recovery model, finds the log file and checks that the broker serves its folder. Set Transaction log file (.ldf) path only when the file can't be found automatically.

10. Choose the tables on the Tables step.

11. On the Prepare step, mark the tables that aren't marked yet:

    • Select Mark now to have the agent mark them with the pipeline login. This needs db_owner, and sysadmin when CDC isn't enabled on the database yet.
    • Or open the DBA SQL tab, select Copy DBA SQL, and have a DBA run the script in the source database. The script runs sys.sp_cdc_enable_table for each table and binds a table without a primary key to its unique index.

    Then select Check again.

Every start checks the privileges and settings again and names every unmet requirement at once.

Encrypted data

Transparent Data Encryption

DirectLog refuses a TDE database, because TDE encrypts the log on disk. The Prepare step shows:

The source database uses Transparent Data Encryption (TDE), so DirectLog cannot read its transaction log.

DirectLog also refuses while TDE decryption is in progress, and while active virtual log files written under TDE remain. On SQL Server 2019 and later, take log backups until no active virtual log file reports vlf_encryptor_thumbprint. On SQL Server 2016 SP2 and 2017, drop the leftover key with DROP DATABASE ENCRYPTION KEY and take a log backup. Turning TDE on while the pipeline runs stops it. For a TDE database, use Polling or Native CDC.

Always Encrypted

A selected table with an Always Encrypted column is refused by default:

always_encrypted_column: SQL Server capture refuses selected column <schema>.<table>.<column> because it is Always Encrypted; decryption and ciphertext pass-through are not supported.

DirectLog can instead copy the ciphertext unchanged, without decrypting it, when every condition holds:

• The source connection has the option alwaysEncrypted=ciphertext. Set it with the CLI, for example sqlflow connection edit <name> --opt alwaysEncrypted=ciphertext; the wizard has no field for it.
• Every target is a SQL Server or Azure SQL target with alwaysEncrypted=ciphertext, written by a local apply agent. A relay is refused.
• Each target table has a matching encrypted column for each encrypted source column, with the same plaintext type, facets and collation, and the same column encryption key. See SQL Server target: Always Encrypted targets.
• Replication keys and clustered index keys aren't encrypted.
• Every column of the table is a bounded scalar type: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, date, datetime, smalldatetime, datetime2, datetimeoffset, time, char, varchar, nchar, nvarchar, binary, varbinary or uniqueidentifier. MAX and computed columns are refused, and an encrypted column is declared between 1 and 8,000 bytes.

alwaysEncrypted=passthrough is refused with always_encrypted_passthrough_unavailable.

Connections

The agent always encrypts its SQL connection. With Encryption at Require (default), it validates the server certificate. For an on-premises server with a self-signed certificate, set Encryption to Prefer: the connection stays encrypted, but the certificate isn't checked. The broker connection always uses TLS.

Pros and cons

Pros

• Lag in milliseconds: changes are read from the log as SQL Server writes it.
• The broker reads the log bytes, so the server does no query work to find changes.
• Captures every change, including every intermediate version of an updated row.
• The pipeline login needs db_owner, not sysadmin.
• Captures tables without a primary key when a usable unique index serves as the row key.

Cons

• Needs a log broker on the SQL Server host, so it can't run on Azure SQL Database or Amazon RDS.
• Needs CDC enabled on the database and the tables marked, and max text repl size at -1 for tables with LOB columns.
• Holds the source log until the target commits. A stopped agent lets the log grow.
• Refuses TDE databases.

Limitations

Tables refused at the start. The start refuses these tables by name:

• Tables that aren't marked for capture. The refusal includes the sp_cdc_enable_table statement for each table.
• Memory-optimized tables, system-versioned temporal tables and tables with a clustered columnstore index. Capture them with Native CDC instead.
• Tables with no row key: no primary key and no usable unique index (enabled, not filtered, every key column NOT NULL and not computed, rowversion or GENERATED ALWAYS, every included column NOT NULL).
• Tables using ROW or PAGE compression.
• Tables whose column names differ only by case.
• Columns of xml typed by an XML schema collection. Untyped xml is supported.
• Tables with a column set (xml COLUMN_SET FOR ALL_SPARSE_COLUMNS), because SQL Server can't mark them for capture. Drop the column set (its sparse columns stay and are captured), or capture the table with Polling.
• Tables with sparse columns and a dropped column that no rebuild has removed yet: on SQL Server 2025 only when the table is partitioned, on earlier versions always. Run ALTER TABLE <table> REBUILD, then start again.
• Tables with a large-value column, or whose declared column widths let a row grow past 8,060 bytes, that also have a column DirectLog can't read back by key, such as a CLR user-defined type.
• Masked columns the login can't UNMASK, and nullable char or binary columns created with ANSI_PADDING OFF.
• Tables that are articles of a snapshot or transactional replication publication.
• When every target of the pipeline is Fabric Open Mirroring: tables whose large-value column, such as varchar(max), varbinary(max), xml, json, text or image, already holds a value larger than 1 MiB. A larger value written later stops the pipeline. With any other target, large values have no such limit.
• A table keyed by a unique index whose capture instance isn't bound to that index.

Data types. Apart from the refusals above and Always Encrypted columns, DirectLog captures every built-in type, including hierarchyid, geography, geometry, json and vector, and sparse columns. sql_variant reaches SQL Server and Azure SQL targets with each value's own base type, outside the key. A CLR user-defined type is captured as its bytes, which SQL Server, Azure SQL and PostgreSQL targets refuse. See Data type support by engine.

Changes that stop the pipeline. Adding a second log file to the database, or turning TDE on, stops the running capture by name. So does adding a column of typed xml or of a CLR user-defined type to a tracked table; see Stopped at a schema change. An added untyped xml column is captured.

TRUNCATE TABLE. SQL Server refuses to truncate a table marked for capture, so a tracked table can't be truncated while the pipeline runs. A TRUNCATE of an untracked table has no effect on the pipeline. DirectLog stops only when a TRUNCATE empties a tracked table whose marking was removed while the capture ran, or when it can't tell which table was emptied:

SQL Server DirectLog capture refuses at <lsn>: a TRUNCATE emptied tracked table <table> (allocation unit <id>, raw transaction id 0x<id>). DirectLog captures only CDC-marked tables and SQL Server refuses to truncate a marked table, so the table's marking was removed while the capture ran. ...

Restore the table's marking and reload the table. When the message says the capture can't place the emptied allocation unit, reload the pipeline.

One capture agent per database. All DirectLog pipelines of one source database share one capture agent and one standby, because the database has one truncation point:

Another DirectLog pipeline of this source database is captured by agent <agent>. A SQL Server database marked for DirectLog has one replication truncation point, which one agent manages from its own captures; ...

Troubleshooting

Tracked tables not marked for capture

marked capture for every tracked table (<n> tracked tables are not marked, ...)

Cause: a selected table isn't marked for capture.

Fix: on the Prepare step, select Mark now, or run the statements in the message or the DBA SQL script, then start again.

Source database not CDC-enabled

The source database is not CDC-enabled. Have a DBA run EXEC sys.sp_cdc_enable_db in the source database and then the generated operator marking script. ...

Cause: Mark now found CDC disabled on the database, and only a sysadmin can enable it.

Fix: have a DBA run EXEC sys.sp_cdc_enable_db; in the source database, then select Mark now again.

Connection lacks the right to mark tables

The connection lacks the right to mark tables for capture (sysadmin to enable the database, db_owner to enable a table). Have a DBA run the script instead.

Cause: the pipeline login can't run the marking.

Fix: open the DBA SQL tab, select Copy DBA SQL, and have a DBA run the script.

Finite max text repl size

max text repl size = -1 or 2147483647 (currently <value>; ...)

Cause: a marked table has a LOB column, and the server option max text repl size is finite. Once a table is marked, your application's LOB writes longer than the option fail with error 7139.

Fix: have a DBA run EXEC sys.sp_configure N'max text repl size', -1; RECONFIGURE;, then select Check again.

Table with a column set

a decodable storage kind for tracked table <table> (the table is with a sparse COLUMN_SET: SQL Server refuses to mark it for capture (sys.sp_cdc_enable_table raises error 22854: CDC does not support a COLUMN_SET), ...

Cause: the table has a column set, and SQL Server can't mark such a table for capture.

Fix: drop the column set; its sparse columns stay and are captured. Or remove the table from the pipeline, or capture it with Polling.

Column can't be read back by key

complete-row LOB fetch projection for table <table> (column '<column>' with SQL type '<type>' cannot be fetched: <reason>. ...

Cause: the table has a large-value column, or its declared column widths let a row grow past 8,060 bytes, so DirectLog reads changed rows back by key. The named column has a type that can't be read that way, such as a CLR user-defined type.

Fix: remove the table from the pipeline, or change the column to a type DirectLog supports.

Stopped at a schema change

SQL Server log-native schema history gap for '<table>': <reason>. No successor plan, row output, or checkpoint was published. A restart, or Pause → Start, reads this change from the log again and stops here, also after the change is undone at the source; Reload from source starts the pipeline over from a fresh copy.

Cause: a tracked table has a committed schema change that the capture can't carry exactly, for example an added column of typed xml (the reason ends with Refusing to flatten it to untyped xml) or of a CLR user-defined type. Every restart reads the same change again, even after you undo it on the source.

Fix: reload the pipeline with Reload from source.

Missing db_owner membership

db_owner on the source database (sysadmin also passes). ...

Cause: the login isn't a member of db_owner, which DirectLog needs to manage the log hold and table marking.

Fix: run ALTER ROLE db_owner ADD MEMBER [<user_name>]; in the source database.

Cannot prove the log is not TDE-encrypted

DirectLog cannot prove that the source log is not TDE-encrypted. ...

Cause: the login can't read the log's encryption state.

Fix: grant VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions.

No full backup after switching to FULL recovery

log-backup chain established (source reports FULL recovery but no base full backup has been taken, ...)

Cause: the database was switched to FULL recovery without a full backup, so SQL Server still truncates the log at every checkpoint.

Fix: take a full database backup.

Log path outside the broker's allowed folders

Discovered log path '<path>' is outside the log broker's allowed folders (<folders>) on host '<host>'. ...

Cause: the broker doesn't serve the folder that holds the .ldf file.

Fix: rerun SqlFlow.LogBroker install on the host with a new join code from Add broker and --allow-paths including that folder. The broker keeps its approval. See Log brokers.

Source log gone after pause

source_log_gone_after_pause: this pipeline was paused at <time> and its source-log hold was released then ...

Cause: SQL Server truncated the log after the pause, and no log backup that SQL Flow can read covers the gap.

Fix: reload the pipeline or its tables. If the message says a log backup folder isn't served by the broker, give the broker that folder and resume instead; no reload is needed.

Warning about a log backup chain gap on pause

log_backup_chain_gap: no log backup of the source database is recorded in msdb ...

Cause: the pause released the log hold, and SQL Flow found no log backup to continue from if SQL Server truncates the log before you resume. The related warnings log_backup_recovery_model_simple and log_backup_device_unsupported mean the database uses SIMPLE recovery, or the newest log backup went to the NUL device.

Fix: take regular log backups to disk, in a folder the broker serves, or resume the pipeline before the log is truncated. Otherwise the resume stops with source_log_gone_after_pause and the pipeline needs a reload.

Source log hold budget exceeded

source_log_hold_budget_exceeded: the source log reached <used> of its <budget>-byte budget ...

Cause: the log reached 85% of its capacity while SQL Flow's hold was what kept it, so SQL Flow released the hold.

Fix: reload the pipeline. Watch for the warning that the hold may reach its budget within 1, 6 or 24 hours, and resume, pause or delete the pipeline before then.

The log keeps growing after a pipeline was deleted

Cause: the delete couldn't release the hold, for example because the agent restarted before it ran. The marking stays, and log_reuse_wait_desc stays REPLICATION.

Fix: have a DBA run sys.sp_cdc_disable_table for each capture instance whose change table carries the extended property sqlflow_directlog_owned.

See also

• SQL Server capture modes
• Log brokers
• Reconstruction, which needs no CDC
• Lag, spool and log retention
• Source log retention