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.

      CREATE LOGIN <user_name> WITH PASSWORD = <password>;
      CREATE USER <user_name> FOR LOGIN <user_name>;
      GRANT SELECT ON <schema>.<table> TO <user_name>;
      GRANT VIEW CHANGE TRACKING ON <schema>.<table> 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 MEDIUM 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.

Set up the connector¶

As a data engineer, perform the following tasks to configure the connector:

[
    {
        "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.