Perform the following steps to enable TCP/IP protocol for your SQL Server instance:

1. Enable the TCP/IP port

1. Open the SQL Server Configuration Manager.

2. In the left pane, click SQL Server Network Configuration, and then double-click Protocols for <Your SQL Server Instance Name>. The default instance names are MSSQLSERVER and MSSQLSERVER01.

   

3. In the right pane, if the Status field of the TCP/IP Protocol Name is Disabled, then perform this step. Else, skip to the Verify the TCP/IP port section.

   1. Right-click TCP/IP and select Enable.

      

   2. Click OK to acknowledge the dialog box that warns you to restart the services for the changes to take effect.

2. Verify the TCP/IP port

Perform the following 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 dialog, click the IP Addresses tab.

3. Scroll down to the IPAll section and note the TCP Port for your SQL Server instance.

   

4. Click OK.

3. Restart the SQL Server instance

1. In the left pane, click SQL Server Services.

2. In the right pane, right-click the SQL Server (<Your SQL Server Instance Name>) and select Restart.

   

You have successfully enabled TCP/IP for your SQL Server instance.

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 SQL Server database as any user with the ALTER DATABASE privilege or 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.

Once change tracking is enabled at both the database and table levels, SQL Server begins tracking data changes for the configured tables, allowing Hevo to capture these incremental updates.

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

   4. Run the following command to update the polling interval of the CDC capture job:

       USE <database_name>;
          
       -- Set the polling interval to 5 seconds
       EXEC sys.sp_cdc_change_job
           @job_type = 'capture',
           @pollinginterval = 5;
      

      The pollinginterval value defines the duration (in seconds) the CDC process waits before reading the database transaction log to capture data changes. If the polling interval is set to a value close to or higher than the Pipeline sync frequency, the transaction logs may expire before CDC captures the data changes. As a result, Hevo may not capture some incremental changes, which can lead to potential data inconsistencies between the Source and the Destination.

      Note: Hevo recommends setting the pollinginterval to 5 seconds to minimize the risk of missing changes, especially in high-traffic production environments.

   5. Restart the CDC capture job for the changes to take effect:

      USE <database_name>;
          
      -- stop the CDC capture job
      EXEC sys.sp_cdc_stop_job @job_type = N'capture';
          
      -- start the CDC capture job
      EXEC sys.sp_cdc_start_job @job_type = N'capture';
      

Once the CDC capture job is restarted, change data capture is enabled for your database and tables. Hevo can then begin capturing incremental changes for the configured tables.

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

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

1. Connect to your 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 CT–enabled tables, the following privileges are required:

Connect to your 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 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.