Generic SQL Server (Edge)

Generic SQL Server (Edge) Setup Guide

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.

Prerequisites

The MS SQL Server is running.
MS SQL version is 12 or higher.
TCP/IP Protocol is enabled with TCP port as 1433.
VIEW CHANGE TRACKING, and ALTER DATABASE privileges are granted to the database user.
SELECT privileges are granted to the database user.

Note: We recommend that you create a database user for configuring your SQL Server Source in Hevo. However, if you already have one, refer to section Grant privileges to the user.

Perform the following steps to configure your Generic MS SQL Source:

Enable TCP/IP Protocol

You need to configure the MS SQL Server instance with TCP port value 1433 to enable Hevo to connect to your MS SQL Server.

Follow these steps to enable TCP/IP protocol for your MS SQL Server:

1. Enable the TCP/IP port

Open the SQL Server Configuration Manager.
In the left navigation pane under SQL Server Network Configuration, click Protocols for <MS SQL Server Instance Name>. The default instance name is MSSQLSERVER.
In the right pane, right click the TCP/IP Protocol Name, and select Enable (if not enabled already) in the Status field.
Click OK to acknowledge the dialogue box that warns you to restart the services for the changes to take effect.

2. Verify the TCP/IP port

Follow these steps to check the port where TCP/IP is enabled:

Right-click the TCP/IP Protocol Name, and select Properties.
In the TCP/IP Properties window, click the IP Addresses tab.
In the IPAII section, ensure the TCP Port value is 1433, which is the default port for MS SQL Server.
Click OK to acknowledge the dialogue box that warns you to restart the services for the changes to take effect.
Click OK, and exit the TCP/IP Properties window.

3. Restart the MS SQL Server instance

In the left navigation pane, click SQL Server Services.
In the right pane, right-click your < MS SQL Server Instance Name >, and select Restart.

Enable Change Tracking

The Change Tracking mechanism captures changes made to a database. In order to enable, or disable change tracking, the database user must have the ALTER DATABASE privilege.

To enable change tracking, connect your MS SQL Server in your SQL Client tool, and enter these commands:

Enable change tracking at the database level:

ALTER DATABASE [<database>]
SET CHANGE_TRACKING = ON;

Enable change tracking for each table you want to integrate:
```
ALTER TABLE [<schema>].[<table>]
ENABLE CHANGE_TRACKING;
```

Create a Database User and Grant Privileges

1. Create a database user (Optional)

Perform the following steps to create a user in your SQL Server database:

Connect to your SQL Server database as an admin user with an SQL client tool, such as sqlcmd.
Select the database:
```
 USE <database>;
```
Create a login and a database user:
```
 CREATE LOGIN <login_username> WITH PASSWORD = '<login_password>';
 CREATE USER <username> FOR LOGIN <login_username>;
```
Note: Replace the placeholder values in the commands above with your own. For example, <username> with hevo.

2. Grant privileges to the user

The database user specified in the Hevo Pipeline must have the following global privileges:

SELECT
VIEW CHANGE TRACKING

To set up these privileges, connect your MS SQL Server in your SQL Client tool, and enter these commands:

Grant SELECT privilege at the table and schema level:

GRANT SELECT ON <schema_name>.<table_name> TO <username>;

Grant VIEW CHANGE TRACKING privilege:

GRANT VIEW CHANGE TRACKING ON <schema_name>.<table_name> TO <username>;

Specify Generic SQL Server Connection Settings

Perform the following steps to configure your SQL Server Source:

Click Sources in the Navigation Bar.
Click + Add Source in the Sources List View.
On the Add New page, select SQL Server.
In the screen that appears, specify the following:
- Source Name: A unique name for your Source, not exceeding 50 characters. For example, SQL Server.
- In the Connect your SQL Server - Log section:
  - Database Host: SQL Server host’s IP address or DNS.
    
    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.
  - Database Port: The port on which your SQL Server is listening for connections. Default value: 1433.
  - Database User: The read-only user who has the permissions to read tables in your database.
  - Database Password: The password for the read-only user.
  - Database Name: The database that you wish to replicate.
- Additional Settings
  - Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel instead of directly connecting your SQL 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 configure your Source to accept connections from Hevo’s IP addresses.
  - Use SSL: Enable this option to use an SSL-encrypted connection. Specify the following:
    - CA File: The file containing the SSL server certificate authority (CA).
    - Client Certificate: The client’s public key certificate file.
    - Client Key: The client’s private key file.
Click Test & Continue.

Last updated on Oct 23, 2024