SQL Server → Fabric Open Mirroring

Mirror a SQL Server table into Microsoft Fabric with DirectLog capture and Fabric Open Mirroring, then verify an insert, an update and a delete in OneLake.

This walkthrough mirrors a SQL Server table into Microsoft Fabric. SQL Flow captures changes with DirectLog and writes them as Parquet files into the landing zone of an open mirrored database, which Fabric ingests into a Delta table in OneLake.

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 transaction log to one agent. The agent signs in as a Microsoft Entra service principal and writes the changes as Parquet files to the mirrored database's landing zone over HTTPS. Fabric then ingests the files into the mirrored table.

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 Fabric workspace where you can create a mirrored database and manage access.
• The right to register an application in Microsoft Entra ID, and a Fabric administrator who can change tenant settings.
• 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 onelake.dfs.fabric.microsoft.com and api.fabric.microsoft.com over HTTPS.
• 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. For what each setting does, see SQL Server DirectLog: Requirements.

2. Prepare Fabric

1. Create a service principal. In Microsoft Entra ID, register an application and add a client secret to it. Note the tenant ID, the application (client) ID and the secret's value.

2. Allow it to call Fabric. Ask a Fabric administrator to enable the tenant setting "Service principals can call Fabric public APIs", under Developer settings in the Admin portal, for the service principal.

3. Create an open mirrored database in your workspace. SQL Flow refuses any other kind of item, including a mirrored database that Fabric replicates from a source itself.

4. Give the service principal access. In the workspace's access settings, add the service principal with the Contributor role, or a higher one.

5. Copy the landing zone URL. Open the mirrored database's Home page and copy its landing zone URL. It has this form:

   https://onelake.dfs.fabric.microsoft.com/<workspace_id>/<mirrored_database_id>/Files/LandingZone

3. Install the agent and the log broker

1. In SQL Flow, open Agents, select Add agent, download the agent for the agent machine's platform, and 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.
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, check that the IP address of the agent machine is listed: the panel fills in 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.

See Agents and Log brokers for the details.

4. Create the pipeline

Open Pipelines and select New pipeline.

Source

1. Select the SQL Server tile, keep DirectLog under Capture mode, and check that Runs on names your agent.

2. 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.

3. 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.

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

Target

1. Under Data platform integration, select the Fabric Mirror 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
   Target	OneLake (default)
   Landing zone URL	The URL from step 2
   Microsoft Entra tenant ID	<tenant_id>
   Client ID	<client_id>
   Password source	Password
   Password	The service principal's client secret

4. Select Test connection. The test checks that the URL names an open mirrored database, that mirroring is running, and that the service principal can write to the landing zone. If mirroring isn't running yet, the red alert under the heading offers Start mirroring: select it, and the test runs again.

5. Select Next.

Tables

The wizard selects dbo.Customers. Select Next. The target is tested again, to check that no other pipeline or producer owns these tables' folders in the landing zone.

Prepare

The source card, marked table marking, lists dbo.Customers as not marked for capture. Select Mark now (1), then Mark tables in the dialog. A Fabric Mirror target has no tables to create ahead of a start, so the step shows nothing for it. Select Next.

Review

1. Check the generated Pipeline name, and change it if you like.
2. Select Show details and check that Capture method reads DirectLog and Selected tables reads 1 table.
3. Select Create and start.

5. Watch the initial load

The pipeline's page shows Loading, then Healthy. The Lag tile reads Lag · to landing zone: it measures lag until a file lands in the landing zone, not Fabric's own ingestion after that.

In the landing zone, the agent creates a Customers folder and writes the first Parquet file to it. When Fabric has ingested it, query the mirrored table from the mirrored database's SQL analytics endpoint:

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

The query returns the three rows from step 1.

6. 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. Within the publication window, 5 seconds by default, the agent writes the changes to the next file in the Customers folder.

3. When Fabric has ingested the file, run the query again. Customer 4 is new, customer 2 has the new email, and customer 3 is gone.

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

7. 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 agent disables the capture instance it created for dbo.Customers and releases its hold on the source log.

3. In Fabric, delete the mirrored database if you created it for this walkthrough. Deleting the pipeline leaves the files it wrote in the landing zone, and the mirrored table, in place.

4. Remove the service principal's workspace access, and delete its client secret or the application registration.

5. 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

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

Next steps

• Fabric Open Mirroring, for every option, the file layout and the limitations
• SQL database in Fabric, to write to a Fabric SQL database through its SQL endpoint instead
• Lag, spool and log retention
• SQL Server DirectLog