Amazon RDS MS SQL

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 MS SQL Server using Hevo Pipelines and replicate it to a Destination of your choice.

Prerequisites

• The MS SQL Server version is 2008 or higher.

• Hevo’s IP addresses are whitelisted.

• If Pipeline mode is Logical Replication:

  • Change Tracking is enabled.

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

• SELECT privileges are granted to the database user.

Whitelisting Hevo’s IP Addresses

You need to whitelist the Hevo IP addresses for your region to enable Hevo to connect to your Amazon RDS MS SQL 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 MS SQL instance.

   

   Note: The instance does not necessarily have to be a replica as long as it whitelists 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 MS SQL 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.

      

8. Click the Outbound rules tab, and then, Edit outbound rules.

9. Repeat step 7, to configure Outbound Rules.

   

Enabling Change Tracking for Logical Replication

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 for Logical Replication, connect your Amazon RDS MS SQL database in your SQL Client tool, and enter these commands:

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

• Enable change tracking at the table 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 MS SQL

Granting Privileges to the User

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

• SELECT
• VIEW CHANGE TRACKING (If Query Mode is Change Tracking)

To set up these privileges, connect your Amazon RDS MS SQL Database in your SQL Client tool, and enter these commands:

• Grant SELECT privilege at the database level:
  GRANT SELECT ON DATABASE::<database> TO <db_username>;

• Grant SELECT privilege at the table, and schema level:
  GRANT SELECT ON <schema_name>.<table_name> TO <db_username>;

• Grant VIEW CHANGE TRACKING privilege, if Query Mode is Change Tracking:
  GRANT VIEW CHANGE TRACKING ON <schema_name>.<table_name> TO <username>;

Limitations

• Hevo does not support Change Data Capture (CDC) for Amazon RDS MS SQL.