Define a new connection

This chapter describes the steps required to create a new connection to the virtual database.

Connections

Go to the "CONNECTIONS" section accessible directly from the sidebar, then on the top right-hand corner please click the "ADD CONNECTION" button.

Provider

Here we define the following parameters: Connection name, Provider and Instance selection mode. Available actions are:

• CANCEL - it abandons the changes and goes back to the connection list.
• NEXT - it goes to the next screen.

Connection name

A mandatory field that contains a name of the new connection. The name can be searched later on in the main Connections screen.

Provider

Contains the list of the supported providers by Lyftron. There are few views for configuring the Provider parameters. Some options depend on this selection, for instance, we see additional Source database section for SQL Server.

Instance selection mode

If you set up a Lyftron cluster, data and queries are exchanged between the nodes. Depending on the security access rights you might be allowed to connect and run queries against any node. Nodes may be distributed in different physical locations; for instance: one node is in Poland, second in the USA. In such case, it might not be a good idea to allow a USA node to access directly connections available in the node in Poland. Therefore, you can limit connectivity to the designated node with the Instance selection mode. You might select from following options:

• Any Lyftron instance
• Selected Lyftron instance
• Selected network

Provider parameters

This view allows specifying connection parameters. It changes depending on the selected Provider.

Is connection trusted and seed value

"is connection trusted" checkbox indicates whether the connection is trusted or not. By default new connections are set as trusted.

Data written by Lyftron using trusted connection will not participate in data encryption based on data sensitivity settings. Every data written using untrusted connection will cause the sensitive data to be encrypted and made unreadable or fake, depending on data type.

When the connection is set as untrusted (checkbox is unchecked), an integer seed value is required to be entered into "Trusted connection seed" field. The seed value is used to initialize the encryption engine and can be reused among connections that need to encrypt and decrypt data in the same way.

Generic ADO.NET

Parameter	Required	Default value	Description
Dialect	Yes		Specifies the connection dialect. The dialects are listed below.
ADO.NET Provider	Yes		Specifies the connection dialect. The dialects are listed below.
Connection string	Yes	Provider dependent	The connection string template. Each driver can use a predefined template.
Server name	No		The server name.
Username	Yes		The name of the login account.
Password	Yes		The password for the login account.
Command timeout	No		The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL	No		SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size	Yes		An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

MySQL

Parameter	Required	Default value	Description
Server dialect	Yes	MySQL	MySQL Dialect, currently regular MySQL and MariaDb is supported.
Server name	Yes		The name of the MySQL Server
Protocol	Yes	TCP/IP	Possible values are TCP/IP and Named pipes.
Port	Yes	3306	The MySQL Server port number.
Authentication method	Yes	Username / password	Possible options are Username / password and Windows authentication on the service account.
Username	Yes*		The name of the MySQL login account.
Password	Yes*		The password for the MySQL login account.
Default database	Yes		The default MySQL database. The datasets are listed once the connection has been successfully tested.
Encrypt	Yes	False	Activates SSL encryption for all data sent between the client and server.
Connection timeout	No		Specifies the length in seconds to wait for a server connection before terminating the attempt and receive an error.
Command timeout	No		Use this one to specify a default command timeout for the connection.
Pooling	Yes		Specifies if the pooling mechanism is enabled.
Min pool size	Yes**	1	Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size	Yes**	100	Specifies the maximum amount of available and borrowed (in use) connections that a pool maintains.
Additional parameters	No		Additional parameters passed to the connection.
Initialization SQL	No	SET @@SQL_MODE=''	SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size	Yes		An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

*only if the Authentication method is set to Username / password.

**only if the Pooling is enabled.

ODBC

Parameter	Required	Default value	Description
Dialect	Yes		Specifies the connection dialect. The dialects are listed below.
Connection string template - use [placeholders]	Yes	Provider dependent	The connection string template. Each driver can use the predefined template.
Server name	Yes		The server name.
Username	Yes		The name of the login account.
Password	Yes		The password for the login account.
Command timeout	No		The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL	No		SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size	Yes		An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

Lyftron offers build-in dialects for ODBC. Editing existing ones and also adding another can be done from Administer -> SQL Dialects section.

JDBC

Parameter	Required	Default value	Description
Dialect	Yes		Specifies the connection dialect. The dialects are listed below.
Connection string template - use [placeholders]	Yes	Provider dependent	The connection string template. Each driver can use the predefined template.
Server name	Yes		The server name.
Database	No		The database name.
Username	Yes		The name of the login account.
Password	Yes		The password for the login account.
Command timeout	No		The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Initialization SQL	No		SQL statements in given provider dialect that will be executed just after the connection was opened.

Lyftron offers some build-in dialects for JDBC. Editing existing ones and also adding another can be done from Administer -> SQL Dialects section.

