Generic SQL Server Setup Guide

Follow these instructions to replicate your generic 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)
We do not support single-user mode.

To use the Binary Log Reader method, you also need:

SQL Server 2017 - 2022 on Windows with .NET Framework 4.8 installed
To check the .NET Framework versions installed, see Microsoft's documentation
Access to your database's host machine
Fivetran DLL for Binary Log Reader (for manual installation)
A SQL Server account with sysadmin privileges to install CLR stored procedures. This account is required only for installing stored procedures that allow Fivetran to read the transaction log on your database.

To connect your SQL Server database on an AlwaysOn Availability Group secondary node to Fivetran, you also need the additional prerequisites below:

The direct host IP address (for example, 1.2.3.4) or domain (for example, your.server.com) of the secondary node or the Availability Group (AG) Listener IP address or domain.
The port (usually 1433) of the secondary node or AG Listener.
For the Change Data Capture (CDC) and Teleport incremental sync methods:
- Connecting via the AG Listener and only want Fivetran to read from the secondary node, you must configure the AG Listener to route traffic to the secondary node and contact Fivetran support to enable this configuration via ApplicationIntent=Readonly mode.
For Binary Log Reader incremental sync method:
- The database must be set to Full Recovery mode.
- A full database backup must be performed on the primary node after enabling Full Recovery mode.

Change Tracking method is not supported for AlwaysOn Availability Groups.

Setup instructions

Choose connection method

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

Connect directly (TLS required)

You must have TLS enabled on your database to connect directly to Fivetran. Follow Microsoft's TLS setup instructions to enable TLS on your database.

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

To connect directly, configure your firewall and/or other access control systems to allow incoming connections to your SQL Server host and port (usually 1433) from Fivetran's IPs for your database's region. How you do this will vary based on how your SQL Server database is hosted (cloud platform, on-premises, etc.)

When connecting to a named instance, provide your SQL Server host and instance name as the Host value (in the format <host>\<instance_name>) in your connection setup form. If the SQL Server Browser service is active on your database, you can use port 1433 as the Port value if you want Fivetran to dynamically retrieve the port number of your named instance using this service. Otherwise, use the named instance port as the Port value.

Connect using SSH (TLS optional)

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.

To connect using SSH, do the following:

In your connection setup form, select Connect via an SSH tunnel to expose Fivetran's public SSH key. Copy the key by clicking the blue clipboard icon.
Add the public key to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.
If you want Fivetran to tunnel SSH over TLS, follow Microsoft's TLS setup instructions to enable TLS on your database. Even though TLS is optional, TLS 1.0 is never supported. This is because the server will attempt to encrypt the authentication traffic with an obsolete TLS version, and Fivetran will refuse any TLS 1.0 connection, as it has been deprecated by the IETF.

You can only connect to named instances through an SSH tunnel if you specify your SQL Server host as the Host value and the named instance port as the Port value in your connection setup form.

Connect using AWS PrivateLink

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 Azure Private Link

You must have a Business Critical plan to use Azure Private Link.

Azure Private Link allows Virtual Networks (VNets) and Azure-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. Learn more in Microsoft's Azure Private Link documentation.

Follow our Azure PrivateLink setup guide to configure Private Link 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.

TLS Versions - Additional Information

For some connection methods listed in the previous section, TLS is either required or optional.

TLS 1.0 is never permitted by Fivetran. Both direct connections and SSH connections will fail with TLS 1.0 enabled.

Steps to check TLS version

If you're unsure of your TLS version, you can check it in the following way:

Navigate to the run menu.
Run regedit.
Check the following registry values:
- for TLS 1.1:
  - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\DisabledByDefault must be set to 0
  - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.1\Client\Enabled must be set to 1
- for TLS 1.2:
  - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\DisabledByDefault must be set to 0
  - HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client\Enabled must be set to 1

Allow TCP/IP protocol

Verify that your database server is configured to allow TCP/IP connections. If your database instance does not have TCP/IP protocol enabled, follow the instructions below.

Enable TCP/IP protocol

Open SQL Server Configuration Manager.
In the tree pane, click SQL Server Network Configuration to expand it.
Click Protocols for YourInstanceName. If you specified the default instance during installation, the instance name will be MSSQLSERVER.
In the Status column, verify that TCP/IP is Enabled.
If Disabled appears, right-click TCP/IP, then click Enable.
Right-click TCP/IP, then select Properties.
Go to the IP Addresses tab and scroll all the way down.
In the IPAll section, enter your database's port number (usually 1433) for the TCP Port, then click Apply.
Click OK in the warning dialog box that pops up.
Click OK in the TCP/IP Properties dialog box.
In the tree pane, click SQL Native Client Configuration to expand it, then click Client Protocols.
In the right-hand column, verify that Enabled appears next to TCP/IP.
If Disabled appears, right-click TCP/IP, then click Enable.
Right-click TCP/IP, then select Properties.
Verify that the Default Port is 1433 and that Yes appears next to Enabled.
Click OK to exit the TCP/IP Properties dialog box.
In the tree pane, click SQL Server Services.
In the right pane, right-click SQL Server (YourInstanceName), then click Restart.

Create user

Create a database user for Fivetran's exclusive use. The Fivetran user must be a SQL database user, not an Active Directory user.

Connect to your SQL Server database as an Admin user.
Execute the following command to create a Fivetran user with database password authentication. Replace <database> with the name of your database and <username> and <password> with a username and password of your choice:
```
USE [<database>];
CREATE LOGIN <username> WITH PASSWORD = '<password>';
CREATE USER <username> FOR LOGIN <username>;
```

Grant user permissions

Grant the Fivetran database user the permissions required to capture changes from the SQL Server database.

