You must enable change tracking on your SQL Server database to replicate incremental data.

Perform the following steps to do this:

1. Connect to your Azure SQL Server database as any user with the ALTER DATABASE privilege or as an admin user, using an SQL client tool such as sqlcmd.

2. Run the following commands to enable change tracking:

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

   1. At the database level:

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

      The CHANGE_RETENTION value specifies the duration for which change tracking information is retained. When the AUTO_CLEANUP value is set to ON, SQL Server runs a cleanup task to remove lapsed or invalid change tracking metadata. Read Enable Change Tracking for a Database for more information on the available options.

      Note: Hevo recommends setting CHANGE_RETENTION to 7 DAYS. This reduces the risk of log expiry and missed data changes for Pipelines with a low sync frequency, such as 24 hours.

   2. At the table level:

      ALTER TABLE <schema_name>.<table_name> ENABLE CHANGE_TRACKING
      

      Run the command given above for each table that you want to replicate. Read Enable Change Tracking for a Table for more information on the available options.

You must enable change data capture on your SQL Server database to replicate incremental data.

Perform the following steps to do this:

1. Connect to your Azure SQL Server database as an admin user with an SQL client tool, such as sqlcmd.

2. Run the following commands to enable change data capture:

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

   1. At the database level:

      USE <database_name>;
      EXEC sys.sp_cdc_enable_db;
      

   2. At the table level:

      USE <database_name>;
      
      --Begin tracking a table
      EXEC sys.sp_cdc_enable_table
          @source_schema          = N'<schema_name>',
          @source_name            = N'<table_name>',
          @role_name              = NULL;
      

      Run the command given above for each table that you want to replicate. Read Tracking tables with change data capture for more information on the available options.

   3. Run the following command to set the retention duration:

       USE <database_name>;
      
       -- Set the retention period to 10080 minutes (7 days)
       EXEC sys.sp_cdc_change_job
           @job_type = N'cleanup'
       ,   @retention = 10080;  -- value in minutes.
      

      The retention value specifies for how long (in minutes) the captured data changes are retained in the CDC change tables.

      Note: Hevo recommends setting the retention period to 10080 minutes. This reduces the risk of missed data changes for Pipelines with a low sync frequency, such as 24 hours.

Detecting schema changes for CDC-enabled tables

Hevo displays a warning in the user interface (UI) when the schemas of a CDC-enabled Source table and the associated change table differ. This typically happens when columns are added or modified in a Source table that is already included in the Pipeline.

To allow Hevo to ingest data from these columns, re-enable CDC for the affected table. Re-enabling CDC recreates the change table with the updated schema. To do this, perform the following steps:

Note: Hevo provides a stored procedure to automatically re-enable CDC when schema changes are detected. Using this procedure removes the need for manual CDC reconfiguration. Refer to Hevo Stored Procedures for Change Tracking and Change Data Capture for the installation steps.

1. Connect to your SQL Server database as an admin user with an SQL client tool, such as sqlcmd.

2. Run the following commands to re-enable CDC for the table included in your Hevo Pipeline:

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

   USE <database_name>; -- Switch context to the database with the changed schema
   
   -- Stop tracking CDC for the affected table
   -- This removes the existing capture instance and its change history.
   EXEC sys.sp_cdc_disable_table
       @source_schema      = N'<schema_name>',
       @source_name        = N'<affected_table_name>',
       @capture_instance   = N'all';
   
   -- Start tracking CDC for the affected table
   -- This creates a new capture instance that includes the updated columns
   EXEC sys.sp_cdc_enable_table
       @source_schema      = N'<schema_name>',   
       @source_name        = N'<affected_table_name>',   
       @role_name          = NULL;
   

Once CDC is successfully re-enabled for the affected table, Hevo does the following:

• Updates the Source schema in its metastore.

• Resyncs the object in the next Pipeline run.

