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

      Note: 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.rds_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;`
      

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

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

1. Connect to your Amazon RDS 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>';
   
   -- Create a database user in 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 Amazon RDS 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 the database user to replicate data from CDC-enabled tables, perform the following steps:

1. Connect to your Amazon RDS 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, <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.

Note: The RDS hostnames start with your database name and end with rds.amazonaws.com. For example, rds-sql-server-database.xxxxxxxxx.rds.amazonaws.com.

Perform the following steps to obtain the configuration details required to create your Hevo Pipeline:

1. Log in to the Amazon RDS console.

2. In the left navigation pane, click Databases.

3. In the Databases section on the right, click the DB identifier of your Amazon RDS SQL Server database instance.

   

4. In the Connectivity & security tab, do the following:

   

   1. Click the copy icon to copy the Endpoint and save it securely.

   2. Copy the Port and save it securely.

Use these values as your Database Host (Endpoint) and Database Port, respectively, while configuring your Amazon RDS SQL Server Source in Hevo.