On-premises SQL Server → Azure SQL over a WAN with relay agents

Replicate on-premises SQL Server to Azure SQL with a capture agent on-premises and an apply agent in Azure, paired through a relay across the WAN.

This walkthrough replicates an on-premises SQL Server table to Azure SQL Database with two agents: a capture agent next to SQL Server, and an apply agent on a virtual machine in Azure, next to the database. The capture agent reads changes with DirectLog and ships them across the WAN to the apply agent, which writes Azure SQL over the local Azure network. This is distributed acceleration: the WAN round trip no longer limits how fast the target is written, and the target's credentials stay in Azure.

What you'll build

A demo database, sqlflow_demo, holds the table dbo.Customers on an on-premises SQL Server. A log broker on the SQL Server host serves the transaction log to the capture agent. The capture agent connects to the apply agent on TCP port 7420 over mutual TLS 1.3. The apply agent applies the changes to dbo.Customers in an Azure SQL database, signing in with the virtual machine's managed identity.

Arrows point from the side that opens the connection. Both agents 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.
• An on-premises 64-bit Windows or Linux machine for the capture agent. It reaches SQL Server on port 1433 and the SQL Server host on port 7003, and can open outbound TCP connections to the Azure virtual machine on port 7420.
• An Azure SQL database, <azure_database> on the logical server <server>, with a Microsoft Entra admin set on the logical server.
• A 64-bit Windows or Linux virtual machine in the database's region, for the apply agent, with its system-assigned managed identity turned on. The capture agent must reach it: at a public IP address, or at a private address over a VPN or ExpressRoute.
• Permission to add an inbound rule to the virtual machine's network security group.
• A SQL Flow user with the Account admin or User role. See Getting started.

1. Prepare SQL Server

Run these statements as a sysadmin. They create the demo table and apply the settings DirectLog checks for. Replace <backup_path> with a file path on the SQL Server host, such as D:\SQLBackup\sqlflow_demo.bak, and <capture_password> with a strong password. On SQL Server 2019 and earlier, grant VIEW SERVER STATE instead of VIEW SERVER PERFORMANCE STATE.

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
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
USE sqlflow_demo;
EXEC sys.sp_cdc_enable_db;
GO
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;
SELECT name, physical_name FROM sys.database_files WHERE type = 1;
GO

The last query returns the database's one log file. Note its folder for the log broker. See SQL Server DirectLog: Requirements for what each setting does.

2. Prepare Azure SQL

1. Connect to <azure_database> as the logical server's Microsoft Entra admin. Create a user for the virtual machine's managed identity, and grant the permissions the target uses. For a system-assigned identity, the user name is the virtual machine's name. Replace <vm_name>.

   CREATE USER [<vm_name>] FROM EXTERNAL PROVIDER;
   GRANT CREATE SCHEMA, CREATE TABLE, CREATE TYPE TO [<vm_name>];
   GRANT VIEW DEFINITION TO [<vm_name>];
   GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON SCHEMA::[dbo] TO [<vm_name>];

2. In the logical server's networking settings, allow the virtual machine: a firewall rule for its outbound IP address, or a virtual network rule for its subnet.

3. Let the virtual machine reach the database on port 1433. If the logical server uses the Redirect connection policy, which Azure applies by default to connections from inside Azure, also allow outbound ports 11000 to 11999.

See Azure SQL target for what each permission is for.

3. Install the capture agent and the log broker on-premises

1. In SQL Flow, open Agents, select Add agent, and download the agent for the capture agent machine's platform. Run the command from Copy command on that machine:

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

2. On Agents, select Approve agent on the new agent. This walkthrough calls it <capture_agent>.
3. Select Add broker, download the broker for the SQL Server host's platform, and move it to a permanent folder on that host. In Log folders, enter the folder of the .ldf file and the folder of <backup_path>. In Agent addresses, keep only the IP address of the capture agent machine: the panel fills in all your known agents, and only listed agents can connect. Run the command from Copy command there:

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

4. Under Brokers, compare the broker's Certificate SHA-256 with the fingerprint on the install's Created broker identity line, and select Approve.

4. Install the apply agent in Azure

1. On Agents, select Add agent again, download the agent for the virtual machine's platform, and run the command from Copy command on the virtual machine, as in step 3.
2. Approve the new agent. This walkthrough calls it <apply_agent>. Don't link it to another agent as a standby: a standby agent can't apply a relay.
3. Open the relay port. In the virtual machine's network security group, add an inbound rule that allows TCP port 7420 from the capture agent's outbound public IP address. If the virtual machine runs a host firewall, such as Windows Firewall, allow the port there too.

The apply agent listens on 7420 unless it already uses another relay port. The Pair the relay screen shows the exact rule under Details > Firewall.

5. Create the pipeline

