SQL Server Binary Log Reader Setup Guide

Follow these instructions to replicate your SQL Server database to your destination using Fivetran's binary log reader connector.

Prerequisites:

To set up your SQL Server Binary Log Reader connector, you need:

• SQL Server 2022 on Windows with .NET Framework 4.8 installed
• A SQL Server database with admin account access
• A SQL Server admin account
• Access to your database's host machine
• Ability to install DLLs (dynamic link libraries) on that machine
• Access to log files on that machine
• Source database must be in full recovery mode

Setup instructions

Acquire Fivetran Binary Log Reader DLL

1. Acquire the Fivetran Binary Log Reader DLL from the Fivetran team.
2. Place the Binary Log Reader DLL on your SQL Server host machine.
3. Record the path to the DLL. You will need it to set up your connector. We'll assume that the path is C:\MSSQL-CLR\.

Create Fivetran user

As an admin user, create a login for the Fivetran user and grant it the UNSAFE ASSEMBLY permission. Use the same admin user you will use to create your connector. Perform these steps once for each database host.

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

Enable CLR

IMPORTANT: If your SQL Server database has CLR enabled, skip ahead to the Sign the DLL step. If you're not sure whether you have CLR enabled, run the following command to check:

SELECT * FROM sys.configurations
WHERE name = 'clr enabled'

As an admin user, run this SQL command to enable CLR on your SQL Server database. Use the same admin user you will use to create your connector. Perform these steps once for each database host.

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

As an admin user, run this SQL command to sign the DLL. Use the same admin user you will use to create your connector. Perform these steps once for each database host.

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 = 'C:\MSSQL-CLR\LogData.dll';     
END;

Create a stored procedure

Create a stored procedure on your database that knows how to call the log reader DLL. Perform this step once for each database that you want to sync with Fivetran. For example, if you have two databases called sales and analytics on the same SQL Server instance, you would run these commands twice - first replacing {your database} with sales, then with analytics.

use {your database};
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = 'Fivetran')
BEGIN
	DROP PROCEDURE sp_Fivetran_ReadLog
	DROP ASSEMBLY Fivetran
END
CREATE ASSEMBLY Fivetran
FROM 'C:\MSSQL-CLR\LogData.dll'
WITH PERMISSION_SET = UNSAFE; 


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;

Enable supplemental logging

For each table that you want Fivetran to sync, enable the table for CDC, then drop the CDC capture job:

DECLARE @capture_instance NVARCHAR(500);
SET @capture_instance = N'fivetran_' + N'source_schema' + N'_' + N'table_name';

EXEC sys.sp_cdc_enable_table
    @source_schema = N'source_schema',
    @source_name = N'table_name',
    @capture_instance = @capture_instance,
    @role_name = NULL;

-- Drop the capture job
EXEC sp_cdc_drop_job @job_type = N'capture';        

TIP: You only need to drop the capture job once per database.

Once you complete this step, supplemental logging is enabled and the DLL/stored procedure is operable by Fivetran.