Azure SQL → SQL Server

Replicate an Azure SQL Database table to SQL Server with Native CDC capture, from enabling CDC to verifying an insert, an update and a delete.

This walkthrough replicates a table from Azure SQL Database to SQL Server with Native CDC, the only capture mode for an Azure SQL source. Nothing is installed on either database host: the agent is the only component you install.

What you'll build

The table dbo.Customers lives in an Azure SQL database with CDC enabled, which copies each change into a change table. One agent reads the change tables and applies the changes to dbo.Customers in a SQL Server database called sqlflow_demo.

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

Before you begin

• An Azure SQL database, on a service tier that supports CDC, that you can connect to as a member of db_owner. This walkthrough calls it <azure_database> on the logical server <server>.
• SQL Server 2016 SP2 or later as the target, in Standard, Enterprise, Express or Developer edition, on-premises or on a virtual machine, with sysadmin for the setup.
• A 64-bit Windows or Linux machine for the agent. It reaches <server>.database.windows.net on port 1433, and SQL Server on port 1433. When the logical server uses the Redirect connection policy, the agent also needs ports 11000 to 11999.
• A SQL Flow user with the Account admin or User role. See Getting started.

1. Prepare Azure SQL

1. Connect to <azure_database> as a member of db_owner, create the demo table, and add three rows:

   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');

   Native CDC keys changes by the primary key, so every captured table needs one.

2. Enable CDC on the database and create a capture instance for the table:

   EXEC sys.sp_cdc_enable_db;
   EXEC sys.sp_cdc_enable_table
       @source_schema = N'dbo',
       @source_name = N'Customers',
       @role_name = NULL,
       @supports_net_changes = 0;

3. Check that the table is tracked. The query returns 1:

   SELECT is_tracked_by_cdc FROM sys.tables WHERE name = N'Customers';

4. Create the capture user and grant it the read access SQL Flow needs. Replace <capture_password>.

   CREATE USER sqlflow_capture WITH PASSWORD = N'<capture_password>';
   GRANT SELECT ON SCHEMA::dbo TO sqlflow_capture;
   GRANT SELECT ON SCHEMA::cdc TO sqlflow_capture;
   GRANT SELECT ON sys.system_internals_partition_columns TO sqlflow_capture;

   Key repair and table resync need db_owner. To use them on this pipeline later, also run ALTER ROLE db_owner ADD MEMBER sqlflow_capture;.

5. In the logical server's firewall settings, allow the agent machine's outbound IP address.

2. Prepare SQL Server

Run these statements on the target SQL Server as a sysadmin. They create the target database and an apply login with the permissions SQL Flow needs to create and write the target table. Replace <apply_password>.

CREATE DATABASE sqlflow_demo;
GO
USE master;
CREATE LOGIN sqlflow_apply WITH PASSWORD = N'<apply_password>';
GO
USE sqlflow_demo;
CREATE USER sqlflow_apply FOR LOGIN sqlflow_apply;
GRANT CREATE SCHEMA, CREATE TABLE, CREATE TYPE TO sqlflow_apply;
GRANT VIEW DEFINITION TO sqlflow_apply;
GRANT SELECT, INSERT, UPDATE, DELETE, ALTER ON SCHEMA::[dbo] TO sqlflow_apply;
GO

See SQL Server target: Permissions for what each permission is for.

3. Install the agent

1. In SQL Flow, open Agents and select Add agent.
2. Choose the agent machine's platform, download the agent, and copy it 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 the new agent's name and Agent id, then select Approve agent.

4. Create the pipeline

Open Pipelines and select New pipeline.

Source

1. Select the Azure SQL tile. Capture mode shows Native CDC, with no other choice.

2. Under Runs on, choose your agent.

3. Fill in the connection:

   Field	Value
   Host	<server>.database.windows.net
   Port	Blank, for 1433
   Database	<azure_database>
   Authentication	SQL login
   Username	sqlflow_capture
   Password source	Password
   Password	<capture_password>

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

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

For an unattended agent that runs on an Azure resource, Microsoft Entra managed identity avoids a password. See Azure SQL Native CDC: Setup.

Target

1. Under Relational databases, select the SQL Server 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	<sqlserver_host>
   Port	Blank, for 1433
   Database	sqlflow_demo
   Authentication	SQL login
   Username	sqlflow_apply
   Password source	Password
   Password	<apply_password>
   Encryption	Require (default), or Prefer if SQL Server uses a self-signed certificate. Prefer keeps the connection encrypted but doesn't check the certificate.

4. Under SQL Flow's own objects in this database, keep Bookkeeping schema as sqlflow. You can't change it after you create the pipeline.

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

6. Select Test connection, then Next.

Tables

The wizard selects every table it can replicate. If <azure_database> holds other tables, select Deselect all, then select dbo.Customers: every selected table needs a capture instance. Select Next.

Prepare

The card marked target schema says the table must exist in the SQL Server target. Select Show SQL to read the CREATE TABLE statement, then Prepare target schema, and confirm with Prepare schema. 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 Native CDC.
3. Select Create and start.

5. Watch the initial load

The pipeline's page shows Loading, then Healthy. Check the target on SQL Server. Names are kept unchanged between SQL Server engines.

USE sqlflow_demo;
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 in <azure_database>:

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

   CustomerId  Name        Email
   ----------  ----------  ----------------------
   1           Ana Silva   ana@example.com
   2           Ben Okafor  ben.okafor@example.com
   4           Dana Lee    dana@example.com

   Changes arrive after Azure SQL's CDC next scans the log, so the lag follows its scan interval, not the moment of the commit.

3. 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. In Native CDC mode SQL Flow doesn't create or remove capture instances, so the one you created stays until you remove it.

3. In <azure_database>, remove the capture instance, the demo table and the user. Run sys.sp_cdc_disable_db only if you enabled CDC on the database for this walkthrough:

   EXEC sys.sp_cdc_disable_table
       @source_schema = N'dbo',
       @source_name = N'Customers',
       @capture_instance = N'all';
   DROP TABLE dbo.Customers;
   DROP USER sqlflow_capture;
   EXEC sys.sp_cdc_disable_db;

4. Remove the agent's firewall rule from the logical server.

5. On SQL Server, drop the demo database and login:

   USE master;
   DROP DATABASE sqlflow_demo;
   DROP LOGIN sqlflow_apply;

6. To remove the agent as well, see Remove an agent.

Next steps

• Azure SQL Native CDC, for serverless databases, retention and limitations
• SQL Server target, for the bookkeeping schema, options and Always Encrypted columns
• Automatic restart, for how a pipeline rides out an Azure SQL failover or auto-pause
• Compare