Create SQL Server Database Connection from Exasol Data Warehouse

On Exasol Analytic Data Warehouse cluster, database administrators can connect to SQL Server database using JDBC driver which is pre-installed and configured on EXAoperation WEB UI tool. Using JDBC drivers SQL developers can connect to remote data sources and Exasol's data virtualization features to execute SQL queries on those data sources like SQL Server databases shown in this Exasol tutorial.

Although Exasol cluster is shipped with some default JDBC drivers enabling Microsoft SQL Server JDBC connections, I have shown the steps of defining a new driver and uploading the driver binary files to Exasol cluster using the EXAoperations tool.

Exasol JDBC connection to SQL Server database

Here are the steps to create a SQL Server database connection on an Exasol Data Warehouse for SQL developers and Exasol adminsitrators.

SQL Server JDBC Driver on Exasol Database

Launch EXAOperation and login to EXAoperation Web UI administration tool.

EXAoperation web ui as Exasol administration tool

Under Configuration section, go to Software node for details like installed EXASolution version, license details as well as the JDBC Drivers

On JDBC Drivers tab, Exasol data warehouse administrators and SQL developers can see the pre-installed JDBC drivers to various data sources.

JDBC Drivers to connect Exasol to other databases like SQL Server

As seen on above screenshot from JDBC Drivers tab on my Exasol instance running on AWS Cloud; MySQL, PostgreSQL, Sybase, SQL Server and Exasol JDBC drivers are already installed with default setup.

Just take note of the Prefix. It is "jdbc:jtds:sqlserver:" We can now continue with SQL code.

Upload and Define JDBC Driver for SQL Server on Exasol Data Warehouse

For some reason, although there is a preconfigured SQL Server JDBC driver on Exasol analytic data warehouse installation, if you decide to upload and install a specific version of SQL Server JDBC driver, how can you do that on Exasol using EXAoperation UI tool?

Let me summarize the steps of uploading a new JDBC driver to your Exasol database uing EXAoperation WEB UI tool.

First of all, find the JDBC driver that you want to install on Exasol to connect SQL Server databases. I can strongly suggest SQL database developers to visit Available downloads of JDBC Driver for officially provided JDBC drivers for SQL Server by Microsoft.

available Microsoft SQL Server JDBC drivers to connect from Exasol

Download a suitable JDBC driver which satisfies the requirements of your Exasol database environment. For example, I download sqljdbc_8.2.0.0_enu.zip file of Microsoft JDBC Driver 8.2 for SQL Server and extracted it for the executable .jar file mssql-jdbc-8.2.0.jre8.jar

I copied the .jar file into a folder under path "C:\My\Exasol\Drivers\JDBC Drivers" where I keep all JDBC drivers for my Exasol Analytic Data Warehouse cluster.

Now launch EXAoperation and go to JDBC Drivers tab under Software section page.

Click Add button

add new JDBC driver

Now as Exasol administrator we will provide the Driver Name, Main Class of the JDBC driver and a Prefix for the driver to use within SQL code.
Although we are nearly free to define any value for driver name and prefix, the main class name should be the class name which is provided for that JDBC driver by the provider.

The class name com.microsoft.sqlserver.jdbc.SQLServerDriver is mentioned with a sample at Making a simple connection to a database and a sample JDBC connection URL is provided.

For SQL Server JDBC driver I am just creating on Exasol database, I use following metadata definition:
Driver Name: SQLServerDriver
Main Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Prefix: jdbc:sqlserver:

A restriction about Prefix is that it must start with ":jdbc" and end with ":" as in the case of our example JDBC driver for SQL Server connections.

add JDBC driver on Exasol to connect SQL Server database

Click on Add to continue.

Now Exasol administrator is back to JDBC Drivers tab as seen below. You will immediately realize that on Files column we have "No files uploaded" text indicating that the driver binaries are not uploaded to Exasol cluster yet.

upload JDBC driver files for new SQL Server driver on Exasol cluster

To upload the JDBC driver files, first of all select the option box next to the JDBC driver line we are creating. Following the line selection, click on Browse button. Then navigate to folder path where the JDBC .jar file is saved.

jdbc driver file for Exasol to connect SQL Server database

Click on Open button to select it and return to previous JDBC Drivers tab. You will see the file name next to the Browse button now.

Upload JDBC driver to Exasol cluster

Click Upload

When the JDBC driver upload is completed in the Files column you will see the name of the .jar file

SQL Server JDBC driver on Exasol Database

After the new JDBC driver file is uploaded and its metadata is created, Exasol database developers can use this driver to connect and query SQL Server database tables and views, etc.

Execute SQL Query on Remote SQL Server Connected to Exasol Database

Launch a SQL editor application and connect to the Exasol Data Warehouse cluster where you want to connect to a SQL Server database and execute your SQL queries.
I prefer to use DbVisualizer Database Management Tool to connect Exasol and develop SQL codes on Exasol DWH.

DbVisualizer Database Management Tool for Exasol Analytic Data Warehouse

Using SQL Commander on DbVisualizer on an Exasol database, first create a connection object to SQL Server database using the prefix for the SQL Server JDBC driver as follows.


CREATE OR REPLACE CONNECTION sqlserver_connection

 TO 'jdbc:jtds:sqlserver://10.11.12.13:1433;databaseName=kodyaz'

 USER 'sqluser'

 IDENTIFIED BY 'MyPassword1*';

Code

Please note that the above Exasol SQL command is using the preinstalled JDBC driver which you can identify by prefix. If you want to use the JDBC driver which we have created and uploaded its .jar file, following command can be used. Please note that only the prefix is changing.


CREATE OR REPLACE CONNECTION sqlserver_connection

 TO 'jdbc:sqlserver://10.11.12.13:1433;databaseName=kodyaz'

 USER 'sqluser'

 IDENTIFIED BY 'MyPassword1*';

Code

After prefix of the JDBC driver, type the server name or the IP address of the SQL Server instance and port number followed by the target database name.

When you execute the command the JDBC connection object is created on the database. Please note that only the connection definition is created. Connection is not established between Exasol and SQL Server yet.

SQL Server connection from Exasol database

Note that the Prefix jdbc:jtds:sqlserver: was specified at EXAoperation JDBC Drivers page for our SQL Server JDBC driver.
Database developers can review the SQL Server JDBC driver connection parameters at Setting the connection properties

After the connection object is defined, database programmers can develop SQL scripts to consume or query SQL Server database tables and views.

Here is a sample SQL query that Exasol database developers can execute.


SELECT S.*

FROM (

 IMPORT FROM JDBC AT sqlserver_connection STATEMENT '

  SELECT @@version as version

 '

) as S;

Code

You see by executing above SQL script on an Exasol database it is possible to get version details of a connected SQL Server instance easily

execute SQL query on remote SQL Server using Exasol database

Another example for SQL developer can be querying the database tables using sys.tables system view. Here is how the SQL command can be executed on Exasol DWH:


SELECT S.*

FROM (

 IMPORT FROM JDBC AT sqlserver_connection STATEMENT '

  select * from sys.tables

 '

) as S;

Code

The output of above Exasol SQL SELECT command executed on remote SQL Server database connected using JDBC connection is as seen in below screenshot.

SQL query on Exasol database connected to SQL Server

I hope this Exasol tutorial helps SQL developers to connect other data sources like SQL Server and query database tables on remote data sources.