Prerequisites

• The Azure SQL Server version is 2022 or higher.

• Change Tracking is enabled for the database and table.

• The public endpoint is enabled for the SQL Managed Instance.

• Hevo’s IP address(es) for your region is added to the Azure 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 Azure SQL Server Change Tracking Source in Hevo. If you already have a database user, grant the required privileges.

• The database hostname and port number of the SQL managed instance are available.

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

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.

Enable Public Endpoint for SQL Managed Instance

You need to enable the public endpoint for your SQL managed instance to allow connections to your Azure SQL Server database over the internet. To keep your database secure, you can control access by allowlisting only trusted IP addresses, such as Hevo’s. This ensures that only authorized connections can access your database.

Perform the following steps to enable the public endpoint for your SQL managed instance:

1. Log in to the Azure Portal.

2. Under Azure services, select More services.

   

3. On the All services page, search and select All resources.

   

4. On the All resources page, click the database you want to connect to Hevo.

   

5. In the right pane of <Your Database Name> page, Essentials section, click the link text under Managed instance.

   

6. In the left navigation pane, click Security, and then click Networking.

   

7. On the Networking page, enable the Public endpoint (data), and then click Save.

   

   After saving the changes, the status of Public endpoint (data) displays as Enable.

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 Azure SQL Server database. This can be done by creating inbound security rules in the network security group associated with your SQL managed instance.

Perform the following steps to allowlist the Hevo IP addresses for your region:

1. Log in to the Azure Portal.

2. Under Azure services, select More services.

   

3. On the All services page, search and select All resources.

   

4. On the All resources page, click the database you want to connect to Hevo.

   

5. In the right pane of <Your Database Name> page, Essentials section, click the link text under Managed instance.

   

6. In the right pane of <Your SQL managed instance Name> page, Essentials section, click the link text under Virtual network / subnet.

   

7. In the left navigation pane, click Subnets.

   

8. On the Subnets page, click the link text under Security group column corresponding to your SQL managed instance.

   

9. In the left navigation pane, click Settings, and then click Inbound security rules.

   

10. On the Inbound security rules page, click +Add.

11. In the Add inbound security rule slide-in page, do the following:

    

    1. Source: Select IP Addresses from the drop-down.

    2. Source IP addresses/CIDR ranges: Enter Hevo’s IP addresses for your region. You can enter multiple addresses, separating them with commas.

    3. Destination port ranges: Enter 3342.

    4. Protocol: Select TCP.

    5. Priority: Enter a priority number between 100 and 4095 as per your requirement.

       Azure processes security rules in order of priority, with lower numbers taking precedence over higher numbers.

       Note: Ensure that the security rule for allowlisting Hevo’s IP addresses has a higher priority than any existing restrictive rules, such as the deny_all_inbound rule. This ensures that connection to Hevo is allowed before any restrictive rules are applied.

    6. Name: Specify your rule name.

    7. Click Add to save the rule.

You can now view the new security rule on the Inbound security rules page of your network security group.

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 Azure SQL Server database:

Connect to your Azure SQL Server database as a masteruser 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.

Retrieve the Database Hostname and Port Number (Optional)