Edit

Quickstart: Scale compute for dedicated SQL pool (formerly SQL DW) with Azure PowerShell

  • Article

You can scale compute for Azure Synapse Analytics dedicated SQL pools in an Azure Synapse Workspace 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 free Azure account before you begin.

Note

This article applies to dedicated SQL pools (formerly SQL DW) or in Azure Synapse connected workspaces. This content does not apply to dedicated SQL pools created in Azure Synapse 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 in Azure Synapse Analytics workspaces, see Quickstart: Scale compute for dedicated SQL pools in Azure Synapse workspaces with 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 (formerly SQL DW). If you need to create one, use Create and Connect - portal to create a dedicated SQL pool (formerly SQL DW) called mySampleDataWarehouse.

Sign in to Azure

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

Connect-AzAccount

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. Select Azure Synapse Analytics (formerly SQL DW) in the main search bar of the Azure portal.

  3. Select mySampleDataWarehouse from the Azure Synapse Analytics (formerly SQL DW) page to open the data warehouse. A screenshot of the Azure portal with the server name and resource group highlighted.

  4. The data warehouse name will be used as the database name. Remember, a data warehouse is one type of database. Also remember down the server name, and the resource group. You will use the server name and the resource group name in the pause and resume commands.

  5. Use only the first part of the server name in the PowerShell cmdlets. In the preceding image, the full server name is sqlpoolservername.database.windows.net. We use sqlpoolservername as the server name in the PowerShell cmdlet.

For example, to retrieve the properties and status of a dedicated SQL pool (formerly SQL DW):

Get-AzSqlDatabase -ResourceGroupName "resourcegroupname" -ServerName "sqlpoolservername" -DatabaseName "mySampleDataWarehouse"

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

Get-AzSqlDatabase -ResourceGroupName "resourcegroupname" -ServerName "sqlpoolservername"
$database | Select-Object DatabaseName,Status

Scale compute

In dedicated SQL pool (formerly SQL DW), you can increase or decrease compute resources by adjusting data warehouse units. The Create and Connect - portal created mySampleDataWarehouse and initialized it with 400 DWUs. The following steps adjust the DWUs for mySampleDataWarehouse.

To change data warehouse units, use the Set-AzSqlDatabase PowerShell cmdlet. The following example sets the data warehouse units to DW300c for the database mySampleDataWarehouse, which is hosted in the resource group resourcegroupname on server sqlpoolservername.

Set-AzSqlDatabase -ResourceGroupName "resourcegroupname" -DatabaseName "mySampleDataWarehouse" -ServerName "sqlpoolservername" -RequestedServiceObjectiveName "DW300c"

After the scaling operation is complete, the cmdlet returns output reflecting the new status, similar to the output of Get-AzSqlDatabase:

ResourceGroupName                : resourcegroupname
ServerName                       : sqlpoolservername
DatabaseName                     : mySampleDataWarehouse
Location                         : North Europe
DatabaseId                       : 34d2ffb8-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Edition                          : DataWarehouse
CollationName                    : SQL_Latin1_General_CP1_CI_AS
CatalogCollation                 :
MaxSizeBytes                     : 263882790666240
Status                           : Online
CreationDate                     : 1/20/2023 9:18:12 PM
CurrentServiceObjectiveId        : 284f1aff-xxxx-xxxx-xxxx-xxxxxxxxxxxx
CurrentServiceObjectiveName      : DW300c
RequestedServiceObjectiveName    : DW300c
RequestedServiceObjectiveId      :
ElasticPoolName                  :
EarliestRestoreDate              :
Tags                             :
ResourceId                       : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/
                                resourceGroups/resourcegroupname/providers/Microsoft.Sql/servers/sqlpoolservername/databases/mySampleDataWarehouse
CreateMode                       :
ReadScale                        : Disabled
ZoneRedundant                    :
Capacity                         : 2700
Family                           :
SkuName                          : DataWarehouse
LicenseType                      :
AutoPauseDelayInMinutes          :
MinimumCapacity                  :
ReadReplicaCount                 :
HighAvailabilityReplicaCount     :
CurrentBackupStorageRedundancy   : Geo
RequestedBackupStorageRedundancy : Geo
SecondaryType                    :
MaintenanceConfigurationId       : /subscriptions/d8392f63-xxxx-xxxx-xxxx-xxxxxxxxxxxx/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default
EnableLedger                     : False
PreferredEnclaveType             :
PausedDate                       :
ResumedDate                      :

Check data warehouse state

To see the current state of the data warehouse, use the Get-AzSqlDatabase PowerShell cmdlet. This cmdlet shows the state of the mySampleDataWarehouse database in resource group resourcegroupname and server sqlpoolservername.database.windows.net.

$database = Get-AzSqlDatabase -ResourceGroupName "resourcegroupname" -ServerName "sqlpoolservername" -DatabaseName "mySampleDataWarehouse"
$database

You can see the Status of the database in the output. In this case, you can see that this database is Online. When you run this command, you should receive a Status value of Online, Pausing, Resuming, Scaling, or Paused.

To see the status by itself, use the following command:

$database | Select-Object DatabaseName, Status

Next steps

You have now learned how to scale compute for dedicated SQL pool (formerly SQL DW). To learn more about dedicated SQL pool (formerly SQL DW), continue to the tutorial for loading data.

Load data into a dedicated SQL pool