SQL Server Requirements
This section describes the requirements, access privileges, and other features of Fivetran HVR when using SQL Server for replication.
For information about compatibility and supported versions of SQL Server with HVR platforms, see Supported Platforms.
For the Capabilities supported by HVR on SQL Server, see Capabilities for SQL Server.
For information about the supported data types and mapping of data types in source DBMS to the corresponding data types in target DBMS or file format, see Data Type Mapping.
For the character encodings supported by HVR on SQL Server, see the Character Encodings page.
Fivetran provides additional solutions for replicating data from SQL Server. For more information, see section SQL Server in Databases.
Supported Editions
HVR supports the following SQL Server editions:
- SQL Server Developer Edition
- SQL Server Enterprise Edition
- SQL Server Standard Edition
Connecting Hub to a Remote SQL Server Database
For connecting HVR Hub machine to a remote SQL Server database, the following three methods are available:
All of the following connection methods are applicable when SQL Server is used as source and as target. Specifically, HVR's log-based capture can get changes from a database without HVR's executables being physically installed on the source machine.
Method 1: Connect the HVR Hub directly to the SQL Server database (available on a remote machine) using the SQL Server protocol - Tabular Data Stream (TDS).
To use this connection method, the Microsoft ODBC Driver for SQL Server must be installed on the machine from which the HVR Hub will connect to the SQL Server database. For information about the supported ODBC driver versions, refer to the HVR release notes (hvr.rel) available in the HVR_HOME directory or the download page.
Method 2: Connect the HVR Hub to an HVR Agent installed on the remote machine containing the SQL Server database using HVR's protocol on a special TCP/IP port number. Then connect the HVR Agent directly to the SQL Server database using the SQL Server protocol - TDS.
On Windows, the special TCP/IP port is serviced by a Windows service called HVR Agent Listener.
This connection method gives the best performance, but is the most intrusive.
Method 3: Connect the HVR Hub to an HVR Agent installed on a separate machine using HVR's protocol on a special TCP/IP port number. Then connect the HVR Agent directly to the SQL Server database (available on a different machine) using the SQL Server protocol - TDS. This connection method involves three separate machines - one with the HVR Hub, another with the HVR Agent, and finally the machine containing the SQL Server database.
On Windows, the special TCP/IP port is serviced by a Windows service called HVR Agent Listener.
To use this connection method, the Microsoft ODBC Driver for SQL Server must be installed on the machine from which the HVR Agent will connect to the SQL Server database. For information about the supported ODBC driver versions, refer to the HVR release notes (hvr.rel) available in the HVR_HOME directory or the download page.
This connection method is useful when connecting from a Linux hub to avoid an (intrusive) installation of HVR on the machine containing the SQL Server database.
Connecting to Amazon RDS for SQL Server
To enable the HVR capture or integrate process to connect to Amazon RDS for SQL Server, you must allow inbound traffic on the database listener port to the system running the HVR process. If an HVR Agent is in place, then communication must be enabled for the system where the HVR Agent is running. When directly connected from an HVR Hub Server, the connection must be allowed for the HVR Hub Server. If the HVR system connecting to Amazon RDS for SQL Server runs in the same VPC as Amazon RDS for SQL Server, you can use the internal rather than public IP address for the service to allow access. It is recommended to restrict access to only the HVR system that requires access, rather than allowing broader or public access.
The default database listener port that must be opened for TCP/IP connection is 1433.
NOTE: The port may have been changed from the default by an administrator.
SQL Server on Linux
HVR supports SQL Server on Linux as source and as target. The following are required for using HVR with SQL Server running on Linux:
Microsoft ODBC Driver for SQL Server. For information about the supported ODBC driver versions, refer to the HVR release notes (hvr.rel) available in the HVR_HOME directory or the download page.
ODBC Driver Configuration
Since v6.1.5/8, skip step 2 (create symbolic link) and step 3 (verify dynamic dependencies).
Download and install the latest Microsoft ODBC Driver for SQL Server on Linux. For more information, refer to the Microsoft documentation - Install the Microsoft ODBC driver for SQL Server (Linux).
Create a symbolic link (symlink) for the ODBC driver. Following is an example for Microsoft ODBC Driver for SQL Server libmsodbcsql-17.5.so.1.1:
ln -s /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.5.so.1.1 $HVR_HOME/lib/libmsodbcsql-17.soAfter installing the Microsoft ODBC Driver for SQL Server, it is recommended to verify the dynamic dependencies. For example:
ldd $HVR_HOME/lib/hvr_ms17.so
The HVR database User (username for connecting HVR to SQL Server) should have read access to the .mdf and .ldf files. For this, the User should typically be added to the operating system user group mssql.