Connect SQL Server on Data Virtuality Studio

Data Virtuality is a logical data warehouse software where you can add a SQL Server database as a data source of Data Virtuality server using Data Virtuality Studio. In this tutorial, I want to show the steps to connect to SQL Server from Data Virtuality Studio. This will enable DV developers to connect SQL Server database and query database table data easily. It is even possible to build a SQL query that joins a table from SQL Server data source and another table from another data source type like Amazon Redshift, S3, Oracle or SAP HANA databases.

To add a SQL Server database as a new data source in Data Virtuality, first of all launch Data Virtuality Studio and connect to the target Data Virtuality server. Then on Data Explorer window, right click on the Data sources node and from the context menu displayed, select Add data source menu option.

When the "Add data source" wizard is displayed start typing SQL in filter box and you will see MS SQL under JDBC data source types.

Highlight MS SQL and click on Next

Provide following required SQL Server connection parameter values on following "data source parameters" screen.

By default connection port for SQL Server databases is 1433.
But if there are multiple SQL Server instances are installed on the same server, or specifically the default port for a SQL Server instance is changed on purpose, you have to provide the correct port here.
Please refer to SQL Server tutorials for port number; Find the Port Number for SQL Server Instance and xp_regread SQL Script to Find SQL Server Port Number

Test connection and if it is successfull continue to following screen.

Successfully connected to MS SQL

If you want Data Virtuality to collect statistics for the data source, mark relevant checkboxes. Otherwise, keep checkboxes clear as default.

Click Finish. If the message "Data source added." is displayed, the new SQL Server data source will be listed among the existing data sources in Data explorer window on Data Virtuality Studio

If you open the SQL Server data source node, you will see Tables/Views node and Procedures node.

By double click on the tables/views node, all existing tables and views on the target SQL Server database will be listed in a seperate window.

Now SQL developers can execute SQL statements on new SQL Server data source.
Following is a sample SQL SELECT command.

The same SQL command can be executed on the target SQL Server data source using the native procedure provided by Data Virtuality for developers.

As shown in this Data Virtuality tutorial, SQL Server databases can be easily added to Data Virtuality Studio as new data sources of the logical data warehouse.