Ingest data from Azure SQL Database

This page shows you how to stream data from Azure SQL Database to Materialize using the SQL Server source.

NOTE: This guide covers Azure SQL Database, the single-database service. For Azure SQL Managed Instance, which runs a SQL Server Agent and exposes msdb, follow the self-hosted SQL Server guide instead.

💡 Tip: For help getting started with your own data, you can schedule a free guided trial.

Before you begin

Make sure Change Data Capture (CDC) is available on your Azure SQL Database. CDC has compute requirements and is not supported on lower service tiers. See Azure SQL documentation for details on service tiers and CDC configuration.
Ensure you have access to your database via the sqlcmd client, or your preferred SQL client, as a member of db_owner.

A. Configure Azure SQL Database

NOTE: To configure Azure SQL Database for data ingestion into Materialize, you must connect to the database you want to replicate as a member of db_owner, which can enable CDC and create/manage the user, role, and privileges.

1. Create a Materialize user in Azure SQL Database.

Azure SQL Database is a single-database service. Because it does not provide reusable server logins or access to the master database for granting server-scoped permissions, create a contained database user directly in the database you want to replicate.

Connect to the database you want to replicate as a member of db_owner, then create the user (replace <PASSWORD> with your own password):

CREATE USER materialize WITH PASSWORD = '<PASSWORD>';

Create a gating role for the capture instances and add the user to it:

CREATE ROLE materialize_role;
ALTER ROLE materialize_role ADD MEMBER materialize;

Grant the privileges Materialize needs:

-- SELECT on the replicated tables and the CDC change tables.
ALTER ROLE db_datareader ADD MEMBER materialize;

-- Read access to the transaction-state views used to track replication
-- progress, in place of the server-scoped VIEW SERVER STATE used for
-- self-hosted SQL Server.
GRANT VIEW DATABASE STATE TO materialize;

NOTE: Unlike self-hosted SQL Server, no explicit grants are issued on the sys.fn_cdc_* functions or the INFORMATION_SCHEMA views. They are executable and readable by default.

2. Enable Change-Data-Capture for the database.

Azure SQL Database drives CDC from an internal scheduler, so no SQL Server Agent is required. Enabling CDC requires the database to be on a service tier that supports it and that you are a member of db_owner.

Connect to the database you want to replicate and run:

EXEC sys.sp_cdc_enable_db;

For guidance on enabling Change Data Capture on Azure SQL Database, see the Azure documentation.

3. Enable `SNAPSHOT` transaction isolation.

Enable SNAPSHOT transaction isolation for the database. Because Azure SQL Database connections cannot switch databases, use the CURRENT keyword to target the connected database:

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;

4. Enable Change-Data-Capture for the tables.

Enable Change Data Capture for each table you wish to replicate, gated by the role you created above (replace <SCHEMA_NAME> and <TABLE_NAME> with your schema and table names):

EXEC sys.sp_cdc_enable_table
  @source_schema = '<SCHEMA_NAME>',
  @source_name = '<TABLE_NAME>',
  @role_name = 'materialize_role',
  @supports_net_changes = 0;

B. (Optional) Configure network security

NOTE: If you are prototyping and your Azure SQL Database is publicly accessible, you can skip this step. For production scenarios, we recommend configuring one of the network security options below.

There are various ways to configure your database’s network to allow Materialize to connect:

Allow Materialize IPs: If your database is publicly accessible, you can configure your database’s firewall to allow connections from a set of static Materialize IP addresses.
Use an SSH tunnel: If your database is running in a private network, you can use an SSH tunnel to connect Materialize to the database.

Select the option that works best for you.

In the SQL Shell, or your preferred SQL client connected to Materialize, find the static egress IP addresses for the Materialize region you are running in:
```
SELECT * FROM mz_egress_ips;
```
Update your Azure SQL Database firewall rules to allow traffic from each IP address from the previous step.

This assumes your Azure SQL Database is already reachable over a private IP in a virtual network via an Azure Private Endpoint, with the privatelink.database.windows.net DNS zone integrated so <server>.database.windows.net resolves to the private IP.

