Generic SQL Server

Last updated on Mar 05, 2024

Microsoft SQL (MS SQL) Server is a popular Relational Database Management System (RDBMS) used by small and large businesses. It can store and retrieve data as requested by software applications.

You can ingest data from your MS SQL Server using Hevo Pipelines and replicate it to a warehouse of your choice.


Connecting to a Local Database

Refer to this section for steps to connect to your local database. For detailed information and troubleshooting help, read Connecting to a LocalHost Database.

Prerequisites

  • MY-SQL/MS-SQL service is running on your local machine.

  • Data to be loaded to the Destination is available in your MY-SQL/MS-SQL database.

  • You have an account on ngrok and an installed ngrok utility on your local machine. To run ngrok on your local machine, follow these one-time steps:

    1. Extract the ngrok utility:

      • On Linux or MacOS, unzip ngrok from a terminal:

        unzip /path/to/ngrok.zip
        
      • On Windows, double-click ngrok.zip to extract it.

    2. Authenticate ngrok in your local machine:

      ./ngrok authtoken <your_auth_token>
      

      You can get the auth token from your ngrok dashboard. For example, in the image below, the auth_token starts with 1t7uGaJshUvnh2Fg1MApl.

      Auth token

Connect to the local database

Perform the following steps to connect to the local database:

  1. Log in to your database server.

  2. Start a TCP tunnel forwarding to your database port.

    ./ngrok tcp <your_database_port>
    

    For example, the port address for MySQL is 3306. Therefore, the command would be:

    ./ngrok tcp 3306
    
  3. Copy the public IP address (hostname and port number) for your local database and port. For example, in the image below, 8.tcp.ngrok.io is the database hostname and 19789 is the port number.

    Getting hostname and port

  4. Paste the hostname and port number into the Database Host and Database Port fields respectively.

    MySQL settings

  5. Specify all other settings and click TEST & CONTINUE.


Limitations

  • Hevo does not support data replication from temporary tables and views.

Revision History

Refer to the following table for the list of key updates made to this page:

Date Release Description of Change
Mar-05-2024 2.21 Added the Data Replication section.
Feb-27-2024 NA Updated sections, Enable Change Tracking, Create a database user, and Grant privileges to the user as per the latest Hevo functionality.
Jan-15-2024 NA Updated section, Limitations to add information about Hevo not supporting data replication from certain tables.
Jan-10-2024 2.19 Updated section, Object and Query Mode Settings as per the latest Hevo functionality.
Nov-03-2023 NA Added section, Object and Query Mode Settings.
Oct-27-2023 NA Added subsection, Create a database user.
Apr-21-2023 NA Updated section, Specify Generic SQL Server Connection Settings to add a note to inform users that all loaded Events are billable for Custom SQL mode-based Pipelines.
Mar-09-2023 2.09 Updated section, Specify Generic SQL Server Connection Settings to mention about SEE MORE in the Select an Ingestion Mode section.
Dec-19-2022 2.04 Updated section, Specify Generic SQL Server Connection Settings to add information that you must specify all fields to create a Pipeline.
Dec-07-2022 2.03 Updated section, Specify Generic SQL Server Connection Settings to mention about including skipped objects post-Pipeline creation.
Dec-07-2022 2.03 Updated section, Specify Generic SQL Server Connection Settings to mention about the connectivity checker.
Apr-21-2022 1.86 Updated section, Specify Generic SQL Server Connection Settings.
Sep-08-2021 NA - Updated the fourth list item under Prerequisites
- Updated sub-section Grant privileges to the user.
Jul-26-2021 1.68 Added a note for the SQL Server Host field.
Jul-12-2021 1.67 Added sections:
- Specify Generic MS SQL Connection Settings
- Connecting to a Local Database.
Mar-09-2021 1.58 Replaced references to Logical Replication with Change Tracking as Change Tracking is a distinct Pipeline mode for SQL Server Source types.

Tell us what went wrong