Amazon RDS for SQL Server Setup Guide

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

Prerequisites

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

SQL Server 2012 - 2022
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

IMPORTANT: Do not perform the Choose a connection method step if you want to use Hybrid Deployment for your data pipeline.

Choose a connection method

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

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 SSH connection instructions 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 AWS’ PrivateLink documentation.

Follow our AWS PrivateLink setup guide to configure PrivateLink for your database.

Connect using Proxy Agent

Fivetran connects to your database through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in your network and creates an outbound network connection to the Fivetran-managed SaaS.

To learn more about the Proxy Agent, how to install it, and how to configure it, see our Proxy Agent documentation.

Enable access

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

Configure security group

Expand for instructions

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

Click on your SQL instance to expand the view, then go to the Configuration Details tab.
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.
Write down the read instance's port number. You will need this later.
Click the link to the read instance's Security Group.
In the security group panel, select the Inbound tab.
Click Edit.
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.
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 Fivetran's IP if you are connecting directly or {your-ssh-tunnel-server-ip-address}/32 if you are connecting through an SSH tunnel.
Click Save.

Configure Network ACLs

Expand for instructions

Return to the RDS Dashboard and click on your instance.
Click the link to the instance's VPC.
Select the VPC.
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

Expand for instructions

Select Inbound Rules.
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.
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 AWS's Network ACLs documentation.

Edit outbound rules

Expand for instructions

Select Outbound Rules.
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.

Connect to your SQL Server database as an Admin user.
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>;

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>;
```
or all tables in a given schema:
```
GRANT SELECT on SCHEMA::<schema> to <username>;
```
or a specific table:
```
GRANT SELECT ON [<schema>].[<table>] TO <username>;
```
or a set of specific columns in a table:
```
GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO <username>;
```
(Optional) For SQL Server for Linux, if you are using Change Tracking with history mode enabled, to improve performance and reduce delays, you may want to grant the necessary permissions to the Fivetran user by running the following command:
```
USE [master]; GRANT VIEW SERVER STATE TO <username>;
```
This grants the Fivetran user access to server state information, minimizing the dependency on checkpoint timing and reducing update delays. See the Sync Delays with SQL Server for Linux Using Change Tracking with History Mode Enabled troubleshooting page for details.

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 when you created the user.
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 updating data documentation.

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

Expand for instructions

Enable change tracking at the database level:
```
ALTER DATABASE [<database>] 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
```
NOTE: While we recommend seven days of change retention, you can set your retention period as low as one day. However, a shorter retention period increases the risk that your logs will expire in between syncs, triggering an automatic full source re-sync.

Enable CT for each table you want to integrate:

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

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>;
```

Change data capture

Expand for instructions

Enable change data capture at the database level:
```
EXEC msdb.dbo.rds_cdc_enable_db [<database>];
```

Enable CDC for each table you want to integrate:

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

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 create a new CDC instance that includes them and delete the old instance.

NOTE: When @supports_net_changes is set to 1, an additional non-clustered index is created on the change table. Because this index needs to be maintained, enabling net changes can degrade CDC performance.

Fivetran Teleport Sync

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

(Optional) Schema changes for change tracking (CT) and change data capture (CDC) Private Preview

By default, when you create a new table in your database, you must manually enable CT or CDC for that table before we can sync it. Additionally, if you add a new column to an existing CDC-enabled table, you must manually recreate the CDC capture instance before we can sync the column.

If you want Fivetran to automatically enable CT or CDC for newly created tables and to update CDC capture instances when a new column is added, install the following stored procedures on your database. If your connector contains only CT-enabled tables, then any subsequent new tables will be CT-enabled. If your connector has a mixture of CT- and CDC-enabled tables or contains CDC-enabled tables only, then we will enable CDC over CT by default.

Expand for instructions

Install the following script on your database. Before running the script, replace <database> with your database name.

NOTE: If you have configured your connector to "Allow columns", you can exclude the [dbo].[sp_ft_enable_cdc] and [dbo].[sp_ft_enable_change_tracking] stored procedures. If you have configured your connector to "Block All", you can exclude all the following stored procedures.

USE [<database>];

