Home  /  Ingest data  /  SQL Server

Ingest data from self-hosted SQL Server

This page shows you how to stream data from a self-hosted SQL Server database to Materialize using the SQL Server source.

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

Before you begin

  • Make sure you are running SQL Server 2016 or higher. Materialize uses Change Data Capture which is not readily available on older versions of SQL Server.

  • Ensure you have access to your SQL Server instance via the sqlcmd client, or your preferred SQL client.

A. Configure SQL Server

1. Enable Change-Data-Capture for the database

Before creating a source in Materialize, you must configure your SQL Server database for change data capture. This requires running the following stored procedures:

EXEC sys.sp_cdc_enable_db;

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

2. Enable SNAPSHOT transaction isolation.

In addition to enabling Change-Data-Capture you must also enable your SNAPSHOT transaction isolation in your SQL Server database. This requires running the following SQL:

ALTER DATABASE <DATABASE_NAME> SET ALLOW_SNAPSHOT_ISOLATION ON;

For guidance on enabling SNAPSHOT transaction isolation, see the SQL Server documentation.

B. (Optional) Configure network security

NOTE: If you are prototyping and your SQL Server instance 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.

    1. 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;

    2. Update your database firewall rules to allow traffic from each IP address from the previous step.

    To create an SSH tunnel from Materialize to your database, you launch an VM to serve as an SSH bastion host, configure the bastion host to allow traffic only from Materialize, and then configure your database’s private network to allow traffic from the bastion host.

    1. Launch a VM to serve as your SSH bastion host.

      • Make sure the VM is publicly accessible and in the same VPC as your database.
      • 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.

    2. Configure the SSH bastion host to allow traffic only 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 traffic from each IP address from the previous step.

    3. Update your database firewall rules to allow traffic from the SSH bastion host.

    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.

    1. 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. Start ingesting data

    Now that you’ve configured your database network, you can connect Materialize to your SQL Server database and start ingesting data. The exact steps depend on your networking configuration, so start by selecting the relevant option.

      1. 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>';

      2. 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>
);
        • Replace <host> with your SQL Server endpoint, and <database> with the database you’d like to connect to.

      3. 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.

      4. After source creation, you can handle upstream schema changes by dropping and recreating the source.

      3. 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.

      1. 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.

      2. 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.

      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 ↑