Amazon RDS for SQL Server Setup Guide

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.

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.

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.

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.

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 Fivetran's IP if you are connecting directly or {your-ssh-tunnel-server-ip-address}/32 if you are connecting through an SSH tunnel.

   

9. Click Save.

   

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.

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

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

  

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

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

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

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

1. Enable change data capture at the database level:

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

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

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.

1. 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 + ''';
            
            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
   
           -- 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
   
   

2. 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>;