The instructions in this section apply only to the CT, CDC, and Fivetran Teleport Sync methods. If you're using the Binary Log Reader method, skip to the next section and follow the Binary Log Reader setup steps.

Grant the Fivetran user SELECT permissions for the databases, schemas, tables, or specific columns you want Fivetran to sync.
- To grant access to the entire database:
```
GRANT SELECT on DATABASE::<database> to <username>;
```
- To grant access to all tables in a schema:
```
GRANT SELECT on SCHEMA::<schema> to <username>;
```
- To grant access to a specific table:
```
GRANT SELECT ON [<schema>].[<table>] TO <username>;
```
- To grant access to specific columns:
```
GRANT SELECT ON [<schema>].[<table>] ([<column 1>], [<column 2>], ...) TO <username>;
```
(Optional) If you're using Change Tracking with history mode enabled on SQL Server on Linux, grant the following permission to allow Fivetran to access server state information directly. This minimizes reliance on checkpoint timing and helps reduce sync delays:
```
USE [master]; 
GRANT VIEW SERVER STATE TO <username>;
```
See Sync Delays with SQL Server for Linux Using Change Tracking with History Mode Enabled for details.

Enable incremental updates

To keep your data up to date after the initial sync, Fivetran supports several incremental sync methods. These methods copy only the rows that have changed since the last data sync so we don't have to copy the entire table every time. Learn more in Updating data documentation.

Choose incremental sync methods

Select the sync method that best fits your SQL Server configuration and data requirements. Then follow the setup steps provided below.

Change tracking (CT)

A lightweight built-in SQL Server feature that records which rows have changed.
Does not capture the content of changes or support delete tracking.
Cannot be used if connecting to a replica.

To learn more, see our change tracking documentation.

Change data capture (CDC)

A built-in tracking mechanism of SQL Server that captures all changes made to tracked tables.
Stores changes in shadow history tables, allowing you to track how many times a row changed and view past values.
Supports both primary and replica databases.

Learn more in our CDC documentation.

Fivetran Teleport Sync

Fivetran’s proprietary method requiring only read-only SQL access.
Supports change and delete tracking with no additional setup.

Learn more in our Fivetran Teleport Sync documentation.

Binary Log Reader

The Binary Log Reader incremental sync method is currently in Beta.

A proprietary Fivetran method that reads the SQL Server transaction log using a dynamic link library (DLL).
Captures row-level changes including what changed and when.
Suitable for high-throughput environments or where delete tracking is required.
Requires setup on the SQL Server host machine.

Learn more in our Binary Log Reader documentation.

See our CT vs CDC vs Binary Log Reader vs Fivetran Teleport Sync chart for more information.

Set up your chosen incremental sync method

Only complete the setup steps for the method you selected. If you selected Fivetran Teleport Sync, no additional configuration is required — you can skip to the next section.

CT setup steps

Enable change tracking at the database level:
```
ALTER DATABASE [<database>] 
SET CHANGE_TRACKING = ON 
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);
```
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 CT enabled:
```
GRANT VIEW CHANGE TRACKING ON [<schema>].[<table>] TO <username>;
```

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

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.

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

CDC setup steps

Enable change data capture at the database level:
```
USE TEST;
EXEC sys.sp_cdc_enable_db;
```
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;
```
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.
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.
Set the CDC retention period:
```
EXEC sys.sp_cdc_change_job
@job_type = N'cleanup',
@retention = 10080; -- Sets the retention period to 7 days
```
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.
Set the CDC polling interval:
```
EXEC sys.sp_cdc_change_job 
@job_type = N'capture',
@pollinginterval = 5; -- in Seconds (5 seconds)
```
We recommend using the default polling interval of 5 seconds. Increasing the polling interval can introduce delays in capturing changes. If the polling interval exceeds the incremental update frequency, changes made during that sync window may not be captured, leading to potential data inconsistencies.

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

Expand for instructions

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

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

Binary Log Reader setup steps

The Binary Log Reader incremental sync method is currently in Beta.

You can setup the Binary Log Reader using one of the following installation methods:

Auto installation – Use a PowerShell script to automatically set up the necessary permissions, stored procedures, and configuration. Recommended if:
- PowerShell is available
- You want a quick, simplified setup
Manual installation – Run SQL commands and configure the DLL manually to retain full control. Recommended if:
- PowerShell is restricted in your environment
- You require granular control over permissions

Proceed with one of the options below:

Auto installation

Copy the following PowerShell script and save it as BinLogInstall.ps1.

Auto installation PowerShell script

param(
[switch]$Help,
[string]$SqlInstance,
[string]$TargetDatabase,
[string]$SqlAdminUser,
[string]$SqlPassword,
[string]$MinimalPermissionsUser,
[string]$DLLPath,
[switch]$BackupLogMode
)

function Show-Help {
    Write-Output @"
Usage: .\BinLogInstall.ps1 -SqlInstance <instance> -TargetDatabase <database> -SqlAdminUser <user> -SqlPassword <password> -MinimalPermission <normal_user> [-DLLPath <PathToDLL>] [-BackupLogMode] [-Help]

Parameters:
-SqlInstance             (Required) SQL Server instance name (e.g., localhost\SQLEXPRESS)
-TargetDatabase          (Required) Database name to install the stored procedures
-SqlAdminUser            (Required) SQL Admin username
-SqlPassword             (Required) SQL Admin password
-MinimalPermissionsUser  (Required) Provide a normal user for minimal permission grants
-DLLPath                 (Optional) Path to the Fivetran CLR DLL (default is auto-download)
-BackupLogMode           (Optional) Configure for BLR to read from backup log 
-Help                    (Optional) Show this help message
"@
}

# If no args or help requested
if ($Help -or !$PSBoundParameters.Count) {
    Show-Help
    exit 0
}

# Validate required params
$missingParams = @()
if (-not $SqlInstance)    { $missingParams += "-SqlInstance" }
if (-not $TargetDatabase) { $missingParams += "-TargetDatabase" }
if (-not $SqlAdminUser)   { $missingParams += "-SqlAdminUser" }
if (-not $SqlPassword)    { $missingParams += "-SqlPassword" }

if ($missingParams.Count -gt 0) {
    Write-Error "Missing required parameters: $($missingParams -join ', ')`n"
    Show-Help
    exit 1
}

