SQL Server → PostgreSQL

Replicate a SQL Server table to PostgreSQL with DirectLog capture, from preparing both databases to verifying an insert, an update and a delete.

This walkthrough builds a pipeline that replicates a SQL Server table to PostgreSQL with DirectLog, the default SQL Server capture mode.

What you'll build

A demo database, sqlflow_demo, holds the table dbo.Customers on SQL Server. A log broker on the SQL Server host serves the database's transaction log to one agent, which applies every change to public.customers in a PostgreSQL database of the same name.

Arrows point from the side that opens the connection. The agent and the broker also connect out to the control plane over HTTPS.

Before you begin

• SQL Server 2016 SP2 or later, in Standard, Enterprise or Developer edition, on Windows Server 2016 or later or on Linux with glibc 2.28 or later. You need sysadmin on the instance, and administrator rights on the host to install the log broker.
• A PostgreSQL 14 to 18 server, and a role on it that can create roles and databases. SQL Flow stops on other versions when it creates target tables for a SQL Server source. The server should offer TLS with a certificate the agent machine trusts, because the connection validates it by default.
• A 64-bit Windows or Linux machine for the agent. It reaches SQL Server on port 1433, the SQL Server host on port 7003, and PostgreSQL on port 5432. It can be the SQL Server host itself.
• A SQL Flow user with the Account admin or User role. See Getting started.
• A SQL client for SQL Server, such as sqlcmd, and psql for PostgreSQL.

1. Prepare SQL Server

Run these statements as a sysadmin.

1. Create the demo database and its table, with three rows:

   CREATE DATABASE sqlflow_demo;
   GO
   USE sqlflow_demo;
   GO
   CREATE TABLE dbo.Customers (
       CustomerId int           NOT NULL PRIMARY KEY,
       Name       nvarchar(100) NOT NULL,
       Email      nvarchar(200) NULL
   );
   INSERT INTO dbo.Customers (CustomerId, Name, Email) VALUES
       (1, N'Ana Silva',  N'ana@example.com'),
       (2, N'Ben Okafor', N'ben@example.com'),
       (3, N'Chen Wei',   N'chen@example.com');
   GO

2. Apply the database settings DirectLog checks for: FULL recovery with a full backup, so SQL Server keeps the log until it is backed up; snapshot isolation, so the initial load reads row versions instead of taking locks; and delayed durability off. Replace <backup_path> with a file path on the SQL Server host, such as D:\SQLBackup\sqlflow_demo.bak.

   ALTER DATABASE sqlflow_demo SET RECOVERY FULL;
   BACKUP DATABASE sqlflow_demo TO DISK = N'<backup_path>';
   ALTER DATABASE sqlflow_demo SET ALLOW_SNAPSHOT_ISOLATION ON;
   ALTER DATABASE sqlflow_demo SET DELAYED_DURABILITY = DISABLED;
   GO

3. Enable CDC on the database. SQL Server logs complete rows only for tables marked for capture, and marking needs CDC on the database. SQL Flow reads the log, not the change tables.

   USE sqlflow_demo;
   EXEC sys.sp_cdc_enable_db;
   GO

4. Create the capture login. db_owner lets it mark tables and advance its hold on the log, and the server permission lets it read the log's state. Replace <capture_password>. On SQL Server 2019 and earlier, grant VIEW SERVER STATE instead of VIEW SERVER PERFORMANCE STATE.

   USE master;
   CREATE LOGIN sqlflow_capture WITH PASSWORD = N'<capture_password>';
   GRANT VIEW SERVER PERFORMANCE STATE TO sqlflow_capture;
   GO
   USE sqlflow_demo;
   CREATE USER sqlflow_capture FOR LOGIN sqlflow_capture;
   ALTER ROLE db_owner ADD MEMBER sqlflow_capture;
   GO

5. Find the log file. The query must return exactly one row, because DirectLog reads a single log file. Note the folder in physical_name: the log broker serves it.

   USE sqlflow_demo;
   SELECT name, physical_name FROM sys.database_files WHERE type = 1;

2. Prepare PostgreSQL

Connect with psql and create the apply role and a database it owns. Owning the database lets the role create the target table and SQL Flow's sqlflow bookkeeping schema. Replace <apply_password>.

CREATE ROLE sqlflow_apply WITH LOGIN PASSWORD '<apply_password>';
CREATE DATABASE sqlflow_demo OWNER sqlflow_apply;

If pg_hba.conf restricts connections by address, allow the agent machine to reach sqlflow_demo as sqlflow_apply, then reload the configuration.

3. Install the agent

1. In SQL Flow, open Agents and select Add agent.
2. Choose the agent machine's platform, select Download SqlFlow.Agent.exe or Download SqlFlow.Agent, and copy the file to the agent machine.
3. Keep Install as a service, select Copy command, and run the command in the folder that holds the download.

.\SqlFlow.Agent.exe install --account <join_code>

4. On Agents, check that the new agent's name and Agent id match the machine and the install output, then select Approve agent. Its chip reads Online.

4. Install the log broker on the SQL Server host

1. On Agents, select Add broker, choose the SQL Server host's platform, and download the broker.
2. Move the executable to a permanent folder on the SQL Server host, such as C:\Program Files\SqlFlow\LogBroker or /opt/sqlflow/logbroker. The service runs it from there.
3. In Log folders, enter the folder that holds the .ldf file from step 1 and the folder of <backup_path>, separated by a comma. A paused DirectLog pipeline resumes from log backups in a folder the broker serves.
4. In Agent addresses, check that the IP address of the agent machine is listed. The panel fills in your known agents, and only listed agents can connect. Select Copy command, and run the command in the permanent folder:

