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.

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

• SELECT privileges are granted to the database 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

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.

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.

3. Restart the 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.

   

---

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;
  

---

Grant Privileges to the User

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

• SELECT

• VIEW CHANGE TRACKING (If Pipeline 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 Pipeline Mode is Change Tracking:

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

---

Limitations

None.

---

Revision History

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

Date	Release	Description of Change
08-Mar-2021	1.58	Replaced references to Logical Replication with Change Tracking as Change Tracking is a distinct Pipeline mode for SQL Server Source types.