Initial load

Learn how SQL Flow copies existing rows before it streams changes, what it does to rows already in the target, and how to tune and watch the load.

When a pipeline starts for the first time, SQL Flow copies the existing rows of every selected table from the source to the target, and then streams the changes made after that point. This copy is the initial load. It runs once per table and target. A later pause and start never repeats it.

How it works

Capture starts before the copy, so no change is lost. Changes that commit on the source during the load are captured and held, and the agent applies them after every table is loaded.

SQL Flow doesn't create target tables during the load. Create them on the Prepare step, or with sqlflow pipeline provision, before the first start.

The progress card names the current step. Most steps pass quickly. These are the ones a load can stay in:

When streaming begins, the status changes to Healthy.

When the load runs

The agent records each table as loaded, per target, when its load completes. These records, not the contents of the target, decide what a start does:

• Nothing recorded: the load runs for every table.
• Every table recorded: no load. A start after a pause resumes from the saved position.
• Some tables recorded: only the others load. This is how a start finishes an interrupted load.
• Records erased: a reload, a stop or a delete erases them, and so does a change to the source host, port or database. The next start loads every table again.
• Table set edited: a table you add loads on the next start, and only it. A table you remove and add back loads again. See Changing tables.

A target you add to an existing pipeline is loaded on its own, while the existing targets keep streaming. See Multiple targets.

Rows already in the target

SQL Flow empties a target table that already holds rows, and copies the source table into it. A table that is empty or new loads as it is. The Review step predicts this for each table, and Create and start asks you to confirm it with Replace existing target rows?. The first start of a pipeline created paused doesn't ask.

SQL Flow never deletes rows it can't prove it wrote. A SQL Server, Azure SQL or Fabric SQL target won't empty a table that SQL Flow has no load record for: for example, a table loaded from a PostgreSQL, MySQL, Oracle or Sybase source, or one a SQL Server pipeline never loaded. The Review step says so before you create the pipeline, and the start stops with a message that the table holds rows SQL Flow didn't load. Empty the table on the target yourself, then start again, or, where the message says so, select Actions > Reload from source.

The progress card, and the Initial load column on the Tables tab, show what the load did with each table:

Parallel loading

SQL Flow loads large tables in parallel, copying several parts of a table at once. A table without a usable key column loads in one stream. If the load of a large table is interrupted, the next start continues it instead of starting the table over.

Set the number of load workers

The number of parts copied at once is the agent's load worker count. By default it is the number of processors on the agent machine, at most 8. Set SQLFLOW_LOAD_WORKERS on the agent to a whole number from 1 to 64 to change it. A higher value helps when the target is slow to commit. It also opens more connections to the source and the target, so check the connection limits of both. For a pipeline with a relay, the value on the apply agent sets its writers.

Set the variable in the agent service's environment, then restart the service. See Set an environment variable for the agent service. To go back to the default, remove the variable and restart the agent. A value outside 1 to 64 stops the agent from starting, with a message that names the variable.

Watch progress

While the load runs:

• The status is Loading.
• The pipeline's page shows an Initial load card under the headline metrics, with the percentage, <copied> / <total> records, a bar and the rate. Select the card to see a row per table.
• On the Pipelines page, the row shows Loading <percent>% with the rows copied and the tables done.
• The Tables tab shows Rows at load, the row count the load saw.

The card says estimated when the split between tables is a guess. Finished tables leave the list, and the card disappears when every table is loaded.

Loads that share resources

Loads of different pipelines can run at the same time on one agent, source database or target database. A notice under the load card says what a slow load shares, so a contended load doesn't read as a broken one:

• Queued behind <pipeline names> for <time>: the copy hasn't started, and the agent starts it when the resources it needs are free. The pipeline's status chip reads queued meanwhile.
• Loading alongside <n> pipelines, with each pipeline and what it shares with this one.
• Waited <time> in this agent's load queue before copying; not counted as load time. once the load has started.

Load again

• To reload everything, use Reload from source. See Lifecycle.
• To reload one table while the others keep streaming, use Resync on the Tables tab. See Changing tables.

Targets that load differently

• A Kafka target can publish the existing rows or skip them. The Initial snapshot setting has Snapshot existing rows, then stream (default) and Stream changes only. See Kafka.
• Tables with large values may load through a slower streamed path. See Large values and LOBs.
• With a relay, the capture agent sends the snapshot to the apply agent. See How relay works.

See also

• Lifecycle
• Changing tables
• Large values and LOBs
• Performance considerations