SQL Server CDC

Overview

Materialize can ingest data from SQL Server databases using Change Data Capture (CDC). This allows you to replicate tables from SQL Server into Materialize in real-time, capturing all inserts, updates, and deletes.

Materialize supports SQL Server 2016 and later versions. SQL Server CDC must be enabled on the source database.

Prerequisites

Before connecting SQL Server to Materialize, you need to:

Enable Change Data Capture (CDC) on your SQL Server database
Enable SNAPSHOT isolation for consistent reads during initial synchronization
Create a dedicated user with appropriate permissions
Configure network access to allow Materialize to connect

Enable CDC on SQL Server

Enable CDC on the database

Connect to your SQL Server instance and enable CDC for the database:

-- Enable CDC for the database
USE YourDatabase;
GO

EXEC sys.sp_cdc_enable_db;
GO

Enable CDC on specific tables

Enable CDC for each table you want to replicate:

-- Enable CDC for a specific table
EXEC sys.sp_cdc_enable_table
    @source_schema = N'dbo',
    @source_name = N'your_table',
    @role_name = NULL,
    @supports_net_changes = 1;
GO

Enable SNAPSHOT isolation

Enable SNAPSHOT isolation for consistent reads:

ALTER DATABASE YourDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON;
GO

ALTER DATABASE YourDatabase
SET READ_COMMITTED_SNAPSHOT ON;
GO

Create a Replication User

Create a dedicated SQL Server user for Materialize:

-- Create login
CREATE LOGIN materialize_user WITH PASSWORD = 'strong_password_here';
GO

-- Create user in the database
USE YourDatabase;
GO

CREATE USER materialize_user FOR LOGIN materialize_user;
GO

-- Grant necessary permissions
GRANT SELECT ON SCHEMA::dbo TO materialize_user;
GRANT SELECT ON SCHEMA::cdc TO materialize_user;
GRANT VIEW DATABASE STATE TO materialize_user;
GO

Network Security

AWS PrivateLink
SSH Tunnel
IP Allowlist

For SQL Server on AWS RDS:

Create a VPC endpoint service for your RDS instance
Configure Materialize connection with the PrivateLink endpoint
Update RDS security groups to allow traffic from Materialize

See Network Security Configuration for detailed setup.

Use an SSH tunnel for secure connections:

CREATE CONNECTION ssh_tunnel TO SSH TUNNEL (
  HOST 'bastion.example.com',
  USER 'materialize',
  PORT 22
);

CREATE CONNECTION sql_server_connection TO SQL SERVER (
  HOST 'sqlserver.internal',
  PORT 1433,
  DATABASE 'YourDatabase',
  USER 'materialize_user',
  PASSWORD SECRET sql_server_password,
  SSH TUNNEL ssh_tunnel
);

Creating a Connection

Create a connection to your SQL Server database:

-- Store the password as a secret
CREATE SECRET sql_server_password AS '<your_password>';

-- Create the connection
CREATE CONNECTION sql_server_connection TO SQL SERVER (
  HOST 'sqlserver.example.com',
  PORT 1433,
  DATABASE 'YourDatabase',
  USER 'materialize_user',
  PASSWORD SECRET sql_server_password
);

Connection Options

HOST

string

required

The SQL Server hostname or IP address

PORT

integer

default:"1433"

The SQL Server port number

DATABASE

string

required

The database name to connect to

USER

string

required

The username for authentication

PASSWORD

secret

required

The password, stored as a secret

SSH TUNNEL

connection

Optional SSH tunnel connection for secure access

Creating a Source

Replicate All Tables

Ingest all tables with CDC enabled in your SQL Server database:

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sql_server_connection
  FOR ALL TABLES;

Materialize will automatically create a subsource for each CDC-enabled table.

Replicate Specific Tables

Replicate only specific tables:

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sql_server_connection
  FOR TABLES (dbo.orders, dbo.customers);

Handle Schema Collisions

If you have tables with the same name in different schemas, use aliases:

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sql_server_connection
  FOR TABLES (
    schema1.products AS s1_products,
    schema2.products AS s2_products
  );

Viewing Subsources

After creating a source, view the created subsources:

SHOW SOURCES;

         name         |   type     |  cluster
----------------------+------------+-----------
 mz_source            | sql-server | 
 mz_source_progress   | progress   |
 orders               | subsource  |
 customers            | subsource  |

Each subsource represents a replicated table and can be queried like a regular table:

SELECT * FROM orders LIMIT 10;

Monitoring Ingestion Progress

Monitor replication progress using the progress subsource:

SELECT lsn FROM mz_source_progress;

