Use Azure Synapse SQL Pool (SQL DW)

Azure Synapse Analytics is a limitless analytics service enabling insights across relational and non-relational big-data.

The Synapse SQL Pool (Previously Azure SQL Data Warehouse), is a massively parallel processing database. You can learn more about this service here.

This functionality enables the use of Azure Synapse Analytics as a target Data Warehouse.

If it is 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.

Table of contents:

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. Use an existing Azure SQL Server or create a new Azure SQL Server. 
   2. Take note of the SQL Server Admin Username & Password.
2. Create a Server Level Firewall Rule
   1. In the Azure SQL Server, click Firewalls and virtual networks, click Add client IP, and 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. Database: 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 Management Studio.  (More Info here). Sample statement:

--Create SQL Server Login in the Master DatabaseUSE MASTERCREATE LOGIN USERNAME WITH PASSWORD = 'STRONGPASSWORD';--Create User in the desired Data WarehouseUSE --***Insert Database Name Here***CREATE USER USERNAME FROM LOGIN USERNAME;--Assign User to the desired role in the Data WarehouseEXEC 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 while the smaller resource classes have a smaller percentage 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 instance in the portal
   1. Give it a name
   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

• Right-Click on Data Warehouse > Create required objects for SQL DW as indicated in step 2 of the instructions above.

• 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