High-Volume Agent SQL Server Setup Guide

To connect your SQL Server database on an AG secondary node to Fivetran, you need:

• A Fivetran account with an Enterprise or Business Critical plan
• SQL Server 2012 - 2022
• A user with db_owner role or sysadmin fixed server role available for use in your Fivetran connector
• A database in full recovery mode, with a full backup taken on the primary node since full recovery mode was enabled
• Log backups in the native SQL Server format, accessible to the secondary node
• The host IP address (for example, 1.2.3.4) or domain (for example, your.server.com) of the secondary node
• The port (usually 1433) of the secondary node
• High-Volume Agent (HVA) installed on the secondary node or on a host that has access to the secondary node
• The IP address and port number (usually 4343) of the host where HVA is installed

1. Create folder structure:

   i. As the root user, create the HVA installation directory (for example, /opt/fivetran):

   mkdir /opt/fivetran
   

   ii. In the /opt/fivetran directory, create three key subdirectories hvr_home, hvr_config, and hvr_tmp:

   mkdir -p /opt/fivetran/hvr_home
   mkdir -p -m 01775 /opt/fivetran/hvr_config /opt/fivetran/hvr_tmp
   

2. Configure environment:

   i. Configure the HVR_HOME, HVR_CONFIG, and HVR_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 variable PATH:

    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

3. 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 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 the HVA are also different for both formats.

1. Create folder structure:

   As the Administrator user, create an HVA installation directory (for example, C:\hvr) with three subdirectories hvr_home, hvr_config, and hvr_tmp:

   mkdir C:\hvr\hvr_home
   mkdir C:\hvr\hvr_config
   mkdir C:\hvr\hvr_tmp
   

2. Configure environment:

   1. Configure the HVR_HOME, HVR_CONFIG, and HVR_TMP environment variables, each pointing to the relevant HVA installation directories hvr_home, hvr_config, and hvr_temp:

      NOTE: The following instructions assume that the directories are created inside the C:\hvr directory. Use your installation directory if it differs from C:\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 and HVR_TMP.

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

      

3. 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 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 the HVA are also different for both formats.

1. Run the downloaded .exe file (for example, hvr-6.1.0_10-hub_and_agent-windows-x64-64bit_ga_patch-setup.exe).

2. In the setup wizard dialog, click Next.

   

3. Read the License Agreement, select I accept the agreement and then click Next.

   

4. Specify the HVA installation directories and click Next.

   

5. Click Next to create a program folder for the HVA.

   

6. Select High-Volume Agent (HVA) and click Next.

   

7. Enter a value for Agent Listener Port and click Next.

   

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

     

9. 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, and HVR_TMP in your operating system. These variables point to the corresponding directories created in step 4 above.

   

10. Click Next to initiate the installation.

    

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

IMPORTANT: For Always On Availability Groups (AG), it is essential that the HVAs running on each replica share the same configuration file to continue functioning during a failover.

First, configure an HVA on one of your replica nodes following the instructions below. Then, for the remaining replica nodes, copy the configuration file from the first node, making sure to place it in the same directory.

• On Windows, you can find the configuration file at C:\Fivetran\hvr_config\etc\hvragent.conf
• On Linux, you can find the configuration file at /opt/fivetran/hvr_config/etc/hvragent.conf.

To configure systemd, perform the following steps as user root:

1. Create a service file called agentListener.service in the /etc/systemd/system directory. The agentListener.service should contain the following:

   NOTE: Change the User=<your_linux_username> field to the Linux user that installed the HVA.

   [Unit]
   Description=Fivetran Agent Listener
   
   [Service]
   # The process start-up type 'forking' allows this service to spawn new processes
   Type=forking
   Environment="HVR_HOME=/opt/fivetran/hvr_home"
   Environment="HVR_CONFIG=/opt/fivetran/hvr_config"
   Environment="HVR_TMP=/opt/fivetran/hvr_tmp"
   # The user that owns HVR_HOME, HVR_CONFIG, etc.
   User=<your_linux_username>
   ExecStart=/opt/fivetran/hvr_home/bin/hvragentlistener -d 4343
   # Restart this service after a crash
   Restart=always
   # The number of seconds to wait before attempting a restart
   RestartSec=5s
   
   [Install]
   WantedBy=multi-user.target
   

