Openflow Connector for SQL Server: Maintenance¶

This topic describes maintenance considerations and best practices for the Openflow Connector for SQL Server, such as reinstalling the connector or setting the change tracking starting position.

These operations are often used in conjunction with Incremental replication without snapshots.

Check the replication status of a table¶

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.

Increase the oversized value limit¶

By default, the connector replicates individual values up to 16 MB and marks any table that contains a larger value as permanently failed. 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.

The increased limit doesn’t apply equally to all column types.

Check the account parameter¶

To verify the value of the ENABLE_OPENFLOW_CDC_SQLSERVER_SSV2 account parameter, run the following query.

SHOW PARAMETERS LIKE 'ENABLE_OPENFLOW_CDC_SQLSERVER_SSV2' FOR ACCOUNT <account_locator>;

If the returned value is not true, the connector continues to enforce the 16 MB limit regardless of any processor configuration.

Configure the processors¶

Update the Oversized Value Limit property to 128 MB on both of the following processors:

• Fetch Table Rows (in the Snapshot Load group)
• Read SQLServer Change Tracking tables (in the Incremental Load group)

For each processor:

1. Locate the processor in the flow.
2. Right-click the processor and select Configure.
3. Open the Properties tab.
4. Set Oversized Value Limit to 128 MB.
5. Apply the change.

For tables that are already being replicated and have destination columns narrower than VARCHAR(134217728) or BINARY(67108864), see Migrate existing tables.

Migrate existing tables¶

The steps in Increase the oversized value limit raise the limit for newly created destination tables. If a table is already being replicated and its destination column type is not VARCHAR(134217728) or BINARY(67108864), but you now want to load values larger than the original 16 MB limit, you must manually widen the column type on both the journal and destination tables.

Before you migrate, check the current destination column type, because it can vary depending on when the snapshot replication was performed.

To migrate a table:

1. Stop replication for the affected table by stopping the topmost processors of the Snapshot Load and Incremental Load groups until all queues are empty. For the equivalent stop procedure, see the substeps in Reinstall the connector.
2. Widen the column on both the journal table and the destination table, according to the column type:
   1. For VARCHAR columns, run ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE VARCHAR(134217728) on the journal table and on the destination table (one statement per table).
   2. For BINARY columns, Snowflake doesn’t allow widening BINARY in place, so do the following on both the journal and destination tables:
      1. Add a new column of type BINARY(67108864).
      2. Copy data from the original column into the new column.
      3. Drop the original column and rename the new column to the original name.
3. Restart replication by re-enabling the processors.

Performance considerations¶

Raising the per-value limit increases the amount of data that the connector loads into memory and moves through the flow, which raises the load on both the runtime and the warehouse. Size the runtime and warehouse accordingly.

During both snapshot and incremental replication, the queue in front of the Upload Rows via Snowpipe Streaming 2 processor can fill with FlowFiles and trigger back pressure, which consumes a large amount of runtime disk space. For larger tables, use a Large runtime to provide additional storage. For guidance on choosing a size, see Runtime sizing.

Snapshot replication¶

To speed up snapshot replication, you can increase the number of channels that the Upload Rows via Snowpipe Streaming 2 processor uses. The number of channels is set by the processor’s Channel Group property, which defaults to ${chunk.index:isEmpty():ifElse('1', ${chunk.index:mod(8)})}.

To increase the number of channels:

1. Locate the Upload Rows via Snowpipe Streaming 2 processor in the flow.
2. Stop the processor. You must stop the processor before you can change its properties.
3. Right-click the processor and select Configure.
4. Open the Properties tab.
5. In the Channel Group property, increase the value 8 in the expression. For example, change 8 to 16 to double the number of channels.
6. Apply the change.
7. Start the processor.

Incremental replication¶

When the source produces frequent changes to rows that contain large values, you might need a Large warehouse. High-frequency merges of many moderately large rows (for example, many 8 MB values) can require a large single merge operation, and smaller warehouses can run out of memory. By contrast, fewer very large rows (for example, 128 MB values) are streamed file by file through the Upload Rows via Snowpipe Streaming 2 processor, and each file is merged incrementally, which typically completes without warehouse errors even on smaller warehouses.

Reinstall the connector¶

This section provides instructions on how to reinstall the connector, and continue replicating data for the same tables without having to snapshot them again. It covers situations where the new connector is installed in the same runtime, as well as moved to a new runtime.

