SQL Server Change Tracking (Edge)

Prerequisites

• The SQL Server is running.

• The SQL Server version is 2022 or higher.

• TCP/IP Protocol is enabled for your SQL Server instance.

• Change Tracking is enabled for the database and table.

• Hevo’s IP address(es) for your region is added to the SQL Server database IP Allowlist.

• The SELECT and VIEW CHANGE TRACKING privileges are granted to the database user.

  Note: We recommend that you create a database user for configuring your SQL Server Change Tracking Source in Hevo. However, if you already have a database user, grant the required privileges.

Perform the following steps to configure your SQL Server Change Tracking Source:

Enable TCP/IP Protocol (Optional)

Enable Change Tracking

The Change Tracking mechanism captures changes made to a database. To enable change tracking, connect to your SQL Server database as a user with ALTER DATABASE privilege using any SQL client tool, such as sqlcmd, and enter the following commands:

Note: Replace the placeholder values in the commands below with your own. For example, <database_name> with demo.

1. Enable change tracking at the database level:

   ALTER DATABASE <database_name> SET CHANGE_TRACKING = ON 
   (CHANGE_RETENTION = 3 DAYS, AUTO_CLEANUP = ON)
   

   The CHANGE_RETENTION value specifies the duration for which change tracking information is retained. You can use AUTO_CLEANUP to enable or disable the cleanup task that removes old change tracking information. Read Enable Change Tracking for a Database.

   Note: Hevo recommends that you set the CHANGE_RETENTION value to 3 DAYS. This reduces the risk of log expiry in the case of Pipelines having a low sync frequency, for example, 24 hours.

2. Enable change tracking for each table that you want to replicate:

   ALTER TABLE <schema_name>.<table_name> ENABLE CHANGE_TRACKING
   

   Repeat this step for each table you want to replicate using Change Tracking. Read Enable Change Tracking for a Table.

   Note: If the Schema Evolution Policy for your Pipeline is set to Allow all changes and a new table is created in your schema after the Pipeline creation, you need to enable change tracking for the new table. After that, refresh the schema so that Hevo can begin ingesting data from it. If change tracking is not enabled, Hevo marks the object as inaccessible and does not ingest any data from it.

Allowlist Hevo IP addresses for your region

You need to allowlist the Hevo IP addresses for your region to enable Hevo to connect to your SQL Server database. To do this:

1. Press Windows + R to open the Run dialog box.

2. Type wf.msc in the Open field and click OK.

   

3. In the window that appears, click Inbound Rules in the left pane, and then click New Rule in the right pane.

   

4. In the Rule Type dialog, select Custom and click Next.

   

5. In the Program dialog, select This program path, specify the path to the sqlservr.exe file, and then click Next.

   Note: The sqlservr.exe file is generally found in the %ProgramFiles%\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Binn\ folder.

   

6. In the Protocol and Ports dialog, do the following:

   

   1. Select TCP from the Protocol type drop-down.

   2. Select Specific Ports from the Local port drop-down.

   3. Specify the port on which your SQL Server listens for connections.

      Note: By default, SQL Server listens for connections on port 1433.

   4. Click Next.

7. In the Scope dialog, select These IP addresses and click Add.

   

8. In the IP Address window that appears, enter Hevo’s IP address for your region and click OK.

   

9. (Optional) Click Add and repeat step 8 to allowlist all the IP addresses for your region.

10. Click Next.

11. In the Action dialog, ensure that Allow the connection is selected and click Next.

    

12. In the Profile dialog, ensure that all the check boxes are selected and click Next.

    

13. In the Name dialog, specify a name for your inbound rule and click Finish.

    

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the user

The database user for Hevo requires the following privileges to connect to and ingest data from your SQL Server database:

Connect to your SQL Server database as an admin user with any SQL client tool, such as sqlcmd, and run the following script:

# Grant SELECT privilege at the database level
GRANT SELECT ON DATABASE::<database_name> TO <database_username>;

# Grant SELECT privilege at the schema level
GRANT SELECT ON SCHEMA::<schema_name> TO <database_username>;

# Grant VIEW CHANGE TRACKING privilege at the schema level
GRANT VIEW CHANGE TRACKING ON SCHEMA::<schema_name> TO <database_username>;

# Grant VIEW CHANGE TRACKING privilege at the table level
GRANT VIEW CHANGE TRACKING ON OBJECT::<schema_name>.<table_name> TO <database_username>;

Note: Replace the placeholder values in the commands above with your own. For example, <database_username> with hevo.