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


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.

    Select Protocols for Instance

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

    Enable TCP/IP

  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.

    Select TCP/IP 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.

    Enter TCP Port

  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.

    Restart MS SQL Server Instance


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.
Last updated on 25 Mar 2021