Generic MS SQL Server

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.

• SELECT privileges are granted to the database user.

• VIEW CHANGE TRACKING, and ALTER DATABASE privileges are granted, if Pipeline mode is Logical Replication.

Enabling 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:

Step 1. Enable the TCP/IP Port

1. Open the SQL Server Configuration Manager.

2. In the left navigation pane under SQL Server Network Configuration, click Protocols for <MS SQL Server Instance Name>. The default instance name is MSSQLSERVER.

   

3. In the right pane, right click the TCP/IP Protocol Name, and select Enable (if not enabled already) in the Status field.

   

4. Click OK to acknowledge the dialogue box that warns you to restart the services for the changes to take effect.

Step 2. Verify the TCP/IP Port

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

1. Right-click the TCP/IP Protocol Name, and select Properties.

   

2. In the TCP/IP Properties window, click the IP Addresses tab.

3. In the IPAII section, ensure the TCP Port value is 1433, which is the default port for MS SQL Server.

   

4. Click OK to acknowledge the dialogue box that warns you to restart the services for the changes to take effect.

5. Click OK, and exit the TCP/IP Properties window.

Step 3. Restart MS SQL Server Instance

1. In the left navigation pane, click SQL Server Services.

2. In the right pane, right click your < MS SQL Server Instance Name >, and select Restart.

   

Enabling Change Tracking for Logical Replication

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 for Logical Replication, 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;

Granting Privileges to a User

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

• SELECT
• VIEW CHANGE TRACKING (If Query Mode is Change Tracking)

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

• Grant SELECT privilege:
  GRANT SELECT ON <schema_name>.<table_name> TO <username>;

• Grant VIEW CHANGE TRACKING privilege, if Query Mode is Change Tracking:
  GRANT VIEW CHANGE TRACKING ON <schema_name>.<table_name> TO <username>;

Limitations

None