Connecting QuerySurge to Azure SQL Data Warehouse

Versions: 8.0+

Versions:4.5+

Microsoft's Azure SQL Data Warehouse, also called SQL Data Warehouse is an Azure implementation of SQL Server built specifically for high performance data warehousing. QuerySurge customers who use Azure SQL as a major Data Warehouse component need to connect QuerySurge to Azure SQL in order to test their ETL processes. This article explains how to connect QuerySurge with Azure SQL.

Note: The setup in this article was originally developed for (and still applies to) Microsoft SQL Server Parallel Data Warehouse (PDW).

Setting up a Connection to Azure SQL

Connecting to Azure SQL is done using the Connection Extensibility feature of the QuerySurge Connection Wizard. The following steps describe what you'll need to set up your QuerySurge Connection to Azure SQL.

1. QuerySurge connects to Azure SQL using the "standard" SQL Server JDBC driver.
   
   
2. Deploy the SQL Server JDBC driver to your Agent(s), if you did not do so when you installed your Agent(s). The procedure for deploying a new driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).
   
   
3. Log into QuerySurge as a QuerySurge Admin user, and navigate to the Admin view. Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the SQL Server driver, you'll need the following information:
   
   Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
   Connection URL: jdbc:sqlserver://<sql-server-host>:<sql-server-port>;DatabaseName=<db-name>;pdw=true
   
   Note that you'll need to provide the URL with the proper values for the server host, the port and the database name. (The default SQL Server port is 1433.) When you've entered your information, the Connection Wizard will look similar to this:
   
   Note: You'll need to append pdw=true to the URL so that QuerySurge handles your Azure SQL Connection properly. This is a QuerySurge-specific notation.
   
   
   If you have a Test Query, feel free to enter it to help verify that your Connection parameters are correct. It should be a standard query that returns a small amount of information - one row is enough. 
   
   
4. If you entered a Test Query, you can use the Test Connection button to test whether your Connection is set up properly:
   
   
   
   Once your driver is set up, you should be able to write SQL queries against Azure SQL Data Warehouse in QuerySurge.