About Openflow Connector for SQL Server¶

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.

Replicate data from tables in multiple SQL Server databases¶

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 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.

Supported SQL Server versions¶

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

Openflow requirements¶

• The runtime size must be at least Medium. Use a bigger runtime when replicating large data volumes, especially when row sizes are large.

• The connector does not support multi-node Openflow runtimes. Configure the runtime for this connector with Min nodes and Max nodes set to 1.

Limitations¶

• You cannot 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 tables with data types that are supported by Snowflake. For a list of these data types, see Summary of data types.

• The connector only replicates database tables that contain primary keys.

• The connector does not 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 does not update existing records in the Snowflake database when a new column is added to the included list in the Column Filter JSON.

• After you delete a column in one of the source databases and add it back with the same name, additional deletes cause errors.

• After you include a column in Column Filter JSON and exclude it, additional include attempts cause errors.

• The connector supports source table schema changes, except for changing primary key definitions, changing the precision, or the scale of a numeric column.

• The connector does not support the truncate table operation.

Next steps¶

Set up the Openflow Connector for SQL Server