The lsn (Log Sequence Number) indicates how far replication has progressed. This value should increase as new changes are captured.

Check Replication Lag

Query the system catalog to monitor replication health:

SELECT
  s.name,
  ss.snapshot_committed,
  ss.offset_known,
  ss.offset_committed
FROM mz_sources s
JOIN mz_internal.mz_source_statistics ss ON s.id = ss.id
WHERE s.name = 'mz_source';

Schema Changes

Compatible Changes

Materialize automatically handles these schema changes:

Adding new columns (nullable or with defaults)
Adding new tables with CDC enabled
Changing column data types (compatible changes)

Incompatible Changes

These changes require recreating the source:

Dropping columns
Renaming columns
Changing column types (incompatible changes)
Dropping tables

Materialize will error if it encounters an incompatible schema change. You’ll need to drop and recreate the source.

Troubleshooting

CDC Not Enabled

Error: Change Data Capture is not enabled on database 'YourDatabase' Solution: Enable CDC on the database and tables:

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

Permission Denied

Error: The user does not have permission to read CDC tables Solution: Grant the necessary permissions:

GRANT SELECT ON SCHEMA::cdc TO materialize_user;

Connection Timeout

Error: Connection timeout while connecting to SQL Server Solution:

Verify network connectivity
Check firewall rules
Ensure SQL Server is accepting TCP/IP connections

High Replication Lag

If replication is lagging behind:

Check SQL Server CDC cleanup: Ensure CDC cleanup isn’t too aggressive
Verify cluster size: Consider scaling up your Materialize cluster
Monitor SQL Server load: High load can slow CDC processing

-- Check CDC cleanup retention (SQL Server)
SELECT * FROM sys.sp_cdc_help_jobs;

-- Increase retention if needed
EXEC sys.sp_cdc_change_job
  @job_type = 'cleanup',
  @retention = 10080; -- 7 days in minutes

Best Practices

Use Dedicated Cluster

Create a dedicated cluster for SQL Server ingestion to isolate resource usage

Monitor CDC Cleanup

Configure CDC cleanup retention to balance storage and recovery needs

Enable for Key Tables First

Start with critical tables, then expand to additional tables

Test Schema Changes

Test schema change handling in a non-production environment

CREATE SOURCE: SQL Server

SQL syntax reference

Network Security

Configure secure connections

Monitoring Sources

Monitor source health and performance

SQL Server CDC Documentation

Microsoft’s CDC documentation

Get Started

Core Concepts

Ingesting Data

Transform Data

Serve Results

Operations

Integrations

Overview

Prerequisites

Enable CDC on SQL Server

Create a Replication User

Network Security

Creating a Connection

Connection Options

Creating a Source

Replicate All Tables

Replicate Specific Tables

Handle Schema Collisions

Viewing Subsources

Monitoring Ingestion Progress

Check Replication Lag

Schema Changes

Compatible Changes

Incompatible Changes

Troubleshooting

CDC Not Enabled

Permission Denied

Connection Timeout

High Replication Lag

Best Practices

Use Dedicated Cluster

Monitor CDC Cleanup

Enable for Key Tables First

Test Schema Changes

CREATE SOURCE: SQL Server

Network Security

Monitoring Sources

SQL Server CDC Documentation

Build docs developers (and LLMs) love

Get Started

Core Concepts

Ingesting Data

Transform Data

Serve Results

Operations

Integrations

​Overview

​Prerequisites

​Enable CDC on SQL Server

​Create a Replication User

​Network Security

​Creating a Connection

​Connection Options

​Creating a Source

​Replicate All Tables

​Replicate Specific Tables

​Handle Schema Collisions

​Viewing Subsources

​Monitoring Ingestion Progress

​Check Replication Lag

​Schema Changes

​Compatible Changes

​Incompatible Changes

​Troubleshooting

​CDC Not Enabled

​Permission Denied

​Connection Timeout

​High Replication Lag

​Best Practices

Use Dedicated Cluster

Monitor CDC Cleanup

Enable for Key Tables First

Test Schema Changes

​Related Resources

CREATE SOURCE: SQL Server

Network Security

Monitoring Sources

SQL Server CDC Documentation

Build docs developers (and LLMs) love

Overview

Prerequisites

Enable CDC on SQL Server

Create a Replication User

Network Security

Creating a Connection

Connection Options

Creating a Source

Replicate All Tables

Replicate Specific Tables

Handle Schema Collisions

Viewing Subsources

Monitoring Ingestion Progress

Check Replication Lag

Schema Changes

Compatible Changes

Incompatible Changes

Troubleshooting

CDC Not Enabled

Permission Denied

Connection Timeout

High Replication Lag

Best Practices

Related Resources