Hevo Stored Procedures for Change Tracking and Change Data Capture

Hevo replicates data from SQL Server using its built-in Change Tracking (CT) and Change Data Capture (CDC) features. At least one of these data tracking mechanisms must be enabled for each new table added to the Source database. If neither CT nor CDC is enabled for a table, Hevo cannot ingest data from that table, and the corresponding object is marked as inaccessible in the Pipeline.

For CDC-enabled tables, if a schema change is detected, CDC must be re-enabled for the affected tables before Hevo can perform a historical sync. Until CDC is re-enabled, Hevo continues ingesting data using the schema stored in its metastore. As a result, newly added or modified columns are not included for ingestion during this period.

To address these limitations, Hevo provides stored procedures that can be installed in your Source database to automatically enable CT, CDC, or both, and to update the CDC schema.

Benefits of installing the stored procedures

• Maintains accessibility for new tables in the Pipeline.

• Supports a large number of tables without manual setup.

• Reduces manual intervention and CDC or CT configuration time.

• Continues data ingestion with existing settings if automation fails, preventing Pipeline failures.

Enabling CT and CDC Using Stored Procedures

Hevo provides stored procedures that help you automatically enable Change Tracking (CT) and Change Data Capture (CDC), and refresh the CDC capture instance when schema changes occur.

These procedures simplify setup and reduce manual intervention when working with SQL Server Sources.

• sp_hevo_enable_cdc: Enables CDC for one or more tables. Use this when your Pipeline uses CDC as the ingestion mode.

• sp_hevo_enable_change_tracking: Enables CT for one or more tables. Use this when your Pipeline uses CT as the ingestion mode.

• sp_hevo_update_cdc: Refreshes the CDC capture instance when schema changes are detected. Use this when a CDC-enabled table has schema changes and you want to include newly added or modified columns for ingestion.

Ingestion Mode Mapping

The following table describes how Hevo enables CT or CDC for new tables in the Source database:

Note:

• CT or CDC must be enabled at the database level.

• When both modes are enabled, CDC takes precedence over CT.

Enable Change Data Capture

USE SampleRetailDB;
GO

EXEC dbo.sp_hevo_enable_cdc 
    @SchemaName = 'dbo', 
    @TableList = 'customers,orders,products', 
    @HevoUser = NULL;
GO

Enable Change Tracking

USE SampleRetailDB;
GO
  
EXEC dbo.sp_hevo_enable_change_tracking 
    @SchemaName = 'dbo', 
    @TableList = 'customers,orders', 
    @HevoUser = 'hevo_user';
GO

Update Change Data Capture Schema

USE SampleRetailDB;
GO

EXEC dbo.sp_hevo_update_cdc
    @SchemaName = 'dbo',
    @TableName = 'customers',
    @CaptureInstanceName = 'dbo_customers_CT', -- The CDC change table for customers
    @ColumnList = 'id,name,email,updated_at', -- The column names to capture
    @HevoUser = NULL;
GO

System Behavior with Stored Procedures Installed

Installing the Stored Procedures

You can install all stored procedures provided by Hevo or only those required for your setup. The selection depends on the Schema Evolution Policy configured in the Pipeline.

The following table summarizes the behavior:

Prerequisites

• The SQL Server version for the Source database is 2016, 2017, 2019, or 2022.

• At least one of CT or CDC is enabled at the database level. Refer to the Enable Data Replication Modes section in your SQL Server variant for the steps.

• You have the sysadmin or db_owner role on the Source database to install the stored procedures.

1. Install the Stored Procedures in your Source database

1. Connect to your SQL Server database using an SQL client as a user with admin privileges.

