SQL Server RDS Setup Guide

Follow these instructions to replicate your Amazon SQL Server RDS database to your destination via Fivetran.

---

Prerequisites

To connect your generic SQL Server database to Fivetran, you need:

• SQL Server 2012 - 2019
• Your database host's IP (e.g., 1.2.3.4) or domain (your.server.com)
• Your database's port (usually 1433)

IMPORTANT: We do not support single-user mode.

---

Setup instructions

Choose a connection method

First, decide whether to connect your SQL Server database directly, using an SSH tunnel, or using AWS PrivateLink.

Connect directly (TLS required)

IMPORTANT: You must have TLS enabled on your database to connect directly to Fivetran.

Fivetran connects directly to your database instance. This is an easy and secure connection method.

To connect directly, create a security rule to allow access to Fivetran's IP.

Connect using SSH

Fivetran connects to a separate server in your network that provides an SSH tunnel to your database. You must connect through SSH if your database is in an inaccessible subnet.

Before you proceed to the next step, you must follow our to give Fivetran access to your SSH tunnel.

To connect using SSH, configure your VPC Security Groups and Network Access Control Lists (ACLs) to allow incoming connections to your SQL server host and port (usually 1433) from your SSH tunnel server's IP address.

Connect using AWS PrivateLink

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in .

Follow to configure PrivateLink for your database.

Enable access

Configure a server firewall to grant Fivetran's data processing servers access to your database.

Configure security group

NOTE: These instructions assume that your instance is in a VPC.

1. Click on your SQL instance to expand the view, then go to the Configuration Details tab.

   

2. A panel of details for your read replica appears. Verify that the Publicly Accessible value is Yes if you choose to connect directly. You do not have to make your database publicly accessible if you choose to connect using SSH.

   

3. Write down the read instance's port number. You will need this later.

   

4. Click the link to the read instance's Security Group.

   

5. In the security group panel, select the Inbound tab.

   

6. Click Edit.

   

7. Click Add Rule. This creates a new Custom TCP Rule at the bottom of the list with a blank space for a Port Range and a Source IP address.

   

8. Enter your Port Range and Source IP address values.

   • In the Port Range field, enter your instance's port number that you wrote down in Step 3 of this section (usually 1433).
   • In the Custom IP field, enter if you are connecting directly or {your-ssh-tunnel-server-ip-address}/32 if you are connecting through an SSH tunnel.

   

9. Click Save.

   

Configure Network ACLs

1. Return to the RDS Dashboard and click on your instance.

   

2. Click the link to the instance's VPC.

   

3. Select the VPC.

   

4. In the Summary tab, click the Network ACL link.

   

You will see tabs for Inbound Rules and Outbound Rules. You need to edit both.

Edit inbound rules

1. Select Inbound Rules.

   

2. If you have a default VPC that was automatically created by AWS, the settings already allow all incoming traffic. To verify that the settings allow incoming traffic, confirm that the Source value is 0.0.0.0/0 and that the ALLOW entry is listed above the DENY entry.

   

3. If your inbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow {your-ssh-tunnel-server-ip-address}/32 to access the port number of your read replica (usually 1433). For additional help, see .

Edit outbound rules

1. Select Outbound Rules.

   

2. If your outbound rules don't include an ALL - 0.0.0.0/0 - ALLOW entry, edit the rules to allow outbound traffic to all ports 1024-65535 for destination {your-ssh-tunnel-ip-address}/32.

Enter host and port in setup form

In your Fivetran setup form, enter your host and port.

• For the Host, enter the URL from the Endpoint field in RDS dashboard, without the port.

• For the Port, enter the port from the Endpoint field in RDS dashboard (1433 is the default).

  

Create user

Create a database user for Fivetran's exclusive use.

1. Connect to your SQL Server database as an admin user.

2. Execute the following SQL commands to create a user for Fivetran. Replace <database> with the name of your database, <username> with the username of your choice, and <password> with a password of your choice:

USE [<database>];
CREATE LOGIN <username> WITH PASSWORD = '<password>';
CREATE USER <username> FOR LOGIN <username>;
content_copy

Grant user permissions

Once the Fivetran user is created, grant it SELECT permission for the set of databases, schemas, tables, or specific columns you would like Fivetran to sync. You can grant access to everything in a given database:

GRANT SELECT on DATABASE::<database> to <username>;
content_copy

or all tables in a given schema:

GRANT SELECT on SCHEMA::<schema> to <username>;
content_copy

or a specific table:

GRANT SELECT ON [<schema>].[<table>] TO <username>;
content_copy

or a set of specific columns in a table:

GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO <username>;
content_copy

Enter user, password, and database in setup form

In your Fivetran setup form, enter your user, password, and database name.

• For the User, enter the username you created.
• For the Password, enter the password you set .
• For the Database, enter the database you want to replicate from.

Enable incremental updates

For incremental updates, we use one of the following tracking mechanisms:

These mechanisms let Fivetran copy only the rows that have changed since the last data sync so we don't have to copy the whole table every time. Learn more in our .

Choose to enable change tracking, change data capture, or Fivetran Teleport Sync.

NOTE: You cannot enable CT or CDC on a read replica. If you enable CDC on your primary database, it applies to your read replica. However, if you enable CT on your primary database, it does not apply to your read replica.

Change tracking

1. Enable change tracking at the database level:

   ALTER DATABASE [<database>] SET CHANGE_TRACKING = ON;
   content_copy

2. Enable CT for each table you want to integrate:

   ALTER TABLE [<schema>].[<table>] ENABLE CHANGE_TRACKING;
   content_copy

3. Grant the Fivetran user VIEW CHANGE TRACKING permission for each of the tables that have change tracking enabled:

   GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO <username>;
   content_copy

Change data capture

1. Enable change data capture at the database level:

   EXEC msdb.dbo.rds_cdc_enable_db [<database>];
   content_copy

2. Enable CDC for each table you want to integrate:

   EXEC sys.sp_cdc_enable_table  
   @source_schema = [<schema>],
   @source_name   = [<table>],
   @role_name     = [<username>];
   content_copy

NOTE: Fivetran only supports tables with a single CDC capture instance. Our syncs only include tables and columns that are present in a CDC instance. If you add new tables or columns, you must that includes them and delete the old instance.

Fivetran Teleport Sync

You do not need to do any additional configuration to use Fivetran Teleport Sync.

---

Choose schema prefix

Each database from your source will be mapped to a schema in the destination by adding a prefix to the source database name. For example, if your source database names are "foo" and "bar" and if you choose the prefix "source1", then you will get schemas "source1_foo" and "source1_bar" in the destination.

---

Setup tests

Fivetran performs the following tests to ensure that we can connect to your SQL Server RDS database and that it is properly configured:

• The Connecting to SSH Tunnel Test validates the SSH tunnel details you provided in the setup form. It then checks that we can connect to your database using the SSH Tunnel. (We skip this test if you aren't connecting using SSH.)
• The Connecting to Host Test validates the database credentials you provided in the setup form. It then verifies that the database host is not private and checks that we can connect to the host.
• The Validating Certificate Test generates a pop-up window where you must choose which certificate you want Fivetran to use. It then validates that certificate and checks that we can connect to your database using TLS. (We skip this test if you aren't connecting directly.)
• The Connecting to Database Test checks that we can access your database.
• The Checking Access to Schema Test checks that we have the correct permissions to access the schemas in your database. It then verifies that your database contains at least one table.
• The Validating Replication Config Test verifies that your database has an incremental update mechanism enabled (either CDC or CT).

NOTE: The tests may take a few minutes to finish running.

---

Related articles