Prerequisites¶

Review and note connector parameter context values. If you reinstall the connector in the same runtime, you can reuse the existing context. If the new instance is located in a different runtime, you must re-enter all parameters.

1. Finish processing all in-flight FlowFiles in the existing connector, then stop the connector.

   1. Sign in to Snowsight.

   2. In the navigation menu, select Ingestion » Openflow.

   3. Select Launch Openflow.

   4. In the Openflow pane select the Runtimes tab.

   5. Select the runtime containing the connector.

   6. Select the connector.

   7. Stop the topmost processor Set Tables for Replication in the Snapshot Load group.

   8. Stop the topmost processor Read SQLServer Change Tracking tables in the Incremental Load group.

   9. If you changed the value of the Merge Task Schedule CRON parameter, return it to * * * * * ?, otherwise queues won’t be emptied until the next scheduled run.

      Wait until all FlowFiles in the connector have been processed, and all queues are empty. When all FlowFiles have been processed, the Queued value on the connector’s processor group becomes zero. If there are any items left in the original connector’s queues, there may be data gaps when the new connector starts.

   10. Stop all processors and controller services in the connector.

   Caution

   The existing connector can remain in the runtime and doesn’t interfere with the new instance, as long as it remains stopped.

2. If you’re moving the connector to a new runtime, download the flow definition from the existing connector so that you can recreate the connector with its current state instead of configuring it from scratch. Downloading a flow definition requires Openflow Runtime Server version 2026.6.4.18 or later.

   1. Right-click the connector’s process group, then select Download flow definition.
   2. Select both of the following options, then download the flow definition:
      • Export with External Services: includes the controller services that the connector references from parent process groups.
      • Export with Components State: includes component state, such as change tracking positions and incremental replication state, so that replication continues from where it left off.

3. Create the connector in the target runtime:

   • If you downloaded the flow definition, import it into the new runtime. Importing the flow definition preserves the component state captured during the export, so the connector resumes incremental replication from its previous positions.
   • Otherwise, create a new instance of the connector. If you use the same runtime as the original connector, you can choose to keep the existing parameter contexts and reuse the settings.

4. If you install into a different runtime or you deleted the previous parameter contexts, enter the configuration settings into the new parameter contexts, including the table names and patterns as described in Set up the Openflow Connector for SQL Server. A downloaded flow definition doesn’t include sensitive values (such as passwords) or uploaded files (such as the Microsoft JDBC driver), so you must re-enter and re-upload them.

5. Navigate to the SQLServer Ingestion Parameters context, and set the following parameters:

   • Set the Ingestion Type parameter to incremental. For information, see Enable incremental replication without snapshots.
   • Set the Starting Change Tracking Position parameter to Earliest. For information, see Specify load from change tracking table position.

   Note

   If you imported the flow definition with Export with Components State selected, the connector retains its previous change tracking positions. In this case, leave Starting Change Tracking Position set to Latest to continue replication from where it stopped.

6. Start the new connector.

Usage notes¶

The new connector uses the existing destination tables created by the original connector, but creates new journal tables.

Specify load from change tracking table position¶

The Openflow Connector for SQL Server connector lets you select the starting position where change tracking tables are read. By default, the connector reads from the latest available position. Alternatively, you can choose the earliest position available on the source instance. Choosing to start from the earliest position is common when reinstalling the connector. This allows the new instance to catch up and continue replicating existing tables without having to snapshot each again.

Switching a running connector from latest to earliest position causes the contents of change tracking tables to be re-read, re-processed, and re-applied to the destination table.

The following parameters are available in the Ingestion Parameters context:

To determine whether the connector finished re-reading the change tracking tables:

1. Navigate to the Openflow canvas.
2. Open the Incremental Load process group.
3. Right-click the topmost processor named Read SQLServer Change Tracking tables, then select View state.
4. Check the state entries for every table with keys starting with position.. If a value is 0/0 then the connector has not yet finished re-reading the changes for this table.

Usage notes¶

• After you switch a running connector to read from the earliest positions and start it, you can’t reconfigure or cancel the process, and it will continue until the currently-read positions reach the latest values.
• Switching to the earliest position on a running connector will, for any tables being re-processed, finish their existing journals, and create new journal tables.