Configuring SQL Server workspace data connections
During workspace creation, under Connection Type, click SQL Server.
Connecting to the source database
In the Source Settings section, provide the details about the source database.
Providing the source database connection details
To provide the connection details for the source database:
In the Server field, provide the server where the database is located.
In the Database field, provide the name of the database.
In the Port field, provide the port to use to connect to the database.
In the Username field, provide the username for the account to use to connect to the database.
In the Password field, provide the password for the specified username.
To test the connection to the source data, click Test Source Connection.
Providing SSL authentication settings
To configure SSL authentication for the source database:
By default, SSL is enabled, and Enable SSL/TLS is in the on position. We strongly recommend that you do not turn off SSL.
To use Kerberos or NTLM authentication:
Toggle Integrated Security to the on position.
For Kerberos authentication, in the Kerberos Domain field, provide the Kerberos domain. For NTLM authentication, leave the field empty.
To indicate that Tonic Structural should trust the server certificate, toggle Trust Server Certificate to the on position.
To connect to an SSH bastion for additional security:
Toggle Enable SSH Tunnel to the on position.
In the SSH Host field, provide the host for the SSH bastion.
In the SSH Port field, provide the port for the SSH bastion.
In the SSH User field, provide the name of the user to use to connect to the SSH bastion.
If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.
If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.
Enabling MultiSubnetFailover for the source database
To enable MultiSubnetFailover for the source database, toggle Enable MultiSubnetFailover to the on position.
Blocking data generation on all schema changes
By default, data generation is not blocked as long as schema changes do not conflict with your workspace configuration.
To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.
Connecting to the intermediate database for upsert
SQL Server supports the upsert process. When you enable upsert for the workspace, the data generation process initially writes the transformed data to an intermediate database.
After the initial data generation is complete, the upsert job writes new records to the destination database, and updates existing records in the destination database. It does not touch any other records that are in the destination database.
In the Upsert section, when you enable upsert, you are prompted to configure the upsert processing and to provide the connection information for the intermediate database.
If the intermediate database is in the same location as the source database, then you can copy the connection and authentication details from the source database.
Copying the connection and authentication details from the source database
To copy the connection and authentication details from the source database:
Click Copy Settings from Source.
In the Password field, provide the password.
To test the connection to the intermediate database, click Test Intermediate Connection.
Providing the intermediate database connection details
To provide the connection details for the intermediate database:
In the Server field, provide the server where the database is located.
In the Database field, provide the name of the database.
In the Port field, provide the port to use to connect to the database.
In the Username field, provide the username for the account to use to connect to the database.
In the Password field, provide the password for the specified username.
To test the connection to the source data, click Test Intermediate Connection.
Providing SSL authentication settings for the intermediate database
To configure SSL authentication for the intermediate database:
By default, SSL is enabled, and Enable SSL/TLS is in the on position. We strongly recommend that you do not turn off SSL.
To use Kerberos or NTLM authentication:
Toggle Integrated Security to the on position.
For Kerberos authentication, in the Kerberos Domain field, provide the Kerberos domain. For NTLM authentication, leave the field empty.
To indicate that Structural should trust the server certificate, toggle Trust Server Certificate to the on position.
To connect to an SSH bastion for additional security:
Toggle Enable SSH Tunnel to the on position.
In the SSH Host field, provide the host for the SSH bastion.
In the SSH Port field, provide the port for the SSH bastion.
In the SSH User field, provide the name of the user to use to connect to the SSH bastion.
If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.
If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.
Enabling MultiSubnetFailover for the intermediate database
To enable MultiSubnetFailover for the intermediate database, toggle Enable MultiSubnetFailover to the on position.
Connecting to the destination database
For a SQL Server workspace, you can write the destination data to either:
A destination database server.
A Tonic Ephemeral snapshot. For more information, go to Writing data generation output to a Tonic Ephemeral snapshot.
Under Destination Settings, to write the transformed data to a destination database on a database server, click Database Server.
Copying source connection and authentication details
To copy the connection and authentication details from the source database:
Click Copy Settings from Source.
In the Password field, provide the password.
To test the connection to the destination database, click Test Destination Connection.
Providing destination database connection details
If you do not copy the source connection details, then to configure the connection to the destination database:
In the Server field, provide the server where the database is located.
In the Database field, provide the name of the database.
In the Port field, provide the port to use to connect to the database.
In the Username field, provide the username for the account to use to connect to the database.
In the Password field, provide the password for the specified username.
To test the connection to the destination data, click Test Destination Connection.
Providing SSL authentication settings
To configure the SSL authentication for the destination database:
By default, SSL is enabled, and Enable SSL/TLS is in the on position. We strongly recommend that you do not turn off SSL.
To use Kerberos or NTLM authentication:
Toggle Integrated Security to the on position.
For Kerberos authentication, in the Kerberos Domain field, provide the name of the Kerberos domain. For NTLM authentication, leave the field empty.
To indicate that Structural should trust the server certificate, toggle Trust Server Certificate to the on position.
To connect to an SSH bastion for additional security:
Toggle Enable SSH Tunnel to the on position.
In the SSH Host field, provide the host for the SSH bastion.
In the SSH Port field, provide the port for the SSH bastion.
In the SSH User field, provide the name of the user to use to connect to the SSH bastion.
If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.
If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.
Enabling MultiSubnetFailover for the destination database
To enable MultiSubnetFailover for the destination database, toggle Enable MultiSubnetFailover to the on position.
Last updated