How to change the DWU allocation in Azure Synapse Analytics?

We have discussed about DWUs and cDWUs in a previous post. In short, DWUs and cDWUs are basic units of measuring performance in Azure Synapse Analytics. In this post, let’s look at the various methods available in Synapse Analytics to update the DWU allocation.  

1. Azure Portal GUI: This method is the most obvious and no-code way for changing the DWU allocation in Azure Synapse Analytics. To do this using the portal, we simply go to the Synapse Analytics Database and select Scale. The slider can then simply moved from left to right to increase (or from right to left to decrease) the DWU allocation.

2. PowerShell: As with most settings within Azure, the DWU allocation can be changed using PowerShell. We can use the Set-AzSqlDatabase PowerShell cmdlet

Set-AzSqlDatabase -DatabaseName "AzureDESQLDW" -ServerName "AzureDEServer" -RequestedServiceObjectiveName "DW1000c"

The above command sets the service level objective to DW1000.

Explanation:

AzureDESQLDW – Database for which we would like the DWU allocation to be updated.

AzureDEServer- Name of the server where the database has been created.

DW1000c – Number of DWUs to be allocated

3. T-SQL: This method of changing the DWUs is the most SQL developer friendly, since it uses the simple SQL keywords ALTER and MODIFY (see example below):

ALTER DATABASE AzureDESQLDW
MODIFY (SERVICE_OBJECTIVE = 'DW1000c')

Please note that the above SQL command should be run after connecting to the master SQL Database.

Explanation:

AzureDESQLDW – Database for which we would like the DWU allocation to be updated.

DW1000c – Number of DWUs to be allocated

4. REST API: The Create or Update Database REST API can be used to update the DWU allocation.

PUT https://management.azure.com/subscriptions/{subscription_azuede}/resourceGroups/{rg_azurede}/providers/Microsoft.Sql/servers/{azurede_server}/databases/{azuredeSQLDB}?api-version=2014-04-01-preview HTTP/1.1
Content-Type: application/json; charset=UTF-8

{
    "properties": {
        "requestedServiceObjectiveName": DW1000c
    }
}

The above command will update the DWU allocation to DW1000c for the database azuredeSQLDB, which is hosted on server azurede_server which is provisioned in the rg_azurede resource group.

Explanation:

azuredeSQLDB– Database for which we would like the DWU allocation to be updated.

azurede_server – Name of the server where the database has been created.

rg_azurede – Resource group name for the database server

DW1000c – Number of DWUs to be allocated

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/resource-consumption-models