2. To enable and start the service, execute the following commands:

   sudo systemctl enable agentListener.service
   sudo systemctl start agentListener.service
   

3. To verify whether the service is active, execute the following command:

   sudo systemctl status agentListener.service
   

   The output should show active (running) in green.

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

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:

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

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

3. If you want Fivetran to tunnel SSH over TLS, follow Microsoft's TLS setup instructions to enable TLS on your database.

NOTE: If you want to use Reverse SSH Tunnel, contact our support team for the additional setup steps.

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.

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.

1. Open SQL Server Configuration Manager.

2. In the tree pane, click SQL Server Network Configuration to expand it.

   

3. Click Protocols for YourInstanceName. If you specified the default instance during installation, the instance name will be MSSQLSERVER.

   

4. In the Status column, verify that TCP/IP is Enabled.

   

   TIP: If Disabled appears, right-click TCP/IP, then click Enable.

5. Right-click TCP/IP, then select Properties.

6. Go to the IP Addresses tab and scroll all the way down.

7. In the IPAll section, enter your database's port number (usually 1433) for the TCP Port, then click Apply.

   

8. Click OK in the warning dialog box that pops up.

   

9. Click OK in the TCP/IP Properties dialog box.

10. In the tree pane, click SQL Native Client Configuration to expand it, then click Client Protocols.

11. In the right-hand column, verify that Enabled appears next to TCP/IP.

    

    TIP: If Disabled appears, right-click TCP/IP, then click Enable.

12. Right-click TCP/IP, then select Properties.

13. Verify that the Default Port is 1433 and that Yes appears next to Enabled.

    

14. Click OK to exit the TCP/IP Properties dialog box.

15. In the tree pane, click SQL Server Services.

16. In the right pane, right-click SQL Server (YourInstanceName), then click Restart.

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

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.

NOTE: The High-Volume Agent must already be installed before performing the following steps.

Create wrapper stored procedures

NOTE: This step is needed once when the 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 the 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 called sp_hvr_dblog, sp_hvr_dbtable, and sp_hvr_loginfo so that the HVA can call the required SQL Server's read-only functions, such as fn_dump_dblog.

  This must be done inside the SQL Server special database msdb, not the actual source database. The SQL query to create these procedures is available in the file hvrcapsysadmin.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 special msdb 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 privilege

NOTE: This step is needed once when the 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 and VIEW SERVER SECURITY STATE. When capturing from SQL Server 2022, the user only needs VIEW SERVER PERFORMANCE STATE.

Enable supplemental logging

For the 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.

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.

NOTE: The High-Volume Agent must already be installed before performing the following steps.

Create wrapper stored procedures

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 called sp_hvr_dblog, sp_hvr_dbtable, and sp_hvr_loginfo so that the HVA can call the required SQL Server's read-only functions, such as fn_dump_dblog. This must be done inside the SQL Server special database msdb, not the actual source database. The SQL query to create these procedures is available in the file hvrcapsysadmin.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 special msdb 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 or sysadmin fixed server role must create 'wrapper' SQL stored procedures in each source database so that HVA can call the SQL Server's read-only procedures sp_helppublication, sp_helparticle, fn_dblog, etc. The SQL query to create these read-only procedures is available in the file called hvrcapdbowner.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 privileges

• 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 and VIEW SERVER SECURITY STATE. When capturing from SQL Server 2022, the user only needs VIEW SERVER PERFORMANCE STATE.

• A user with db_owner role or sysadmin 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 logging

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.

Adding new table

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.

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

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

3. Make a note of the command outputs. You will need them later to configure your connector in the Fivetran dashboard.

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 on the primary node 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 primary node. 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.

Adding new table

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 primary node. 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.