.\SqlFlow.LogBroker.exe install --account <join_code> --allow-paths '<log_folder>,<backup_folder>' --allow-ips <agent_addresses>

5. Note the fingerprint on the output line that starts Created broker identity.
6. On Agents, under Brokers, open the new broker's Details. Check that Certificate SHA-256 matches the fingerprint and that Serves lists both folders, then select Approve.

On Windows, the broker runs as LocalSystem, because an allowed folder holds SQL Server database files. See Log brokers.

5. Create the pipeline

Open Pipelines and select New pipeline.

Source

1. Select the SQL Server tile, and keep DirectLog under Capture mode.

2. Check that Runs on names your agent.

3. Fill in the connection:

   Field	Value
   Host	<sqlserver_host>, the SQL Server host's machine name
   Port	Blank, for 1433
   Database	sqlflow_demo
   Authentication	SQL login
   Username	sqlflow_capture
   Password source	Password
   Password	<capture_password>
   Encryption	Require (default), which encrypts the connection and validates the server certificate. Choose Prefer if SQL Server uses a self-signed certificate: the connection stays encrypted, but the certificate isn't checked.

4. Under the form, the Log broker panel reads Log broker active on and the SQL Server host. If it reports no broker for your host, for example because you entered an IP address, select Use an existing broker and choose the broker you installed.

5. Select Test connection & list tables. The result reads Connected and finds dbo.Customers. Select Next.

Target

1. Under Relational databases, select the PostgreSQL tile.

2. Under Where this target is applied, keep This pipeline’s agent (local, no relay). With one agent online, the step states this as a fact.

3. Fill in the connection:

   Field	Value
   Host	<postgres_host>
   Port	Blank, for 5432
   Database	sqlflow_demo
   Username	sqlflow_apply
   Password source	Password
   Password	<apply_password>
   Encryption	Require (default), which uses TLS and validates the server certificate for <postgres_host>. On a test server without TLS, choose Prefer, which connects unencrypted when the server offers no TLS.

4. Keep Collapse row versions (faster) selected, and keep the Atomic transaction limits at their defaults.

5. Select Test connection, then Next.

If the test fails on the certificate under Require, see PostgreSQL target: Options for how the agent finds the certificate authority.

Tables

The wizard selects dbo.Customers, the only table in the database. Select Next.

Prepare

The step shows two cards.

1. The source card, marked table marking, lists dbo.Customers as not marked for capture. Select Mark now (1), read the Mark these tables for capture? dialog, and select Mark tables. Because the database had no CDC jobs, SQL Flow drops the capture and cleanup jobs that marking creates.
2. The card marked target schema says the table must exist in the PostgreSQL target. Select Show SQL to read the statements, then Prepare target schema, and confirm with Prepare schema.

When both cards are clear, select Next.

Review

1. Keep the generated Pipeline name, sqlflow_demo → PostgreSQL.
2. Select Show details and check that Capture method reads DirectLog, Selected tables reads 1 table, and Source encryption and Target encryption show the choices you made.
3. Select Create and start.

6. Watch the initial load

The pipeline's page opens with the status Loading and an Initial load card. When the copy is done, the status changes to Healthy.

Check the target in psql, connected to sqlflow_demo. The table and column names are recased for PostgreSQL: CustomerId becomes customer_id.

SELECT customer_id, name, email FROM public.customers ORDER BY customer_id;

The query returns the three rows from step 1.

7. Verify changes

1. Change the source table:

   USE sqlflow_demo;
   INSERT INTO dbo.Customers (CustomerId, Name, Email) VALUES (4, N'Dana Lee', N'dana@example.com');
   UPDATE dbo.Customers SET Email = N'ben.okafor@example.com' WHERE CustomerId = 2;
   DELETE FROM dbo.Customers WHERE CustomerId = 3;

2. Run the PostgreSQL query again. Customer 4 is new, customer 2 has the new email, and customer 3 is gone:

    customer_id |    name    |         email
   -------------+------------+------------------------
              1 | Ana Silva  | ana@example.com
              2 | Ben Okafor | ben.okafor@example.com
              4 | Dana Lee   | dana@example.com

3. On the pipeline's Overview tab, the Changes card counts one insert, one update and one delete under Observed and Applied. The Lag tile shows the end-to-end lag.

4. Optional: open the Compare tab and select Run compare. The run ends clean when the target matches the source. See Compare.

8. Clean up

1. On the pipeline's page, select Pause and wait for Paused.

2. Select Actions > Archive and confirm.

3. Select Actions > Delete and confirm. The agent disables the capture instance it created for dbo.Customers and releases its hold on the source log.

4. Drop the SQL Server demo database and login:

   USE sqlflow_demo;
   EXEC sys.sp_cdc_disable_db;
   GO
   USE master;
   DROP DATABASE sqlflow_demo;
   DROP LOGIN sqlflow_capture;
   GO

5. In psql, connect to another database, such as postgres, and drop the demo database and role:

   DROP DATABASE sqlflow_demo;
   DROP ROLE sqlflow_apply;

6. To remove the agent or the broker as well, see Remove an agent and Remove a broker.

Next steps

• SQL Server DirectLog, for every requirement and limitation
• Source log retention, for how DirectLog holds and releases the log
• In-flight processing, to filter rows and mask columns on the way
• On-premises SQL Server → Azure SQL over a WAN with relay agents