To create the SSH tunnel, you launch an instance to serve as an SSH bastion host in that network and configure the bastion host to allow traffic from Materialize. The bastion forwards traffic to the database’s private endpoint.

Launch a Linux VM with a static public IP address to serve as your SSH bastion host.
- Make sure the VM is publicly accessible and in the same virtual network as the private endpoint (or a peered network).
- Add a key pair and note the username. You’ll use this username when connecting Materialize to your bastion host.
- Make sure the VM has a static public IP address. You’ll use this IP address when connecting Materialize to your bastion host.
Configure the SSH bastion host to allow traffic from Materialize.
1. In the SQL Shell, or your preferred SQL client connected to Materialize, get the static egress IP addresses for the Materialize region you are running in:
```
SELECT * FROM mz_egress_ips;
```
2. Update your SSH bastion host’s firewall rules to allow SSH traffic from each IP address from the previous step.
Set the server’s connection policy to Proxy:
```
az sql server conn-policy update \
  --resource-group <resource-group> \
  --server <server-name> \
  --connection-type Proxy
```
With the Redirect policy, the gateway tells the client to reconnect directly to the backend node on a high port, which bypasses the single port the SSH tunnel forwards. Proxy keeps all traffic on the gateway at port 1433, which is the port the tunnel forwards.

If the connection policy is left as Redirect, creating the source or validating the connection fails with an error like:
```
Server requested a connection to an alternative address:
`<backend-node>.worker.database.windows.net:<high-port>`
```

C. Ingest data in Materialize

1. (Optional) Create a cluster

NOTE: If you are prototyping and already have a cluster to host your SQL Server source (e.g. quickstart), you can skip this step. For production scenarios, we recommend separating your workloads into multiple clusters for resource isolation.

In Materialize, a cluster is an isolated environment, similar to a virtual warehouse in Snowflake. When you create a cluster, you choose the size of its compute resource allocation based on the work you need the cluster to do, whether ingesting data from a source, computing always-up-to-date query results, serving results to clients, or a combination.

In this case, you’ll create a dedicated cluster for ingesting source data from your SQL Server database.

In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE CLUSTER command to create the new cluster:
```
CREATE CLUSTER ingest_sqlserver (SIZE = '200cc');

SET CLUSTER = ingest_sqlserver;
```
A cluster of size 200cc should be enough to process the initial snapshot of the tables in your SQL Server database. For very large snapshots, consider using a larger size to speed up processing. Once the snapshot is finished, you can readjust the size of the cluster to fit the volume of changes being replicated from your upstream SQL Server database.

2. Create a connection

Once you have configured your network, create a connection in Materialize per your networking configuration. Azure SQL Database requires an encrypted connection, so the SQL Server connection must specify SSL MODE 'required'.

In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE SECRET command to securely store the password for the SQL Server role you’ll use to replicate data into Materialize:
```
CREATE SECRET sqlserver_pass AS '<PASSWORD>';
```

Use the CREATE CONNECTION command to create a connection object with access and authentication details for Materialize to use:

CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST <host>,
    PORT 1433,
    USER 'materialize',
    PASSWORD SECRET sqlserver_pass,
    DATABASE <database>,
    SSL MODE 'required'
);

Replace <host> with your SQL Server endpoint, and <database> with the database you’d like to connect to.

In the SQL Shell, or your preferred SQL client connected to Materialize, use the CREATE CONNECTION command to create an SSH tunnel connection:
```
CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST '<SSH_BASTION_HOST>',
    PORT <SSH_BASTION_PORT>,
    USER '<SSH_BASTION_USER>'
);
```
- Replace <SSH_BASTION_HOST> and <SSH_BASTION_PORT> with the public IP address and port of the SSH bastion host you created earlier.
- Replace <SSH_BASTION_USER> with the username for the key pair you created for your SSH bastion host.
Get Materialize’s public keys for the SSH tunnel connection:
```
SELECT * FROM mz_ssh_tunnel_connections;
```

