关于 Openflow Connector for SQL Server¶

工作流程¶

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

1. SQL 服务器数据库管理员需执行以下任务：

   1. Configures SQL Server replication settings and enables change tracking on the databases and tables being replicated.

   2. 为连接器创建凭据。

   3. (Optional) Provides the SSL certificate to connect to the SQL Server instance over SSL.

2. Snowflake 账户管理员需执行以下任务：

   1. Creates a service user for the connector, a destination database to store replicated data, and a warehouse for the connector.

   2. 安装连接器。

   3. 为连接器流程定义指定所需的参数。

   4. Runs the flow.

The connector does the following when run in Openflow:

1. 创建与源表对应的架构和目标表，用于复制。

2. Begins replication according to the table replication lifecycle.

   For more information, see 如何复制表.

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>

如何复制表¶

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.

支持的 SQL 服务器版本¶

下表列出了经过测试和官方支持的 SQL 服务器版本。

Openflow 要求¶

• 运行时大小必须至少为“Medium”。复制大容量数据时请使用更大的运行时环境，尤其是在行大小较大的情况下。

• 该连接器不支持多节点 Openflow 运行时。使用 Min nodes 配置此连接器的运行时，并将 Max nodes 设置为 1。

限制¶

• You cannot run multiple connectors of the same type in a single runtime instance.

• 连接器仅支持使用用户名和密码的方式进行身份验证，以连接 SQL 服务器。

• 该连接器仅复制受 Snowflake 支持的数据类型的表。有关这些数据类型的列表，请参阅 数据类型摘要。

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

• 当在 Column Filter JSON 的包含列表中添加新列时，连接器不会更新 Snowflake 数据库中已有的记录。

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

• 该连接器不支持截断表操作。

后续步骤¶

设置 Openflow Connector for SQL Server