If you want Hevo to ingest data immediately, Resync the Objects. If re-enabling CDC is unsuccessful, the Pipeline continues ingesting data from the affected tables using the previously stored schema.

Perform the following steps to create the firewall rules in your SQL database network settings 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 for and select All resources.

   

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

   

5. In the right pane of <Your Database Name> page, click Set server firewall.

   

6. On the Networking page, under the Public access tab, select the Selected networks option.

   

7. Under Firewall rules, click + Add a firewall rule.

   

8. In the Add a firewall rule dialog box, do the following to add your firewall rule:

   

   1. Specify the following:

      • Rule name: A name to identify the rule. For example, HevoIndia.

      • Start IP: The starting address of the IP range.

      • End IP: The ending address of the IP range.

      Note: As Hevo has specific IP addresses and not a range, the value in the Start IP and End IP fields is the same. For example, 13.235.131.126 for the India region.

   2. Click OK.

9. Repeat steps 7 and 8 to add all the IP addresses for your Hevo region.

10. Click Save to save the firewall rules.

    

You need to enable the public endpoint for your Azure SQL managed instance to allow connections to your SQL managed database over the internet. To keep your database secure, you can control access by allowlisting only trusted IP addresses, such as Hevo’s. This is done by creating inbound security rules in the network security group associated with your SQL managed instance. These rules ensure that only authorized connections can access your database.

1. Enable Public Endpoint for Azure SQL Managed Instance

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 for and select All resources.

   

4. On the All resources page, click the SQL managed 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.

2. Create Inbound Security Rules for Azure SQL Managed Instance

Perform the following steps to create inbound security rules in the network security group associated with 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 for and select All resources.

   

4. On the All resources page, click the SQL managed 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.

Note: Skip to the Grant privileges to the user section if you are using an existing database user.

Perform the following steps to create a database user in your Azure SQL Server database:

1. Connect to your Azure SQL Server database as an admin user with any SQL client tool, such as sqlcmd.

2. Run the following commands:

   -- Select the master database to create a login
   USE master;
   CREATE LOGIN <login_user> WITH PASSWORD = '<password>';
   
   -- Add a database user to your database for the login created above
   USE <database_name>;
   CREATE USER <database_user> FOR LOGIN <login_user>;
   

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

To enable the database user to replicate data from Change Tracking–enabled tables, the following privileges are required:

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

-- 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.

To enable the database user to replicate data from CDC-enabled tables, perform the following steps:

1. Connect to your Azure SQL Server database as an admin user with any SQL client tool, such as sqlcmd.

2. Run the following commands:

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

   USE <database_name>;
   
   -- Grant SELECT privilege at the schema level
   GRANT SELECT ON SCHEMA::<schema_name> TO <database_user>;
   
   -- Optionally, grant read access to the CDC schema
   GRANT SELECT ON SCHEMA::cdc TO <database_user>;
   

Note: Hevo recommends using a dedicated, minimum-privilege database user in production Pipelines.

Perform the following steps to obtain the configuration details for your Azure SQL database:

1. Log in to the Azure Portal.

2. Under Azure services, select More services.

   

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

   

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

   

5. In the right pane of <Your Database Name> page, hover on the Server name and click the copy icon to copy it. Use this as the Database Host while configuring your Azure SQL Server Source in Hevo.

   

The default port number is 1433.

Perform the following steps to obtain the configuration details for your Azure SQL managed instance:

1. Log in to the Azure Portal.

2. Under Azure services, select More services.

   

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

   

4. On the All resources page, click the SQL managed 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, under Endpoint, click the copy icon.

   

   The endpoint consists of the database host, followed by a comma and the port number. Remove the comma, and then save the database host and port number separately.

   For example, if exampledatabase.abc123xyz789.database.windows.net,3342 is the endpoint, use exampledatabase.abc123xyz789.database.windows.net as the database host and 3342 as the port number while configuring your Azure SQL Server Source in Hevo.