Repository Database in SQL Server

Fivetran Local Data Processing allows you to create repository database in SQL Server.

It is recommended to create a new database (schema) for Local Data Processing repository. If an existing database is to be used for Local Data Processing repository, then the Local Data Processing can be separated by creating a new database schema and associating it with Local Data Processing's user as follows:

create schema schemaname;
content_copy

Grants for Repository Database

The following grants are required for the repository database user in SQL Server:

grant create table to username;
grant create procedure to username;
grant select, insert, delete, update on schema::schemaname to username;
grant control on schema::schemaname to username;
alter user username with default_schema=schemaname;
content_copy

Repository Database Connection

This section describes the details required for connecting to the repository database in SQL Server:

Field	Description	Equivalent Hub Server Property
SERVER	Name of the server (host) on which SQL Server is running and the Port number or the instance name of SQL Server. The following formats are supported for this field: server_name : Specify only the server name and Local Data Processing will automatically use the default port to connect to the server on which SQL Server is running. Example: myserver server_name,port_number : Specify the server name and port number separated by a comma (,) to connect to the server on which SQL Server is running. This format is required when using custom port for connection or when is defined. Example: myserver,1435 server_name\server_instance_name : Specify the server name and server instance name separated by a backslash (\) to connect to the server on which SQL Server is running. This format is not supported on Linux. Example: myserver\instance6048
DATABASE	Name of the SQL Server database.
USER	Username for connecting Local Data Processing to the SQL Server database. The login/user account used for connecting Local Data Processing to SQL Server database engine should be defined with SQL Server Authentication or Windows Authentication. If Windows Authentication is used for connecting to SQL Server database, the USER and PASSWORD field should be left blank (empty).
PASSWORD	Password for the USER.

Advanced Settings

Field	Description	Equivalent Hub Server Property
LINUX / UNIX ODBC DRIVER MANAGER LIBRARY PATH	Directory path where the ODBC Driver Manager Library is installed. This field is applicable only for Linux/Unix operating system. For a default installation, the ODBC Driver Manager Library is available at /usr/lib64 and does not need to be specified. However, when UnixODBC is installed in for example /opt/unixodbc the value for this field would be /opt/unixodbc/lib.
LINUX / UNIX ODBCSYSINI	Directory path where the odbc.ini and odbcinst.ini files are located. This field is applicable only for Linux/Unix operating system. For a default installation, these files are available at /etc directory and do not need to be specified using this field. However, when UnixODBC is installed in for example /opt/unixodbc the value for this field would be /opt/unixodbc/etc.
ODBC DRIVER	Name of the user defined (installed) ODBC driver used for connecting Local Data Processing to the SQL Server database.It is recommended to leave this field empty, Local Data Processing will automatically load the correct driver for your current platform. Otherwise, select one of the available SQL Server Native Client options.