# Command for Linux
echo "ssh-ed25519 AAAA...76RH materialize" >> ~/.ssh/authorized_keys
echo "ssh-ed25519 AAAA...hLYV materialize" >> ~/.ssh/authorized_keys

Back in the SQL client connected to Materialize, validate the SSH tunnel connection you created using the VALIDATE CONNECTION command:
```
VALIDATE CONNECTION ssh_connection;
```
If no validation error is returned, move to the next step.
Use the CREATE SECRET command to securely store the password for the materialize user you created:
```
CREATE SECRET sqlserver_pass AS '<PASSWORD>';
```
Use the CREATE CONNECTION command to create the SQL Server connection, routed through the SSH tunnel. Azure SQL Database requires an encrypted connection, so include SSL MODE 'required':
```
CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST '<host>',
    PORT 1433,
    USER 'materialize',
    PASSWORD SECRET sqlserver_pass,
    DATABASE '<database>',
    SSL MODE 'required',
    SSH TUNNEL ssh_connection
);
```
- Replace <host> with your Azure SQL Database endpoint, and <database> with the database you’d like to connect to.

3. Start ingesting data

NOTE: For a new SQL Server source, if none of the replicating tables are receiving write queries, snapshotting may take up to an additional 5 minutes to complete. For details, see snapshot latency for inactive databases

Use the CREATE SOURCE command to connect Materialize to your SQL Server instance and start ingesting data:

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

By default, the source will be created in the active cluster; to use a different cluster, use the IN CLUSTER clause.
To ingest data from specific tables use the FOR TABLES (<table1>, <table2>) options instead of FOR ALL TABLES.
To handle unsupported data types, use the TEXT COLUMNS or EXCLUDE COLUMNS options. Check out the reference documentation for guidance.

After source creation, refer to schema changes considerations for information on handling upstream schema changes.

4. Right-size the cluster

After the snapshotting phase, Materialize starts ingesting change events from the SQL Server replication stream. For this work, Materialize generally performs well with a 100cc replica, so you can resize the cluster accordingly.

Still in a SQL client connected to Materialize, use the ALTER CLUSTER command to downsize the cluster to 100cc:
```
ALTER CLUSTER ingest_sqlserver SET (SIZE '100cc');
```
Behind the scenes, this command adds a new 100cc replica and removes the 200cc replica.

Use the SHOW CLUSTER REPLICAS command to check the status of the new replica:

SHOW CLUSTER REPLICAS WHERE cluster = 'ingest_sqlserver';

     cluster       | replica |  size  | ready
-------------------+---------+--------+-------
 ingest_sqlserver  | r1      | 100cc  | t
(1 row)

D. Explore your data

With Materialize ingesting your SQL Server data into durable storage, you can start exploring the data, computing real-time results that stay up-to-date as new data arrives, and serving results efficiently.

Explore your data with SHOW SOURCES and SELECT.
Compute real-time results in memory with CREATE VIEW and CREATE INDEX or in durable storage with CREATE MATERIALIZED VIEW.
Serve results to a PostgreSQL-compatible SQL client or driver with SELECT or SUBSCRIBE or to an external message broker with CREATE SINK.
Check out the tools and integrations supported by Materialize.

Considerations

Schema changes

Materialize supports schema changes in the upstream database as follows:

Compatible schema changes (Legacy syntax)

NOTE: This section refer to the legacy CREATE SOURCE ... FOR ... that creates subsources as part of the CREATE SOURCE operation. To be able to handle the upstream column additions and drops, use CREATE SOURCE (New Syntax) and CREATE TABLE FROM SOURCE instead. For details, see SQL Server: Source versioning guide.

Adding columns to tables. Materialize will not ingest new columns added upstream unless you use DROP SOURCE to first drop the affected subsource, and then add the table back to the source using ALTER SOURCE...ADD SUBSOURCE.
Dropping columns that were added after the source was created. These columns are never ingested, so you can drop them without issue.
Adding or removing NOT NULL constraints to tables that were nullable when the source was created.

Incompatible schema changes

All other schema changes to upstream tables will set the corresponding subsource into an error state, which prevents you from reading from the source.

