About Openflow Connector for SQL Server¶

Note

The connector is subject to the Connector Terms.

This topic describes the basic concepts of Openflow Connector for SQL Server, its workflow and limitations.

The Openflow Connector for SQL Server connects a SQL Server database instance to Snowflake and replicates data from selected tables in near real-time or on a specified schedule.

Use this connector if you’re looking to do the following:

  • CDC replication of Microsoft SQL Server data with Snowflake for comprehensive, centralized reporting

Workflow¶

  1. A SQL Server database administrator performs the following tasks:

    1. Configures SQL Server replication settings and enables change tracking on the database and tables.

    2. Creates credentials for the connector.

    3. (Optional) Provides the SSL certificate.

  2. A Snowflake account administrator performs the following tasks:

    1. Creates a service user for the connector, a warehouse for the connector, and a destination database to replicate into.

    2. Downloads and imports the connector definition file into the Snowflake Openflow canvas.

    3. Specifies the required parameters for the connector flow definition.

    4. Runs the flow. The connector does the following when run in Openflow:

      1. Creates a schema for journal tables.

      2. Creates the schemas and destination tables matching the source tables configured for replication.

      3. Starts replication following the table replication lifecycle as described in How tables are replicated.

How tables are replicated¶

The tables are replicated in the following stages:

  1. Schema introspection: The connector discovers the columns in the source table, including the column names and types, then validates them against Snowflake’s and the connector’s Limitations. Validation failures cause this stage to fail, and the cycle completes. After successful completion of this stage, the connector creates an empty destination table.

  2. Snapshot load: The connector copies all data available in the source table into the destination table. If this stage fails, then no more data is replicated. After successful completion, the data from the source table is available in the destination table.

  3. Incremental load: The connector tracks changes in the source table and applies those changes to the destination table. This process continues until the table is removed from replication. Failure at this stage permanently stops replication of the source table, until the issue is resolved.

Note

Interim failures (such as connection errors) do not prevent the table from being replicated. However, permanent failures (such as unsupported data types) do prevent the table from being replicated. If a permanent failure prevents a table from being replicated, remove the table from the list of tables to be replicated. After you address the problem that caused the failure, you can add the table back to the list of tables to be replicated.

Supported SQL Server versions¶

The following table lists the tested and officially supported SQL Server versions:

2017

2022

Standard

Yes

Yes

AWS RDS

Yes

Yes

Limitations¶

  • The connector supports only username and password authentication with SQL Server.

  • The connector only replicates tables with data types that are supported by Snowflake. For a list of these data types, see Summary of data types.

  • The connector requires every replicated table to have a primary key.

Note

Limitations affecting certain table columns can be bypassed by excluding these specific columns from replication.

Next steps¶

Set up the Openflow Connector for SQL Server

Language: English