Generic SQL Server

Specify Generic SQL Server Connection Settings

Perform the following steps to configure SQL Server as a Source in Hevo:

1. Click PIPELINES in the Navigation Bar.

2. Click + CREATE PIPELINE in the Pipelines List View.

3. On the Select Source Type page, select SQL Server.

4. On the Configure your SQL Server Source page, specify the following:

   

   • Pipeline Name: A unique name for the Pipeline, not exceeding 255 characters.

   • SQL Server Host: SQL Server host’s IP address or DNS.

     The following table lists a few examples of SQL Server hosts:

     Variant	Host
     Amazon RDS SQL Server	ms-sql-server-1.xxxxx.rds.amazonaws.com
     Azure MS SQL	mssql.database.windows.net
     Generic MS SQL	10.123.10.001 or mssql.westeros.inc
     Google Cloud SQL Server	35.220.150.0

     Note: For URL-based hostnames, exclude the http:// or https:// part. For example, if the hostname URL is https://mssql.database.windows.net, enter mssql.database.windows.net.

   • SQL Server Port: The port on which your SQL Server is listening for connections. Default value: 1433.

   • SQL Server User: The read-only user who has the permissions to read tables in your database.

   • SQL Server Password: The password for the read-only user.

   • Select an Ingestion Mode: The desired mode by which you want to ingest data from the Source. You can expand this section by clicking SEE MORE to view the list of ingestion modes to choose from. Default value: Change Tracking. The available ingestion modes are Change Tracking, Table, and Custom SQL.

     

     Depending on the ingestion mode you select, you must configure the objects to be replicated. Refer to section, Object and Query Mode Settings for the steps to do this.

     Note: For Custom SQL ingestion mode, all Events loaded to the Destination are billable.

   • Database Name: The database that you wish to replicate.

   • Schema Name: The schema that holds the tables to be replicated. Default value: dbo.

   • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your SQL Server database host to Hevo. This provides an additional level of security to your database by not exposing your SQL Server setup to the public. Read Connecting Through SSH.

     If this option is disabled, you must whitelist Hevo’s IP addresses. Refer to the content for your SQL Server variant for steps to do this.

   • Advanced Settings:

     • Include New Tables in the Pipeline: Applicable for all ingestion modes except Custom SQL. If enabled, Hevo automatically ingests data from tables created after the Pipeline has been built. If disabled, the new tables are listed in the Pipeline Detailed View in Skipped state, and you can manually include the ones you want and load their historical data. You can include these objects post-Pipeline creation to ingest data.

       You can change this setting later.

5. Click TEST CONNECTION. This button is enabled once you specify all the mandatory fields. Hevo’s underlying connectivity checker validates the connection settings you provide.

6. Click TEST & CONTINUE to proceed for setting up the Destination. This button is enabled once you specify all the mandatory fields.

Object and Query Mode Settings

Once you have specified the Source connection settings in Step 4 above, do one of the following:

• For Pipelines configured with the Change Tracking ingestion mode:

  1. On the Select Objects page, select the objects you want to replicate.

  2. Click CONTINUE. This button is enabled once you select at least one object for which Change Tracking is enabled.

     

     Note:

     • Each object represents a table in your database.

     • You must enable Change Tracking for the objects you want to ingest data from. If disabled, Hevo adds these objects to your Pipeline in the SKIPPED state.

     • For customers signing up after Release 2.19, Hevo automatically uses the Unique Incrementing Append Only (UIAO) query mode for the objects that contain a unique column. For the others, it ingests data using the Full Load query mode.

• For Pipelines configured with the Table ingestion mode:

  1. On the Select Objects page, select the objects you want to replicate and click CONTINUE.

     

     Note: Each object represents a table in your database.

  2. On the Configure Objects page, specify the query mode you want to use for each selected object.

     

• For Pipelines configured with the Custom SQL ingestion mode:

  1. On the Provide Query Settings page, enter the custom SQL query to fetch data from the Source.

  2. In the Query Mode drop-down, select the query mode, and click CONTINUE.

     

Data Replication

Note: The custom frequency must be set in hours as an integer value. For example, 1, 2, or 3 but not 1.5 or 1.75.

• Historical Data: In the first run of the Pipeline, Hevo ingests all available data for the selected objects from your Amazon Redshift database.
• Incremental Data: Once the historical load is complete, data is ingested as per the ingestion frequency.