关于 Openflow Connector for SQL Server¶

Change tracking behavior¶

The connector uses SQL Server Change Tracking (https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server) (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 are not 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 is not 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>

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>

如何复制表¶

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. 增量加载连接器跟踪源表中的更改并将这些更改应用到目标表。此过程将一直持续，直到从复制中移除该表。如果该阶段失败，将永久停止源表的复制，直到问题解决。

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

Table replication status¶

Interim failures, such as connection errors, do not prevent table replication. However, permanent failures, such as unsupported data types, prevent table replication.

To troubleshoot replication issues or verify that a table has been successfully removed from the replication flow, check the Table State Store:

1. In the Openflow runtime canvas, right-click a processor group and choose Controller Services. A table listing controller services displays.

2. Locate the row labeled Table State Store, click the More button on the right side of the row, and then choose View State.

A list of tables and their current states displays. Type in the search box to filter the list by table name. The possible states are:

• NEW: The table is scheduled for replication but replication hasn't started.

• SNAPSHOT_REPLICATION: The connector is copying existing data. This status displays until all records are stored in the destination table.

• INCREMENTAL_REPLICATION: The connector is actively replicating changes. This status displays after snapshot replication ends and continues to display indefinitely until a table is either removed from replication or replication fails.

• FAILED: Replication has permanently stopped due to an error.

Replication state for table <database_name>.<schema_name>.<table_name> changed from <old_state> to <new_state>

If a permanent failure prevents table replication, remove the table from replication. After you address the problem that caused the failure, you can add the table back to replication. For more information, see Restart table replication.

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 deadlocks between the connector and other database clients, enable Read Committed Snapshot Isolation (RCSI) (https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server) 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.

Destination table metadata columns¶

Each destination table includes the following metadata columns that track replication information:

Soft-deleted rows¶

When a row is deleted from the source table, the connector does not physically remove it from the destination table. Instead, the row is marked as deleted by setting the _SNOWFLAKE_DELETED metadata column to true.

This approach allows you to:

• Retain historical data for auditing or compliance purposes.

• Query deleted records when needed.

• Decide when and how to permanently remove data based on your requirements.

To query only active (non-deleted) rows, filter on the _SNOWFLAKE_DELETED column:

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = FALSE;

To query deleted rows:

SELECT * FROM my_table WHERE _SNOWFLAKE_DELETED = TRUE;

Dropped columns¶

When a column is dropped from the source table, the connector does not drop the corresponding column from the destination table. Instead, the column is renamed by appending the __SNOWFLAKE_DELETED suffix to preserve historical values.

For example, if a column named EMAIL is dropped from the source table, it is renamed to EMAIL__SNOWFLAKE_DELETED in the destination table. Rows that existed before the column was dropped retain their original values, while rows added after the drop have NULL in this column.

You can still query historical values from the renamed column:

SELECT EMAIL__SNOWFLAKE_DELETED FROM my_table;

Renamed columns¶

Due to limitations in CDC (Change Data Capture) mechanisms, the connector cannot distinguish between a column being renamed and a column being dropped followed by a new column being added. As a result, when you rename a column in the source table, the connector treats this as two separate operations: dropping the original column and adding a new column with the new name.

For example, if you rename a column from A to B in the source table, the destination table will contain:

• A__SNOWFLAKE_DELETED: Contains values from before the rename. Rows added after the rename have NULL in this column.

• B: Contains values from after the rename. Rows that existed before the rename have NULL in this column.

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;

Journal tables¶

During incremental replication, changes from the source database are first written to journal tables before being merged into the destination tables. The connector does not automatically remove data from journal tables, as this data may be useful for auditing, debugging, or reprocessing purposes.

Journal tables are created in the same schema as their corresponding destination tables and follow this naming convention:

<TABLE_NAME>_JOURNAL_<timestamp>_<number>

Where:

• <TABLE_NAME> is the name of the destination table.

• <timestamp> is the creation timestamp in Unix epoch format (seconds since January 1, 1970), ensuring uniqueness.

• <number> starts at 1 and increments whenever the destination table schema changes, either due to schema changes in the source table or modifications to column filters.

For example, if your destination table is SALES.ORDERS, the journal table might be named SALES.ORDERS_JOURNAL_1705320000_1.