Large values and LOBs

How SQL Flow captures, loads and applies large values such as nvarchar(max), CLOB and bytea, per source and target, and the limits that apply.

Large values, or LOBs, are column values too big to travel inside an ordinary log record: varchar(max), nvarchar(max), varbinary(max), xml, text and image on SQL Server, CLOB, NCLOB and BLOB on Oracle, and large text, bytea and BLOB values elsewhere. SQL Flow never truncates a large value to fit. It either writes the whole value or stops with a message that names the value and the limit.

How large values are captured

Reads by key give the current value

When SQL Server DirectLog or Polling reads a value from the table, it gets the row as it is now, not as it was at the change. The target converges to the source, but may skip an intermediate version of a large value. SQL Server DirectLog reads by key on versions before 2022 (except for xml columns), and for compressed, partitioned, FILESTREAM, memory-optimized, sparse and encrypted columns.

Source requirements

• Oracle LogMiner reads the historical value, so it needs enough undo and the FLASHBACK privilege on the tables. If the history is gone, capture stops; it never substitutes the current value.
• Oracle DirectLog needs SecureFile LOBs with RETENTION MIN of at least 3,600 seconds and Flashback Database on, or RETENTION MAX with a MAXSIZE, and guaranteed undo retention. See Oracle DirectLog requirements.
• MySQL needs binlog_row_image=FULL and an empty binlog_row_value_options; the start refuses PARTIAL_JSON. One binlog event can be at most 1 GiB.
• Sybase ASE Reconstruction may run checkpoint in the source database so that a value's pages are on disk before it reads them. A value whose page chain is longer than 4,096 pages is refused with LOB chain exceeded 4096 pages; refusing to walk a runaway/corrupt chain.

SQL Server DirectLog: max text repl size

When a selected table has large-value columns, the server option max text repl size must be -1 (unlimited) or 2147483647. Once a table is marked for capture, SQL Server applies this option to every writer, and a longer write fails with error 7139. SQL Flow never changes a server option, and doesn't mark these tables until the option is met. The Prepare step shows the requirement. A DBA with ALTER SETTINGS (sysadmin or serveradmin) runs:

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

RECONFIGURE also activates any other sp_configure change that is pending on the instance.

The initial load

For a SQL Server source in DirectLog or Reconstruction mode, a table whose rows all hold at most 8 MiB of large values loads like any other table. A table with a larger row loads each row as an INSERT followed by updates that stream the large values, in one target transaction per row.

This streamed load runs into SQL Server, Azure SQL, SQL database in Fabric, Oracle and PostgreSQL targets. It reads the source without locks, so the source database needs READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION on. An interrupted streamed load starts those tables again from the beginning.

Allow streamed large-object loads despite triggers

Because each row arrives as an INSERT and then updates, a target trigger sees different statements than it would for one INSERT. When a mapped target table has an enabled trigger, the streamed load is refused until you tick Allow streamed large-object loads despite triggers on the wizard's Target step. Tick it only if your triggers accept that. The option key is lobInitialLoadAcceptsInsertThenStreamedUpdates, and it is off by default.

Size limits

Large values in flight are kept encrypted in the agent's spool directory on disk, not in memory, so plan the agent's disk space for them.

Applying large values

SQL Server, Azure SQL, SQL database in Fabric, Oracle and PostgreSQL targets stream a large value into the target rather than building it in memory. On SQL Server, a very large value is written in pieces within the row's own transaction, so a partly written value is never committed or visible to other readers. SQL Flow tracks partly written values in the control table __sqlflow_lob_apply_head in its bookkeeping schema.

With relay, the capture agent sends large values to the apply agent, including during the initial load. The apply agent never reads the source.

Large values in processing rules

A large-value column can be kept, filtered on or masked by in-flight processing rules, with limits: it can't be a key, it can't be listed twice in the output columns, and values over 1 MiB pass only through the operations that stream them. See Output and computed columns.

See also

• Initial load
• Data type mapping
• PostgreSQL target
• Performance considerations