# Assign variables from command-line parameters
$serverName = $SqlInstance
$databaseName = $TargetDatabase
$sqlAdmin = $SqlAdminUser
$sqlPwd = $SqlPassword
$installMinimalPermission = [string]::IsNullOrWhiteSpace($MinimalPermissionsUser) -ne $true
$normalUser = $MinimalPermissionsUser

# Output summary
Write-Host "Parameters validated."
Write-Host "Server Name: $serverName"
Write-Host "Database Name: $databaseName"
Write-Host "SQL Admin: $sqlAdmin"
Write-Host "Enable Minimal Permission: $installMinimalPermission"
Write-Host "Normal User: $normalUser"

if ($DLLPath) {
    Write-Host "DLL Path: $DLLPath"
}
if ($BackupLogMode) {
Write-Output "Log Mode: Backup Log"
}


#####################################  INSTALLATION SCRIPT START HERE ######################################
$installationDir          = "C:\Fivetran"
$zipUrl                   = "https://fivetran.com/assets-docs/sql_server_binary_log_reader/Fivetran-Log-Data.zip"

$zipFileName = [System.IO.Path]::GetFileName($zipUrl)
$zipFilePath = Join-Path -Path $installationDir -ChildPath $zipFileName


# Ensure destination folder exists
if (-not (Test-Path $installationDir) -and -not $DllPath) {
    New-Item -Path $installationDir -ItemType Directory | Out-Null
}

# Utility Functions
function Download-ZipFile {
    Write-Host "Downloading $zipUrl..."
    Invoke-WebRequest -Uri $zipUrl -OutFile $zipFilePath -ErrorAction Stop
    Write-Host "Downloaded to $zipFilePath" -ForegroundColor Green
}

function Unzip-ZipFile {
    Write-Host "Extracting to $installationDir..."
    Expand-Archive -Path $zipFilePath -DestinationPath $installationDir -Force
    Write-Host "Unzipped successfully." -ForegroundColor Green
}

function Find-DllAndMd5 {
    if ($DLLPath) {
        $dll = Get-ChildItem -Path $DLLPath -Recurse -Filter "Fivetran-Log-Data-*.dll" | Select-Object -First 1
    } else {
        $dll = Get-ChildItem -Path $installationDir -Recurse -Filter "Fivetran-Log-Data-*.dll" | Select-Object -First 1
    }
    if (-not $dll) {
        throw "Fivetran DLL not found in the extracted files."
    }

    $baseName = [System.IO.Path]::GetFileNameWithoutExtension($dll.Name)
    if ($DLLPath) {
        $md5 = Get-ChildItem -Path $DLLPath -Recurse -Filter "$baseName.dll.md5" | Select-Object -First 1
    } else {
        $md5 = Get-ChildItem -Path $installationDir -Recurse -Filter "$baseName.dll.md5" | Select-Object -First 1
    }
    
    if (-not $md5) {
        throw "Matching .md5 file not found for $($dll.Name)."
    }

    return @{ Dll = $dll.FullName; Md5 = $md5.FullName }
}

function Verify-Checksum {
    param (
        [string]$dllPath,
        [string]$md5Path
    )

    $expectedHash = Get-Content $md5Path | ForEach-Object { $_.Trim().ToLower() }
    $actualHash = Get-FileHash -Path $dllPath -Algorithm MD5 | Select-Object -ExpandProperty Hash

    if ($expectedHash -ne $actualHash.ToLower()) {
        throw "MD5 checksum mismatch. Expected: $expectedHash, Actual: $actualHash"
    }

    Write-Host "Checksum verified successfully." -ForegroundColor Green
}

function Is-VersionAtLeast {
param (
    [string]$actualVersion,
    [string]$requiredVersion
)
return ([version]$actualVersion -ge [version]$requiredVersion)
}

function Run-SqlScript {
    param(
        [string]$script,
        [string]$name = "SQL script"
    )
    $escapedServerName = $serverName -replace '\\', '\\\\'
    $connString = "Server=$escapedServerName;Database=$databaseName;User Id=$sqlAdminUser;Password=$sqlPassword;"
    $connection = New-Object System.Data.SqlClient.SqlConnection $connString
    $connection.Open()
    $cmd = $connection.CreateCommand()
    $cmd.CommandText = $script
    $rowsAffected = $cmd.ExecuteNonQuery()
    $connection.Close()

    if ($rowsAffected -ge 0) {
        Write-Host "$name executed. $rowsAffected row(s) affected." -ForegroundColor Green
    } else {
        Write-Host "$name executed." -ForegroundColor Green
    }
}


###################### Main Execution Flow #################################

if (-not $BackupLogMode) {
    try {
        if (-not $DLLPath) {
            Download-ZipFile
        }
    } catch {
        Write-Error "[ERROR] Failed to download the zip file: $_"
        exit 1
    }

    try {
        if (-not $DLLPath) {
            Unzip-ZipFile
        }
    } catch {
        Write-Error "[ERROR] Failed to unzip the file: $_"
        exit 1
    }

    try {
        $files = Find-DllAndMd5
    } catch {
        Write-Error "[ERROR] Failed to find DLL and MD5: $_"
        exit 1
    }

    try {
        Verify-Checksum -dllPath $files.Dll -md5Path $files.Md5
    } catch {
        Write-Error "[ERROR] Checksum verification failed: $_"
        exit 1
    }

    $dllVersion = (Get-Item $files.Dll).VersionInfo.FileVersion
    Write-Host "DLL Version detected: $dllVersion"

    # Escape backslashes for SQL path
    $dllPathSql = $files.Dll -replace '\\', '\\\\'
    Write-Host "Installing CLR assembly" $files.DLL
}