/****** Object:  StoredProcedure [dbo].[sp_ft_enable_cdc]    Script Date: 1/17/2024 11:12:55 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_cdc]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

   -- Enable CDC for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);

   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);

      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');

      -- Build and execute the SQL statement to enable CDC for the table
      SET @SqlStatement = '
      BEGIN TRY
         EXEC sys.sp_cdc_enable_table
             @source_schema = ''' + PARSENAME(@TableName, 2) + ''',
             @source_name   = ''' + PARSENAME(@TableName, 1) + ''',
             @role_name     = ''' + @FivetranUser + ''',
             @supports_net_changes = 0;
         
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';

     -- Execute the dynamic SQL statement
     EXEC sp_executesql @SqlStatement;
   END

   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO


/****** Object:  StoredProcedure [dbo].[sp_ft_enable_change_tracking]    Script Date: 1/17/2024 11:10:45 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO
   
CREATE OR ALTER PROCEDURE [dbo].[sp_ft_enable_change_tracking]
   @TableList NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX)
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;
      
   -- Table to store tables with results
   CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));
      
   -- Enable change tracking for each table
   DECLARE @TableName NVARCHAR(MAX);
   DECLARE @SqlStatement NVARCHAR(MAX);
      
   WHILE LEN(@TableList) > 0
   BEGIN
      -- Get the first table in the list
      SET @TableName = NULL;
      SET @TableName = SUBSTRING(@TableList, 1, CHARINDEX(',', @TableList + ',') - 1);
      
      -- Remove the processed table from the list
      SET @TableList = STUFF(@TableList, 1, LEN(@TableName) + 1, '');
         
      -- Build and execute the SQL statement to enable change tracking
      SET @SqlStatement = '
      BEGIN TRY
         ALTER TABLE ' + @TableName + ' ENABLE CHANGE_TRACKING;
         GRANT VIEW CHANGE TRACKING ON ' + @TableName + ' TO ' + @FivetranUser + ';
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ''Success'');
      END TRY
      BEGIN CATCH
         PRINT ERROR_MESSAGE();
         INSERT INTO #ResultTables (TableName, Result) VALUES (''' + @TableName + ''', ERROR_MESSAGE());
      END CATCH
      ';
         
      -- Execute the dynamic SQL statement
      EXEC sp_executesql @SqlStatement;
   END
      
   -- Return the list of tables with results
   SELECT * FROM #ResultTables;
      
   -- Drop the temporary table
   DROP TABLE #ResultTables;
END;
GO

  
/****** Object:  StoredProcedure [dbo].[sp_ft_get_tables_with_ddl_changes]    Script Date: 1/30/2024 9:10:47 PM ******/
SET ANSI_NULLS ON
GO
   
SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_get_tables_with_ddl_changes]
   @TableList NVARCHAR(MAX),
   @ddlTime DATETIME = NULL -- New parameter for ddlTime
WITH EXECUTE AS OWNER -- Change to an appropriate sysadmin user
AS
BEGIN
   SET NOCOUNT ON;

   -- Declare a table variable to store the results
   DECLARE @ChangedTables TABLE (
      TableName NVARCHAR(MAX),
      DdlTime DATETIME
   );

   -- Split the comma-separated list into a table variable
   DECLARE @TableNames TABLE (
      TableName NVARCHAR(MAX)
   );
      
   INSERT INTO @TableNames (TableName)
   SELECT value
   FROM STRING_SPLIT(@TableList, ',');
      
   -- Check for DDL changes in cdc.ddl_history
   INSERT INTO @ChangedTables (TableName, DdlTime)
   SELECT
      tn.TableName,
      MAX(dh.ddl_time) AS LatestDdlTime
   FROM cdc.ddl_history dh
   INNER JOIN @TableNames tn ON CONCAT(OBJECT_SCHEMA_NAME(dh.source_object_id), '.', OBJECT_NAME(dh.source_object_id)) = tn.TableName
   WHERE @ddlTime IS NULL OR dh.ddl_time >= @ddlTime
   GROUP BY tn.TableName;
      
   -- Return the list of tables with the latest DDL changes
   SELECT TableName, DdlTime
   FROM @ChangedTables;
END;
GO



