Amazon RDS MS SQL
On This Page
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.
-
If the Pipeline Mode is Change Tracking or Table, and the Query mode is Change Tracking:
-
SELECT and VIEW CHANGE TRACKING privileges are granted to the database user.
Perform the following steps to configure your Amazon RDS MS SQL 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 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 useAUTO_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 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 MS SQL.
Whitelist 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:
-
Open the Amazon RDS console.
-
In the left navigation pane, click Databases (or Instances if you are using an older version).
-
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.
-
In the Connectivity & security tab, ensure Public Accessibility is set to Yes.
-
Click the link text under Security, VPC security groups to open the Security Groups panel.
-
In the Security Groups panel, click Inbound rules tab, and then, Edit inbound rules.
-
In the Edit inbound rules page:
-
Click Add rule.
-
In the Port range column, enter the port of your Amazon RDS MS SQL instance. The default value is 1433.
-
In the Source column, select Custom from the drop-down and enter Hevo’s IP addresses for your region.
-
Click Save rules.
-
Create a User and Grant Privileges
1. Create a user (optional)
To create a database user, log in to your Amazon RDS MS SQL instance as a masteruser
in your SQL Client tool, and enter these commands:
-
Select a database schema
USE <schema_name>;
-
Create a database user:
CREATE LOGIN hevo WITH PASSWORD = '<enter_password>'; CREATE USER hevo for login hevo;
Note: Skip this step if you are using an existing database user.
2. Grant privileges to the user
The database user specified in the Hevo Pipeline must have the following global privileges:
-
SELECT
-
VIEW CHANGE TRACKING
(If Pipeline Mode is Change Tracking or Table, and Query mode is Change Tracking)
To assign these privileges, log in to your Amazon RDS MS SQL instance as a masteruser
in your SQL Client tool and enter the following 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 Pipeline Mode is Change Tracking or Table, and Query mode is Change Tracking:GRANT VIEW CHANGE TRACKING ON <schema_name>.<table_name> TO <username>;
Specify Amazon RDS SQL Server Connection Settings
In the Configure your Amazon RDS SQL Server Source page, specify the following:
-
Pipeline Name: A unique name for the Pipeline.
-
SQL Server Host: SQL Server host’s IP address or DNS.
The following table lists a few examples of SQL Server hosts:
Variant Host Amazon RDS MS SQL ms-sql-server-1.xxxxx.rds.amazonaws.com Azure MS SQL mssql.database.windows.net Generic MS SQL 10.123.10.001 or mssql.westeros.inc Google Cloud SQL Server 35.220.150.0 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.
-
SQL Server Port: The port on which your SQL Server is listening for connections. Default value: 1433.
-
SQL Server User: The read-only user who has the permissions to read tables in your database.
-
SQL Server Password: The password for the read-only user.
-
Select an Ingestion Mode: The desired mode by which you want to ingest data from the Source. The available Ingestion Modes are Change Tracking, Table, and Custom SQL. Read Ingestion Modes.
For Ingestion mode as Table, read Object Settings for steps to configure the objects to be replicated.
-
Database Name: The database that you wish to replicate.
-
Schema Name: The schema that holds the tables to be replicated. Default value: dbo.
-
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 whitelist Hevo’s IP addresses. Refer to the content for your SQL Server variant for steps to do this.
-
Advanced Settings:
- Include New Tables in the Pipeline: Applicable for all Ingestion modes except Custom SQL. If enabled, Hevo automatically ingests data from tables created after the Pipeline has been built. If disabled, the new tables are listed in the Pipeline Detailed View in Skipped state, and you can manually include the ones you want and load their historical data.
You can change this setting later.
-
Click TEST & CONTINUE to proceed for setting up the Destination.
Limitations
- Hevo does not support Change Data Capture (CDC) for Amazon RDS MS SQL.
Revision History
Refer to the following table for the list of key updates made to this page:
Date | Release | Description of Change |
---|---|---|
Apr-21-2022 | 1.86 | Updated section, Specify Amazon RDS SQL Server Connection Settings. |
Feb-07-2022 | 1.81 | Updated section, Whitelist Hevo’s IP Address to remove details about Outbound rules as they are not required. |
Jan-03-2022 | 1.79 | Updated the description of the Include New Tables in the Pipeline advance setting in the Specify Amazon RDS SQL Server Connection Settings section. |
Sep-08-2021 | NA | Updated the second list item under Prerequisites and corrected the numbering of sections. |
Jul-26-2021 | 1.68 | Added a note for the SQL Server Host field. |
Jul-12-2021 | NA | Added section, Specify Amazon RDS SQL Server Connection Settings. |
Mar-09-2021 | 1.58 | Replaced references to Logical Replication with Change Tracking as Change Tracking is a distinct Ingestion mode for SQL Server Source types. |
Feb-22-2021 | 1.57 | Added section Create a User and Grant Privileges. |