###################### CLR DLL ASSEMBLY SCRIPT #############################
if (-not $BackupLogMode) {

$clrDllSetupSql = @"
-- MASTER CONTEXT
USE master;
IF (SELECT value_in_use FROM sys.configurations WHERE name = 'clr enabled') = 0
BEGIN
    EXEC sp_configure 'clr enabled', 1;  
    RECONFIGURE;
END;

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
    DROP LOGIN FivetranUser;

IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'FivetranCLRKey')
    DROP ASYMMETRIC KEY FivetranCLRKey;

EXEC ('CREATE ASYMMETRIC KEY FivetranCLRKey FROM EXECUTABLE FILE = ''$dllPathSql''');

IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
BEGIN
    CREATE LOGIN FivetranUser FROM ASYMMETRIC KEY FivetranCLRKey;
    GRANT UNSAFE ASSEMBLY TO FivetranUser;
END;

-- USER DATABASE CONTEXT
USE [$databaseName];

DECLARE @assemblyname SYSNAME = 'Fivetran';
DECLARE @cmd NVARCHAR(MAX) = N'';

SELECT @cmd = @cmd + N'DROP PROCEDURE [' + SCHEMA_NAME(SP01.schema_id) + N'].[' + SP01.name + N']; '
FROM sys.assembly_modules SM01
JOIN sys.assemblies SA01 ON SM01.assembly_id = SA01.assembly_id
JOIN sys.procedures SP01 ON SP01.object_id = SM01.object_id
WHERE SA01.name = @assemblyname;

SELECT @cmd = @cmd + N'DROP ASSEMBLY [' + name + N']; ' 
FROM sys.assemblies 
WHERE name = @assemblyname;

EXEC (@cmd);

EXEC('CREATE ASSEMBLY Fivetran FROM ''$dllPathSql'' WITH PERMISSION_SET = UNSAFE');

