Migrate HVA SQL Server to SQL Server with Binary Log Reader

This guide describes the steps to migrate an HVA SQL Server connection to a generic SQL Server connection that uses the Binary Log Reader incremental sync method.

We are ending support for the HVA SQL Server connector, with End of Availability on December 1, 2026 and End of Life on March 1, 2027. To continue syncing data, you must migrate your HVA SQL Server connection to a SQL Server connection that uses Binary Log Reader as the incremental sync method. Migration happens in place, preserves your connection's replication state, and does not require a historical re-sync.

For more information about the migration process and related details, see our HVA SQL Server migration FAQs.

Prerequisites

• A supported SQL Server version (2016–2025).
• Administrative rights on the database host machine to install the Fivetran DLL.
• A SQL Server account with sysadmin privileges to set up Binary Log Reader. These privileges are required to run the PowerShell script and install the stored procedures.

For the complete list of prerequisites, see our SQL Server setup guide.

Check migration readiness

1. Open the Status tab on the connection details page.
2. Review the warnings and required setup steps in the migration section.

Complete prerequisites

Complete the prerequisites listed in the migration section of the Status tab.

Database setup

Complete the following steps to set up Binary Log Reader:

Perform the following steps to set up the Binary Log Reader method for SQL Server.

1. Set up the Binary Log Reader.

   Choose the Binary Log Reader setup method that applies to your operating system.

   If you configure Binary Log Reader to read backup transaction logs on Windows or Linux, enable Full recovery mode, take an initial full database backup, configure regular transaction log backups, and store the backup files in a location supported by Fivetran.

   WindowsLinuxWindows (Manual)

   This method is for SQL Server instances running on Windows. It uses a PowerShell script that automatically configures all required components for the Binary Log Reader, including the CLR, DLL, required permissions, and related stored procedures. We recommend using this method if PowerShell is available and you want a fast, reliable setup that configures all prerequisites for you.

   1. Download the Binary Log Reader PowerShell installation script (BinLogInstall.ps1) for Windows.

   2. Open the PowerShell terminal as an Administrator.

   3. Configure the Binary Log Reader to read either from the online transaction logs or backup transaction logs.

      Online transaction logsBackup transaction logs

      1. (Optional) Download the Fivetran SQL Server Binary Log Reader DLL. When running the script in the next step, specify the DLL path using the -DLLPath parameter. If you omit this parameter, the script automatically downloads the latest DLL from Fivetran.

         Instructions to download the DLL

         1. In the Fivetran dashboard, go to the Downloads page under Account Settings.
         2. Scroll down to the Fivetran SQL Server Binary Log Reader section and download the latest Fivetran SQL Server Binary Log Reader DLL.
         3. Place the Binary Log Reader DLL file on your SQL Server host machine.
         4. 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.

      2. Run the BinLogInstall.ps1 script using the following command to install the the Binary Log Reader assembly (from the DLL file) and create the related CLR stored procedures required for reading online transaction 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 resume them once the script completes.

         .\BinLogInstall.ps1  -SqlInstance <localhost> -SourceDatabase <database> -SqlAdminUser <sqlAdminUser> -SqlPassword <password> -MinimalPermissionsUser <username> [-DLLPath <path_to_Fivetran_DLL>] [-UseDBDatareaderRole]
         

         Replace the placeholders with actual values:

         • <localhost>: the SQL Server instance name.

         • <database>: the source database for which you are configuring the Binary Log Reader.

         • <sqlAdminUser> and <password>: credentials for a user with the sysadmin role. The script uses this user to apply all required permissions and create the Binary Log Reader objects (assembly and stored procedures).

         • <username>: the database user you created for Fivetran’s exclusive use. The script does not require this user’s password.

         • <path_to_Fivetran_DLL>: the path to the DLL file you downloaded manually.

         • -UseDBDatareaderRole: optional. Adds the Fivetran user to db_datareader instead of using the default fivetran_datareader role, so you do not need to grant separate SELECT permissions on the database, schemas, or tables.

           Without this flag, the script creates the fivetran_datareader role in the source database, adds the Fivetran user to it, and grants SELECT on the sys and cdc schemas to that role. After the script completes, you must manually grant SELECT to fivetran_datareader on the database, schemas, or tables you want Fivetran to discover and sync.

         If the target database is on a read-only AOAG secondary, the script installs the msdb helper procedures that are required on that secondary and skips the target-database objects, because the target-database assembly and procedures replicate from the primary.

      Binary Log Reader requires supplemental logging, which is activated by enabling CDC on the database and on each table selected for sync. By default, Fivetran handles this automatically using helper stored procedures (sp_fivetran_cdc_*) installed by the PowerShell script (BinLogInstall.ps1). If you prefer to manage supplemental logging yourself, you can remove these procedures after installation, but you must then enable CDC manually on every table before syncing it. For more information, see our Self-managed supplemental logging documentation.

      1. Run the BinLogInstall.ps1 script using the following command:

         This command does not download or install the DLL or CLR stored procedures, because they are required only for reading online transaction logs.

         .\BinLogInstall.ps1  -SqlInstance <localhost> -SourceDatabase <database> -SqlAdminUser <sqlAdminUser> -SqlPassword <password> -MinimalPermissionsUser <username> -BackupLogMode [-UseDBDatareaderRole]
         

         Replace the placeholders with actual values:

         • <localhost>: the SQL Server instance name.

         • <database>: the source database for which you are configuring the Binary Log Reader.

         • <sqlAdminUser> and <password>: credentials for a user with the sysadmin role. The script uses this user to apply all required permissions and create the Binary Log Reader objects (assembly and stored procedures).

         • <username>: the database user you created for Fivetran’s exclusive use. The script does not require this user’s password.

         • -UseDBDatareaderRole: optional. Adds the Fivetran user to db_datareader instead of using the default fivetran_datareader role, so you do not need to grant separate SELECT permissions on the database, schemas, or tables.

           Without this flag, the script creates the fivetran_datareader role in the source database, adds the Fivetran user to it, and grants SELECT on the sys and cdc schemas to that role. After the script completes, you must manually grant SELECT to fivetran_datareader on the database, schemas, or tables you want Fivetran to discover and sync.

         If the target database is on a read-only AOAG secondary, the script installs only the required msdb helper procedures on that secondary.

   This method is for SQL Server instances running on Linux. It uses a Bash script that automatically configures the components required for the Binary Log Reader, including the necessary permissions and related stored procedures. On Linux, Binary Log Reader supports reading only from backup transaction logs.

   1. Download the Binary Log Reader Bash installation script (BinLogInstall.sh) for Linux.

   2. Make the installation script executable:

      chmod +x BinLogInstall.sh
      

      You can run the installation script on the SQL Server host or another Linux host. sqlcmd must be installed on the host where you run the script. If you run the script on another host, ensure that the host has network access to your SQL Server instance.

   3. Run the installation script with the following command to automatically configure the required components for the Binary Log Reader, including required permissions and related stored procedures:

      ./BinLogInstall.sh --SqlInstance <sql_instance> --SourceDatabase <database> --SqlAdminUser <sqlAdminUser> --SqlPassword <password> --MinimalPermissionsUser <username>
      

      Replace the placeholders with actual values:

      • <sql_instance>: the SQL Server instance name or address.
      • <database>: the source database for which you are configuring the Binary Log Reader.
      • <sqlAdminUser> and <password>: the credentials for a user with the sysadmin role. The script uses this user to apply the required permissions and create the Binary Log Reader database objects, including related stored procedures.
      • <username>: the database user you created for Fivetran's exclusive use. The script does not require this user's password.

   This method is for SQL Server instances running on Windows. It lets you run each configuration step yourself by executing SQL commands, manually registering the DLL, and explicitly applying permissions and wrapper stored procedures. Use this method if PowerShell is restricted in your environment or if you need granular control over permissions and database objects.

   This approach uses the minimal permissions model, which enables you to define precisely which stored procedures are created and what the Fivetran user can execute. It leverages wrapper stored procedures to safely delegate essential tasks from high-privileged roles to the Fivetran user, ensuring secure, limited access while maintaining the functionality required for log-based capture.

   You can configure the Binary Log Reader to read from either the online transaction logs or the backup transaction logs. The configuration steps depend on the log source you choose:

   • For online transaction logs, you must perform the steps in both the Configure minimal permissions and wrapper procedures and Register DLL and configure CLR access sections.
   • For backup transaction logs, you must perform the steps only in the Configure minimal permissions and wrapper procedures section.

   
   

   Configure minimal permissions and wrapper procedures

   The configuration steps in this section include:

   • Granting essential privileges for log-based sync: metadata access, table reads, and stored procedure execution.
   • Creating wrapper procedures that safely expose the required system functions for CDC, replication, and log access.
   • Granting execution rights that allow the Fivetran user to run the wrapper procedures.

   Expand for instructions

   Replace the placeholders <database> and <username> with the actual values.

   1. Connect to your SQL Server instance using a login with the sysadmin role.

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

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

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

   4. Enable supplemental logging (cdc) for database to allow operations such as executing sp_cdc_enable_table.

      USE [<database>];
      EXEC sys.sp_cdc_enable_db;
      

   5. Grant table read access using one of the following models: broad read access, where you add the Fivetran user to db_datareader, or scoped read access, where you create a fivetran_datareader role, add the Fivetran user to it, and grant SELECT only on the databases, schemas, or tables you want Fivetran to discover and sync.

      -- Broad read access model
      USE [<database>];
      ALTER ROLE db_datareader ADD MEMBER <username>;
      

      -- Scoped read access model
      USE [<database>];
      IF NOT EXISTS (
      SELECT 1
      FROM sys.database_principals
      WHERE name = N'fivetran_datareader'
      AND type = 'R'
      )
      BEGIN
      CREATE ROLE [fivetran_datareader];
      END;
      
      IF NOT EXISTS (
      SELECT 1
      FROM sys.database_role_members drm
      JOIN sys.database_principals r
      ON drm.role_principal_id = r.principal_id
      JOIN sys.database_principals m
      ON drm.member_principal_id = m.principal_id
      WHERE r.name = N'fivetran_datareader'
      AND m.name = N'<username>'
      )
      BEGIN
      ALTER ROLE [fivetran_datareader] ADD MEMBER <username>;
      END;
      -- Grant access only to the databases, tables, or schemas you want Fivetran to read
      GRANT SELECT ON DATABASE::[<database>] TO [fivetran_datareader];
      -- or
      GRANT SELECT ON OBJECT::[<schema>].[<table>] TO [fivetran_datareader];
      -- or
      GRANT SELECT ON SCHEMA::[<schema>] TO [fivetran_datareader];
      

   6. Grant the Fivetran user bulk operation for reading backup log files.

      USE [master];
      GRANT ADMINISTER BULK OPERATIONS TO <username>;
      

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

      Binary Log Reader requires supplemental logging, which is activated by enabling CDC on the database and on each table selected for sync. The following list includes the sp_fivetran_cdc_* helper stored procedures that Fivetran uses to manage supplemental logging. If you prefer to manage supplemental logging yourself, you can skip installing these procedures, but you must then enable CDC manually on every table before syncing it. For more information, see our Self-managed supplemental logging documentation.

      Create the following procedures in the target database:

      • 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.
      • sp_fivetran_replflush: Flushes the transaction log using sp_replflush.
      • sp_fivetran_repldone: Marks a transaction as processed using sp_repldone.

      Create the following procedures in msdb:

      • sp_fivetran_xp_dirtree: Allows directory listing for reading backup log files.
      • sp_fivetran_xp_fileexist: Allows to check if the directory exists.
      • sp_fivetran_restore_info: Retrieves metadata from backup files for backup log reading.
      For each AOAG secondary, install only the helper Fivetran stored procedures in msdb. The target-database assembly and target-database stored procedures replicate from the primary to the secondary, so you do not need to install those target-database objects on the secondary.

      View SQL script

      USE [<database>];
      
      -- 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
      
      -- 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
      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
      GO
      
      -- Create stored procedures in msdb database
      USE [msdb];
      GO
      
      -- Create user in msdb if it does not exist
      IF NOT EXISTS (
      SELECT 1
      FROM sys.database_principals
      WHERE name = N'<username>'
      )
      BEGIN
      CREATE USER [<username>] FOR LOGIN [<username>];
      END
      GO
      
      IF OBJECT_ID(N'dbo.sp_fivetran_xp_dirtree', 'P') IS NOT NULL
      DROP PROCEDURE dbo.sp_fivetran_xp_dirtree;
      GO
      
      CREATE PROCEDURE dbo.sp_fivetran_xp_dirtree
      @path NVARCHAR(4000),
      @depth INT = 1,
      @fileFlag INT = 1
      WITH EXECUTE AS OWNER
      AS
      SET NOCOUNT ON;
      EXEC master.dbo.xp_dirtree @path, @depth, @fileFlag;
      GO
      
      IF OBJECT_ID(N'dbo.sp_fivetran_xp_fileexist', 'P') IS NOT NULL
      DROP PROCEDURE dbo.sp_fivetran_xp_fileexist;
      GO
      
      CREATE PROCEDURE dbo.sp_fivetran_xp_fileexist
      @path NVARCHAR(4000)
      WITH EXECUTE AS OWNER
      AS
      SET NOCOUNT ON;
      EXEC master.dbo.xp_fileexist @path;
      GO
      
      IF OBJECT_ID(N'dbo.sp_fivetran_restore_info', 'P') IS NOT NULL
      DROP PROCEDURE dbo.sp_fivetran_restore_info;
      GO
      
      CREATE PROCEDURE dbo.sp_fivetran_restore_info
      @backupFile NVARCHAR(4000),
      @infoType NVARCHAR(20) -- 'HEADER', 'FILELIST', 'LABEL', 'VERIFY'
      WITH EXECUTE AS OWNER
      AS
      BEGIN
      SET NOCOUNT ON;
      DECLARE @sql NVARCHAR(MAX);
      DECLARE @source NVARCHAR(10);
      
      -- Validate input to prevent SQL injection
      -- Block invalid characters: semicolon, single quote, double quote, and SQL comments
      IF @backupFile LIKE '%[;''""]%' OR @backupFile LIKE '%---%'
      BEGIN
      RAISERROR('Invalid characters detected in backup file path', 16, 1);
      RETURN;
      END
      
      -- Determine if backup source is URL, TAPE, or DISK
      IF LOWER(@backupFile) LIKE 'https://%'
      SET @source = 'URL';
      ELSE IF LOWER(@backupFile) LIKE '\\.\tape%' OR LOWER(@backupFile) LIKE 'tape%'
      SET @source = 'TAPE';
      ELSE
      SET @source = 'DISK';
      
      IF @infoType = 'HEADER'
      BEGIN
      SET @sql = 'RESTORE HEADERONLY FROM ' + @source + ' = ''' + @backupFile + ''';';
      EXEC sp_executesql @sql;
      END
      ELSE IF @infoType = 'FILELIST'
      BEGIN
      SET @sql = 'RESTORE FILELISTONLY FROM ' + @source + ' = ''' + @backupFile + ''';';
      EXEC sp_executesql @sql;
      END
      ELSE IF @infoType = 'LABEL'
      BEGIN
      SET @sql = 'RESTORE LABELONLY FROM ' + @source + ' = ''' + @backupFile + ''';';
      EXEC sp_executesql @sql;
      END
      ELSE IF @infoType = 'VERIFY'
      BEGIN
      SET @sql = 'RESTORE VERIFYONLY FROM ' + @source + ' = ''' + @backupFile + ''';';
      EXEC sp_executesql @sql;
      END
      ELSE
      BEGIN
      RAISERROR('Invalid @InfoType. Use HEADER, FILELIST, LABEL, or VERIFY.', 16, 1);
      END
      END
      GO
      

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

      USE [<database>];
      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>;
      GRANT EXECUTE ON sp_fivetran_replflush TO <username>;
      GRANT EXECUTE ON sp_fivetran_repldone TO <username>;
      
      USE [msdb];
      GRANT EXECUTE ON dbo.sp_fivetran_xp_dirtree TO <username>;
      GRANT EXECUTE ON dbo.sp_fivetran_xp_fileexist TO <username>;
      GRANT EXECUTE ON dbo.sp_fivetran_restore_info TO <username>;
      

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

      Create the following procedure in the target database:

      • sp_fivetran_repltrans: Retrieves active replication transactions using sp_repltrans.

      Create the following procedures in msdb:

      • sp_fivetran_dbcc_dbtable: Executes DBCC DBTABLE to retrieve table information.
      • sp_fivetran_dbcc_loginfo: Executes DBCC LOGINFO to retrieve transaction log details.
      View SQL script

      USE [<database>];
      
      -- Execute sp_repltrans
      IF OBJECT_ID(N'sp_fivetran_repltrans', 'P') IS NOT NULL
      DROP PROCEDURE sp_fivetran_repltrans;
      GO
      
      CREATE PROCEDURE sp_fivetran_repltrans
      WITH EXECUTE AS OWNER
      AS
      SET NOCOUNT ON;
      EXEC sp_repltrans;
      GO
      
      USE [msdb];
      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
      @db_name NVARCHAR(500)
      WITH EXECUTE AS OWNER
      AS
      SET NOCOUNT ON
      DBCC LOGINFO (@db_name) WITH TABLERESULTS
      GO
      

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

       USE [msdb];
       GRANT EXECUTE ON sp_fivetran_dbcc_dbtable TO <username>;
       GRANT EXECUTE ON sp_fivetran_dbcc_loginfo TO <username>;
       USE [<database>];
       GRANT EXECUTE ON sp_fivetran_repltrans TO <username>;
       

   
   

   Register DLL and configure CLR access

   Skip this section if you're configuring the Binary Log Reader to read from backup transaction logs.

   The configuration steps in this section include:

   • Enabling CLR to let SQL Server load and execute the Binary Log Reader's .NET-based DLL
   • Downloading the Fivetran SQL Server Binary Log Reader DLL, registering it on the host, and configuring access using an asymmetric key and a login created from that key
   • Creating the DLL-backed CLR stored procedures used to read online transaction logs
   • Granting execution rights that allow the Fivetran user to run the DLL-backed procedures.

   Expand for instructions

   1. Download the Fivetran SQL Server Binary Log Reader DLL and save it on your SQL Server host machine.

      Instructions to download the DLL

      1. Log in to your Fivetran account.
      2. In the Fivetran dashboard, go to the Downloads page under Account Settings.
      3. Scroll down to the Fivetran SQL Server Binary Log Reader section and 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 (for example, C:\MSSQL-CLR\Fivetran_Log_Reader.dll). You'll need it in the next steps.

   2. Connect to your SQL Server instance using a login with the sysadmin role.

      You must use the same admin user that you'll use to create the Fivetran connector. This is required only during the initial setup.

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

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

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

   6. Create an assembly and stored procedures to invoke the log reader DLL. You only need to do this in one database per SQL Server host. The connector automatically reuses the assembly and stored procedures for all other databases on the same host.

      If the database is in an AOAG and you connect to a read-only secondary, do not create these target-database CLR objects on the secondary. The assembly and target-database stored procedures replicate from the primary. On the secondary, you only need the required msdb helper procedures.

      1. Create the assembly that the stored procedure will execute. Replace <database> with the name of the database you intend to place the CLR stored procedures in 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>;
         
         -- Add assembly to trusted assemblies for SQL Server 2017+ (supports CLR strict security)
         IF CAST(SERVERPROPERTY('ProductMajorVersion') AS INT) >= 14
         BEGIN
         DECLARE @hash VARBINARY(64);
         SELECT @hash = HASHBYTES('SHA2_512', (SELECT * FROM OPENROWSET(BULK N'{Path to Fivetran Binary Log Reader DLL}', SINGLE_BLOB) AS x));
         IF NOT EXISTS (SELECT * FROM sys.trusted_assemblies WHERE hash = @hash)
         BEGIN
         EXEC sys.sp_add_trusted_assembly @hash, N'Fivetran CLR Assembly';
         END
         END
         
         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;
         

      2. Create the stored procedures that invoke the corresponding methods 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;
         
         CREATE OR ALTER PROCEDURE sp_fivetran_logslice (
         @process INT,
         @handle BIGINT,
         @offset BIGINT,
         @bufSize INT
         )
         AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_LogSlice;
         

      3. (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;
         

      4. (If using TDE with decrypted DEKs) Create the stored procedure for importing a decrypted DEK:

         CREATE OR ALTER PROCEDURE sp_fivetran_importdecrypteddek (
         @thumbprint nvarchar(100),
         @decryptedDek varbinary(500)
         )
         AS EXTERNAL NAME Fivetran.LogReader.sp_Fivetran_ImportDecryptedDek;
         

      Once you complete this step, the DLL and associated stored procedures are ready for use by Fivetran.

   7. Grant the Fivetran user EXECUTE permissions on the wrapper procedures:

      GRANT EXECUTE ON sp_fivetran_readlog TO <username>;
      GRANT EXECUTE ON sp_fivetran_logslice TO <username>;
      GRANT EXECUTE ON sp_fivetran_importtdecertificate TO <username>;
      GRANT EXECUTE ON sp_fivetran_istdecertificateimported TO <username>;
      -- If you created sp_fivetran_importdecrypteddek
      GRANT EXECUTE ON sp_fivetran_importdecrypteddek TO <username>;
      

   
   

   Self-managed supplemental logging

   By default, Fivetran manages supplemental logging automatically using helper stored procedures. You can also choose to manage supplemental logging yourself by skipping the helper procedures installation during setup or by removing them after installation.

   • Self-managed supplemental logging is supported only when Binary Log Reader is configured to read from backup transaction logs and log truncation is managed by SQL Server Agent.
   • We don't recommend managing supplemental logging yourself for most use cases. Choose this approach only if you have dedicated database administration resources and proven operational procedures for managing supplemental logging independently.
   Expand for details

   The following are the helper stored procedures that Fivetran uses to manage supplemental logging:

   • sp_fivetran_cdc_enable_db: Enables supplemental logging (CDC) at the database level
   • sp_fivetran_cdc_enable_table: Enables supplemental logging (CDC) on individual tables
   • sp_fivetran_cdc_stop_job: Stops the supplemental logging capture job
   • sp_fivetran_cdc_drop_job: Drops the supplemental logging capture job

   Managing supplemental logging yourself carries critical risks and requires you to take full responsibility for the following:

   • Activating supplemental logging: Enable CDC on every new table in your source database before syncing it. Failure to do this causes Fivetran to raise a SupplementalLoggingNotEnabledTask, pausing syncing until you manually enable supplemental logging for that table.
     • Execute sys.sp_cdc_enable_db at the database level before syncing any tables
     • Execute sys.sp_cdc_enable_table for each table before syncing it
   • Coordinating log truncation: SQL Server Agent manages the capture job and log truncation. Use sp_cdc_drop_job and sp_cdc_stop_job as needed to ensure log truncation is properly coordinated with your database backups and to prevent excessive transaction log growth.
   • Handling all CDC administration overhead: Without the helper procedures, every new table and every schema change requires manual database administration and CDC setup.

   ---

2. (Optional) Export Transparent Data Encryption (TDE) certificate and private key. This step applies only if you use the Binary Log Reader method and your database has SQL Server transparent data encryption (TDE) enabled.

   Expand for instructions

   1. Run the following SQL command to export the TDE certificate and its private key to files. Replace the placeholders <TDE_certificate_name>, <path_to_TDE_certificate_file>, <path_to_TDE_private_key_file>, and <TDE_password> with the actual values specific to your environment. Make sure 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>'
          )
      

   2. Run the following commands in the command prompt to encode the certificate and private key:

      If you are using a Windows machine, download a Base64 utility. Base64 encoding ensures that the certificate and key are in a format that is easily transferable, especially when they need to be imported into systems that require a specific format or do not handle binary files well.

      base64 <path_to_TDE_certificate_file>
      base64 <path_to_TDE_private_key_file>
      

   3. Make a note of the command outputs. You will need them later when you specify replication parameters in the Fivetran setup form.

   
   

3. (Optional) Configure Always On Availability Group (AOAG). This step applies only if you use the Binary Log Reader method and connect to a secondary node in an Always On Availability Group.

   If you connect directly to a secondary node, you must manually enable supplemental logging for your tables on the primary node and configure a separate task to advance the CDC log truncation point. However, if you connect to a standalone database or an AG listener of an Always On Availability Group, supplemental logging is already enabled.

   For more information on how the Binary Log Reader interacts with Always On Availability Groups, see Using Binary Log Reader with Always On Availability Groups and read replicas section.

   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.

   Expand for instructions

   1. Enable supplemental logging on the primary node for each table you want to sync. Replace <schema> and <table> with the actual 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 created for this configuration are used only to log the primary key during updates. They do not contain actual replicated data and are different from running SQL Server's native CDC replication. This process does not add any additional load to the database server and is different from running SQL Server's native CDC replication.

   2. Create a separate task on the primary node to manage the truncation point for replication. A recommended approach is to schedule a SQL Server Agent job that periodically runs:

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

   
   

Windows authentication setup (optional)

If you are using the Windows Authentication method to connect to the SQL Server database, complete the following setup steps. Skip this step if you are using a SQL Server login and password.

1. Perform the Windows Authentication Server setup steps.
2. Edit your HVA SQL Server connection. In the setup form, enter the Windows Auth Server port and Windows Auth Server certificate.

Validate eligibility

1. Click Test Setup.
2. If a test fails, follow the remediation instructions to resolve the issue.
3. Run a sync to re-check eligibility.

Ensure no migration warnings remain in the Status tab. If no warnings appear, the connection is ready for migration.

Migrate your connection

Once your connection is eligible, you can run the migration by pressing the Migrate button. If it is successful, you will see a green Migration successful banner above the Migrate button.

Verify after migration

Ensure that your tables are syncing as expected and monitor your connection for errors or warnings for 24 to 48 hours.

For additional information or troubleshooting guidance, see our HVA SQL Server migration FAQs or contact our support team.