SQL Server Requirements

This section describes the requirements, access privileges, and other features of Fivetran Local Data Processing when using SQL Server for replication.

For information about compatibility and supported versions of SQL Server with Local Data Processing platforms, see .

For the supported by Local Data Processing on SQL Server, see .

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 .

For the character encodings supported by Local Data Processing on SQL Server, see the page.

Supported Editions

Local Data Processing 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 Local Processing 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, Local Data Processing's log-based capture can get changes from a database without Local Data Processing's executables being physically installed on the source machine.

• Method 1: Connect the Local Processing 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 should be installed on the machine from which the Local Processing Hub will connect to SQL Server database. SQL Server Native Client can be downloaded from this . For the install instructions, refer to the .

• Method 2: Connect the Local Processing Hub to a High-Volume Agent (HVA) installed on the remote machine containing the SQL Server database using Local Data Processing's protocol on a special TCP/IP port number. Then connect the HVA 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 .

  This connection method gives the best performance, but is the most intrusive.

• Method 3: Connect the Local Processing Hub to a HVA installed on a separate machine using Local Data Processing's protocol on a special TCP/IP port number. Then connect the HVA 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 Local Processing Hub, another with the HVA, and finally the machine containing the SQL Server database.

  On Windows, the special TCP/IP port is serviced by a Windows service called .

  To use this connection method, the Microsoft should be installed on the machine from which the HVA will connect to SQL Server database. SQL Server Native Client can be downloaded from this . For the install instructions, refer to the .

  This connection method is useful when connecting from a Linux hub to avoid an (intrusive) installation of Local Data Processing on the machine containing the SQL Server database.

Connecting to Amazon RDS for SQL Server

To enable the Local Data Processing 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 Local Data Processing process. If a HVA is in place, then communication must be enabled for the system where the HVA is running. When directly connected from a Local Processing Hub Server, the connection must be allowed for the Local Processing Hub Server. If the Local Data Processing 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 Local Data Processing 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

Local Data Processing supports SQL Server on Linux as source and as target. The following are required for using Local Data Processing with SQL Server running on Linux:

• Microsoft ODBC Driver (version 17.5 or higher) for SQL Server.

  ODBC Driver Configuration

  1. Download and install the latest Microsoft ODBC Driver for SQL Server on Linux. For more information, refer to the .

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

  3. After 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
     content_copy

• The Local Data Processing database User (username for connecting Local Data Processing 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.

Related Articles