EXEC('
CREATE OR ALTER PROCEDURE sp_Fivetran_ReadLog (
    @process INT,
    @handle BIGINT,
    @offset BIGINT,
    @bufSize INT,
    @parity TINYINT,
    @filter VARBINARY(1000) = NULL
)
AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ReadLog;
');
"@

if (Is-VersionAtLeast $dllVersion "1.1.3") {
$clrDllSetupSql += @"

EXEC('
    CREATE OR ALTER PROCEDURE sp_Fivetran_ImportTDECertificate (@cerFilename nvarchar(1000), @pvkFilename nvarchar(1000), @password nvarchar(100))
    AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ImportTDECertificate;
    ');

EXEC('
CREATE OR ALTER PROCEDURE sp_Fivetran_IsTDECertificateImported (@thumbprint nvarchar(100))
AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_IsTDECertificateImported;
');

"@
}

# Execute CLR DLL setup script
Run-SqlScript -script $clrDllSetupSql "Fivetran CLR DLL SQL Server Setup Script"

}
###################### MINIMAL PERMISSION SCRIPT FUNCTION #############################

function Run-MinimalPermissionScript {
    param (
        [string]$userName,
        [string]$dbName
    )


$sql = @"
USE [$dbName];

IF NOT EXISTS (
    SELECT 1
    FROM sys.database_principals
    WHERE name = N'$userName'
)
BEGIN
    CREATE USER [$userName] FOR LOGIN [$userName];
END
"@

    Run-SqlScript -script $sql -name "Create database user [$userName] if not exists"


$sql = @"
USE [master];
DECLARE @sql NVARCHAR(MAX);
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) >= 16
    SET @sql = 'GRANT VIEW SERVER PERFORMANCE STATE TO [$userName];';
ELSE
    SET @sql = 'GRANT VIEW SERVER STATE TO [$userName];';
EXEC sp_executesql @sql;

USE [$dbName];
ALTER DATABASE [$dbName] SET TRUSTWORTHY ON;
GRANT VIEW DEFINITION ON DATABASE::[$dbName] TO [$userName];
USE [$dbName];
ALTER ROLE db_datareader ADD MEMBER [$userName];

IF OBJECT_ID(N'sp_fivetran_cdc_enable_db', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_enable_db;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_enable_db WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sys.sp_cdc_enable_db; END');

IF OBJECT_ID(N'sp_fivetran_cdc_drop_job', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_drop_job;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_drop_job WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_cdc_drop_job @job_type = N''capture''; END');

IF OBJECT_ID(N'sp_fivetran_cdc_stop_job', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_stop_job;
EXEC('CREATE PROCEDURE sp_fivetran_cdc_stop_job WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_cdc_stop_job @job_type = N''capture''; END');

IF OBJECT_ID(N'sp_fivetran_cdc_enable_table', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_cdc_enable_table;
EXEC('
CREATE PROCEDURE sp_fivetran_cdc_enable_table
    @source_schema NVARCHAR(500),
    @source_name   NVARCHAR(500),
    @capture_instance NVARCHAR(500) = NULL,
    @role_name        NVARCHAR(500) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    IF @capture_instance IS NULL
        SET @capture_instance = N''fivetran_'' + CAST(OBJECT_ID(@source_schema + ''.'' + @source_name) AS NVARCHAR(500));
    EXEC sys.sp_cdc_enable_table
        @source_schema    = @source_schema,
        @source_name      = @source_name,
        @capture_instance = @capture_instance,
        @role_name        = @role_name;
END
');

GRANT EXECUTE ON sp_fivetran_cdc_enable_db TO [$userName];
GRANT EXECUTE ON sp_fivetran_cdc_drop_job TO [$userName];
GRANT EXECUTE ON sp_fivetran_cdc_stop_job TO [$userName];
GRANT EXECUTE ON sp_fivetran_cdc_enable_table TO [$userName];
"@
    
if (-not $BackupLogMode) {
    
$sql += @"

IF OBJECT_ID(N'sp_fivetran_replflush', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_replflush;
EXEC('CREATE PROCEDURE sp_fivetran_replflush WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; EXEC sp_replflush; END');

IF OBJECT_ID(N'sp_fivetran_repldone', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_repldone;
EXEC('
CREATE PROCEDURE sp_fivetran_repldone
    @xactid     BINARY(10),
    @xact_seqno BINARY(10),
    @numtrans   INTEGER = NULL,
    @time       INTEGER = NULL,
    @reset      INTEGER = NULL
WITH EXECUTE AS SELF
AS
BEGIN
    DECLARE @stmt VARCHAR(200);
    SET @stmt = ''EXEC sp_repldone '' +
                ''@xactid= '' + COALESCE(CONVERT(VARCHAR, @xactid, 1), ''NULL'') +
                '', @xact_seqno= '' + COALESCE(CONVERT(VARCHAR, @xact_seqno, 1), ''NULL'');
    IF @numtrans IS NOT NULL SET @stmt += '', @numtrans= '' + CONVERT(VARCHAR, @numtrans);
    IF @time IS NOT NULL     SET @stmt += '', @time= '' + CONVERT(VARCHAR, @time);
    IF @reset IS NOT NULL    SET @stmt += '', @reset= '' + CONVERT(VARCHAR, @reset);
    EXEC(@stmt);
END
');

IF OBJECT_ID(N'sp_fivetran_dbcc_dbtable', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_dbcc_dbtable;
EXEC('
CREATE PROCEDURE sp_fivetran_dbcc_dbtable
    @db_name NVARCHAR(500)
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON;
    DBCC DBTABLE (@db_name) WITH TABLERESULTS
');

IF OBJECT_ID(N'sp_fivetran_dbcc_loginfo', 'P') IS NOT NULL DROP PROCEDURE sp_fivetran_dbcc_loginfo;
EXEC('CREATE PROCEDURE sp_fivetran_dbcc_loginfo WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; DBCC LOGINFO; END');

GRANT EXECUTE ON sp_fivetran_dbcc_dbtable TO [$userName];
GRANT EXECUTE ON sp_fivetran_dbcc_loginfo TO [$userName];
GRANT EXECUTE ON sp_fivetran_replflush TO [$userName];
GRANT EXECUTE ON sp_fivetran_repldone TO [$userName];
GRANT EXECUTE ON sp_fivetran_readlog TO [$userName];
"@
}

if (-not $BackupLogMode -and (Is-VersionAtLeast $dllVersion "1.1.3")) {
$sql += @"

GRANT EXECUTE ON sp_fivetran_importtdecertificate TO [$userName];
GRANT EXECUTE ON sp_fivetran_istdecertificateimported TO [$userName];
"@
}

    Run-SqlScript -script $sql -name "Grant Minimal Permissions to $userName"
}


# Run minimal permission if required
if ($installMinimalPermission) {   
    if (-not $normalUser) {
       throw "Normal user name must be provided when installMinimalPermission is true."
    }
    Run-MinimalPermissionScript -userName $normalUser -dbName $databaseName
}

Open the PowerShell terminal as an Administrator.
Configure the Binary Log Reader to read either from the online transaction logs or backup transaction logs.
- Online transaction logs:
  1. [Optional] Download Fivetran SQL Server Binary Log Reader DLL and specify the path to the downloaded file as a parameter in the following command. If you don't provide the -DDLPath parameter, the script will automatically download the latest DLL from Fivetran.
    Instructions to download the DLL
    1. Log in to your Fivetran account.
    2. In the Fivetran dashboard, go to the Downloads page.
    3. In the Fivetran SQL Server DLL for Binary Log Reader section, download the latest Fivetran SQL Server Binary Log Reader DLL.
    4. Place the Binary Log Reader DLL file on your SQL Server host machine.
    5. Make a note of the directory path to the DLL file (e.g., C:\MSSQL-CLR\Fivetran_Log_Reader.dll).
  2. Execute the BinLogInstall.ps1 script using the following command to install the CLR DLL and the related CLR stored procedures that are required for the Binary Log Reader to read from the online logs:
    If you have existing connections that use the SQL Server Binary Log Reader, pause them before running this script. Otherwise, the script might temporarily interrupt those connections. You can unpause them once the script has finished running.
```
.\BinLogInstall.ps1  -sqlInstance <localhost> -targetDatabase <database> -sqlAdminUser <sqlAdminUser> -sqlPassword <password> -minimalPermissionsUser <normal_user> [-DLLPath <path_to_Fivetran_DLL>]
```
- Backup transaction logs:
  1. Execute the BinLogInstall.ps1 script using the following command:
```
.\BinLogInstall.ps1  -sqlInstance <localhost> -targetDatabase <database> -sqlAdminUser <sqlAdminUser> -sqlPassword <password> -minimalPermissionsUser <normal_user> -BackupLogMode
```
    This command will not install the CLR DLL and the related CLR stored procedures that are only required for reading from the online logs.

Manual installation

You can manually configure the Binary Log Reader using the minimal permissions model, which allows you to have granular control over database permissions. This approach leverages wrapper stored procedures to safely delegate only the essential tasks from high-privileged roles to the Fivetran user. It ensures secure, limited access while maintaining the functionality required for log-based capture.

The manual installation includes the following steps:

Grant minimal permissions – Assign only essential privileges for log-based sync: metadata access, table reads, and stored procedure execution.
Create wrapper procedures – Safely expose required system functions to the Fivetran user for CDC, replication, and log access.
Enable CLR – Allow SQL Server to run the Binary Log Reader’s .NET-based DLL.
Register and sign the DLL – Download the DLL, register it on the host, and set up access using an asymmetric key and login.
Grant execution rights – Allow the Fivetran user to run the stored procedures and DLL methods.

We can skip the CLR setup and DLL registration steps if you're going to configure reading from backup transaction log files.

Configure minimal permissions

Grant server-level permissions to view metadata, such as table and index structures, needed for log parsing and performance optimization:

USE [master];
GO
DECLARE @sql NVARCHAR(MAX);
IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) >= 16
    SET @sql = 'GRANT VIEW SERVER PERFORMANCE STATE TO <username>;';
ELSE
    SET @sql = 'GRANT VIEW SERVER STATE TO <username>;';
EXEC sp_executesql @sql;
GO

Grant database-level permissions to identify which tables have CDC enabled:

 USE [<database_name>];
 GRANT VIEW DEFINITION ON DATABASE::[<database_name>] TO <username>;

Set the database as trustworthy to allow operations such as executing sp_repldone.

USE [<database_name>];
ALTER DATABASE [<database_name>] SET TRUSTWORTHY ON;

Grant the Fivetran user read access to all tables in the database.

USE [<database_name>];
ALTER ROLE db_datareader ADD MEMBER <username>;

Create the stored procedures required for the Binary Log Reader to read from either the online transaction logs or the backup transaction logs.

sp_fivetran_cdc_enable_db: Enables CDC at the database level.
sp_fivetran_cdc_drop_job: Drops the CDC capture job to reduce resource usage.
sp_fivetran_cdc_stop_job: Stops the CDC capture job to pause change tracking.
sp_fivetran_cdc_enable_table: Enables CDC on individual tables for supplemental logging.

View SQL script

USE [<database_name>];

-- Create CDC enable procedure
IF OBJECT_ID(N'sp_fivetran_cdc_enable_db', 'P') IS NOT NULL
    DROP PROCEDURE sp_fivetran_cdc_enable_db;
GO
CREATE PROCEDURE sp_fivetran_cdc_enable_db
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON
    EXEC sys.sp_cdc_enable_db;
GO

-- Drop CDC job
IF OBJECT_ID(N'sp_fivetran_cdc_drop_job', 'P') IS NOT NULL
    DROP PROCEDURE sp_fivetran_cdc_drop_job;
GO
CREATE PROCEDURE sp_fivetran_cdc_drop_job
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON
    EXEC sp_cdc_drop_job @job_type = N'capture'
GO

-- Stop CDC job 
IF OBJECT_ID(N'sp_fivetran_cdc_stop_job', 'P') IS NOT NULL
    DROP PROCEDURE sp_fivetran_cdc_stop_job;
GO
CREATE PROCEDURE sp_fivetran_cdc_stop_job
WITH EXECUTE AS OWNER
AS
    SET NOCOUNT ON
    EXEC sp_cdc_stop_job @job_type = N'capture'
GO
IF OBJECT_ID(N'sp_fivetran_cdc_enable_table', 'P') IS NOT NULL
    DROP PROCEDURE sp_fivetran_cdc_enable_table;
GO

-- Enable CDC on a table
CREATE PROCEDURE sp_fivetran_cdc_enable_table
    @source_schema NVARCHAR(500),
    @source_name   NVARCHAR(500),
    @capture_instance NVARCHAR(500) = NULL, -- Optional; generated if not provided
    @role_name        NVARCHAR(500) = NULL  -- Optional
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;

    -- Generate the capture instance if not provided
    IF @capture_instance IS NULL
    BEGIN
        SET @capture_instance = N'fivetran_' + CAST(OBJECT_ID(@source_schema + '.' + @source_name) AS NVARCHAR(500));
    END;

    -- Enable CDC for the specified table
    EXEC sys.sp_cdc_enable_table
        @source_schema    = @source_schema,
        @source_name      = @source_name,
        @capture_instance = @capture_instance,
        @role_name        = @role_name;
END;
GO

Grant the Fivetran user execute permissions on those stored procedures you created.

USE [<database_name>];
GRANT EXECUTE ON sp_fivetran_cdc_enable_db TO <username>;
GRANT EXECUTE ON sp_fivetran_cdc_drop_job TO <username>;
GRANT EXECUTE ON sp_fivetran_cdc_stop_job TO <username>;
GRANT EXECUTE ON sp_fivetran_cdc_enable_table TO <username>;

Create the following additional stored procedures if you are configuring the Binary Log Reader to read from the online transaction logs. Skip this step if you are configuring the Binary Log Reader to read from the backup transaction logs.

sp_fivetran_dbcc_loginfo: Executes DBCC LOGINFO to retrieve transaction log details.
sp_fivetran_replflush: Flushes the transaction log using sp_replflush.
sp_fivetran_repldone: Marks a transaction as processed using sp_repldone.

View SQL script

USE [<database_name>];

-- Execute sp_replflush
IF OBJECT_ID(N'sp_fivetran_replflush', 'P') IS NOT NULL
   DROP PROCEDURE sp_fivetran_replflush;
GO

CREATE PROCEDURE sp_fivetran_replflush
WITH EXECUTE AS OWNER
AS
   SET NOCOUNT ON
   EXEC sp_replflush
GO

-- Execute sp_repldone
IF OBJECT_ID(N'sp_fivetran_repldone', 'P') IS NOT NULL
   DROP PROCEDURE sp_fivetran_repldone;
GO

CREATE PROCEDURE sp_fivetran_repldone
   @xactid     BINARY(10),
   @xact_seqno BINARY(10),
   @numtrans   INTEGER = NULL,
   @time       INTEGER = NULL,
   @reset      INTEGER = NULL
WITH EXECUTE AS SELF
AS
   DECLARE @stmt VARCHAR(200)

   SET @stmt = 'EXEC sp_repldone ' +
               '@xactid= ' + CASE WHEN @xactid IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR, @xactid, 1) END +
               ', @xact_seqno= ' + CASE WHEN @xact_seqno IS NULL THEN 'NULL' ELSE CONVERT(VARCHAR, @xact_seqno, 1) END
   IF @numtrans IS NOT NULL
       SET @stmt = @stmt + ', @numtrans= ' + CONVERT(VARCHAR, @numtrans)
   IF @time IS NOT NULL
       SET @stmt = @stmt + ', @time= ' + CONVERT(VARCHAR, @time)
   IF @reset IS NOT NULL
       SET @stmt = @stmt + ', @reset= ' + CONVERT(VARCHAR, @reset)

   EXEC(@stmt)
GO

IF OBJECT_ID(N'sp_fivetran_dbcc_dbtable', 'P') IS NOT NULL
  DROP PROCEDURE sp_fivetran_dbcc_dbtable;
GO

CREATE PROCEDURE sp_fivetran_dbcc_dbtable
   @db_name NVARCHAR(500)
WITH EXECUTE AS OWNER
AS
   SET NOCOUNT ON;
   DBCC DBTABLE (@db_name) WITH TABLERESULTS
GO

-- Run DBCC LOGINFO
IF OBJECT_ID(N'sp_fivetran_dbcc_loginfo', 'P') IS NOT NULL
   DROP PROCEDURE sp_fivetran_dbcc_loginfo;
GO
CREATE PROCEDURE sp_fivetran_dbcc_loginfo
WITH EXECUTE AS OWNER
AS
   SET NOCOUNT ON
   DBCC LOGINFO
GO

Grant the Fivetran user execute permissions on those additional stored procedures you created for reading from the online transaction logs.

USE [<database_name>];
GRANT EXECUTE ON sp_fivetran_dbcc_dbtable TO <username>;
GRANT EXECUTE ON sp_fivetran_dbcc_loginfo TO <username>;
GRANT EXECUTE ON sp_fivetran_replflush TO <username>;
GRANT EXECUTE ON sp_fivetran_repldone TO <username>;

Register the Binary Log Reader DLL and configure CLR access

Skip the steps for registering the CLR DLL if you’re configuring the Binary Log Reader to read from backup transaction log files.

Log in to your Fivetran account.
Go to the Downloads tab.
In the Fivetran SQL Server DLL for Binary Log Reader section, download the latest Fivetran SQL Server Binary Log Reader DLL.
Place the Binary Log Reader DLL file on your SQL Server host machine.
Make a note of the directory path to the DLL file (for example, C:\MSSQL-CLR\Fivetran_Log_Reader.dll). You’ll need it in the next steps.
Connect to your SQL Server database instance using an admin user.
You must use the same admin user that you’ll use to create the Fivetran connector. This is required only during the initial setup.
Enable CLR on your SQL Server database. Perform this step once for each database host.
If CLR is already enabled on your SQL Server database, skip to the next step (sign the DLL). To check if CLR is enabled, run SELECT * FROM sys.configurations WHERE name = 'clr enabled';.
```
USE master
if (SELECT value_in_use
FROM sys.configurations
WHERE name = 'clr enabled') = 0
BEGIN
    EXEC sp_configure 'clr enabled', 1;  
    RECONFIGURE;  
END; 
```

Sign the DLL with an asymmetric key. Perform this step once for each database host. Replace '{Path to Fivetran Binary Log Reader DLL}' with the full directory path to the Fivetran Binary Log Reader DLL file on your SQL Server host machine.

USE master;
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
BEGIN
    DROP LOGIN FivetranUser
END
IF EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'FivetranCLRKey')
BEGIN
    DROP ASYMMETRIC KEY FivetranCLRKey
END
IF NOT EXISTS (SELECT * FROM sys.asymmetric_keys WHERE name = 'FivetranCLRKey')
BEGIN
    CREATE ASYMMETRIC KEY FivetranCLRKey FROM EXECUTABLE FILE = '{Path to Fivetran Binary Log Reader DLL}';
END;

Create a login for the Fivetran user from the asymmetric key and grant UNSAFE ASSEMBLY permissions. Perform this step once for each database host.

USE master
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = 'FivetranUser')
BEGIN
    CREATE LOGIN FivetranUser FROM ASYMMETRIC KEY FivetranCLRKey;    
    GRANT UNSAFE ASSEMBLY TO FivetranUser ;  
END

Create a stored procedure to invoke the log reader DLL. Perform this step once for each database you want to sync with Fivetran. For example, if you have two databases named sales and analytics on the same SQL Server instance, run the following commands twice - first by replacing <database> with sales, then with analytics.

i. Create the assembly that the stored procedure will execute. Replace <database> with the name of your database and '{Path to Fivetran Binary Log Reader DLL}' with the full directory path to the Fivetran Binary Log Reader DLL file on your SQL Server host machine:

USE <database>;
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Fivetran')
BEGIN
    DROP PROCEDURE sp_Fivetran_ReadLog
    DROP ASSEMBLY Fivetran
END
CREATE ASSEMBLY Fivetran
FROM '{Path to Fivetran Binary Log Reader DLL}'
WITH PERMISSION_SET = UNSAFE;

ii. Create the stored procedure that invokes the corresponding method in the Binary Log Reader assembly:

CREATE OR ALTER PROCEDURE sp_Fivetran_ReadLog (
   @process int, 
   @handle bigint, 
   @offset bigint, 
   @bufSize int, 
   @parity tinyint, 
   @filter varbinary(1000) = NULL
)
AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ReadLog;

iii. (If using TDE) Create stored procedures for importing TDE certificates:

CREATE OR ALTER PROCEDURE sp_Fivetran_ImportTDECertificate (
   @cerFilename nvarchar(1000), 
   @pvkFilename nvarchar(1000), 
   @password nvarchar(100)
)
AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ImportTDECertificate;
GO

CREATE OR ALTER PROCEDURE sp_Fivetran_IsTDECertificateImported (
   @thumbprint nvarchar(100)
)
AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_IsTDECertificateImported;

Once you complete this step, the DLL and associated stored procedures are ready to be used by Fivetran.

Grant the Fivetran user execute permissions on the wrapper procedures:

GRANT EXECUTE ON sp_fivetran_readlog TO <username>;
GRANT EXECUTE ON sp_fivetran_importtdecertificate TO <userbane>;
GRANT EXECUTE ON sp_fivetran_istdecertificateimported TO <username>;

Export transparent data encryption certificate (optional)

This step is required if you are using the Binary Log Reader incremental update method and your database has SQL Server transparent data encryption (TDE) enabled.

Run the following SQL command to export the TDE certificate and its private key to a file. Be sure to replace the placeholders with the actual values specific to your environment (<TDE_certificate_name>, <path_to_TDE_certificate_file>, <path_to_TDE_private_key_file>, and <TDE_password>). Also, ensure that the SQL Server service account has write permissions to the specified paths:
```
BACKUP CERTIFICATE <TDE_certificate_name> TO FILE ='<path_to_TDE_certificate_file.cer>'
   WITH PRIVATE KEY 
   (
     FILE='<path_to_TDE_private_key_file.pvk>',
     ENCRYPTION BY PASSWORD='<TDE_password>'
   )
```
Make a note of the command outputs. You will need the two files to configure Fivetran.

Always On Availability Groups For Binary Log Reader (optional)Beta

If you are connecting to a secondary node in an Always On Availability Group using the Binary Log Reader, you must manually enable supplemental logging on the primary node and configure a separate task to advance the CDC log truncation point. For more information on how the Binary Log Reader interacts with Always On Availability Groups, see Using Always On Availability Groups with Binary Log Reader section.

Supplemental logging is already enabled if you're connecting Fivetran to a standalone database or an AG Listener of an Always On availability group. However, if you're connecting directly to the secondary node, you must enable supplemental logging for your tables on the primary node.

When you add a new table to the source database, it is essential to enable supplemental logging for the new table, otherwise the sync will fail.

View SQL script to enable supplemental logging

Replace the <schema> and <table> placeholders with the names of the schema and the table for which you want to enable supplemental logging.

-- You only need to enable CDC once if the database is not already enabled
EXEC sys.sp_cdc_enable_db;

-- Enable CDC for the specified table
DECLARE @source_schema NVARCHAR(500) = <schema>;
DECLARE @source_name NVARCHAR(500) = <table>;
DECLARE @capture_instance NVARCHAR(500);
SET @capture_instance = N'fivetran_' + CAST(OBJECT_ID(@source_schema + '.' + @source_name) AS NVARCHAR);
EXEC sys.sp_cdc_enable_table
    @source_schema = @source_schema,
    @source_name = @source_name,
    @capture_instance = @capture_instance,
    @role_name = NULL;

-- Drop the capture job. You only need to drop the capture job once per database.
EXEC sp_cdc_drop_job @job_type = N'capture';

The CDC tables will not be used by SQL Server and will not contain any actual data. They are created to log the primary key during updates. This process does not add any additional load to the database server and is different from running SQL Server's native CDC replication.

Log truncation

You must create a separate task on the primary node to manage the truncation point for replication. A recommended method is to schedule a SQL Server Agent job that periodically runs the following SQL command to invoke the sp_repldone procedure without any conditions:

EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1;

Finish Fivetran configuration

In your connection setup form, enter a Destination schema prefix. This is used as the connection name and cannot be modified once the connection is created.
Depending on your selection in the Destination schema names field, we will either prefix the connection 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, make sure to keep the Require TLS through Tunnel toggle turned 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.
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.
(Optional and only applicable for Binary Log Reader) Choose your Log Type.
- If you selected Online Transaction Log (default), Fivetran reads changes from the online transaction log.
- If you selected Backup Transaction Log, Fivetran reads changes from backup transaction log files. This option is only supported when the backup transaction log files are stored in Azure Blob Storage. Enter the following information:
- Container Name: Enter the name of the Azure Blob Storage container where your backup log files are stored.
- Connection String: Enter the Shared Access Signature (SAS) connection string for your Azure Blob Storage account. This connection string must have read access to the container specified in Container Name. For more information about generating a SAS connection string, see our Create shared access signature in Azure documentation.
The SAS connection string must remain valid for Fivetran to continuously access the backup log files. Since the SAS token expires after a set time, you must renew it before expiration and update the connection string in the Fivetran connector settings to ensure uninterrupted syncs.
(Optional and only applicable for Binary Log Reader) Set the Use transparent data encryption (TDE) toggle to ON and enter the following information:
- TDE Certificate Path: Enter the directory path containing the certificate public key file you exported in the Export TDE certificate (optional) step (for example, c:\\certificates\tde_certificate.cer).
- TDE Private Key Path: Enter the directory path containing the certificate private key file you exported in the Export TDE certificate (optional) step (for example, c:\\certificates\tde_private_key.pvk).
- TDE Private Key Password: Enter the password for the TDE private key.
In 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 generic 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).

The tests may take a few minutes to finish running.

Connector Overview

Schema Information

Release Notes

API Connection Configuration

Documentation Home