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 as a masteruser, 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 the CHANGE_RETENTION value 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.

3. At the table level:

    ```sql
    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 SQL Server database as a masteruser 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:

      EXEC msdb.dbo.gcloudsql_cdc_enable_db '<database_name>';
      

   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 using CDC. Read Enable Table CDC for more information on the available options.

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

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

1. Connect to your Google Cloud SQL Server database as a masteruser 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 your database user to replicate data from Change Tracking–enabled tables, the following privileges are required:

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

Connect to your Google Cloud SQL Server database as a masteruser 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 your database user to replicate data from CDC-enabled tables, perform the following steps:

1. Connect to your Google Cloud SQL Server database as a masteruser 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, __ 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 required to create your Hevo Pipeline:

1. Retrieve the database hostname

1. Log in to the Google Cloud SQL Console.

2. Locate the database hostname of the master instance under the Public IP address column.

   

Note: Google Cloud SQL always uses the default port number, 1433, for the SQL Server instance.

2. Retrieve the database names

1. Log in to the Google Cloud SQL Console and click the Instance ID that you want to connect to Hevo.

   

2. In the left navigation pane, click Databases.

   

On the Databases page, you can locate the name of your database under the Name column.