CREATE SOURCE: SQL Server (Legacy Syntax)

CREATE SOURCE connects Materialize to an external system you want to read data from, and provides details about how to decode and interpret that data.

Materialize supports SQL Server (2016+) as a real-time data source. To connect to a SQL Server database, you first need to tweak its configuration to enable Change Data Capture and SNAPSHOT transaction isolation for the database that you would like to replicate. Then create a connection in Materialize that specifies access and authentication parameters.

Syntax

CREATE SOURCE [IF NOT EXISTS] <src_name>
[IN CLUSTER <cluster_name>]
FROM SQL SERVER CONNECTION <connection_name>
  [ ( EXCLUDE COLUMNS (<col1> [, ...]) ) ]
  [ ( TEXT COLUMNS (<col1> [, ...]) ) ]
<FOR ALL TABLES | FOR TABLES ( <table1> [AS <subsrc_name>] [, ...] )>
[WITH ( <with_option> [, ...] )]

Creating a source

Materialize ingests the CDC stream for all (or a specific set of) tables in your upstream SQL Server database that have Change Data Capture enabled.

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

When you define a source, Materialize will automatically:

1. Create a subsource for each capture instance upstream, and perform an initial, snapshot-based sync of the associated tables before it starts ingesting change events.

   SHOW SOURCES;
   

            name         |   type     |  cluster  |
   ----------------------+------------+------------
    mz_source            | sql-server |
    mz_source_progress   | progress   |
    table_1              | subsource  |
    table_2              | subsource  |
   

2. Incrementally update any materialized or indexed views that depend on the source as change events stream in, as a result of INSERT, UPDATE and DELETE operations in the upstream SQL Server database.

SQL Server schemas

CREATE SOURCE will attempt to create each upstream table in the same schema as the source. This may lead to naming collisions if, for example, you are replicating schema1.table_1 and schema2.table_1. Use the FOR TABLES clause to provide aliases for each upstream table, in such cases, or to specify an alternative destination schema in Materialize.

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sql_server_connection
  FOR TABLES (schema1.table_1 AS s1_table_1, schema2.table_1 AS s2_table_1);

Monitoring source progress

By default, SQL Server sources expose progress metadata as a subsource that you can use to monitor source ingestion progress. The name of the progress subsource can be specified when creating a source using the EXPOSE PROGRESS AS clause; otherwise, it will be named <src_name>_progress.

The following metadata is available for each source as a progress subsource:

And can be queried using:

SELECT lsn
FROM <src_name>_progress;

The reported lsn should increase as Materialize consumes new CDC events from the upstream SQL Server database. For more details on monitoring source ingestion progress and debugging related issues, see Troubleshooting.

Known limitations

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

char and nchar columns

To preserve values exactly as SQL Server returns them, char and nchar columns are replicated as text rather than fixed-length. SQL Server and Materialize measure fixed-length character types differently, so replicating as text avoids truncation and padding mismatches.

To replicate tables that contain the following unsupported data types, you can use either the TEXT COLUMNS or the EXCLUDE COLUMNS option:

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.

Adding a table to 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.

Handling upstream operations

This section describes how changes to upstream tables that Materialize ingests affect the corresponding Materialize tables.

Adding a column

When you add a new column to your upstream table, Materialize continues to ingest only the existing columns.

To incorporate the new column:

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, create a new table from the source. See Handle upstream column addition.

• If using the legacy CREATE SOURCE ... FOR ... syntax that creates subsources, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE. The re-added subsource includes the new column.

Dropping a column

Dropping columns that Materialize does not ingest (for example, columns added after the source was created, or columns that are excluded) is supported. As these columns were never ingested, you can drop them without issue.

If your Materialize source ingests a column, dropping that column from your upstream table puts the affected table into an error state.

• If using the new CREATE SOURCE and CREATE TABLE FROM SOURCE syntax, you can safely drop a column by first ignoring it in Materialize. See Handle upstream column drop.

• If using legacy CREATE SOURCE ... FOR ... syntax, use DROP SOURCE to drop the affected subsource, and then add the table back to the source using ALTER SOURCE ... ADD SUBSOURCE.