Oracle

Parameter	Required	Default value	Description
Connection method	Yes	TNS alias	Describes the connection method to the Oracle database. Possible options are: TNS alias, Connect Descriptor and Easy Connect. Each option has its own parameter set that is shown between Connection method and Server dialect parameters.
Server dialect	Yes	Oracle	The dialect used when conversing with Oracle.
Connection pooling enabled	Yes	False	When turned on, this option enables caching the database connections.
Min pool size	Yes*		Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size	Yes*		Specifies the maximum number of available and borrowed (in use) connections that a pool maintains.
Increment pool size	Yes*		Controls the number of the connections that are established when all the connections in the pool are used.
Decrement pool size	Yes*		Controls the number of connections that are closed when an excessive amount of established connections are unused.
Connection timeout	No		Maximum time (in seconds) to wait for a free connection from the pool.
Connection lifetime	No		Maximum lifetime (in seconds) of the connection.
Command timeout	No		Maximum lifetime (in seconds) of the connection.
Enlist (always False)	Yes	False	Enables or disables serviced components to automatically enlist in distributed transactions.
Fetch size (kB)	Yes	4096	Rows fetch size in kB between 64kB and 1GB (1048576kB) returned from the database cursor.
Additional parameters	No		Additional parameters passed to the connection.
Initialization SQL	No		SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size	Yes	500	An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted at once.

*only when the Connection pooling enabled is checked.

TNS alias

This connection method enables connecting with a Transparent Network Substrate (TNS) alias.

Parameter	Required	Default value	Description
TNS Alias name	Yes		The name of the TNS Alias.
Username	Yes		The name of the Oracle login account.
Password	Yes		The password for the Oracle login account.

Connect Descriptor

A specially formatted description of the destination for a network connection. A connect descriptor contains destination service and network route information.

Parameter	Required	Default value	Description
Connect Descriptor - use [placeholders]	Yes		Connect Descriptor text.
Username	Yes		The name of the Oracle login account.
Password	Yes		The password for the Oracle login account.

Easy Connect

Enables clients to connect to a database server without any configuration. Clients use a connect string for a simple TCP/IP address, which includes a host name, port number and service name.

Parameter	Required	Default value	Description
Service name	Yes		Specifies the service name.
Host name	Yes		Specifies the host name or IP address of the database server computer.
Port	Yes	1521	The port number where the server listens for the requests.
Username	Yes		The name of the Oracle login account.
Password	Yes		The password for the Oracle login account.

Spark

See chapter: Spark configuration for further information.

SQL Server

Parameter	Required	Default value	Description
Server dialect	Yes	SQL Server	The SQL server dialect. Possible options are SQL Server and Azure SQL Data Warehouse.
Server name	Yes		Specifies the server name.
Authentication method	Yes	Windows authentication on the service account	Possible options are Username / password and Windows authentication on service account.
Username	Yes*		The name of the SQL Server login account.
Password	Yes*		The password for the SQL Server login account.
Connection pooling	Yes	True	Specifies if the pooling mechanism is enabled.
Min pool size 1	Yes**		Specifies the minimum amount of available and borrowed (in use) connections that a pool maintains.
Max pool size 100	Yes**		Specifies the maximum amount of available and borrowed (in use) connections that a pool maintains.
Application intent	No		Possible options are Read/Write and Read only.
Connection timeout	No		The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error.
Command timeout	No		The length of time (in seconds) before terminating the attempt to execute a command and generating an error.
Encrypt	Yes	False	Specifies if the connection should be encrypted.
Trust server certificate	Yes	False	Indicates if the channel will be encrypted while bypassing walking the certificate chain to validate trust.
Data Push-down method	Yes***	Table Valued Parameter	Specifies the data Push-down method. Available options are No push-down and Table Valued Parameter. Data Push-down method is not supported on Azure SQL Data Warehouse dialect.
Initialization SQL	No		SQL statements in given provider dialect that will be executed just after the connection was opened.
BULK INSERT batch size	Yes	0	An integer positive value; defines a bulk INSERT command size meaning how many rows a provider accepts to be inserted in each batch; the default value the most recommended for SQL Server;

*only if the Authentication method is set to Username / password.

**only if the Connection pooling is enabled.

***only if Server dialect is set to SQL Server.

Available actions

There are two actions available on the screen:

• CANCEL - it abandons the changes and goes back to the connection list.
• TEST CONNECTION - it checks if a connection can be established with given parameter set.
• NEXT - it goes to the next screen. This button is only available when there is Source database section visible.
• SAVE - stores the connection. When the connection was not tested the following prompt appears:

Source database

This section appears only when SQL Server provider is selected. It has optional Initial catalog selection that lists all the databases visible for given connection.

NOTE: the selected Initial catalog collation will determine what database can be selected when adding the database.