Open Pipelines and select New pipeline.

Source

1. Select the SQL Server tile, and keep DirectLog under Capture mode. The wizard offers a relay for every source and capture mode; this walkthrough uses DirectLog.

2. Under Runs on, choose <capture_agent>. The agent you choose here captures the source.

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	<capture_password>
   Encryption	Require (default), or Prefer for a self-signed certificate. Prefer keeps the connection encrypted but doesn't check the certificate.

4. Check that the Log broker panel reads Log broker active on and the SQL Server host. If it doesn't find the broker, select Use an existing broker and choose it.

5. Select Test connection & list tables, then Next.

Target

1. Under Relational databases, select the Azure SQL tile.

2. Under Where this target is applied, set Apply this pipeline target on to <apply_agent> (over the relay). From now on, the target's host is resolved, and the connection test runs, on the apply agent.

3. Fill in the connection:

   Field	Value
   Host	<server>.database.windows.net
   Port	Blank, for 1433
   Database	<azure_database>
   Authentication	Microsoft Entra managed identity
   Client ID (user-assigned identity)	Blank, for the virtual machine's system-assigned identity

   An Azure SQL connection always encrypts and validates the server certificate, so the form has no Encryption field.

4. Keep Bookkeeping schema as sqlflow, Collapse row versions (faster) selected, and the Atomic transaction limits at their defaults.

5. Select Test connection. It runs on <apply_agent> and signs in with its managed identity.

6. Select Next. The Pair the relay screen opens.

Pair the relay

Pair & verify starts as soon as the screen opens: SQL Flow pairs the two agents, and <capture_agent> dials <apply_agent> on the Relay port, 7420.

1. By default the capture agent dials the virtual machine's registered host name and the addresses it reports, which are usually private Azure addresses. Unless your network routes to them, set the address first: select Change beside Relay host, enter the virtual machine's public IP address or DNS name, <vm_public_address>, and select Re-pair & verify.
2. Wait for the status line to read Verified: <capture_agent> connected. If it reads Failed at tcp, refused or timeout, check the inbound rule from step 4, then select Verify dial. See Setting up relay agents: Troubleshooting.
3. Select Next.

Tables

The wizard selects dbo.Customers. Select Next.

Prepare

1. The source card, marked table marking, lists dbo.Customers. Select Mark now (1), then Mark tables in the dialog.
2. The card marked target schema says the table must exist in the Azure SQL target. Select Show SQL, then Prepare target schema, and confirm with Prepare schema.

Select Next.

Review

1. Select Show details and check the relay rows: Capture agent is <capture_agent>, Apply agent is <apply_agent>, Relay port is 7420, Relay trust reads TLS 1.3, mutual, and Relay dial reads verified.
2. Select Create and start.

6. Watch the initial load

The pipeline's page shows Loading, then Healthy.

• The line under the headline metrics reads Relay, with a capture: chip and an apply: chip for the two halves.
• Open Details to see capture lag, apply lag, serialized payload:, and wire: with the compression ratio.

Check the target in <azure_database>:

SELECT CustomerId, Name, Email FROM dbo.Customers ORDER BY CustomerId;

The query returns the three rows from step 1.

7. Verify changes

1. Change the source table on-premises:

   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 Azure SQL query again. Customer 4 is new, customer 2 has the new email, and customer 3 is gone.

3. On the pipeline's Overview tab, the Changes card counts one insert, one update and one delete under Observed and Applied.

8. Optional: see the relay ride out an outage

1. Stop the apply agent's service on the virtual machine:

Stop-Service -Name 'SqlFlow.Agent'

2. Insert another row on SQL Server, for example customer 5. The capture agent keeps capturing into its spool: under Details, capture spool: grows and wire: drops to zero.
3. Start the service again with Start-Service -Name 'SqlFlow.Agent' or sudo systemctl start sqlflow-agent. The capture agent reconnects and resends what the apply agent hasn't stored yet. Customer 5 arrives in Azure SQL, and nothing is applied twice.

While the apply side is down, the target doesn't commit, so DirectLog keeps holding the source log. See Source log retention.

9. Clean up

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

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

3. In Azure, delete <azure_database> if you created it for this walkthrough. Otherwise drop the demo table and the user. The sqlflow bookkeeping schema and its objects stay until you drop them.

   DROP TABLE dbo.Customers;
   DROP USER [<vm_name>];

4. Remove the inbound rule for port 7420 from the network security group, and the virtual machine's firewall rule from the logical server.

5. Drop the on-premises 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

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

Next steps

• How relay works, for what happens when the link or an agent fails
• Monitoring relay, for every figure on a relayed pipeline's page
• Sizing and tuning, for link bandwidth and spool disk
• High availability: Agents, to protect both relay agents with standbys
• Azure SQL target