2. Copy the following script into your SQL client:

   Note: Replace all placeholder values in the script below with your own. For example, [YourDatabaseName] with SampleRetailDB.

   -- =============================================
   -- Hevo Stored Procedures for SQL Server CDC/CT Management
   -- This script creates three stored procedures to automate CDC/CT enablement
   -- and schema change repair for Hevo data ingestion.
   --
   -- Prerequisites:
   -- - SQL Server version for the Source database is 2016, 2017, 2019, or 2022
   -- - Must be executed by a user with sysadmin or db_owner role
   -- - CDC/CT enabled on database
   --
   -- Security Model:
   -- - Procedures use WITH EXECUTE AS OWNER
   -- - They run with the creator's permissions (typically dbo)
   -- - Hevo user only needs EXECUTE permission on these procedures
   --
   -- Usage:
   -- 1. Connect to your database as a DATABASE ADMINISTRATOR
   -- 2. Execute this entire script
   -- 3. Grant EXECUTE permission to Hevo user (see end of script)
   -- 4. Configure Hevo connector with: sqlserver_enable_hevo_stored_procedures=true
   -- =============================================
       
   USE [YourDatabaseName]; -- Replace with your database name
   GO
       
   -- =============================================
   -- Procedure: sp_hevo_enable_cdc
   -- Description: Enables CDC on a batch of tables
   -- Parameters:
   --   @SchemaName: Schema name (e.g., 'dbo')
   --   @TableList: Comma-separated list of table names
   --   @HevoUser: Hevo database user name (for role assignment)
   -- Returns: Result set with TableName and Result columns
   -- =============================================
       
   CREATE OR ALTER PROCEDURE dbo.sp_hevo_enable_cdc
       @SchemaName NVARCHAR(128),
       @TableList NVARCHAR(MAX),
       @HevoUser NVARCHAR(128)
   WITH EXECUTE AS OWNER
   AS
   BEGIN
       SET NOCOUNT ON;
           
       -- Create temp table for results
       CREATE TABLE #Results (
           TableName NVARCHAR(128),
           Result NVARCHAR(500)
       );
           
       -- Variables
       DECLARE @TableName NVARCHAR(128);
       DECLARE @SQL NVARCHAR(MAX);
       DECLARE @ErrorMessage NVARCHAR(500);
           
       -- Split comma-separated table list
       DECLARE @Tables TABLE (TableName NVARCHAR(128));
       INSERT INTO @Tables (TableName)
       SELECT LTRIM(RTRIM(value))
       FROM STRING_SPLIT(@TableList, ',');
           
       -- Process each table
       DECLARE table_cursor CURSOR FOR
       SELECT TableName FROM @Tables;
       
       OPEN table_cursor;
       FETCH NEXT FROM table_cursor INTO @TableName;
           
       WHILE @@FETCH_STATUS = 0
       BEGIN
           BEGIN TRY
               -- Check if CDC is already enabled
               IF EXISTS (
                   SELECT 1
                   FROM cdc.change_tables ct
                   INNER JOIN sys.tables t ON ct.source_object_id = t.object_id
                   INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                   WHERE s.name = @SchemaName AND t.name = @TableName
               )
               BEGIN
                   INSERT INTO #Results VALUES (@TableName, 'INFO: CDC already enabled');
               END
               ELSE
               BEGIN
                   -- Enable CDC on table
                   -- Note: @supports_net_changes = 0 because:
                   --   - Hevo uses fn_cdc_get_all_changes_* (all changes), not fn_cdc_get_net_changes_* (net changes)
                   --   - Setting to 1 creates an additional non-clustered index on change table
                   --   - This index degrades CDC write performance with no benefit for Hevo
                   EXEC sys.sp_cdc_enable_table
                       @source_schema = @SchemaName,
                       @source_name = @TableName,
                       @role_name = @HevoUser,     -- set this to NULL if you have not created a role explicitly for CDC
                       @supports_net_changes = 0;
                           
                   INSERT INTO #Results VALUES (@TableName, 'SUCCESS: CDC enabled');
               END
           END TRY
           BEGIN CATCH
               SET @ErrorMessage = ERROR_MESSAGE();
               INSERT INTO #Results VALUES (@TableName, 'FAILED: ' + @ErrorMessage);
           END CATCH
               
           FETCH NEXT FROM table_cursor INTO @TableName;
       END
       
       CLOSE table_cursor;
       DEALLOCATE table_cursor;
       
       -- Return results
       SELECT TableName, Result FROM #Results;
       
       DROP TABLE #Results;
   END
   GO
       
   -- =============================================
   -- Procedure: sp_hevo_enable_change_tracking
   -- Description: Enables Change Tracking on a batch of tables
   -- Parameters:
   --   @SchemaName: Schema name (e.g., 'dbo')
   --   @TableList: Comma-separated list of table names
   --   @HevoUser: Hevo database user name (for permission grant)
   -- Returns: Result set with TableName and Result columns
   -- =============================================
       
   CREATE OR ALTER PROCEDURE dbo.sp_hevo_enable_change_tracking
       @SchemaName NVARCHAR(128),
       @TableList NVARCHAR(MAX),
       @HevoUser NVARCHAR(128)
   WITH EXECUTE AS OWNER
   AS
   BEGIN
       SET NOCOUNT ON;
       -- Create temp table for results
       CREATE TABLE #Results (
           TableName NVARCHAR(128),
           Result NVARCHAR(500)
       );
       
       -- Variables
       DECLARE @TableName NVARCHAR(128);
       DECLARE @SQL NVARCHAR(MAX);
       DECLARE @FullTableName NVARCHAR(300);
       DECLARE @ErrorMessage NVARCHAR(500);
           
       -- Split comma-separated table list
       DECLARE @Tables TABLE (TableName NVARCHAR(128));
       INSERT INTO @Tables (TableName)
       SELECT LTRIM(RTRIM(value))
       FROM STRING_SPLIT(@TableList, ',');
       
       -- Process each table
       DECLARE table_cursor CURSOR FOR
       SELECT TableName FROM @Tables;
       
       OPEN table_cursor;
       FETCH NEXT FROM table_cursor INTO @TableName;
           
       WHILE @@FETCH_STATUS = 0
       BEGIN
           BEGIN TRY
               SET @FullTableName = QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
                   
               -- Check if CT is already enabled
               IF EXISTS (
                   SELECT 1
                   FROM sys.change_tracking_tables ctt
                   INNER JOIN sys.tables t ON ctt.object_id = t.object_id
                   INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                       WHERE s.name = @SchemaName AND t.name = @TableName
               )
               BEGIN
                   INSERT INTO #Results VALUES (@TableName, 'INFO: CT already enabled');
               END
               ELSE
               BEGIN
                   -- Enable CT on table
                   SET @SQL = 'ALTER TABLE ' + @FullTableName + ' ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF);';
                   EXEC sp_executesql @SQL;
       
                   -- Grant VIEW CHANGE TRACKING permission
                   SET @SQL = 'GRANT VIEW CHANGE TRACKING ON ' + @FullTableName + ' TO ' + QUOTENAME(@HevoUser) + ';';
                   EXEC sp_executesql @SQL;
       
                   INSERT INTO #Results VALUES (@TableName, 'SUCCESS: CT enabled');
               END
           END TRY
           BEGIN CATCH
               SET @ErrorMessage = ERROR_MESSAGE();
               INSERT INTO #Results VALUES (@TableName, 'FAILED: ' + @ErrorMessage);
           END CATCH
               
           FETCH NEXT FROM table_cursor INTO @TableName;
       END
           
       CLOSE table_cursor;
       DEALLOCATE table_cursor;
           
       -- Return results
       SELECT TableName, Result FROM #Results;
         
       DROP TABLE #Results;
   END
   GO
       
   -- =============================================
   -- Procedure: sp_hevo_update_cdc
   -- Description: Updates CDC capture instance with new column list
   --              (disables and re-enables CDC to fix schema mismatches)
   -- Parameters:
   --   @SchemaName: Schema name (e.g., 'dbo')
   --   @TableName: Single table name
   --   @CaptureInstance: CDC capture instance name (usually schemaName_tableName)
   --   @ColumnList: Comma-separated list of column names to capture
   --   @HevoUser: Hevo database user name (for role assignment)
   -- Returns: Result set with TableName and Result columns
   -- =============================================
   CREATE OR ALTER PROCEDURE dbo.sp_hevo_update_cdc
       @SchemaName NVARCHAR(128),
       @TableName NVARCHAR(128),
       @CaptureInstance NVARCHAR(128),
       @ColumnList NVARCHAR(MAX),
       @HevoUser NVARCHAR(128)
   WITH EXECUTE AS OWNER
   AS
   BEGIN
       SET NOCOUNT ON;
       
       -- Create temp table for results
       CREATE TABLE #Results (
           TableName NVARCHAR(128),
           Result NVARCHAR(500)
       );
       
       DECLARE @ErrorMessage NVARCHAR(500);
       DECLARE @NetChangesSupported BIT = 0; -- Default: Hevo doesn't need net changes
       
       BEGIN TRY
           -- Check if CDC capture instance exists
           IF NOT EXISTS (
               SELECT 1
               FROM cdc.change_tables
               WHERE capture_instance = @CaptureInstance
           )
           BEGIN
               INSERT INTO #Results VALUES (@TableName, 'FAILED: CDC instance not found - ' + @CaptureInstance);
           END
           ELSE
           BEGIN
               -- Check if net changes were supported in old instance
               SELECT @NetChangesSupported = supports_net_changes
               FROM cdc.change_tables
               WHERE capture_instance = @CaptureInstance;
       
               -- Step 1: Disable CDC
               EXEC sys.sp_cdc_disable_table
                   @source_schema = @SchemaName,
                   @source_name = @TableName,
                   @capture_instance = @CaptureInstance;
       
               -- Step 2: Re-enable CDC with updated column list
               EXEC sys.sp_cdc_enable_table
                   @source_schema = @SchemaName,
                   @source_name = @TableName,
                   @role_name = @HevoUser,     -- set this to NULL if you have not created a role explicitly for CDC
                   @captured_column_list = @ColumnList,
                   @supports_net_changes = @NetChangesSupported;
       
               INSERT INTO #Results VALUES (@TableName, 'SUCCESS: CDC updated with new schema');
           END
       END TRY
       BEGIN CATCH
           SET @ErrorMessage = ERROR_MESSAGE();
           INSERT INTO #Results VALUES (@TableName, 'FAILED: ' + @ErrorMessage);
       END CATCH
       
       -- Return results
       SELECT TableName, Result FROM #Results;
       
       DROP TABLE #Results;
   END
   GO
       
   -- =============================================
   -- Verification: Check if procedures were created successfully
   -- =============================================
   SELECT
       ROUTINE_NAME,
       CREATED,
       LAST_ALTERED
   FROM INFORMATION_SCHEMA.ROUTINES
   WHERE ROUTINE_TYPE = 'PROCEDURE'
       AND ROUTINE_SCHEMA = 'dbo'
       AND ROUTINE_NAME IN (
           'sp_hevo_enable_cdc',
           'sp_hevo_enable_change_tracking',
           'sp_hevo_update_cdc'
       )
   ORDER BY ROUTINE_NAME;
       
   PRINT 'Hevo stored procedures created successfully!';
   PRINT 'Next steps:';
   PRINT 'Grant EXECUTE permission to Hevo user:';
   PRINT 'GRANT EXECUTE ON dbo.sp_hevo_enable_cdc TO [YourHevoUser];';
   PRINT 'GRANT EXECUTE ON dbo.sp_hevo_enable_change_tracking TO [YourHevoUser];';
   PRINT 'GRANT EXECUTE ON dbo.sp_hevo_update_cdc TO [YourHevoUser];';
   

