About Openflow Connector for SQL Server¶

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

About the Openflow Connector for SQL Server¶

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 schedule. The connector uses SQL Server Change Tracking to detect and apply changes to replicated tables. Change data is recorded in journal tables alongside the current state of the replicated tables.

Snowflake also provides the Openflow Connector for SQL Server (CDC), which uses SQL Server Change Data Capture instead. To choose between the two connectors, see Comparison of Openflow connectors for SQL Server.

Use cases¶

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

• Synchronization of SQL Server data with Snowflake for comprehensive, centralized reporting.

Supported SQL Server versions¶

The following SQL Server database versions and platforms are supported:

• Microsoft SQL Server 2022
• Microsoft SQL Server 2019
• Microsoft SQL Server 2017
• Microsoft SQL Server 2016
• Azure SQL Database
• Azure SQL Managed Instance
• AWS RDS for SQL Server
• Google Cloud SQL for SQL Server

Openflow requirements¶

• Choose the runtime size based on the sustained replication workload. For sizing guidance and how to run multiple connectors on one runtime, see Runtime sizing.
• The connector doesn’t support multi-node Openflow runtimes. Configure the runtime for this connector with Min nodes and Max nodes set to 1.

Limitations¶

• You can’t run multiple connectors of the same type in a single runtime instance.
• The connector supports only username and password authentication with SQL Server.
• The connector only replicates database tables that contain primary keys.
• The connector doesn’t update existing records in the Snowflake database when a new NOT NULL column with a default value is added to one of the source databases.
• The connector doesn’t update existing records in the Snowflake database when a new column is added to the included list in the Column Filter JSON.
• The connector supports common source table schema changes during replication, such as adding, dropping, and renaming columns. See Schema changes for the full list and a few unsupported change types.
• The connector doesn’t support the truncate table operation.

Workflow¶

The following workflow outlines the steps to set up and run the Openflow Connector for SQL Server:

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

   1. Configures SQL Server replication settings and enables change tracking on the databases and tables being replicated.
   2. Creates credentials for the connector.
   3. (Optional) Provides the SSL certificate to connect to the SQL Server instance over SSL.

2. A Snowflake account administrator performs the following tasks:

   1. Creates a service user for the connector, a destination database to store replicated data, and a warehouse for the connector.
   2. Installs the connector.
   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 the schemas and destination tables matching the source tables configured for replication.

2. Begins replication according to the table replication lifecycle.

   For more information, see How tables are replicated.

How the connector works¶

The following sections describe how the connector works in various scenarios, including replication, snapshots of partitioned tables, changes in schema, and data retention.

Change tracking behavior¶

The connector uses SQL Server Change Tracking (CT) to detect changes in the source tables. Change Tracking reports the net effect of changes between polling intervals. If a row is updated multiple times between two consecutive polls, the connector sees only the most recent version of that row. Intermediate states aren’t preserved.

This makes the connector suitable for data synchronization use cases, where the goal is to keep the destination table in sync with the source. It isn’t suitable for audit or history use cases where every intermediate change to a row must be captured.

Data replication¶

The connector supports replicating tables from multiple SQL Server databases in a single SQL Server instance. The connector creates replicated tables from different databases in separate schemas in the destination Snowflake database.

Reference replicated tables by combining the source database name, the source schema name, and the table name in the following format:

<database_name>.<schema_name>.<table_name>

The name of the destination schema is determined by the Destination Schema Pattern parameter. For more information, see SQLServer Destination Parameters. By default, the destination schema name is the source database name and source schema name joined by an underscore, so the fully qualified name of a destination table is:

<destination_database>.<source_database_name>_<source_schema_name>.<source_table_name>

How tables are replicated¶

The connector replicates tables 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, the connector stops replicating data. After successful completion, the data from the source table is available in the destination table. For how large partitioned tables are handled, see Snapshot of partitioned tables.
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.

For information on bypassing snapshot load and using the incremental load process, see Incremental replication.

Snapshot of partitioned tables¶

During snapshot load, the connector pages through each source table in primary key order. For a large table that is physically partitioned on the source, this ordering can force SQL Server to sort the whole table on every batch when no index delivers rows in the required order, which makes the snapshot extremely slow or prevents it from completing.

To avoid this, the connector snapshots a physically partitioned table one partition at a time. Reading a single partition lets SQL Server stream rows in index order without a sort, so each batch finishes quickly. This behavior is automatic: the connector detects partitioned tables and requires no configuration change. Non-partitioned tables are unaffected and continue to be read as a whole.

