Home  /  Ingest data

SQL Server

View as Markdown

Change Data Capture (CDC)

Materialize supports SQL Server as a real-time data source. The SQL Server source uses SQL Server’s change data capture feature to continually ingest changes resulting from CRUD operations in the upstream database. The native support for SQL Server Change Data Capture (CDC) in Materialize gives you the following benefits:

  • No additional infrastructure: Ingest SQL Server change data into Materialize in real-time with no architectural changes or additional operational overhead. In particular, you do not need to deploy Kafka and Debezium for SQL Server CDC.

  • Transactional consistency: The SQL Server source ensures that transactions in the upstream SQL Server database are respected downstream. Materialize will never show partial results based on partially replicated transactions.

  • Incrementally updated materialized views: Incrementally updated Materialized views are considerably limited in SQL Server, so you can use Materialize as a read-replica to build views on top of your SQL Server data that are efficiently maintained and always up-to-date.

Supported versions

Materialize supports replicating data from SQL Server 2016 or higher with Change Data Capture (CDC) support.

Integration Guides

Considerations

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:

Unsupported type Supported option(s)
text TEXT COLUMNS (exposed as varchar) or EXCLUDE COLUMNS
ntext TEXT COLUMNS (exposed as nvarchar) or EXCLUDE COLUMNS
image EXCLUDE COLUMNS
varbinary(max) 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.

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:

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.

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.

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.
Back to top ↑