Quickstart: Scale compute for dedicated SQL pools in Azure Synapse Workspaces with Azure PowerShell

  • Article

You can scale compute for Azure Synapse Analytics dedicated SQL pools using Azure PowerShell. Scale out compute for better performance, or scale back compute to save costs.

If you don't have an Azure subscription, create a trial Azure account before you begin.

Note

This article applies to dedicated SQL pools created in Azure Synapse Analytics workspaces. This content does not apply to dedicated SQL pools (formerly SQL DW) or dedicated SQL pools (formerly SQL DW) in connected workspaces. There are different PowerShell cmdlets to use for each, for example, use Set-AzSqlDatabase for a dedicated SQL pool (formerly SQL DW), but Update-AzSynapseSqlPool for a dedicated SQL pool in an Azure Synapse Workspace. For similar instructions for dedicated SQL pools (formerly SQL DW), see Quickstart: Scale compute for dedicated SQL pools (formerly SQL DW) using Azure PowerShell. For more on the differences between dedicated SQL pools (formerly SQL DW) and dedicated SQL pools in Azure Synapse Workspaces, read What's the difference between Azure Synapse (formerly SQL DW) and Azure Synapse Analytics Workspace.

Before you begin

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. See Install Azure PowerShell to get started. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

This quickstart assumes you already have a dedicated SQL pool that was created in a Synapse workspace. If you need, Create an Azure Synapse workspace and then create a dedicated SQL pool using Synapse Studio.

Sign in to Azure

Sign in to your Azure subscription using the Connect-AzAccount command and follow the on-screen directions.

Connect-AzAccount -Environment AzureChinaCloud

To see which subscription you're using, run Get-AzSubscription.

Get-AzSubscription

If you need to use a different subscription than the default, run Set-AzContext.

Set-AzContext -SubscriptionName "MySubscription"

Look up data warehouse information

Locate the database name, server name, and resource group for the data warehouse you plan to pause and resume.

Follow these steps to find location information for your data warehouse.

  1. Sign in to the Azure portal.

  2. Search for Azure Synapse Analytics in the search bar of the Azure portal.

  3. Select your Synapse workspace from the list.

  4. Select SQL pools under Analytics pools in the menu list.

  5. If you see the message The dedicated pools listed below are hosted on the connected SQL Server, your dedicated SQL pool (formerly SQL DW) is in a Connected workspace. Stop, and instead use the PowerShell examples in Quickstart: Scale compute for dedicated SQL pool (formerly SQL DW) with Azure PowerShell. Proceed for dedicated SQL pools created in a Synapse workspace.

  6. Select the name of your dedicated SQL pool from the Synapse workspace | SQL pools page. In the following samples, we use contoso_dedicated_sql_pool.

  7. As in the following image, we use contoso-synapse-workspace as the Azure Synapse workspace name in the following PowerShell samples, in the resource group contoso.

    A screenshot of the Azure portal with the server name and workspace highlighted.

For example, to retrieve the properties and status of a dedicated SQL pool created in a Synapse workspace:

Get-AzSynapseSqlPool -ResourceGroupName "contoso" -Workspacename "contoso-synapse-workspace" -name "contoso_dedicated_sql_pool"

To retrieve all the data warehouses in a given server, and their status:

$pools = Get-AzSynapseSqlPool -ResourceGroupName "resourcegroupname" -Workspacename "synapse-workspace-name"
$pools | Select-Object DatabaseName,Status,Tags

Scale compute

You can increase or decrease compute resources by adjusting the dedicated SQL pool's data warehouse units. The Workload management menu of the Azure portal provides scaling, but this can also be accomplished with PowerShell.

To change data warehouse units, use the Update-AzSynapseSqlPool PowerShell cmdlet. The following example sets the data warehouse units to DW300c for the database contoso_dedicated_sql_pool, which is hosted in the resource group contoso in the Synapse workspace contoso-synapse-workspace.

Update-AzSynapseSqlPool -ResourceGroupName "contoso" -Workspacename "contoso-synapse-workspace" -name "contoso_dedicated_sql_pool" -PerformanceLevel  "DW300c"

The PowerShell cmdlet will begin the scaling operation. Use the Get-AzSynapseSqlPool cmdlet to observe the progress of the scaling operation. For example, you will see Status reported as "Scaling". Eventually, the pool will report the new Sku value and Status of "Online".

ResourceGroupName     : contoso
WorkspaceName         : contoso-synapse-workspace
SqlPoolName           : contoso_dedicated_sql_pool
Sku                   : DW300c
MaxSizeBytes          : 263882790666240
Collation             : SQL_Latin1_General_CP1_CI_AS
SourceDatabaseId      :
RecoverableDatabaseId :
ProvisioningState     : Succeeded
Status                : Scaling
RestorePointInTime    :
CreateMode            :
CreationDate          : 2/21/2023 11:33:45 PM
StorageAccountType    : GRS
Tags                  : {[createdby, chrisqpublic]}
TagsTable             :
                        Name       Value
                        =========  =======
                        createdby  chrisqpublic
                        
Location              : chinanorth3
Id                    : /subscriptions/abcdefghijk-30b0-4d4f-9ebb-abcdefghijk/resourceGroups/contoso/providers/Microsoft.Synapse/workspaces/contoso-synapse-workspace/sqlPools/contoso_dedicated_sql_pool
Type                  : Microsoft.Synapse/workspaces/sqlPools

Next steps

You have now learned how to scale compute for dedicated SQL pool in a Synapse workspace. To learn more about dedicated SQL pools, continue to the tutorial for loading data.

Load data into a dedicated SQL pool