To recover, restart replication for the affected table: see Restart table replication.

The same soft-delete mechanism applies when you change a table’s Column Filter JSON. For details, see Replicate a subset of columns in a table.

Oversized values¶

By default, the connector replicates individual values up to 16 MB. When the connector encounters a larger value, it marks the associated table as permanently failed and stops replicating it. To change how the connector handles oversized values (for example, to replace them with NULL instead), modify the Oversized Value Strategy destination parameter.

If your Snowflake account has the ENABLE_OPENFLOW_CDC_SQLSERVER_SSV2 parameter set to true, the per-value limit can be raised from 16 MB to 128 MB.

For details and instructions on enabling the 128 MB per-value limit, see Increase the oversized value limit.

For connection settings, see Always On Availability Groups.

Error handling for invalid rows¶

An invalid row is a row that Snowflake rejects during ingestion because it can’t be written to the destination table, for example a value that can’t be converted to the destination column’s type, or a missing required column. The Error Handling Strategy parameter controls what the connector does when it encounters an invalid row:

• Fail Table (default): On the first invalid row, the connector marks the table as permanently failed and stops replicating it, preserving strict, all-or-nothing replication. After you fix the source data, resume replication as described in Restart table replication.
• Log Errors and Continue: The connector keeps replicating the valid rows and records each rejected row, together with its original payload and error details, in the table’s error table. The table isn’t marked as failed.

To change the strategy, set the Error Handling Strategy parameter. For more information, see SQLServer Destination Parameters.

How rejected rows are captured¶

The connector loads data with Snowpipe Streaming, so error logging behaves exactly as described in Error logging in Snowpipe Streaming. When you select Log Errors and Continue, the connector creates new destination and journal tables with the ERROR_LOGGING property set to TRUE, so rejected rows are captured in a dedicated error table instead of aborting the load. The error table stores the original payload sent to Snowflake before any transformation, along with error details.

Query a table’s error table with the ERROR_TABLE table function:

SELECT * FROM ERROR_TABLE(<destination_database>.<schema>.<table>) ORDER BY timestamp;

Where a rejected row lands depends on the replication stage:

• Snapshot load: The rejected row is written to the destination table’s error table.
• Incremental (change tracking) load: The rejected row is written to the journal table’s error table, because changes are first written to the journal table. For more information about journal tables, see .

The connector enables error logging only on tables that it creates after you select Log Errors and Continue. To capture rejected rows for tables that were already being replicated, enable error logging on their existing destination and journal tables with the stored procedure in Enable error logging on an existing schema.

Consume rejected rows¶

To process rejected rows programmatically, create a stream on the error table and consume it like any other Snowflake stream. For more information, see Streams on error tables.

Source database locking behavior¶

During snapshot and incremental replication, the connector reads from the source database tables to retrieve row data and track changes.

Under SQL Server’s default READ COMMITTED isolation level, these read operations acquire shared locks on the source tables. If other database clients hold conflicting locks on the same tables at the same time, this can lead to deadlocks, where SQL Server terminates one of the conflicting sessions.

To avoid these deadlocks without affecting the isolation level that other applications use, configure the connector to read under SNAPSHOT isolation. SNAPSHOT isolation reads from row versions instead of acquiring shared locks, so the connector’s queries no longer contend with concurrent writes on the source tables.

Because this approach makes SNAPSHOT isolation available only to the connector’s own sessions, it doesn’t change the default READ COMMITTED isolation level that other applications rely on. Avoid using Read Committed Snapshot Isolation (RCSI) for this purpose, because RCSI redefines the default isolation level for every connection to the database.

For the steps to enable SNAPSHOT isolation for the connector, see Read the source under SNAPSHOT isolation.

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = FALSE;

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = TRUE;

SELECT EMAIL__SNOWFLAKE_DELETED FROM my_table;

SELECT
    COALESCE(B, A__SNOWFLAKE_DELETED) AS A_RENAMED_TO_B
FROM my_table;

SELECT
    CASE
        WHEN B IS NOT NULL THEN B
        ELSE A__SNOWFLAKE_DELETED
    END AS A_RENAMED_TO_B
FROM my_table;

CREATE VIEW my_table_unified AS
SELECT
    *,
    COALESCE(B, A__SNOWFLAKE_DELETED) AS A_RENAMED_TO_B
FROM my_table;

Next steps¶

Review Openflow connectors for SQL Server: Data mapping to understand how the connector maps data types to Snowflake data types.

Review Set up the Openflow Connector for SQL Server to set up the connector.