About Openflow Connector for SQL Server (CDC)¶

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

About the Openflow Connector for SQL Server (CDC)¶

The Openflow Connector for SQL Server (CDC) connects a SQL Server database instance to Snowflake and replicates data from selected tables in near real-time or on schedule. The connector uses SQL Server Change Data Capture (CDC) to detect and apply changes to replicated tables. Change data is recorded in journal tables alongside the current state of the replicated tables.

Use cases¶

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

• Synchronizing SQL Server data with Snowflake for comprehensive, centralized reporting.
• Capturing every individual row-level change from the source database, including intermediate states between polling intervals.

Supported SQL Server versions¶

The following SQL Server database versions and platforms are supported:

• Microsoft SQL Server 2025
• Microsoft SQL Server 2022
• Microsoft SQL Server 2019
• Microsoft SQL Server 2017
• Microsoft SQL Server 2016 (SP1 or later, Enterprise / Standard / Developer edition)
• Azure SQL Database
• Azure SQL Managed Instance
• AWS RDS for SQL Server
• Google Cloud SQL for SQL Server

Openflow requirements¶

• The runtime size must be at least Medium. For guidance on choosing a size and resizing the runtime later, 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 source table schema changes, except for changes to a table’s primary key or to the precision or scale of a numeric column. For more information about how the connector applies schema changes, see Schema changes.
• When a new column is added to a source table and an update changes only that newly added column, SQL Server Change Data Capture doesn’t record those updates in the change table, so the connector can’t replicate them as individual change events. This is a SQL Server CDC limitation: a change is captured only when an update modifies at least one column that the table’s capture instance already tracks. The connector still reflects the latest value of the new column in the destination table after the connector switches to the new capture instance.
• 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 (CDC):

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

   1. Enables Change Data Capture on each database using sys.sp_cdc_enable_db, then creates a capture instance for each table to be replicated using sys.sp_cdc_enable_table.
   2. Creates credentials for the connector.
   3. Deploys the Openflow CDC wrapper procedures so the connector can manage capture instances and apply source schema changes autonomously.
   4. (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, changes in schema, and data retention.

Change Data Capture behavior¶

The connector uses SQL Server Change Data Capture (CDC) to detect changes in the source tables. CDC captures row-level insert, update, and delete activity from the SQL Server transaction log into dedicated change tables.

Because CDC preserves every individual change, the connector is suitable for both data synchronization use cases and audit or history use cases where every change to a row must be captured. For a comparison with the Openflow Connector for SQL Server, which uses Change Tracking instead, see Comparison of Openflow connectors for SQL Server.

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>

For each schema in each source database being replicated, the connector creates a separate schema in the destination Snowflake database. The name of the destination schema is a combination of the source database name and the source schema name, separated by an underscore character (_) as shown in the following example:

<source_database_name>_<source_schema_name>

The connector creates tables in the destination schema with the same name as the source table name as shown in the following example:

<destination_database>.<destination_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.
3. Incremental load: The connector reads new entries from the CDC change tables 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.

Schema changes¶

The connector applies supported source table schema changes (DDL) automatically, without stopping replication or requiring a manual re-snapshot of the table. To do this, the connector manages SQL Server capture instances through the wrapper procedures deployed during setup. For more information, see Deploy the Openflow CDC wrapper procedures.

When a column is added on the source, the connector starts replicating it. Existing rows in the destination table aren’t backfilled with values for the new column. When a column is dropped on the source, the connector stops replicating it. Changes to a table’s primary key, or to the precision or scale of a numeric column, aren’t supported. For more information, see Limitations.

How the schema transition works¶

A SQL Server capture instance records changes against a fixed set of columns that’s frozen when the capture instance is created. When the schema of a source table changes, the connector can’t keep using the same capture instance, so it transitions to a new capture instance that reflects the updated schema. This transition happens in the following stages:

1. Schema change detection. The connector detects DDL changes through a polling mechanism: it periodically queries SQL Server’s cdc.ddl_history table for the tables it replicates. The polling interval is configurable and defaults to 30 seconds, so a schema change is picked up shortly after it occurs rather than in the same cycle.

2. New capture instance creation. When the connector detects a schema change, it creates a new capture instance that reflects the post-change column set. The new capture instance starts recording changes from the point at which it’s created.

3. Draining the old capture instance. The connector keeps reading from the old capture instance up to the point where the schema changed, so that no change committed under the old schema is lost. These changes are emitted using the old column set. All changes committed under the old schema are delivered to the destination before any changes recorded under the new schema, so the destination always receives changes in schema-consistent commit order.

4. Mini-snapshot. A gap exists between the point where the schema changed and the point where the new capture instance started recording. To fill this gap, the connector replays the changes in that gap from the old capture instance, joining them against the live source table so that newly added columns carry their current values. This mini-snapshot brings the destination table up to date with the new schema without a full re-snapshot of the whole table.

5. Switching to the new capture instance. After the mini-snapshot completes, the connector retires (drops) the old capture instance and resumes normal replication from the new one.

Replaying already-sent changes¶

When the connector detects a schema change, it might need to rewind and replay change records that it already sent. For example, records sent before a new column was detected don’t include a value for that column, so the connector replays the affected rows to populate the new column. Because the connector applies changes idempotently by primary key, replaying these records doesn’t create duplicate rows in the destination table.

A schema change during a schema transition¶

A second schema change can occur while the connector is still transitioning to a new capture instance because of an earlier change. The connector detects the follow-up change, provisions a newer capture instance that reflects the latest schema, and continues the transition against that newer instance, rewinding as needed so the affected changes are replayed under the most recent schema. No data is lost, and the transition completes against the most recent schema.

Oversized values¶

The connector doesn’t replicate individual values larger than 16 MB. By default, processing such a value marks the associated table permanently failed. To change this behavior, modify the Oversized Value Strategy destination parameter.

Source database locking behavior¶

During the snapshot phase, the connector reads directly from the source tables to perform the initial full copy. Under SQL Server’s default READ COMMITTED isolation level, these read operations acquire shared locks on the source tables, which can lead to deadlocks if other database clients hold conflicting locks at the same time.

During incremental replication, the connector reads from dedicated CDC change tables, not from the source tables. This means incremental replication doesn’t acquire any locks on the source tables.

To avoid deadlocks during the snapshot phase, enable Read Committed Snapshot Isolation (RCSI) on the source database:

ALTER DATABASE <database> SET READ_COMMITTED_SNAPSHOT ON;

With RCSI enabled, read operations use row versioning instead of shared locks, which eliminates lock contention between the connector and concurrent write transactions on the source database.

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 (CDC) to set up the connector.