Changing constraints

Materialize ignores foreign key and CHECK constraint changes. You can add or drop them without affecting ingestion.

Adding a UNIQUE constraint does not affect ingestion. Dropping a UNIQUE constraint puts the affected table into an error state.

SQL Server does not allow dropping a PRIMARY KEY from a table while change data capture is enabled on it. A primary key that existed when Materialize began ingesting the table therefore cannot be dropped upstream.

Adding or removing a NOT NULL constraint on an ingested column requires an upstream ALTER COLUMN, which puts the affected table into an error state. See Changing a column’s data type.

Changing a column’s data type

Any upstream ALTER COLUMN on an ingested column puts the affected Materialize table into an error state. This covers every ALTER COLUMN operation, not just data-type changes. Changing a column’s collation, sparseness, masking, or nullability all error the table the same way. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Renaming a column

Renaming a column that Materialize ingests puts the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the table in Materialize to resume ingestion.

Removing a capture instance

SQL Server allows up to two capture instances to exist for a table at once. Materialize ingests from one of them.

Removing the capture instance that Materialize is using puts the affected table into an error state. Removing a capture instance that Materialize is not using does not affect ingestion.

Disabling CDC on a table

Running sys.sp_cdc_disable_table removes the capture instance Materialize is ingesting from, which puts the affected table into an error state. The other tables in the source keep replicating. You can recover without re-creating the whole source by dropping just the affected table in Materialize:

DROP TABLE table_1;

Then re-create it, optionally after re-enabling CDC on the upstream table with sys.sp_cdc_enable_table.

Table-level operations

The following upstream operations put the affected table into an error state. Ingestion for that table stops, and you must drop and recreate the affected table in Materialize to resume:

• Dropping a table (DROP TABLE).
• Renaming a table or moving it to a different schema.

Source failure states and recovery

Operations that do not require re-creating the source

For operations that are supported automatically, Materialize is able to resume replication from a log sequence number (LSN) that it tracks as it consumes the upstream change data capture (CDC) change tables. Because LSNs live in the SQL Server transaction log, they survive routine operational events: after a transient interruption the source stalls, then resumes from its last committed LSN and catches up automatically. No action is required for the operations in the first section below.

The source recovers on its own. It will briefly reports a stalled status while the condition persists, then returns to running and catch up for all of the following scenarios:

• Restarting or patching SQL Server (including OS-level restarts).
• Restarting Materialize. The source resumes from its tracked LSN and does not re-snapshot already-ingested data.
• Transient network interruptions between Materialize and SQL Server.
• Taking the database OFFLINE and back ONLINE.
• Toggling the database between SINGLE_USER/MULTI_USER or READ_ONLY/READ_WRITE (for example, during patching).
• Data-file, filegroup, or index maintenance that rewrites data in place.
• Availability group failover, with the configuration change described below.

Operations that require re-creating the source

A smaller set of events breaks LSN or CDC-change-table continuity. When this happens, Materialize cannot guarantee a correct, gap-free view of your data, so it puts the entire source into an error state that requires re-creating the source. Re-creating triggers a fresh snapshot and rehydration of dependent objects. Upstream changes to an individual table’s schema are handled separately, and do not error the entire source.

The following events put the entire source into an error state. In each case, the remediation is to drop and re-create the source:

DROP SOURCE mz_source CASCADE;

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sql_server_connection;

-- Re-create the tables you were ingesting.
CREATE TABLE table_1 FROM SOURCE mz_source (REFERENCE dbo.table_1);

Point-in-time restore

Restoring the source database from a backup — including restoring to a different server for disaster recovery — is detected as a discontinuity. The source fails with an error of the form:

source must be dropped and recreated due to failure: Restore history id changed
from None to Some(<n>)

Materialize detects the restore by reading msdb.dbo.restorehistory. (This check does not apply to Azure SQL Database, which does not expose msdb.)

CDC disabled at the database level

Running sys.sp_cdc_disable_db drops all change tables. The source stalls with:

invalid SQL Server system setting 'database CDC'. Expected 'true'. Got 'Some(false)'.

