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:

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. 

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]…"

• 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.

References

The Fastest Way to Implement Azure Synapse Analytics