How to Auto scaleup and scaledown the DWU in Azure Synapse Dedicated SLQ Pool by pipeline?

Question

How do you Auto increase or auto decrease the DWU in Azure synpase Dedicated SQL Pool using a pipeline?

Answer 1

@K Madhavi ,

Welcome to Microsoft Q&A platform and thanks for posting your query here.

As I understand your question, you want to know how to create a pipeline which automatically scales up and down the dedicated sql pool in synapse . Please let me know if thats not the ask here.

Pre-requisite: Provide contributor access to synapse managed identity in synapse workspace.

Follow the below steps while creating the synapse pipeline:

1. Inside the web activity settings, use the URL:

https://management.azure.com/subscriptions/{SubscriptionID}/resourceGroups/{ResourceGroupName}/providers/Microsoft.Synapse/workspaces/{SynapseWorkspaceName}/sqlPools/{SQLPoolName}?api-version=2021-04-01-preview

2. Select method as PUT

3. Provide Header as : Content-Type application/json

4. Provide body in this format :

{ "location": "eastus", "sku": {"name": "DW200c"} }

Other supported api-versions are '2019-06-01-preview, 2020-12-01, 2021-03-01, 2021-04-01-preview, 2021-05-01, 2021-06-01-preview, 2021-06-01, 2020-04-01-preview'. Other supported locations are 'westus2, eastus, northeurope, westeurope, southeastasia, australiaeast, westcentralus, southcentralus, eastus2, uksouth, westus, australiasoutheast, eastasia, brazilsouth, centralus, centralindia, japaneast, northcentralus, canadacentral, canadaeast, koreacentral, southafricanorth, ukwest, japanwest, francecentral, switzerlandnorth, germanywestcentral, norwayeast, uaenorth, westus3, jioindiawest, southindia'

5. Provide authentication method as : 'System Assigned Managed Identity' and resource as : https://management.azure.com/

Attaching the video for reference:

Helpful Resources:

https://erwindekreuk.com/2021/02/scale-your-sql-pool-dynamically-in-azure-synapse/

https://learn.microsoft.com/en-us/answers/questions/931306/unable-scale-synapse-pool-using-adf-managed-identi

---

Hope it helps. Kindly accept the answer by clicking Accept answer and mark it as helpful by clicking yes.

Answer 2

Hey, Can you be a little more clear w.r.t your ask as to when do you need auto increase or decrease?
scenarios at which you want to upgrade or downgrade?

But you can use TSQL script :

ALTER DATABASE MySQLDW MODIFY (SERVICE_OBJECTIVE = 'DW1000c') ;

to modify the DWUs