Re-enable CDC on the database and on each table (sys.sp_cdc_enable_db, sys.sp_cdc_enable_table), then re-create the source.

Change-table retention

SQL Server’s CDC cleanup job removes change-table rows older than the retention period (3 days by default). If Materialize is disconnected long enough that cleanup removes rows past the source’s resume LSN, the source stalls with:

the requested LSN '...' is less than the minimum '...' for `dbo_<table>`

To avoid this during a planned outage, keep the outage shorter than the retention period, or increase retention beforehand with sys.sp_cdc_change_job (@job_type = 'cleanup', @retention).

Always-On failovers

Materialize supports SQL Server configured with Always On availability groups, including failover between replicas, with one configuration change.

By default, an availability group failover is misdetected as a point-in-time restore and fails the source with the Restore history id changed error described above. This is a false positive: the LSN stream is continuous across an availability group failover, but seeding a secondary replica writes rows to msdb.dbo.restorehistory, which the restore-detection check reads as a restore.

To allow the source to survive failover, disable restore-history validation with the sql_server_source_validate_restore_history system parameter:

ALTER SYSTEM SET sql_server_source_validate_restore_history = false;

With the check disabled, the source no longer fails on failover. Because msdb is per-instance, the CDC capture and cleanup jobs do not move with the availability group database — after a failover, confirm that CDC is healthy on the new primary (the capture and cleanup jobs exist, SQL Server Agent is running, and the change tables are advancing) so that replication continues. Adding the jobs on a replica that lacks them is done with sys.sp_cdc_add_job.

Examples

Creating a connection

A connection describes how to connect and authenticate to an external system you want Materialize to read data from.

Once created, a connection is reusable across multiple CREATE SOURCE statements. For more details on creating connections, check the CREATE CONNECTION documentation page.

CREATE SECRET sqlserver_pass AS '<SQL_SERVER_PASSWORD>';

CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    PORT 1433,
    USER 'materialize',
    PASSWORD SECRET sqlserver_pass,
    DATABASE '<DATABASE_NAME>'
);

If your SQL Server instance is not exposed to the public internet, you can tunnel the connection through and SSH bastion host.

CREATE CONNECTION ssh_connection TO SSH TUNNEL (
    HOST 'bastion-host',
    PORT 22,
    USER 'materialize',
    DATABASE '<DATABASE_NAME>'
);

CREATE CONNECTION sqlserver_connection TO SQL SERVER (
    HOST 'instance.foo000.us-west-1.rds.amazonaws.com',
    SSH TUNNEL ssh_connection,
    DATABASE '<DATABASE_NAME>'
);

Creating a source

You must enable Change Data Capture. See the setup instructions for Azure SQL Database or self-hosted SQL Server.

Once CDC is enabled for all of the relevant tables, you can create a SOURCE in Materialize to begin replicating data!

Create subsources for all tables in SQL Server

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

Create subsources for specific tables in SQL Server

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sqlserver_connection
  FOR TABLES (mydb.table_1, mydb.table_2 AS alias_table_2);

Handling unsupported types

If you’re replicating tables that use data types unsupported by SQL Server’s CDC feature, use the EXCLUDE COLUMNS option to exclude them from replication. This option expects the upstream fully-qualified names of the replicated table and column (i.e. as defined in your SQL Server database).

CREATE SOURCE mz_source
  FROM SQL SERVER CONNECTION sqlserver_connection (
    EXCLUDE COLUMNS (mydb.table_1.column_of_unsupported_type)
  )
  FOR ALL TABLES;

Handling errors and schema changes

To handle upstream schema changes or errored subsources, use the DROP SOURCE syntax to drop the affected subsource, and then ALTER SOURCE...ADD SUBSOURCE to add the subsource back to the source.

-- List all subsources in mz_source
SHOW SUBSOURCES ON mz_source;

-- Get rid of an outdated or errored subsource
DROP SOURCE table_1;

-- Start ingesting the table with the updated schema or fix
ALTER SOURCE mz_source ADD SUBSOURCE table_1;

Related pages

• CREATE SECRET
• CREATE CONNECTION
• CREATE SOURCE