Note: There is an updated version of this article, which applies to the next generation of TimeXtender.
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 with TimeXtender. Here's a video guide to setting up SQL Pool for use with TimeXtender.
In this article you will learn how to:
- Create an Azure Synapse SQL Pool
- Connect to Azure Synapse SQL Pool from TimeXtender
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:
- In the Azure Portal, click "Create a Resource" and type "Dedicated SQL Pool"
- You may use an existing Azure SQL Server, or you can create a new one.
- Note the SQL Server Admin Username & Password as.
- Create a Server Level Firewall Rule
- In the Azure SQL Server, click Firewalls and virtual networks, click Add client IP, click save.
Connect to the Server
- Get the fully qualified server name
- On the Synapse Dedicated SQL Pool resource Overview page, find the "Server Name" and click copy.
- Using SQL Server Management Studio, connect to the database
- Server name: fully qualified Server name
- Datbase: name of the Dedicated SQL Pool
- 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
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.
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
- Add a Data Warehouse in TimeXtender
- Right Click on Data Warehouses in TimeXtender Solution Explorer > Add Data Warehouse.
- Type the Azure SQL Server name from the Azure portal
- Type the User Name and Password created earlier.
- Type the Data Warehouse name from the Azure portal.
- Go to Advanced Settings > Deployment Target > SQL Data Warehouse
- Right Click Data Warehouse > Create required objects for SQL Data Warehouse
- 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.
- Add Tables, ideally from ODX Server with Azure Data Lake storage.
- Right click a Table > Table Settings > SQL Data Warehouse Tab > Select desired Distribution method.
- Deploy & Execute!
Error: "An error occurred during executing this statement \r\n DELETE FROM [dbo].[TimeXtenderExtendedProperties]…"
- You need to Right Click Data Warehouse > Create required objects for SQL DW as indicated in step 2 of the instructions above.
Error: "Please create a master key in the database or open the master key in the session before performing this operation."
- You need to create the Master Key by running the SQL query directly against the database.
- More info in step 3 of the instructions above.
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?
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.
Hi, if there is an existing project with a data warehouse attached plus tables added. How do I point that to Synapse?