Amazon Relational Database Service (RDS) allows you to deploy, and scale multiple editions of MS SQL Server in minutes with cost-efficient and resizable compute capacity.

You can ingest data from your Amazon RDS SQL Server using Hevo Pipelines and replicate it to a Destination of your choice.

---

Prerequisites

• The MS SQL Server version is 2008 or higher.

• If the Pipeline Mode is Change Tracking or Table, and the Query mode is Change Tracking:

  • Change Tracking is enabled.

  • CHANGE TRACKING and ALTER DATABASE privileges are granted to the database user.

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

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

---

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

Enable Change Tracking

Note: This step is valid only for Pipelines with Change Tracking as their Pipeline Mode.

The Change Tracking mechanism captures changes made to a database. In order to enable, or disable change tracking, the database user must have the ALTER DATABASE privilege.

To enable change tracking, connect your Amazon RDS SQL Server database in your SQL Client tool, and enter these commands:

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 time period 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 ingestion frequency, for example, 24 hours.

2. Enable change tracking at the table and schema level:

    ALTER TABLE <schema_name>.<table> ENABLE CHANGE_TRACKING
   

   Repeat this step for each table you want to replicate using log-based incremental replication. Read Enable Change Tracking for a Table.

Note: Hevo does not support Change Data Capture (CDC) for Amazon RDS SQL Server.

---

Add Hevo IP Addresses to your Database Allowlist

You must add Hevo’s IP address for your region to the database IP allowlist, enabling Hevo to connect to your Amazon RDS SQL Server database. To do this:

1. Open the Amazon RDS console.

2. In the left navigation pane, click Databases (or Instances if you are using an older version).

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

   

   Note: The instance does not necessarily have to be a replica as long as it allowlist Hevo’s IP address for the region.

4. In the Connectivity & security tab, ensure Public Accessibility is set to Yes.

5. Click the link text under Security, VPC security groups to open the Security Groups panel.

   

6. In the Security Groups panel, click Inbound rules tab, and then, Edit inbound rules.

   

7. In the Edit inbound rules page:

   1. Click Add rule.

   2. In the Port range column, enter the port of your Amazon RDS SQL Server instance. The default value is 1433.

   3. In the Source column, select Custom from the drop-down and enter Hevo’s IP addresses for your region.

   4. Click Save rules.

      

---

Create a User and Grant Privileges

1. Create a user (optional)

2. Grant privileges to the user

The database user specified in the Hevo Pipeline must have the following global privileges:

• SELECT

• VIEW CHANGE TRACKING

To assign these privileges, log in to your Amazon RDS SQL Server instance as a masteruser in your SQL Client tool and enter the following commands:

1. Grant SELECT privilege at the database level:

   GRANT SELECT ON DATABASE::<database> TO <db_username>;
   

2. Grant SELECT privilege at the table and schema level:

   GRANT SELECT ON <schema_name>.<table_name> TO <db_username>;
   

3. Grant VIEW CHANGE TRACKING privilege:

   GRANT VIEW CHANGE TRACKING ON <schema_name>.<table_name> TO <username>;
   

---

Specify Amazon RDS SQL Server Connection Settings

Perform the following steps to configure your PostgreSQL Source:

1. Click Sources in the Navigation Bar.

2. Click + Add Source in the Sources List View.

3. On the Add New page, select PostgreSQL.

4. In the screen that appears, specify the following:

   

   • Source Name: A unique name for your Source, not exceeding 50 characters. For example, SQL Server.

   • In the Connect your Amazon RDS SQL Server - Log section:

     • Database Host: SQL Server host’s IP address or DNS.

       Note: For URL-based hostnames, exclude the http:// or https:// part. For example, if the hostname URL is https://mssql.database.windows.net, enter mssql.database.windows.net.

     • Database Port: The port on which your SQL Server is listening for connections. Default value: 1433.

     • Database User: The read-only user who has the permissions to read tables in your database.

     • Database Password: The password for the read-only user.

   • Additional Settings

     • Connect through SSH: Enable this option to connect to Hevo using an SSH tunnel, instead of directly connecting your SQL Server database host to Hevo. This provides an additional level of security to your database by not exposing your SQL Server setup to the public. Read Connecting Through SSH.

     If this option is disabled, you must configure your Source to accept connections from Hevo’s IP addresses.

     • Use SSL: Enable this option to use an SSL-encrypted connection. Specify the following:

       • CA File: The file containing the SSL server certificate authority (CA).

       • Client Certificate: The client’s public key certificate file.

       • Client Key: The client’s private key file.

5. Click Test & Continue.