SQL database in Microsoft Fabric as Target

Fivetran HVR supports integrating changes into SQL database in Microsoft Fabric. This section describes the configuration requirements for integrating changes (using Integrate and Refresh) into SQL database in Microsoft Fabric location.

HVR uses the Microsoft ODBC Driver for SQL Server to write data into a SQL database in Microsoft Fabric location:

• Continuous Integrate and Row-wise Refresh are performed through the ODBC driver.
• Burst Integrate and Bulk Refresh are used to copy data into database tables.

Grants for Integrate and Refresh

This section lists the grants/permissions required for integrating changes into SQL database in Microsoft Fabric.

For simplicity, we have categorized the required permissions into the following two models:

• DbOwner
  In this permission model, the HVR database User must be made a database owner (db_owner role). Normally, the database objects that HVR sometimes creates will be part of the dbo schema as the replicated tables.
  Alternatively, these HVR database objects can be put in a special database schema so that they are not visible to other users. Following are the SQL commands for this alternate method:

  create schema schemaname;
  
  grant control on schema::schemaname to username;
  
  alter user username with default_schema=schemaname;
  

• Minimal
  In this permission model, the HVR database User does not need to be a database owner. The following SQL commands are needed so that HVR can create its own tables:

  grant create table to username;
  
  create schema schemaname;
  
  grant control on schema::schemaname to username;
  
  alter user username with default_schema=schemaname;
  

  This permission model cannot be used if action TableProperties is defined with parameter Schema to change tables with a different owner.

Intermediate Directory

This option in the HVR UI allows you to specify a directory path for storing intermediate (temporary) files generated during Compare. These files are created during both "direct file compare" and "online compare" operations.

This option is displayed in the location creation dialog when creating a new location, and in the Source and Target Properties pane on the Location Details page when editing an existing location.

Using an intermediate directory can enhance performance by ensuring that temporary files are stored in a location optimized for the system's data processing needs.

This setting is particularly relevant for target file locations, as it determines where the intermediate files are placed during the Compare operation. If this option is not enabled, the intermediate files are stored by default in the integratedir/_hvr_intermediate directory, where integratedir is the replication DIRECTORY (File_Path) defined for the target file location.

Intermediate Directory is Local

This option indicates that the Intermediate Directory will be created on the local drive of the location's server. This option is displayed when you select the Intermediate Directory option in the HVR UI. It is selected by default and cannot be modified.

Storing intermediate files locally is crucial for optimizing performance by reducing network latency and avoiding potential permission issues associated with remote storage. It enables HVR to process data more efficiently by leveraging the speed and reliability of local storage. This is particularly beneficial when the HVR Agent has access to ample local storage, allowing it to handle large data volumes without relying on networked storage solutions.

Recommendations for Long-Running Operations

Microsoft Fabric may terminate database connections that remain open for 35 minutes or longer. This can interrupt long-running Refresh and Integrate operations — particularly bulk loads or full-table refreshes on large datasets — before they complete, commonly resulting in F_JD20F2: DBMS error [TCP Provider: Error code 0x68. Communication link failure].

To avoid this, we recommend provisioning an HVR agent (or hub) on an Azure Virtual Machine (VM) in the same region as your Microsoft Fabric environment, or in close network proximity to it. Running HVR operations from an Azure VM maintains stable connections to Microsoft Fabric and significantly reduces the risk of disconnection during long-running operations.

HVR can be deployed on the Azure VM in one of the following ways:

• Agent mode: Deploy HVR as an agent on the Azure VM and control it from your existing on-premises HVR hub. This lets you keep your current hub infrastructure while offloading Fabric-bound operations to a cloud-hosted agent with a more reliable network path.
• Hub-only mode: Deploy HVR in hub-only mode directly on the Azure VM, with no dependency on an on-premises hub. This is a self-contained option where the hub manages all operations from within Azure, offering the most straightforward path to stable connectivity with Microsoft Fabric.

For more information, see Avoiding Connection Timeouts During Long-Running Operations in Microsoft Fabric.