Reconstruction

Capture SQL Server changes from the transaction log through a log broker, with no CDC, no table marking and no database role.

Reconstruction reads the SQL Server transaction log through the log broker, like DirectLog, but it needs no CDC on the database, no marked tables and no sysadmin or db_owner login. Use it when the database may not have CDC enabled and the target may converge to the source's current state rather than replay every intermediate version of a row.

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. Inserts and deletes are exact.

SQL Server logs an update to an unmarked table only as the changed bytes. For a row the agent hasn't seen since capture started, it reads the row's current value by its key, so the update carries that current value and the target converges to the source.

How Reconstruction affects the log

Reconstruction holds no log and doesn't change log_reuse_wait_desc, so your log backups truncate the log as usual whether the pipeline is running, paused or stopped.

When the capture needs log that SQL Server has already truncated, it reads your log backups through the broker, once the pipeline's initial load is complete. See Log backups SQL Flow can read. When no readable backup covers the gap, the pipeline stops with F5 retention-gap and needs a reload. Include your log backup folders in the broker's --allow-paths, and keep the pipeline running between log backups.

Requirements

Setup

1. Put the database in FULL recovery and take a full backup, so SQL Server keeps the log until it is backed up. 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.

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

4. Create the capture login and grant its 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>];

5. Create the database user and grant the database and table permissions. Repeat the last statement for each selected table, replacing <schema_name> and <table_name>, or grant on the whole schema with ON SCHEMA::[<schema_name>].

   USE [<database_name>];
   CREATE USER [<login_name>] FOR LOGIN [<login_name>];
   GRANT VIEW DATABASE STATE TO [<login_name>];
   GRANT SELECT ON sys.system_internals_partitions TO [<login_name>];
   GRANT SELECT ON sys.system_internals_allocation_units TO [<login_name>];
   GRANT SELECT ON sys.system_internals_partition_columns TO [<login_name>];
   GRANT SELECT, VIEW DEFINITION ON OBJECT::[<schema_name>].[<table_name>] TO [<login_name>];

6. Install a log broker on the SQL Server host. Include the folder that holds the .ldf file, and the folders your log backups are written to, in --allow-paths. Approve the broker under Agents → Brokers.

7. In the pipeline wizard, choose SQL Server on the Source step, select Reconstruction under Capture mode, and enter the connection.

8. Select Test connection & list tables. The test checks the recovery model, the broker and the log file path.

9. On the Prepare step, review the readiness check. It lists every requirement that isn't met, each with the statement that fixes it. On SQL Server 2016 SP2 and 2017, it includes the install script of the page-header helper, which a sysadmin runs once in the source database. Select Check again after you fix them.

Encrypted data

• TDE. Refused, because TDE encrypts the log on disk. The readiness check reports the encryption state it found, for example:

  a transaction log without Transparent Data Encryption (TDE) for Reconstruction capture (the database is TDE-encrypted (sys.databases.is_encrypted = 1). Reconstruction reads the raw log, and TDE encrypts it on disk; use Polling or Native CDC for this database.)

  It also refuses while TDE decryption is in progress, and while active virtual log files written under TDE remain. Turning TDE on while the pipeline runs stops it with F13 encryption-enabled.

• Always Encrypted columns. Refused with always_encrypted_column. The ciphertext copy that DirectLog offers isn't available in Reconstruction.

• Connections. The agent always encrypts its SQL connection. With Encryption at Require (default), it validates the server certificate; Prefer skips that check, for an on-premises server with a self-signed certificate. The broker connection always uses TLS.

Pros and cons

Pros

• Needs no CDC, no table marking, no CDC jobs and no sysadmin or db_owner login.
• Runs no fn_dblog, sp_repldone or sp_cdc_* procedure on the source, and on SQL Server 2019 and later no DBCC either.
• The server's query work is limited to keyed reads of a row's current value for some updates.
• Holds no source log, so it never blocks log truncation.
• Captures tables without a primary key when a usable unique index serves as the row key.

Cons

• The target converges to the source: an update to a row not seen since capture started carries the row's current value, not every intermediate version.
• Needs a log broker on the SQL Server host.
• On SQL Server 2016 SP2 and 2017, needs a signed helper procedure that a sysadmin installs once.
• ALTER INDEX ... REORGANIZE, DBCC SHRINKFILE, partition changes and a partition TRUNCATE on a tracked table stop the pipeline until you reload it (see Limitations).

Limitations

Tables refused before the start. The readiness check refuses these tables by name:

• Memory-optimized tables.
• Tables with no row key: no primary key and no usable unique index (enabled, not filtered, every key and included column NOT NULL, no computed, rowversion or GENERATED ALWAYS key column).
• Tables with a clustered columnstore index, or with no clustered rowstore index that aren't heaps.
• Tables using ROW or PAGE compression.
• Tables with a column set. Capture them with Polling.
• Tables with xml typed by an XML schema collection. Untyped xml is supported.
• Tables with a text, ntext or image column whose values are stored in row (sp_tableoption 'text in row'), or whose other columns let a row grow past 8,060 bytes.
• Columns of a CLR user-defined type. hierarchyid, geography, geometry and sql_variant are supported.
• Sparse MAX and sparse xml columns, FILESTREAM, masked columns the login can't UNMASK, and nullable char or binary columns created with ANSI_PADDING OFF.

Data types. Apart from the refusals above and Always Encrypted columns, Reconstruction captures every built-in type, including json and vector. sql_variant reaches SQL Server and Azure SQL targets with each value's own base type, outside the key. See Data type support by engine.

TRUNCATE TABLE. A TRUNCATE TABLE of a whole tracked table is captured in order and applied to the target as a truncate. See Supported changes for what each target does with it.

Operations that stop the pipeline. These stop capture with a named halt; the label in parentheses appears in the message:

• TRUNCATE TABLE ... WITH (PARTITIONS (...)) on a tracked partitioned table, and a whole-table TRUNCATE while some of its partitions are empty (F9 truncate).
• An ALTER INDEX ... REBUILD or ALTER TABLE ... REBUILD that is resumable, runs online on a partitioned table, or changes the row layout, for example the first rebuild after a dropped column (F10 physical-remap). Other rebuilds don't stop capture.
• ALTER INDEX ... REORGANIZE or DBCC SHRINKFILE moving a tracked table's rows (F10 physical-remap).
• Partition SWITCH, SPLIT or MERGE on a tracked table (F10 physical-remap).
• A page logged as a bulk page image instead of rows (F7 bulk-page-image).
• UPDATETEXT or WRITETEXT on a legacy LOB column (F8 lob-change-unattributed).
• A change of recovery model or delayed durability while capturing (F12 posture-changed).

A halt is final: the automatic restart doesn't retry it. To continue, reload the pipeline with Reload from source. Plan index maintenance and partition changes on tracked tables for a time when a reload is acceptable.

Adding a column of typed xml or of a CLR user-defined type to a tracked table also stops the pipeline until you reload it; see Stopped at a schema change. An added untyped xml column is captured.

Troubleshooting

Page-header helper missing or not executable

EXECUTE on the page-header helper dbo.sqlflow_page_header (this server has no sys.dm_db_page_info): ...

Cause: the source runs SQL Server 2016 SP2 or 2017, and the helper procedure isn't installed in the source database or the login can't execute it. A related finding, a signed page-header helper dbo.sqlflow_page_header (it exists and this login may execute it, but it cannot run DBCC PAGE: ...), means the helper lost its signature, for example because someone altered it.

Fix: have a sysadmin run the install script that the finding includes, in the source database, then select Check again. Running the script again replaces the helper and its signature.

Source requirements not met

Reconstruction cannot capture this source as configured: <n> requirement(s) are not met.

Cause: the Prepare step found database settings, permissions or table shapes that don't meet the requirements.

Fix: open the findings. Each one names what is missing and the GRANT, ALTER DATABASE or scope change that fixes it. Apply them, then select Check again.

Table refused by Reconstruction

Reconstruction refuses table <table>: it <reason>; remove the table from the pipeline's scope, or capture it with Polling or Native CDC.

Cause: the table has a shape listed under Limitations. A restart doesn't fix it.

Fix: remove the table from the pipeline on the Tables step, or capture it in another pipeline that uses Polling or Native CDC.

Reconstruction halted on a table

Reconstruction halted [<label>] on <table> at LSN <lsn>: ...

Cause: the log holds a change Reconstruction can't carry exactly, such as an index REORGANIZE or a partition TRUNCATE on a tracked table. The label, for example F10 physical-remap, names the cause. See Limitations.

Fix: let the operation finish on the source, then reload the pipeline with Reload from source. A halt doesn't clear on a restart.

Reconstruction halted by a retention gap

Reconstruction halted [F5 retention-gap] ...

Cause: SQL Server truncated log the capture hadn't read, while the pipeline was stopped or paused, and no log backup SQL Flow can read covers the gap.

Fix: reload the pipeline. To avoid it, include your log backup folders in the broker's --allow-paths and take log backups SQL Flow can read.

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 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.

More than one transaction log file

single transaction-log file (the database currently has <n> log files; ...)

Cause: the database has more than one log file.

Fix: back up the log, empty the additional log file, then remove it with ALTER DATABASE [<database_name>] REMOVE FILE [<logical_name>];.

Delayed durability is not disabled

DELAYED_DURABILITY=DISABLED on the source database (currently '<value>'; ...)

Cause: delayed durability is ALLOWED or FORCED.

Fix: run ALTER DATABASE [<database_name>] SET DELAYED_DURABILITY = DISABLED;.

See also

• SQL Server capture modes
• DirectLog
• Log brokers
• Lag, spool and log retention