Amazon RDS SQL Server Change Tracking (Edge)

Prerequisites

• The Amazon RDS SQL Server version is 2019 or higher.

• Change Tracking is enabled for the database and table.

• Hevo’s IP address(es) for your region is added to the Amazon RDS SQL Server database IP Allowlist.

• The SELECT and VIEW CHANGE TRACKING privileges are granted to the database user.

  Note: We recommend that you create a database user for configuring your Amazon RDS SQL Server Change Tracking Source in Hevo. However, if you already have a database user, grant the required privileges.

• The database hostname and port number of the Source instance are available.

Perform the following steps to configure your Amazon RDS SQL Server Change Tracking Source:

Enable Change Tracking

The Change Tracking mechanism captures changes made to a database. To enable change tracking, connect to your SQL Server database as a user with ALTER DATABASE privilege using any SQL client tool, such as sqlcmd, and enter the following commands:

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

1. Enable change tracking at the database level:

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

   The CHANGE_RETENTION value specifies the duration for which change tracking information is retained. You can use AUTO_CLEANUP to enable or disable the cleanup task that removes old change tracking information. Read Enable Change Tracking for a Database.

   Note: Hevo recommends that you set the CHANGE_RETENTION value to 3 DAYS. This reduces the risk of log expiry in the case of Pipelines having a low sync frequency, for example, 24 hours.

2. Enable change tracking for each table that you want to replicate:

   ALTER TABLE <schema_name>.<table_name> ENABLE CHANGE_TRACKING
   

   Repeat this step for each table you want to replicate using Change Tracking. Read Enable Change Tracking for a Table.

   Note: If the Schema Evolution Policy for your Pipeline is set to Allow all changes and a new table is created in your schema after the Pipeline creation, you need to enable change tracking for the new table. After that, refresh the schema so that Hevo can begin ingesting data from it. If change tracking is not enabled, Hevo marks the object as inaccessible and does not ingest any data from it.

Allowlist Hevo IP addresses for your region

You need to allowlist the Hevo IP addresses for your region to enable Hevo to connect to your Amazon RDS SQL Server database. To do this:

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, click the link text under Security, VPC security groups.

   

5. On the Security Groups page, select the check box for your Security group ID, and from the Actions drop-down, click Edit inbound rules.

   

6. On the Edit inbound rules page:

   

   1. Click Add rule.

   2. In the Port range column, enter the port of your Amazon RDS SQL Server database instance. For example, 1433.

   3. In the Source column, select Custom from the drop-down and enter Hevo’s IP addresses for your region. Repeat steps 1-3 to allowlist all the IP addresses.

   4. Click Save rules.

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the user

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

Connect to your Amazon RDS SQL Server database as a masteruser with any SQL client tool, such as sqlcmd, and run the following script:

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

Retrieve the Database Hostname and Port Number (Optional)