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. | |
| Log Base CDC | Yes | Value = True | CDC will be enabled |
*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.
