Home  /  Ingest data

SQL Server

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.

Integration Guides

Considerations

Schema changes

NOTE: Work to more smoothly support ddl changes to upstream tables is currently in progress. The work introduces the ability to re-ingest the same upstream table under a new schema and switch over without downtime.

Materialize supports schema changes in the upstream database as follows:

Compatible schema changes

  • 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
  • bit
  • decimal
  • numeric
  • money
  • smallmoney
  • char
  • nchar
  • varchar
  • nvarchar
  • 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
  • varchar(max)
  • nvarchar(max)
  • 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.

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'

Back to top ↑