3. Run the entire script in your SQL client.

   Note: In some SQL client tools, you may need to select the entire script before running it to ensure that all commands are executed in sequence.

   If the script runs successfully, you see the following message:

   Hevo stored procedures created successfully!
   

The script also verifies that the stored procedures were installed correctly. You should see an output similar to the one below:

ROUTINE_NAME                  |CREATED                |LAST_ALTERED           |
------------------------------+-----------------------+-----------------------+
sp_hevo_enable_cdc            |2026-02-12 20:09:54.483|2026-02-12 20:09:54.483|
sp_hevo_enable_change_tracking|2026-02-12 20:09:54.790|2026-02-12 20:09:54.790|
sp_hevo_update_cdc            |2026-02-12 20:09:55.090|2026-02-12 20:09:55.090|

2. Grant Permission on the Stored Procedures

After installing the stored procedures, grant the database user permission to execute them. This allows Hevo to run the procedures to enable CT or CDC for new tables and refresh the CDC capture instance when schema changes occur.

To do this, perform the following steps:

1. Connect to your SQL Server database using an SQL client as a user with admin privileges.

2. Run the following commands to grant the database user configured for Hevo permission to execute the stored procedures:

   Note: The database user must be the same as the one configured in your Pipeline.

   USE [YourDatabaseName];
   GO
       
   GRANT EXECUTE ON dbo.sp_hevo_enable_cdc TO [YourHevoUser];
   GRANT EXECUTE ON dbo.sp_hevo_enable_change_tracking TO [YourHevoUser];
   GRANT EXECUTE ON dbo.sp_hevo_update_cdc TO [YourHevoUser];
   GO
   

Uninstalling the Stored Procedures