/****** Object:  StoredProcedure [dbo].[sp_ft_update_cdc]    Script Date: 1/31/2024 1:19:40 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE OR ALTER PROCEDURE [dbo].[sp_ft_update_cdc]
   @TableName NVARCHAR(MAX),
   @FivetranUser NVARCHAR(MAX),
   @CaptureInstance NVARCHAR(MAX),
   @CapturedColumnList NVARCHAR(MAX)
WITH EXECUTE AS OWNER
AS
BEGIN
   SET NOCOUNT ON;

    -- Table to store tables with results
    CREATE TABLE #ResultTables (TableName NVARCHAR(MAX), Result NVARCHAR(MAX));

    DECLARE @SourceSchema NVARCHAR(MAX);
    DECLARE @SourceName NVARCHAR(MAX);

    BEGIN TRY
        -- Assign values to variables for PARSENAME function
        SET @SourceSchema = PARSENAME(@TableName, 2);
        SET @SourceName = PARSENAME(@TableName, 1);

        -- Step 1: Disable the current CDC instance
        EXEC sys.sp_cdc_disable_table
            @source_schema    = @SourceSchema,
            @source_name      = @SourceName,
            @capture_instance = @CaptureInstance;

        -- Step 2: Create a new CDC instance
        EXEC sys.sp_cdc_enable_table
            @source_schema        = @SourceSchema,
            @source_name          = @SourceName,
            @role_name            = @FivetranUser,
            @capture_instance     = @CaptureInstance,
            @captured_column_list = @CapturedColumnList,
            @supports_net_changes = 0;

        -- Insert the result into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, 'Success');
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        -- Insert the error message into the #ResultTables table
        INSERT INTO #ResultTables (TableName, Result) VALUES (@TableName, ERROR_MESSAGE());
    END CATCH;

    -- Return the list of tables with results
    SELECT * FROM #ResultTables;

    -- Drop the temporary table
    DROP TABLE #ResultTables;
END;
GO

Grant the Fivetran user execute permission.

USE [<database>];
GRANT EXECUTE ON dbo.sp_ft_enable_cdc to <username>;
GRANT EXECUTE ON dbo.sp_ft_enable_change_tracking to <username>;
GRANT EXECUTE ON dbo.sp_ft_get_tables_with_ddl_changes to <username>;
GRANT EXECUTE ON dbo.sp_ft_update_cdc to <username>;

Finish Fivetran configuration

In your connector setup form, enter a Destination schema prefix. This is used as the connector name and cannot be modified once the connector is created.
NOTE: Depending on your selection in the Destination schema names field, we will either prefix the connector name to each replicated schema or use the source schema names instead.
In the Host field, enter your database host's IP (for example, 1.2.3.4) or domain (for example, your.server.com).
Enter your database instance's port number. The port number is usually 1433.
Enter the Fivetran-specific user that you created in Step 3.
Enter the password for the Fivetran-specific user that you created in Step 3.
Enter the name of your database (for example, your_database).
(Hybrid Deployment only) If your destination is configured for Hybrid Deployment, the Hybrid Deployment Agent associated with your destination is pre-selected in the Select an existing agent drop-down menu. To use a different agent, select the agent of your choice, and then select the same agent for your destination.
(Not applicable to Hybrid Deployment) Choose your connection method. If you selected Connect via an SSH tunnel, copy or make a note of the Public Key and add it to the authorized_keys file while configuring the SSH tunnel, and provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port
- SSH user
- If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.
(Optional for Hybrid Deployment) If you want to use a TLS connection between your Hybrid Deployment Agent and Fivetran cloud, set the Require TLS toggle to ON.
IMPORTANT: Before you set this toggle to ON, follow Microsoft's setup instructions to enable TLS on your database. We do not support TLS 1.0.
Choose your incremental Update Method.
Private PreviewIn the Destination schema names field, select Fivetran naming or Source naming to determine how the schema names appear in your destination. For more information, see the Schema information section.
Click Save & Test. Fivetran tests and validates our connection to your SQL Server database. Upon successful completion of the setup tests, you can sync your data using Fivetran.

Setup tests

Fivetran performs the following tests to ensure that we can connect to your Amazon RDS for SQL Server 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 selected an indirect connection method and then disabled the Require TLS through Tunnel toggle.)
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 sync method enabled (either CDC or CT).