To handle incompatible schema changes, use DROP SOURCE and ALTER SOURCE...ADD SUBSOURCE to first drop the affected subsource, and then add the table back to the source. When you add the subsource, it will have the updated schema from the corresponding upstream table.

Supported types

Materialize natively supports the following SQL Server types:

tinyint
smallint
int
bigint
real
double precision
float
bit
decimal
numeric
money
smallmoney
char
nchar
varchar
varchar(max)
nvarchar
nvarchar(max)
sysname
binary
varbinary
json
date
time
smalldatetime
datetime
datetime2
datetimeoffset
uniqueidentifier

Replicating tables that contain unsupported data types is possible via the EXCLUDE COLUMNS option for the following types:

text
ntext
image
varbinary(max)

Columns with the specified types need to be excluded because SQL Server does not provide the “before” value when said column is updated.

To replicate tables that contain the following unsupported data types:

text
ntext
image
varbinary(max)

You can use either the TEXT COLUMNS or the EXCLUDE COLUMNS option.

For text and ntext columns:
- You can use TEXT COLUMNS to expose them as varchar and nvarchar, respectively.
- You can use EXCLUDE COLUMNS to omit them from replication.
For image and varbinary(max) columns:
- You can use EXCLUDE COLUMNS.

Timestamp Rounding

The time, datetime2, and datetimeoffset types in SQL Server have a default scale of 7 decimal places, or in other words a accuracy of 100 nanoseconds. But the corresponding types in Materialize only support a scale of 6 decimal places. If a column in SQL Server has a higher scale than what Materialize can support, it will be rounded up to the largest scale possible.

-- In SQL Server
CREATE TABLE my_timestamps (a datetime2(7));
INSERT INTO my_timestamps VALUES
  ('2000-12-31 23:59:59.99999'),
  ('2000-12-31 23:59:59.999999'),
  ('2000-12-31 23:59:59.9999999');

-- Replicated into Materialize
SELECT * FROM my_timestamps;
'2000-12-31 23:59:59.999990'
'2000-12-31 23:59:59.999999'
'2001-01-01 00:00:00'

Snapshot latency for inactive databases

When a new Source is created, Materialize performs a snapshotting operation to sync the data. However, for a new SQL Server source, if none of the replicating tables are receiving write queries, snapshotting may take up to an additional 5 minutes to complete. The 5 minute interval is due to a hardcoded interval in the SQL Server Change Data Capture (CDC) implementation which only notifies CDC consumers every 5 minutes when no changes are made to replicating tables.

See Monitoring freshness status

Capture Instance Selection

When a new source is created, Materialize selects a capture instance for each table. SQL Server permits at most two capture instances per table, which are listed in the sys.cdc_change_tables system table. For each table, Materialize picks the capture instance with the most recent create_date.

If two capture instances for a table share the same timestamp (unlikely given the millisecond resolution), Materialize selects the capture_instance with the lexicographically larger name.

Modifying an existing source

When you add a new subsource to an existing source (ALTER SOURCE ... ADD SUBSOURCE ...), Materialize starts the snapshotting process for the new subsource. During this snapshotting, the data ingestion for the existing subsources for the same source is temporarily blocked. As such, if possible, you can resize the cluster to speed up the snapshotting process and once the process finishes, resize the cluster for steady-state.

Ingest data from Azure SQL Database

Before you begin

A. Configure Azure SQL Database

1. Create a Materialize user in Azure SQL Database.

2. Enable Change-Data-Capture for the database.

3. Enable SNAPSHOT transaction isolation.

4. Enable Change-Data-Capture for the tables.

B. (Optional) Configure network security

C. Ingest data in Materialize

1. (Optional) Create a cluster

2. Create a connection

3. Start ingesting data

4. Right-size the cluster

D. Explore your data

Considerations

Schema changes

Compatible schema changes (Legacy syntax)

Incompatible schema changes

Supported types

Timestamp Rounding

Snapshot latency for inactive databases

Capture Instance Selection

Modifying an existing source

3. Enable `SNAPSHOT` transaction isolation.