Overview
Architecture
Learn how the control plane, agents and log brokers divide the work, how they trust each other, how to lay them out, and how each one recovers from failure.
SQL Flow divides replication between three components: a control plane that holds your configuration and decides what runs where, agents that move the data, and log brokers that serve transaction-log files on database hosts. This page explains what each component does, how the components establish trust, how to lay them out, and what happens when one of them fails.
For a short tour of the components and the path a change takes, read How SQL Flow works first.
Design principles
- The control plane orchestrates and never touches row data. It stores definitions and sends commands. It never connects to a source or target database, so even the wizard's connection tests run on an agent.
- Your data stays in your network. Agents and log brokers run on your machines. In a hybrid deployment, the hosted control plane receives configuration and operational metadata: status, metrics, logs, alerts, schema events, the keys of rows that a compare finds different, and the redacted diagnostics bundles you request. It receives a database password only if you enter one, and stores it encrypted. See Security considerations.
- Every control connection is outbound. Agents and brokers connect to the control plane over HTTPS, and the control plane never connects to them. On agent and broker hosts, the only SQL Flow listeners are a broker's data port and a relay apply agent's port, both inside your network.
- Privilege lives in the log broker. Reading a live transaction log needs operating-system rights, so a small, auditable log broker holds them, and the agent runs as an ordinary service account. See Log brokers.
- Capture once, apply many times. The agent decodes each change once into its durable, encrypted spool. Every target reads the spool at its own pace, so a slow or paused target holds back neither capture nor the other targets.
Control plane
The control plane (SqlFlow.App) is a web application backed by a PostgreSQL database. You reach it over HTTPS, through the web UI or the sqlflow CLI (see CLI overview and authentication). It runs hosted for you or on your own servers; see Deployment models.
Responsibilities
The control plane stores your pipelines and connections, each inside one account, and tracks every agent and broker. It assigns each pipeline to an agent, finds the log broker for a source's host, and pairs relay agents. It sends the wizard's connection tests and source checks to an agent, collects the alerts, logs and metrics that agents report, and sends alerts to your notification channels.
Agents start every exchange: an approved agent keeps a request open to the control plane, so a command such as start, pause or reload reaches it within one network round trip. A command that fails raises the alert Agent command '<command>' failed, with the agent's error.
State
The control plane's PostgreSQL database holds pipeline definitions, connections, accounts, registrations, queued commands, the audit log, and the logs, alerts and metrics that agents report. Stored passwords and broker keys are encrypted with its Data Protection key ring. The recent history behind the throughput and lag charts isn't kept across a restart.
When you run the control plane yourself, back up the database, and back up the Data Protection certificate and its password separately. See Air-gapped deployment.
Several instances
In an air-gapped deployment you can run two or more control-plane instances against one PostgreSQL database. One instance leads and serves every request, the others forward requests to it, and one of them takes over when the leader stops. Agents and brokers retry while no instance leads. All instances serving at once (active-active) isn't available in this release.
In a hybrid deployment, the hosted control plane's availability is operated for you. See High availability: Control plane.
Agents
An agent (SqlFlow.Agent) is a service on a 64-bit Windows or Linux machine that runs pipelines. Everything that touches your data happens in an agent.
What an agent runs
Each pipeline has one capture role, which reads the source, writes the changes to the spool and runs the initial load's reads, and one apply role per target, which reads the spool and writes the target.
By default, one agent holds every role of a pipeline. With relay agents, a capture agent ships its spool to an apply agent near the target, which holds the apply roles. An agent can run many pipelines, and it also runs the wizard's tests and its pipelines' compares and repairs. The wizard offers only agents that can capture the source you chose. See Pipeline wizard: Agent.
Identity and credentials
The agent runs as NT SERVICE\SqlFlow.Agent on Windows by default, or as sqlflow-agent on Linux, and needs administrator rights only to install and uninstall. Database sign-in methods that use the host's identity, such as Windows (agent service account), sign in as this account.
The agent's identity is stored in its data directory. Running install again keeps it, so an upgraded agent needs no new approval. --reset discards it and registers a new agent.
The data directory and spool
The data directory holds everything that makes an agent the same agent after a restart: its identity, its encryption keys, the spool and its resume checkpoints. Keep it across restarts and upgrades.
The spool is the durable store between capture and apply, encrypted with AES-256-GCM under the agent's own key. When a target is slow or unreachable, the spool grows and capture continues, so size the volume for the longest outage you want to ride out. See Lag, spool and log retention.
The spool is a buffer, not the record of truth. Every target records the position it committed, and a restarted or replacement agent resumes from there. Losing a spool costs a second read of the source, not data, as long as the source still holds that part of its log.
How a pipeline is assigned and started
In the wizard, Runs on names the agent that captures the pipeline. That agent also applies every target, unless you choose an apply agent over a relay. To move a paused pipeline to another agent, use Actions > Assign agent… or sqlflow pipeline assign.
At start, the agent resolves any secret references on its own host and checks that the pipeline fits in its memory. It then runs the initial load and streams changes.
Connections start at the agent
Every connection that carries data starts at an agent:
- to the source and target databases or services;
- to a log broker, on TCP port 7003;
- from a capture agent to its apply agent, on TCP port 7420 by default.
Host names in a connection are resolved on the agent that opens it. localhost means the agent's own machine, and in a relay pipeline the target's host names are resolved by the apply agent. Because the wizard's tests run on the agent, a passing Test connection & list tables proves the agent's own route to the source. See Network and ports.
Secrets on the agent
A connection's password is either one you enter, which the control plane stores encrypted and delivers only to the agent that needs it, or a secret reference that the agent resolves on its own host, so the password never reaches the control plane:
env:<name>reads the agent service'sSQLFLOW_SECRET_<name>environment variable.file:<name>reads<data-dir>/secrets/<name>.
Each agent receives only the credentials of the roles it holds. In a relay pipeline, the capture agent never receives the target's credentials, and the apply agent never receives the source's. A secret reference must exist on every agent host that may run the role, including a standby agent's host. See Database credentials and secret references.
Resource limits
The agent sizes itself from the memory its host or container reports, and --memory-limit caps it on a shared machine. A pipeline the agent's memory can't hold is refused at start by name, and isn't restarted automatically. The spool volume must hold the backlog of the longest target or relay-link outage you plan for. See Performance considerations: Agent resources and Limit memory.
Standby pairs
You can link two agents as a standby pair. The standby runs no work of its own, and takes over the primary's pipelines about 30 seconds after the primary's last successful heartbeat. It resumes every pipeline from its targets' committed positions, whatever the source, capture mode or target. An agent that was cut off and comes back can't overwrite the work of the agent that replaced it: the target refuses its writes. See High availability: Agents.
When the control plane is unreachable
An agent keeps running the work it has already accepted for up to 24 hours after its last successful contact, and reconnects on its own. An agent in a standby pair stops its roles after about 25 seconds instead, so its partner can take them over safely. See High availability: Control plane.
Agents ignore instructions issued more than 5 minutes ahead of their own clock, so keep every agent, broker and control-plane host synchronized with NTP.
Log brokers
A log broker (SqlFlow.LogBroker) is a small service on the database host that serves transaction-log files, read-only, to agents. SQL Server DirectLog, SQL Server Reconstruction, Oracle DirectLog, and Sybase ASE DirectLog and Reconstruction read through one. Every other capture mode reads over the ordinary database connection and needs no broker.
Why a separate component
The log broker exists for security. All access to log files sits behind one small, single-purpose executable whose complete source code Spectral Core provides for your security audit on request. It serves only the folders you allow (--allow-paths), only to the agent addresses you allow (--allow-ips), read-only, and records every decision in its audit log, audit.jsonl. See Log brokers: Why a log broker.
Because only the broker holds the rights to read a live log, the agent service needs no administrator rights on any host, and the database login needs fewer privileges. Reconstruction, for example, reads the log through the broker without a sysadmin or db_owner login, while Polling, which reads the same log over the connection, needs one of them.
What a broker serves, and what it never does
A broker serves files from the folders given in --allow-paths: SQL Server transaction logs (.ldf) and log backups, Oracle online and archived redo logs, and Sybase ASE device files (the log device for DirectLog, and the log, data and LOB devices for Reconstruction).
A broker never writes to a file, lists a folder, runs a command or decodes the log; decoding happens on the agent. It reads from the file system only, so it can't read Oracle ASM storage or a Sybase ASE raw device.
How the control plane finds the broker for a source
You never enter a broker address in the wizard. The control plane looks up the approved broker for the source's host, inside your account, in this order:
- A broker you bound. A broker you chose with Use an existing broker wins over every other rule. The binding belongs to that host and agent, and clears if you change either.
- The capture agent's machine. If the host is
localhost, a loopback address,.,(local)or another name of the capture agent's machine, the control plane uses the broker registered under the agent's host. - The host you typed. Otherwise, the machine part of the host, without an instance name such as
\SQLEXPRESS, compared without regard to case.
If several brokers are registered on one host, bind one. The control plane refuses to create the pipeline when it finds no approved broker, or when a log file the source test found lies outside the broker's allowed folders, and names the cause. See Pipeline wizard: Broker.
Agent-to-broker security
Each connection from an agent to a broker passes four checks:
- Address. With
--allow-ips, the broker refuses any other address before the TLS handshake starts. - TLS. The agent accepts the broker's certificate only by its exact SHA-256 fingerprint, the same one you compare when you approve the broker.
- Pipeline key. The agent presents the key minted for its pipeline. Only the agent that reads the source receives it, never an apply agent.
- Scope. Every file the agent opens must lie inside the folders the key covers, and inside the broker's allowed folders.
See Security considerations: Encryption in transit for the TLS versions.
Service accounts
On Windows, install runs the broker as LocalSystem when an allowed folder holds SQL Server database files, because only a highly privileged account can read SQL Server's open log, and as the virtual account NT SERVICE\sqlflow-logbroker otherwise. On Linux, the broker runs as the sqlflow-logbroker user, with read access granted on the allowed folders. See Choose the service account.
Broker approval
A new broker is Pending and serves nothing until a user approves it. Before you approve, check its host name, that Serves lists the folders you allowed, and that Certificate SHA-256 under Details matches the fingerprint the install printed. Running install again keeps the broker's identity and approval, including when you change its allowed folders.
Standby brokers and recovery
When a broker stops, capture stops with it. The pipeline restarts automatically, while the service manager restarts the broker. A pipeline can also name up to two standby brokers that read the same log files: a second service on the same host, or a broker on another host that sees the same files. At every start, the agent tries the preferred broker first, then each standby in order. See High availability: Log brokers.
A broker on the agent's own host
A pipeline reads through a broker even when the agent runs on the database host. The agent stays an unprivileged service, the allowed folders and audit trail still apply, and a standby agent, or an agent you move the pipeline to later, reads the log through the same broker with no change to the pipeline.
Identity and trust
Agents and brokers join the control plane in two steps: a join code proves which account they belong to and which control plane to trust, and a user's approval activates them.
Join codes
- Add agent and Add broker mint a join code, and so does
sqlflow agent join-code. A code is valid for 15 minutes and for one registration by default.--usesmints one that admits from 1 to 1000 registrations, each still needing approval. - The code carries the fingerprint of the control plane's certificate authority, so the agent or broker trusts only that control plane.
- A code can only create a pending registration in the account that minted it.
- An enrolled agent or broker restarts with its stored identity and needs no new code.
The trust anchor and certificates
| Certificate | Who issues it | Who trusts it, and how |
|---|---|---|
| Control plane's certificate authority | The control plane, or your own authority in an air-gapped deployment | Agents and brokers. At install, the executable downloads the authority, keeps it only if it matches the join code's fingerprint, and stores it as ca.pem. It never consults the operating system's trust store. |
| Broker's TLS certificate | The broker, for itself | Agents, by its exact SHA-256 fingerprint, recorded at registration. |
| Relay certificate | The control plane, for each relay agent's role | The paired agent, by the exact certificate. Each side accepts only its peer. |
A firewall or proxy that decrypts HTTPS to inspect it replaces the control plane's certificate with its own, and agents and brokers then refuse the connection. Add an exception that doesn't decrypt traffic to the control plane's host name; see Proxies and TLS inspection. Replacing the control plane's authority means running install with a new join code on every agent and broker host.
Approval
A registration stays Pending until a user approves it, from inside the account it registered into, on the Agents page or with sqlflow agent approve. Holding a join code isn't enough: a code can create a registration but never approve it. Viewers can't approve.
What an approval grants:
- An agent can pull the configuration of the pipelines assigned to it, receive their commands, receive the credentials and broker keys of the roles it holds, and report status. It sees no other pipeline and no other account.
- A broker can serve files from its allowed folders to agents that present a key of a pipeline that reads through it. It never receives a database credential.
Revocation
- Revoke on an agent, online or not, rejects its credential at once, and the agent stops on its next contact. A revoked agent counts as stopped: its pipelines pause, or its standby agent takes them over. To use that installation again, run
install --resetwith a new join code and approve the new agent. - Revoke on a broker stops it serving at once, and pipelines reading through it stop. Its identity is never accepted again, unless you select Cancel revocation, which returns it to Pending. Delete forgets the registration.
- Remove on an offline or revoked agent deletes its record and unassigns its pipelines.
See Agents and Log brokers.
Topologies
The diagrams show where each component runs. Arrows point from the side that opens the connection. Every agent and broker also opens outbound HTTPS to the control plane, which the diagrams leave out.
Everything on one host
The database, its log broker and the agent share one machine. Use it to evaluate SQL Flow, or for a small server where no other machine has a route to the database. The agent's CPU, memory and spool disk compete with the database, so move the agent off the host for production load.
An agent near the databases
The broker runs on the database host, and the agent runs on its own machine in the same network as the source and the target. Use it for production in one site: the database host runs only the small broker, and the agent's resources are its own. One agent can capture from several database hosts, each with its own broker.
Capture and apply agents across a WAN
A capture agent near the source and an apply agent near the target share the pipeline, so the link's round-trip time no longer limits apply, and the target's credentials stay on the apply agent. Use it when the target is in another region, data center or cloud, for example on-premises SQL Server to Azure SQL. See Distributed acceleration.
Standby pairs for high availability
Agent A holds the pipeline's roles, and agent B, on another host, takes them over if A stops. The pipeline also names a standby broker that reads the same log files. In an air-gapped deployment, also run two or more control-plane instances. Use this layout for any pipeline that must keep replicating through the loss of a host. See High availability.
Fan-out to several targets
One pipeline can write several targets. The source is read once, and each target reads the spool at its own pace, so pausing one doesn't stop the others. A target can also be applied by another agent over a relay. See Multiple targets.
Failure domains
Each component fails and recovers on its own, and every pipeline resumes from the position its targets last committed, for every source, capture mode and target. High availability lists what happens while each component is down and how it recovers.
One case needs you: if an agent's data directory is lost and the agent has no standby, revoke the old agent, install and approve a new one, and assign the pipelines to it. Each resumes from its targets' committed positions.
