Set up the Openflow Connector for SQL Server¶

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

4. As a database administrator, perform the following tasks:

   1. Enable change tracking on the database and tables. The connector requires that change tracking be enabled on the database and tables before replication starts. Make sure that every table that you plan to have replicated has enabled change tracking. You can also enable change tracking for more tables while the connector is running. See the following code snippet:

      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

   2. Create a user for the connector. The connector requires a user with the VIEW CHANGE TRACKING grant on replicated tables. Give that user a password to access the connector’s configuration.

      GRANT SELECT ON SCHEMA_NAME.TABLE_NAME TO USER_NAME;
      GRANT VIEW CHANGE TRACKING ON SCHEMA_NAME.TABLE_NAME TO USER_NAME;
      

      Copy

   3. Connect via SSL. If you’re planning to use an SSL connection to SQL Server, prepare the root certificate for your database server. It is required during configuration.

5. As a Snowflake account administrator, perform the following tasks:

   1. Create a Snowflake user with the type as SERVICE. Create a database to store the replicated data, and set up privileges for the Snowflake user to create objects in that database by granting the USAGE and CREATE SCHEMA privileges.

      CREATE DATABASE <destination_database>;
      CREATE USER <openflow_user> TYPE=SERVICE COMMENT='Service user for automated access of Openflow';
      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>;
      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

   2. Create a pair of secure keys (public and private). Store the private key for the user in a file to supply to the connector’s configuration. 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.

   3. Designate a warehouse for the connector to use. Start with the smallest warehouse size, then experiment with size depending on the amount of tables being replicated, and the amount of data transferred. Large table numbers typically scale better with multi-cluster warehouses, rather than the warehouse size.

Import the connector definition into Openflow¶

1. Download the connector definition file.

2. Import the connector definition into Openflow:

   1. Open the Snowflake Openflow canvas.

   2. Add a process group. To do this, drag and drop the Process Group icon from the tool palette at the top of the page onto the canvas. Once you release your pointer, a Create Process Group dialog appears.

   3. On the Create Process Group dialog, select the connector definition file to import.

Configure the connector¶

You can configure the connector for the following use cases:

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

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.