Why read the transaction log directly?

Understand what reading the transaction log gains over triggers, query polling and change tables, and what it costs on the database host.

Every committed change to a database is written to its transaction log first. SQL Flow captures changes from that log rather than from triggers or repeated queries. This page explains what that gains, and what the most direct form, DirectLog, asks of you in return.

Where the changes come from

A database makes a transaction durable by writing it to its log. The log therefore already holds every committed change, in commit order, with the values before and after. Every SQL Flow capture mode is based on the log. They differ in who reads it and how:

SQL Flow has no trigger-based or timestamp-polling mode.

What reading the log gains

No triggers and no polling queries

A trigger adds a second write to every transaction on a captured table, inside that transaction, and slows the application that commits it. Polling a table for rows whose timestamp changed misses deletes, misses every version between two polls, and needs a suitable column on every table. Reading the log needs neither: the application's transactions run exactly as before.

Lower latency

DirectLog reads the log as the database writes it, so changes reach the agent within milliseconds of the commit. Modes that ask the database add a wait: Polling and LogMiner read on an interval, with lag in seconds, and Native CDC's lag follows the capture job's scan interval.

Less work on the source

With DirectLog, the log broker reads file bytes and the agent decodes them on its own machine. The database runs no query to find changes and writes no change table. SQL Flow still reads table metadata, and some large values, over an ordinary SQL connection. By comparison:

• Native CDC makes SQL Server write every change a second time, into a change table, and run a cleanup job.
• Polling makes SQL Server parse its log with sys.fn_dblog for every read.
• LogMiner runs the mining inside Oracle, on its CPU, memory and temporary space.

Every change, in order

The log holds every committed version of every row, grouped by transaction, in commit order. SQL Flow applies the changes in commit order and keeps each source transaction whole. A target that must see every version receives each one: a Kafka topic, for example, or a table whose triggers audit every change, with Collapse row versions (faster) unticked. Rolled-back work never reaches a target.

SQL Server and Sybase ASE Reconstruction are the exceptions: they can carry a row's current value instead of an earlier version, so the target converges to the source.

What it costs

A log broker on the database host

DirectLog and Reconstruction read the log files through a log broker, a small, auditable service that you install on the database host and approve. It exists for security, so the agent itself never needs access to the host. See Log brokers: Why a log broker. The broker brings these requirements:

• You need administrator rights on the host to install it, and the host must run Windows Server 2016 or later, or Linux with glibc 2.28 or later.
• Managed database services, such as Azure SQL Database, Azure SQL Managed Instance and Amazon RDS, can't run it. Use Native CDC there, or Polling on a Managed Instance.
• Capture depends on the broker. While it is down, the pipeline restarts automatically, and a standby broker lets capture continue meanwhile.

Support for the physical format

SQL Flow decodes the database's own on-disk log format, so it supports the formats it has proven and refuses the rest by name rather than guess:

• SQL Server: a single log file, no TDE, and no ROW or PAGE compression on captured tables. SQL Server logs complete before and after images only for tables marked for capture, so DirectLog needs CDC enabled and the tables marked, although SQL Flow doesn't use the change tables. On SQL Server 2016 SP2 and 2017, Reconstruction, and DirectLog for tables with LOB columns or long rows, need a signed helper procedure that a sysadmin installs once.
• Oracle: redo on a file system rather than ASM, one redo thread, no TDE encryption on the captured tables, and supplemental logging with an (ALL) COLUMNS group on every table.
• Sybase ASE: one ASE build with a 16 KB page size, the log on a device of its own, device files rather than raw devices, no database encryption, and no compressed tables. ASE logs complete row images only for tables marked for replication, so DirectLog needs the tables marked with sp_setreptable; Reconstruction completes partially logged updates from the data pages instead.
• Versions: each source page lists the versions SQL Flow supports or is tested on.

When a database setting or table shape is outside what the decoder supports, the start is refused with the reason, and the mode pages list each one. When SQL Server DirectLog meets a log record it doesn't recognize, it stops right before that record, so no change is skipped, and you can send diagnostics to SQL Flow support. Native CDC and LogMiner let the database decode its own log, and Polling lets SQL Server read it, so they work in some places DirectLog can't, such as a SQL Server database encrypted with TDE, at the cost of lag and load.

The log must be kept until SQL Flow reads it

Any log-based capture needs the source to keep its log until SQL Flow has read it. SQL Server DirectLog holds the log until the target commits, releases the hold when you pause the pipeline, and releases it before the log fills. Both Sybase ASE modes hold the log while the pipeline runs and release it when you pause. Other modes depend on your log backups, archived logs, binlog retention or replication slot. See Source log retention.

When to choose another mode

See Choosing a capture mode for the full decision tree.

See also

• Choosing a capture mode
• Log brokers
• SQL Server DirectLog
• Oracle DirectLog
• Performance considerations