Follow

Use Azure Synapse SQL Pool (SQL DW)

Azure Synapse Analytics is a limitless analytics service enabling insights across relational and non-relational big-data. At it's core is the SQL Pool (Previously Azure SQL Data Warehouse), a massively parallel processing database. You can learn more about this service here.

TimeXtender began supporting Azure Synapse as a target database in version 19.11.2. This functionality enables the use of Azure Synapse Analytics as a target Data Warehouse or Staging Database. When also connected to Azure Data Lake via the ODX Server, users can simply drag and drop data from Data Lake to a Synapse SQL Pool.

In this article you will learn how to:

  1. Create an Azure Synapse SQL Pool
    1. Connect to the Server
    2. Create SQL Server Logins 
    3. Assign users to a Resource Class
    4. Create a Master Key
  2. Connect to Azure Synapse SQL Pool from TimeXtender
  3. Troubleshooting

Create an Azure Synapse SQL Pool

You can view the Microsoft documentation on how to create an Azure Synapse SQL Pool. However the basic steps are listed below:

  1. In the Azure Portal, click "Create a Resource" and type "Dedicated SQL Pool"
    1. You may use an existing Azure SQL Server, or you can create a new one. 
    2. Note the SQL Server Admin Username & Password as.
  2. Create a Server Level Firewall Rule
    1. In the Azure SQL Server, click Firewalls and virtual networks, click Add client IP, click save. 

Connect to the Server

  1. Get the fully qualified server name
    1. On the Synapse Dedicated SQL Pool resource Overview page, find the "Server Name" and click copy. 
  2. Using SQL Server Management Studio, connect to the database
    1. Server name: fully qualified Server name
    2. Datbase: name of the Dedicated SQL Pool
    3.  Username & Password: use the SQL Server administrator

Create SQL Server Logins 

Create SQL Server Logins by running queries on the database using SQL Server Managment Studio.  (More Info here). Sample statement:

--Create SQL Server Login in the Master Database
USE MASTER
CREATE LOGIN USERNAME WITH PASSWORD = 'STRONGPASSWORD';

--Create User in the desired Data Warehouse
USE --***Insert Database Name Here***
CREATE USER USERNAME FROM LOGIN USERNAME;

--Assign User to the desired role in the Data Warehouse
EXEC sp_addrolemember 'db_owner', 'USERNAME'

Assign users to a Resource Class

By default, each user is a member of the dynamic resource class smallrc. However, it is recommended to adjust your resource class depending on the workloads you will be performing (more about Resource Classes here).  Keep in mind that the while the smaller resource classes have a smaller precentage of memory, the max concurrency is higher. However, the xlargerc class may use up to 70% of available memory but it will only be able to run 1-2 jobs concurrently. 

Be mindful of the concurrency limits of your resource class when configuring max threads of your TimeXtender execution packages. 

SynapseResourceClassConcurrency.png

Sample Statement:

EXEC sp_addrolemember 'mediumrc', 'USERNAME'

Create a Master Key

(more about Master Keys here). Sample Statement:

CREATE MASTER KEY ENCRYPTION BY PASSWORD ='STRONGPASSWORD';

Connect to Azure Synapse SQL Pool from TimeXtender

  1. Add a Data Warehouse in TimeXtender
    1. Right Click on Data Warehouses in TimeXtender Solution Explorer > Add Data Warehouse. 
    2. Type the Azure SQL Server name from the Azure portal
    3. Type the User Name and Password created earlier.
    4. Type the Data Warehouse name from the Azure portal.
    5. Go to Advanced Settings > Deployment Target > SQL Data Warehouse
  2. Right Click Data Warehouse > Create required objects for SQL Data Warehouse
  3. If you have not already, run a query to create a Master key in the SQL Data Warehouse. This can be done using the TimeXtender Query Tool or Management Studio. 
  4. Add Tables, ideally from ODX Server with Azure Data Lake storage. 
  5. Right click a Table > Table Settings > SQL Data Warehouse Tab > Select desired Distribution method.
  6. Deploy & Execute! 

Troubleshooting

Error: "An error occurred during executing this statement \r\n DELETE FROM [dbo].[TimeXtenderExtendedProperties]…"

Error: "Please create a master key in the database or open the master key in the session before performing this operation."

Was this article helpful?
0 out of 0 found this helpful

4 Comments

  • 0
    Avatar
    Luis Simoes (MiddleEast)

    Is the reference to Data Lake meaning Data Lake Storage (ADLS Gen2) or the Data Lake Analytics (Being deprecated)?

    Also, using TimeXtender with Data Lake where is the data being processed? Is it on the ODX server? Databricks? Data Factory?

     

    Thank you

  • 0
    Avatar
    Taylor Hagin

    Hi Luis, this is using ADLS Gen 2. The transformations, lookups, & calculations happen in the SQL pool itself using stored procedures & views etc. In most cases this approach is less expensive and time consuming.

  • 0
    Avatar
    MB

    Hi, if there is an existing project with a data warehouse attached plus tables added. How do I point that to Synapse? 

  • 0
    Avatar
    Joseph Treadwell

Please sign in to leave a comment.