Set up the Openflow Connector for SQL Server¶

Prerequisites¶

Before setting up the connector, ensure that you have completed the following prerequisites:

1. Ensure that you have reviewed About Openflow Connector for SQL Server.

2. Ensure that you have reviewed Supported SQL Server versions.

3. Ensure that you have set up your runtime deployment. For more information, see the following topics:

   • Set up Openflow - BYOC

   • Set up Openflow - Snowflake Deployments.

4. If you use Openflow - Snowflake Deployments, ensure that you have reviewed configuring required domains and have granted access to the required domains for the SQL Server connector.

Set up your SQL Server instance¶

Before setting up the connector, perform the following tasks in your SQL Server environment:

1. Enable change tracking on the databases and tables that you plan to replicate, as shown in the following SQL Server example:

   ALTER DATABASE <database>
     SET CHANGE_TRACKING = ON
     (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
   
   ALTER TABLE <schema>.<table>
     ENABLE CHANGE_TRACKING
     WITH (TRACK_COLUMNS_UPDATED = ON);
   

   Copy

   Note

   Run these commands for every database and table that you plan to replicate.

   The connector requires that change tracking is enabled on the databases and tables before replication starts. Ensure that every table that you plan to replicate has enabled change tracking. You can also enable change tracking on additional tables while the connector is running.

2. Create a login for the SQL Server instance:

   CREATE LOGIN <user_name> WITH PASSWORD = '<password>';
   

   Copy

   This login is used to create users for the databases you plan to replicate.

3. Create a user for each database you are replicating by running the following SQL Server command in each database:

   USE <source_database>;
   CREATE USER <user_name> FOR LOGIN <user_name>;
   

   Copy

4. Grant the SELECT and VIEW CHANGE TRACKING permissions to the user for each database that you are replicating:

   GRANT SELECT ON <database>.<schema>.<table> TO <user_name>;
   GRANT VIEW CHANGE TRACKING ON <database>.<schema>.<table> TO <user_name>;
   

   Copy

   Run these commands in each database for every table that you plan to replicate. These permissions must be granted to the user of each database that you created in a previous step.

5. (Optional) Configure SSL connection.

   If you use an SSL connection to connect SQL Server, create the root certificate for your database server. This is required when configuring the connector.

Set up your Snowflake environment¶

As a Snowflake administrator, perform the following tasks:

1. Create a destination database in Snowflake to store the replicated data:

   CREATE DATABASE <destination_database>;
   

   Copy

2. Create a Snowflake service user:

   CREATE USER <openflow_user>
     TYPE = SERVICE
     COMMENT='Service user for automated access of Openflow';
   

   Copy

3. Create a Snowflake role for the connector and grant the required privileges:

   CREATE ROLE <openflow_role>;
   GRANT ROLE <openflow_role> TO USER <openflow_user>;
   GRANT USAGE ON DATABASE <destination_database> TO ROLE <openflow_role>;
   GRANT CREATE SCHEMA ON DATABASE <destination_database> TO ROLE <openflow_role>;
   

   Copy

   Use this role to manage the connector’s access to the Snowflake database.

   To create objects in the destination database, you must grant the USAGE and CREATE SCHEMA privileges on the database to the role used to manage access.

4. Create a Snowflake warehouse for the connector and grant the required privileges:

   CREATE WAREHOUSE <openflow_warehouse> WITH
     WAREHOUSE_SIZE = 'MEDIUM'
     AUTO_SUSPEND = 300
     AUTO_RESUME = TRUE;
   GRANT USAGE, OPERATE ON WAREHOUSE <openflow_warehouse> TO ROLE <openflow_role>;
   

   Copy

   Snowflake recommends starting with a MEDIUM warehouse size, then experimenting with size depending on the number of tables being replicated and the amount of data transferred. Large numbers of tables typically scale better with multi-cluster warehouses, rather than a larger warehouse size. For more information, see multi-cluster warehouses.

5. Set up the public and private keys for key pair authentication:

   1. Create a pair of secure keys (public and private).

   2. Store the private key for the user in a file to supply to the connector’s configuration.

   3. Assign the public key to the Snowflake service user:

      ALTER USER <openflow_user> SET RSA_PUBLIC_KEY = 'thekey';
      

      Copy

      For more information, see Key-pair authentication and key-pair rotation.

Configure the connector¶

As a data engineer, install and configure the connector using the following sections.

Install the connector¶

1. Navigate to the Openflow overview page. In the Featured connectors section, select View more connectors.

2. On the Openflow connectors page, find the connector and select Add to runtime.

3. In the Select runtime dialog, select your runtime from the Available runtimes drop-down list and click Add.

   Note

   Before you install the connector, ensure that you have created a database and schema in Snowflake for the connector to store ingested data.

4. Authenticate to the deployment with your Snowflake account credentials and select Allow when prompted to allow the runtime application to access your Snowflake account. The connector installation process takes a few minutes to complete.

5. Authenticate to the runtime with your Snowflake account credentials.

The Openflow canvas appears with the connector process group added to it.

Configure the connector¶

To configure the connector, perform the following steps:

1. Right-click on the imported process group and select Parameters.

2. Populate the required parameter values as described in Flow parameters.

Remove and re-add a table to replication¶

To remove a table from replication, remove it from the Included Table Names or Included Table Regex parameters in the Replication Parameters context.

To re-add the table to replication later, first delete the corresponding destination table in Snowflake. Afterward, add the table back to the Included Table Names or Included Table Regex parameters. This ensures that the replication process starts fresh for the table.

This approach can also be used to recover from a failed table replication scenario.

[
    {
        "schema": "<source table schema>",
        "table" : "<source table name>",
        "included": ["<column name>", "<column name>"],
        "excluded": ["<column name>", "<column name>"],
        "includedPattern": "<regular expression>",
        "excludedPattern": "<regular expression>",
    }
]

customers_1234_1
customers_1234_2
orders_5678_1
orders_5678_2

Run the flow¶

1. Right-click on the plane and select Enable all Controller Services.

2. Right-click on the imported process group and select Start. The connector starts the data ingestion.