High-Volume Agent SQL Server Setup Guidelink
Follow these instructions to replicate your SQL Server database to your destination using Fivetran.
Prerequisiteslink
To connect your SQL Server database to Fivetran, you need:
- A Fivetran account with an Enterprise or Business Critical plan
- SQL Server 2012 - 2022
- A user with
db_owner
role orsysadmin
fixed server role available to use in your Fivetran connector - A database in full recovery mode, with a full backup since full recovery mode was enabled
- Log backups in the native SQL Server format, accessible to the source server
- Your database host's IP address (e.g.,
1.2.3.4
) or domain (your.server.com
) - Your database's port (usually
1433
) - The IP address and port number (usually
4343
) of HVA - Installation of High-Volume Agent (HVA) on your SQL Server source database host
IMPORTANT: We do not support single-user mode.
Setup instructionslink
NOTE: Steps 1-10 are pre-configuration steps that must be completed before setting up the connector in the Fivetran Setup Form in Step 11.
Choose connection method link
First, decide whether to connect your generic SQL Server database:
IMPORTANT: No matter which option you select, you must install the Fivetran HVA on your database host.
Connect directly (TLS required)link
IMPORTANT: 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 the HVA installed on the database host. All communication between Fivetran and the HVA is encrypted. The HVA has a direct connection to your SQL Server database.
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 depends on how your SQL Server database is hosted (cloud platform, on-premises, etc.)
Connect using SSH (TLS optional)link
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 connector 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.
Connect using private networkinglink
IMPORTANT: You must have a Business Critical plan to use private networking.
Private networking enables communication between private networks and services without exposing traffic to the public internet. Private networking is the most secure connection method. We support the following providers:
- AWS PrivateLink – used for VPCs and either AWS-hosted or on-premises services. See our AWS PrivateLink setup guide for details.
- Azure PrivateLink – used for Virtual Networks (VNets) and either Azure-hosted or on-premises services. See our Azure PrivateLink setup guide for details.
- Google Cloud Private Service Connect – used for VPCs and either Google-hosted or on-premises services. See our Google Cloud Private Service Connect setup guide for details.
Connect using Proxy Agentlink
NOTE: To connect using a Proxy Agent, you must download and install a High-Volume Agent (HVA).
Fivetran connects to the HVA through the Proxy Agent, providing secure communication between Fivetran processes and your database host. The Proxy Agent is installed in the customer's 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.
Allow TCP/IP protocollink
Verify that your database server is configured to allow TCP/IP connections. If your database instance does not have TCP/IP protocol enabled, do the following:
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.
TIP: 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.
TIP: 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 userlink
Create a database user for Fivetran's exclusive use.
Connect to your SQL Server database as an admin user.
Decide whether the Fivetran user will authenticate using a database password or Windows authentication.
NOTE: You can use Windows Authentication only if the HVA and the database are operating on a Windows platform.
Password Authentication
To create a user with database password authentication, execute the following SQL command. Replace
<database>
with the name of your database,<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>;
Windows Authentication
To create a user with Windows authentication, execute the following SQL command. Replace
<database>
,<domain>
, and<username>
with the name of your database, your Active Directory domain name, and the existing domain username you would like Fivetran to use.NOTE: The domain user must be the same user to start the High-Volume Agent process.
USE [<database>]; CREATE LOGIN <domain>\<username> FROM WINDOWS; CREATE USER <username> FOR LOGIN <username>;
Grant user permissionslink
This section outlines the necessary permissions for capturing changes from SQL Server database. For simplicity, we have categorized these permissions into three models: Minimal, SysAdmin, and DbOwner.
All three models can be applied to both the Direct Capture and Archive Log Only capture methods. The choice of which model to use depends on your specific business requirements and needs.
- SysAdmin: This is the easiest to set up and use, but it grants extensive permissions and authority over the SQL Server instance, potentially introducing security risks.
- DbOwner This model provides extensive permissions within a specific database only but requires more effort to set up.
- Minimal: This model does not necessitate the
sysadmin
ordb_owner
permissions, but involve the most effort in configuration.
NOTE: The DbOwner and Minimal models are only available for SQL Server 2012 and above. For the older versions of SQL Server, the SysAdmin model must be used.
SysAdminlink
In this permission model, the Fivetran database user must be granted the sysadmin
fixed server role. To grant the Fivetran login a sysadmin
role in the SQL Server instance, run the following command:
ALTER SERVER ROLE sysadmin ADD MEMBER <loginname>;
DbOwnerlink
In this permission model, the Fivetran database user must be granted the db_owner
role. To grant the Fivetran user a db_owner
role in the SQL Server database, run the following command:
ALTER ROLE db_owner ADD MEMBER <username>;
Additionally, the following configuration requirements must be met/performed for this permission model.
Expand for instructions
NOTE: High-Volume Agent must already be installed before performing the following steps.
Create wrapper stored procedureslink
NOTE: This step is needed once when High-Volume Agent is installed for a SQL Server instance. But in the Always On AG configuration, this step is needed on each Always On AG node.
The 'wrapper' stored procedures allow HVA to interact with designated read-only SQL Server objects required for log-based capture on the source database.
Create special 'wrapper' SQL stored procedures in
msdb
database:A user with the
sysadmin
fixed server role must create special 'wrapper' SQL stored procedures calledsp_hvr_dblog
,sp_hvr_dbtable
, andsp_hvr_loginfo
so that the HVA can call the required SQL Server's read-only functions, such asfn_dump_dblog
. This must be done inside the SQL Server special databasemsdb
, not the actual source database. The SQL query to create these procedures is available in the filehvrcapsysadmin.sql
in the%HVR_HOME%\dbms\sqlserver
directory. The database user must then be allowed to execute these procedures. For this, the user must be added to the specialmsdb
database and the following grants must be provided:use msdb; create user <username> for login <username>; grant execute on sp_hvr_dblog to <username>; grant execute on sp_hvr_dbtable to <username>; grant execute on sp_hvr_loginfo to <username>;
Grant special read-only privilegelink
NOTE: This step is needed once when High-Volume Agent is installed for a SQL Server instance. But in the Always On AG configuration, this step is needed on each Always On AG node.
A sysadmin
user must grant the database user a special read-only privilege in the master database.
use master;
grant view server state to <username>;
NOTE: In SQL Server 2022, the
VIEW SERVER STATE
permission was split into two permissions:VIEW SERVER PERFORMANCE STATE
andVIEW SERVER SECURITY STATE
. When capturing from SQL Server 2022, the user only needsVIEW SERVER PERFORMANCE STATE
.
Enable supplemental logginglink
For HVA to perform log-based capture, SQL Server needs to generate full row images and add supplementary information to the transaction log, which is referred to as supplemental logging. Supplemental logging must be enabled on all tables selected for sync. To do this, use a dedicated script called supp_log_add.sql
, which should be executed on the source database before the initial sync.
IMPORTANT: This script is not applicable in scenarios where native Change Data Capture (CDC) or native replication is used for purposes unrelated to HVA.
The following is a sample of the supp_log_add.sql
script. In the script body, substitute source_db
parameter with your corresponding value.
use [source_db]
go
set implicit_transactions off
go
—- Enable CDC on the database
EXEC sp_cdc_enable_db
go
If you do not run the supp_log_add.sql
for the required tables before the initial sync, Fivetran will automatically generate the necessary SQL statements and write them into the supp_log_add.sql
script file on the source machine. An error message will be displayed in the UI specifying the script file name and the required permission level to execute it. The script must be manually run on the source database by a user with the sysadmin
fixed server role.
NOTE: 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. For steps to verify if CDC is enabled for your tables, see section Verify CDC status for tables.
Minimallink
In this permission model, the Fivetran database user is not granted or does not require sysadmin
or db_owner
roles at runtime. However, the following configurations must be performed for this permission model.
Expand for instructions
NOTE: High-Volume Agent must already be installed before performing the following steps.
Create wrapper stored procedureslink
The 'wrapper' stored procedures allow HVA to interact with designated read-only SQL Server objects required for log-based capture on the source database.
Create special 'wrapper' SQL stored procedures in
msdb
database:NOTE: This step is needed once when High-Volume Agent is installed for a SQL Server instance. But in the Always On AG configuration, this step is needed on each Always On AG node.
A user with the
sysadmin
fixed server role must create special 'wrapper' SQL stored procedures calledsp_hvr_dblog
,sp_hvr_dbtable
, andsp_hvr_loginfo
so that the HVA can call the required SQL Server's read-only functions, such asfn_dump_dblog
. This must be done inside the SQL Server special databasemsdb
, not the actual source database. The SQL query to create these procedures is available in the filehvrcapsysadmin.sql
in the%HVR_HOME%\dbms\sqlserver
directory. The database user must then be allowed to execute these procedures. For this, the user must be added to the specialmsdb
database and the following grants must be provided:use msdb; create user <username> for login <username>; grant execute on sp_hvr_dblog to <username>; grant execute on sp_hvr_dbtable to <username>; grant execute on sp_hvr_loginfo to <username>;
Create 'wrapper' SQL stored procedures in each source database:
NOTE: This step is needed once when each new source database is being set up.
A user with
db_owner
role orsysadmin
fixed server role must create 'wrapper' SQL stored procedures in each source database so that HVA can call the SQL Server's read-only proceduressp_helppublication
,sp_helparticle
,fn_dblog
, etc. The SQL query to create these read-only procedures is available in the file calledhvrcapdbowner.sql
in the%HVR_HOME%\dbms\sqlserver
directory. The database user must then be allowed to execute these procedures.The following grants must be granted to the user inside each source database:
use <source_database>; grant execute on sp_hvr_check_publication to <username>; grant execute on sp_hvr_check_article to <username>; grant execute on sp_hvr_dblog to <username>; grant execute on sp_hvr_repldone to <username>; grant execute on sp_hvr_repltrans to <username>;
Grant special read-only privilegeslink
A user with
sysadmin
fixed server role must grant the database user a special read-only privilege in the master database.NOTE: This step is needed once when High-Volume Agent is installed for a SQL Server instance. But in the Always On configuration, this step is needed on each Always On AG node.
use master; grant view server state to <username>;
NOTE: In SQL Server 2022, the
VIEW SERVER STATE
permission was split into two permissions:VIEW SERVER PERFORMANCE STATE
andVIEW SERVER SECURITY STATE
. When capturing from SQL Server 2022, the user only needsVIEW SERVER PERFORMANCE STATE
.A user with
db_owner
role orsysadmin
fixed server role must grant the database user the following read-only privilege.NOTE: This step is needed once when each new source database is being set up.
use <source_database>; alter role db_datareader add member <username>;
Enable supplemental logginglink
For HVA to perform log-based capture, SQL Server needs to generate full row images and add supplementary information to the transaction log, which is referred to as supplemental logging. Supplemental logging must be enabled on all tables selected for sync. To do this, use a dedicated script called supp_log_add.sql
, which should be executed against the source database before the initial sync.
IMPORTANT: This script is not applicable in scenarios where native Change Data Capture (CDC) or native replication is used for purposes unrelated to HVA.
The following is a sample of the supp_log_add.sql
script. In the script body, substitute source_db
, source_schema
, table_name
parameters with your corresponding values.
use [source_db]
go
set implicit_transactions off
go
—- Enable CDC on the database
EXEC sp_cdc_enable_db
go
—- Enable CDC on each table needed for replication
EXEC sp_cdc_enable_table @source_schema = N'[source_schema]',
@source_name = N'[table_name]',
@capture_instance = CONCAT('hvr_', OBJECT_ID(N'[source_schema.table_name]')),
@role_name = null
go
—- If the customer is not using CDC with an existing process, we can safely drop the capture job —- to minimize the overhead introduced with native CDC.
EXEC sp_cdc_drop_job @job_type = N'capture'
go
If you do not run the supp_log_add.sql
for the required tables before the initial sync, Fivetran will automatically generate the necessary SQL statements and write them into the supp_log_add.sql
script file on the source machine. An error message will be displayed in the UI specifying the script file name and the required permission level to execute it. The script must be manually run on the source database. The first time the message is displayed, a user with sysadmin
fixed server role is required to execute the script. Subsequently, a user with db_owner
privileges can run the script.
NOTE: 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. For steps to verify if CDC is enabled for your tables, see section Verify CDC status for tables.
Adding new tablelink
When you add a new table to the source database, it is essential to include the table in the supp_log_add.sql
script and execute it once again on the source database. This will enable supplemental logging for the table. If you omit this step, it will cause an error during the initial and incremental syncs. The error will also generate the script that you can use to add the table.
Export transparent data encryption certificatelink
NOTE: This step is necessary only for databases encrypted using SQL Server transparent data encryption (TDE).
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>' WITH PRIVATE KEY ( FILE='<path_to_TDE_private_key_file>', ENCRYPTION BY PASSWORD='<TDE_password>' )
Run the following commands in the command prompt to encode the certificate and private key.
NOTE: 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>
Make a note of the command outputs. You will need them later to configure the connector in the Fivetran dashboard.
Install HVAlink
This section provides detailed instructions on how to install HVA and outlines the necessary requirements.
HVA requirementslink
Before proceeding with the installation of HVA, ensure that you have the following prerequisites in place:
Compatibility: Verify that the HVA version is compatible with your operating system and DBMS. Refer to the COMPATIBILITY section in the relevant release notes on the Downloads page of your Fivetran dashboard.
Sufficient disk space: Ensure that the machine where you want to install HVA has ample disk space. We recommend a minimum of 10 GB of available disk space.
HVA installation file: Download the HVA installation file from the Downloads page of your Fivetran dashboard. Select the installation file suitable for your database server's operating system.
IMPORTANT: You must install HVA on the same host where your SQL Server source database is running. For Always On Availability Group, you will need to install HVA on every replica node.
System permissions on Windowslink
The High-Volume Agent Listener service should be configured to run under:
either the same Windows user account as the source SQL Server service, or
as a different Windows user account that has the
Debug programs
(SeDebugPrivilege
) policy enabled. Note that members of the Administrators group usually have this privilege enabled by default. For more information about theDebug programs
security policy, refer to the Microsoft documentation.Click here for the steps to enable/grant the Debug programs policy
User account policies can be managed using the Windows Local Security Policy console (accessible from Control Panel ▶ Administrative Tools). The shortcut command to access this console is
secpol.msc
.In the Local Security Policy window, expand Local Policies and click User Rights Assignment.
Double-click Debug Programs policy available in the list of policies.
In the Debug programs Properties dialog, click Add User or Group; displays the Select Users, Computers, Accounts, or Groups dialog.
In the Enter the object names to select field, enter the user account name to whom you want to enable this policy, and click OK.
Click OK.
Additionally, ensure that the user account has the Log on as a service policy enabled.
System permissions on Linuxlink
The Fivetran operating system user must have access to the .ldf
files generated by your SQL Server database. For this, add the Fivetran user to the Operating System user group mssql
.
Install stepslink
Depending on your database server's operating system, follow the instructions below to install HVA.
Install HVA on Linuxlink
Expand for instructions
Create folder structure
i. Create the HVA installation directory (for example,
/opt/fivetran
).NOTE: This command must be executed by the
root
user.mkdir /opt/fivetran
ii. In the
/opt/fivetran
directory, create three key subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir -p /opt/fivetran/hvr_home mkdir -p -m 01775 /opt/fivetran/hvr_config /opt/fivetran/hvr_tmp
Configure environment
i. Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
environment variables to point to the relevant HVA installation subdirectories:export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp
ii. Add the
$HVR_HOME/bin
executable directory path to the environment variablePATH
:export PATH=$PATH:$HVR_HOME/bin
iii. Add the environment variables and the executable directory path into the startup file (for example,
.profile
or.bash_profile
):export HVR_HOME=/opt/fivetran/hvr_home export HVR_CONFIG=/opt/fivetran/hvr_config export HVR_TMP=/opt/fivetran/hvr_tmp export PATH=$PATH:$HVR_HOME/bin
Install HVA
Navigate to the
$HVR_HOME
directory and extract the contents of the HVA installation file (for example,fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz
).cd $HVR_HOME tar xzf /tmp/fivetran-6.1.0_26-hub_and_agent-linux_glibc2.12-x64-64bit_ga_patch.tar.gz
Once completed, the HVA and all required components are installed into the
$HVR_HOME
directory (/opt/fivetran/hvr_home
).NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.
Install HVA on Windows using ZIP filelink
Expand for instructions
Windows user account permissions:
NOTE: HVA installation file for Windows is available in the
.exe
and.zip
formats. The compressed file (.zip
) distribution is normally used as an alternative for the Windows executable based (.exe
) distribution. The steps to install HVA are also different for both formats.
Create folder structure
As the Administrator user, create an HVA installation directory (for example,
C:\hvr
) with three subdirectorieshvr_home
,hvr_config
, andhvr_tmp
:mkdir C:\hvr\hvr_home mkdir C:\hvr\hvr_config mkdir C:\hvr\hvr_tmp
Configure environment
Configure the
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
environment variables, each pointing to the relevant HVA installation directorieshvr_home
,hvr_config
, andhvr_temp
.NOTE: The following instructions assume that the directories are created inside the
C:\hvr
directory. Use your installation directory if it differs fromC:\hvr
.i. Navigate to Control Panel ▶ System and Security ▶ System ▶ Advanced system settings.
ii. Under the Advanced tab, click Environment Variables...
iii. In the System variables or User Variables for user_name section, click New.
iv. Enter Variable name (e.g,
HVR_HOME
) and Variable value (e.g,C:\hvr\hvr_home
).v. Click OK. Repeat the above steps iii and iv for environment variables
HVR_CONFIG
andHVR_TMP
.Add the HVR executable directory path to the environment variable
Path
.i. In the System variables or User Variables for user_name section, from the list of variables, select Path and click Edit....
ii. In the Edit environment variable dialog, click New and enter the path for the HVA executable. Click OK.
Install HVA
Navigate to the
$HVR_HOME
directory (C:\hvr\hvr_home
) and extract the contents of the downloaded.zip
file:Once completed, the HVA and all required components are installed into the
$HVR_HOME
directory (C:\hvr\hvr_home
).NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.
Install HVA on Windows using EXE filelink
Expand for instructions
Windows user account permissions:
NOTE: HVA's installation file for Windows is available in the
.exe
and.zip
formats. The compressed file (.zip
) distribution is normally used as an alternative for the Windows executable based (.exe
) distribution. The steps to install HVA are also different for both formats.
Run the downloaded
.exe
file (for example,hvr-6.1.0_10-hub_and_agent-windows-x64-64bit_ga_patch-setup.exe
).In the setup wizard dialog, click Next.
Read the License Agreement, select I accept the agreement and then click Next.
Specify the HVA installation directories and click Next.
Click Next to create a program folder for HVA.
Select High-Volume Agent (HVA) and click Next.
Enter a value for Agent Listener Port and click Next.
Select the user account to run the HVA service.
If you select Specified user, enter the values for User, Password, Confirm Password and click Next.
NOTE: For user with Windows authentication (if applicable), specify your Active Directory domain and username in the format
<domain>\<username>
.If you select Local System account, click Next.
Select Add HVR_HOME, HVR_CONFIG, and HVR_TMP (if required) and click Next.
This is to set the environment variables
HVR_HOME
,HVR_CONFIG
, andHVR_TMP
in your operating system. These variables point to the corresponding directories created in step 4 above.Click Next to initiate the installation.
Click Finish to start the HVA service.
Once the installation is complete, the HVA and all required components are installed into the
$HVR_HOME
directory (C:\hvr\hvr_home
).NOTE: The size of the installation is less than 150 MB. After installing (extracting), you can delete the installation file as it is no longer required.
Configure HVAlink
IMPORTANT: For Always On AG, the HVAs running on each replica must have the same configuration file to continue functioning in the event of a failover. First, configure an HVA on one of your replica nodes following the instructions below. For the remaining replica nodes, copy the configuration file from the first node using the same path. On Windows, you can find the configuration file at
C:\Fivetran\hvr_config\etc\hvragent.conf
. On Linux, it is located at/opt/fivetran/hvr_config/etc/hvragent.conf
.
Follow the instructions below to configure HVA:
Create an HVA user to set up your Fivetran connector. In the following example, we use
hva_user
.NOTE: The minimum password length for the user must be 10 characters and not contain special characters.
hvragentuserconfig -c hva_user Password for 'hva_user': <enter a password> Retype password: <confirm the password>
Make a note of the username and password. You will need them to configure Fivetran.
Run the following command to disable the setup mode:
hvragentconfig Setup_Mode_Timed_Until=
Run the following command to extract the HVA public certificate:
hvragentconfig Agent_Server_Public_Certificate
Save the output value of the
Agent_Server_Public_Certificate
. You will need it to set up your connector.
Start HVAlink
Perform the following steps as the user who runs HVA.
To start the agent, use the hvragentlistener
command. Follow the instructions for the operating system of your database server:
In Linux, add flag
-d
followed by the port number that you want to run on. In the example, the default port is4343
.hvragentlistener -d 4343
In Windows, add flag
-acs
followed by the port number that you want to run on. In the example, the default port is4343
.hvragentlistener -acs 4343
Advanced configurationlink
Autostartlink
NOTE: This section is applicable for Linux only.
You must add the HVA to the system auto startup/shutdown sequence to ensure that the HVA starts and stops with the database host. To configure systemd to start HVA automatically at system boot time, perform the following steps as user root
:
Create the systemd unit files
hvr.socket
andhvr@.service
in the/etc/systemd/system
directory.The
hvr.socket
file should contain the following:[Unit] Description=Fivetran agent service socket [Socket] ListenStream=4343 Accept=true TriggerLimitIntervalSec=1s TriggerLimitBurst=10000 MaxConnectionsPerSource=100 MaxConnections=500 KeepAlive=true [Install] WantedBy=sockets.target
NOTE:
TriggerLimitIntervalSec
is supported since systemd version 230.TriggerLimitBurst
is supported since systemd version 230.MaxConnectionsPerSource
is supported since systemd version 232.TIP: In a IPv4/IPv6 mixed system, if you want to force systemd to use IPv4, set
ListenStream
to 0.0.0.0:4343.The
hvr@.service
should contain the following:[Unit] Description=Fivetran Agent service [Service] Environment="HVR_HOME=/opt/fivetran/hvr_home" Environment="HVR_CONFIG=/opt/fivetran/hvr_config" Environment="HVR_TMP=/opt/fivetran/hvr_tmp" User=mssql ExecStart=/opt/fivetran/hvr_home/bin/hvragent StandardInput=socket KillMode=process [Install] WantedBy=multi-user.target
NOTE: Set
User
to the user for which HVR is installed/running.To enable and start the service, execute the following commands:
systemctl enable hvr.socket systemctl start hvr.socket
To verify whether the service is active, execute the following command:
systemctl status hvr.socket
A sample output should look like this:
hvr.socket - HVR service socket Loaded: loaded (/etc/systemd/system/hvr.socket; enabled; vendor preset: enabled) Active: active (listening) since Mon 2020-09-07 17:54:44 CEST; 5s ago Listen: [::]:4343 (Stream) Accepted: 0; Connected: 0
Always On Availability Group Private Previewlink
NOTE: This step is only applicable to Always On Availability Group (AG) configuration.
Fivetran allows you to capture from SQL Server Always On AG - a technology that provides High-Availability and Disaster-Recovery solutions in SQL Server.
For Always On AG, we recommend using the Mininal permission model.
Log truncationlink
We recommend creating a separate job/task on the primary node to release the truncation point for replication. One way to do this is to schedule a separate SQL Server Agent job, which periodically invokes sp_repldone
without any conditions. The following SQL command can be used for this purpose:
EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1
Verify CDC status for tableslink
The High-Volume Agent requires CDC (change data capture) enabled to incrementally update your tables.
To check that CDC is enabled on a table, execute the following query:
SELECT IS_TRACKED_BY_CDC FROM SYS.TABLES WHERE NAME = '<table_name>';
For example, to check a table named test_table
, you would execute the following query:
SELECT IS_TRACKED_BY_CDC FROM SYS.TABLES WHERE NAME = 'test_table';
You should see a result like this:
IS_TRACKED_BY_CDC
-----------------
1
Finish Fivetran configurationlink
In your connector setup form, enter a destination schema prefix. This prefix applies to each replicated schema and cannot be changed once your connector is created.
Select an authentication method for your database:
NOTE: You can use Windows Authentication only if the HVA and the database are operating on a Windows platform.
- Password Authentication: This authentication method uses the user credentials you've created for database password authentication. If you choose this method, enter the following details:
- Host: Enter your database host's IP address (for example,
1.2.3.4
), domain (for example,my.server.com
), or named instance (for example,my.server.com\myInstanceName
). If you installed the HVA on the database server, enterlocalhost
and for named instances, enterlocalhost\<instance_name>
. - Port: Enter the port number of your database instance (default port:
1433
). If you used<host>\<instance_name>
as a Host, use the default port1433
. Our connector will use the SQL Server Browser to find the port your instance is listening to. - User: Enter the name of the user you've created for database password authentication.
- Password: Enter the password for the user you've created for database password authentication.
- Host: Enter your database host's IP address (for example,
- Windows Authentication: This authentication method uses the user credentials you've created for Windows authentication. If you choose this method, in the Server Name field, enter the server name of your SQL Server instance.
- For Always On Availability Groups, enter the name of the Availability Group listener.
- Password Authentication: This authentication method uses the user credentials you've created for database password authentication. If you choose this method, enter the following details:
Enter the name of your Database (for example,
my_database
).(Optional for Hybrid Deployment) By default, the Enable local data processing toggle is set to ON and the local processing agent associated with your destination is selected in the Select an existing local processing agent drop-down menu. To use a different local processing agent, select the agent of your choice, and then select the same agent for your destination.
NOTE: After completing this step, skip to Step 6.
Choose your Connection method. If you selected Connect via an SSH tunnel, provide the following information:
- SSH hostname (do not use a load balancer's IP address/hostname)
- SSH port. The default port is
22
. - SSH user
- If you enabled TLS on your database in Step 1, set the Require TLS through tunnel toggle to ON.
(TDE encrypted database only) Set the Use TDE Encryption toggle to ON and enter the following information:
Set the Use archive log only mode toggle to ON if you want to use the Archive Log Only capture method instead of capturing directly from online transaction log files. Enter the following details:
Archive Log Path: Enter the directory path containing the transaction log backup files on the source machine where HVA is installed.
Archive Log Format: Enter the filename format (template) of the transaction log backup files. Fivetran will only read the transaction log backup files that match the specified format. Use the following variables for your filename format:
%d
: Database name%Y
: Year (up to 4-digit decimal integer)%M
: Month (up to 2-digit decimal integer)%D
: Day (up to 2-digit decimal integer)%h
: Hours (up to 2-digit decimal integer)%m
: Minutes (up to 2-digit decimal integer)%s
: Seconds (up to 2-digit decimal integer)%n
: File sequence number (up to 64-bit decimal integer)%%
: Matches %*
: Wildcard, matches zero or more characters
For example, the format
archive_%d_%Y%M%D_%h%m%s.log
translates toarchive_<database_name>_YYYYMMDD_HHMMSS.log
.
In the Agent Host field, enter the hostname or IP address of the HVA. In most cases, it should match the database hostname or IP address. However, it may vary depending on the connection method you have chosen. For more information, see our High-Volume Agent network configuration options documentation.
- If you are using an Always On Availability Group, then this value should be the public host of the Availability Group listener.
NOTE: Ensure that your Firewall/Security Group allows Fivetran's IPs for your database's region.
In the Agent Port field, enter the HVA's port number. The default port number is
4343
.In the Agent User ID field, enter the name of the HVA user created in Step 7.
In the Agent User Password, enter the password of the HVA user created in Step 7.
In the Agent Public Cert, enter the HVA's Server Public Certificate you found in Step 7.
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 testslink
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 Validating Agent Setup Test checks that we can connect to the HVA and whether it operates correctly.
- 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 aren't connecting directly.)
- 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 Log Accessibility Test checks that the HVA can access the database log files.
NOTE: The tests may take a few minutes to finish running.
Related articleslink
description Connector Overview
account_tree Schema Information
settings API Connector Configuration