Tech Talk Corner Sign up with your email address to be the first to know about new publications

Scale Azure Synapse Analytics SQL Pool with Azure Data Factory

Automatically scaling Azure Synapse Analytics is a must for your data movement solutions. While we wait for this capability to be completely available and built into the service, I’ll show you how to easily implement this functionality using Azure Data Factory pipelines. 

Download solution to Scale Azure Synapse Analytics SQL Pool with Azure Data Factory

You can find 2 different solutions in this blog post: 

  • Solution for scaling Azure Synapse SQL Pool in Synapse Studio. Download JSON Definition here
  • Solution for scaling Azure Synapse Analytics SQL Pool as a standalone service (formerly known as Azure SQL Data Warehouse). Download JSON Definition here

The reason for doing this is that the APIs are different. 

The most amazing fact about this solution is that it’s synchronous. It won’t finalize until new resources have been allocated or deallocated. Check the definition of synchronous and asynchronous here

Example data movement process:

Data Movement Process

You can download the script to Pause and Resume Azure Synapse Analytics SQL Pool here.

Creating a reusable pipeline to scale Data Warehouse Units (DWUs) 

To begin, Azure Data Factory can scale out resources for you by using an Azure Data Factory pipeline and Web Activity capabilities. 

First, you need to create a new pipeline. 

Create a new pipeline

To make it reusable across different SQL Pools, create the following parameters. You can add a default value as well. 

ServerName is the Azure Synapse Analytics workspace name when using a workspace SQL Pools solution. 

Drag and drop Web activity into the pipeline. We will use Azure Synapse Analytics Workspace APIs and Azure Synapse Analytics (formerly known as Azure SQL Data Warehouse)APIs

Drag and drop web activity into the pipeline

Now, let’s configure the Web Activity as follow. 

Scale Azure Synapse Analytics SQL Pool in Synapse Studio 

Next, in Azure Synapse Studio, configure the web activity using the following configuration.  This solution is for SQL Pools created within Azure Synapse Analytics Workspaces. 

1. REST API endpoint  

@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionId,'/resourceGroups/',pipeline().parameters.ResourceGroupName,'/providers/Microsoft.Synapse/workspaces/',pipeline().parameters.ServerName,'/sqlPools/',pipeline().parameters.DatabaseName,'?api-version=2019-06-01-preview') 

2. The method for this API endpoint needs to be PATCH. 

3. HTTP Body 

@concat( 

'{ 

 "sku": { 

    "name": ''',pipeline().parameters.DWU,''' 

  } 

}' 

 

) 

4. Use MSI Authentification, if you want to know more about managed identities (check this blog post). 

5. Resources to manage. 

https://management.azure.com/ 

The pipeline is ready for testing! Click debug. 

Scale Azure Synapse Analytics SQL Pool as a standalone service (formerly known as Azure SQL Data Warehouse) 

If you are using Azure Synapse Analytics SQL Pools, you need to give Azure Data Factory or Synapse Analytics access to manage your SQL Pool using Azure role-based access control (RBAC). 

Give access control to Azure Synapse Analytics SQL Pools

Now, configure the Web activity using the following configuration. 

1. REST API endpoint  

@concat('https://management.azure.com/subscriptions/',pipeline().parameters.SubscriptionId,'/resourceGroups/',pipeline().parameters.ResourceGroupName,'/providers/Microsoft.Sql/servers/',pipeline().parameters.ServerName,'/databases/',pipeline().parameters.DatabaseName,'?api-version=2014-04-01-preview') 

2. The method for this API endpoint needs to be PATCH. 

3. HTTP Body 

@concat( 

'{ 

    "properties": { 

        "requestedServiceObjectiveName": ''',pipeline().parameters.DWU,''' 

    } 

}' 

) 

4. Use MSI Authentification, if you want to know more about managed identities (check this blog post). 

5. Resources to manage. 

https://management.azure.com/ 

The pipeline is ready for testing! Click debug. 

Testing the pipeline 

Finally, when testing the pipeline, it should work. You’ll see that it takes some time to perform the operation. 

In the activity logs, you can also see the operation. 

Summary  

In summary, today you’ve created a reusable Azure Data Factory pipeline for scaling resources in your Azure Synapse Analytics SQL Pools. You can re-use it across any other existing workloads that you already have. 

What’s Next?  

In upcoming blog posts, we’ll continue to explore Azure Data Services features.   

Please  follow Tech Talk Corner on Twitter for blog updates, virtual presentations, and more!          

Check out these other blog posts 

comment [ 0 ]
share
No tags 0

No Comments Yet.

Do you want to leave a comment?

Your email address will not be published. Required fields are marked *