Quickstart: Scale compute in Azure SQL Data Warehouse in PowerShell

Scale compute in Azure SQL Data Warehouse in 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 account before you begin.

Before you begin

Note

This article has been updated to use the new Azure PowerShell Az module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. For Az module installation instructions, see Install Azure PowerShell.

This quickstart assumes you already have a SQL Data Warehouse that you can scale. If you need to create one, use Create and Connect - portal to create a data warehouse called mySampleDataWarehouse.

Log in to Azure

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

Connect-AzAccount

To see which subscription you are 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. Click SQL data warehouses in the left page of the Azure portal.

  3. Select mySampleDataWarehouse from the SQL data warehouses page. This opens the data warehouse.

    Server name and resource group

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

  5. If your server is foo.database.windows.net, use only the first part as the server name in the PowerShell cmdlets. In the preceding image, the full server name is newserver-20171113.database.windows.net. We use newserver-20180430 as the server name in the PowerShell cmdlet.

Scale compute

In SQL Data Warehouse, 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 myResourceGroup on server mynewserver-20180430.

Set-AzSqlDatabase -ResourceGroupName "myResourceGroup" -DatabaseName "mySampleDataWarehouse" -ServerName "mynewserver-20171113" -RequestedServiceObjectiveName "DW300c"

Check data warehouse state

To see the current state of the data warehouse, use the Get-AzSqlDatabase PowerShell cmdlet. This gets the state of the mySampleDataWarehouse database in ResourceGroup myResourceGroup and server mynewserver-20180430.database.windows.net.

$database = Get-AzSqlDatabase -ResourceGroupName myResourceGroup -ServerName mynewserver-20171113 -DatabaseName mySampleDataWarehouse
$database

Which will result in something like this:

ResourceGroupName             : myResourceGroup
ServerName                    : mynewserver-20171113
DatabaseName                  : mySampleDataWarehouse
Location                      : North Europe
DatabaseId                    : 34d2ffb8-b70a-40b2-b4f9-b0a39833c974
Edition                       : DataWarehouse
CollationName                 : SQL_Latin1_General_CP1_CI_AS
CatalogCollation              :
MaxSizeBytes                  : 263882790666240
Status                        : Online
CreationDate                  : 11/20/2017 9:18:12 PM
CurrentServiceObjectiveId     : 284f1aff-fee7-4d3b-a211-5b8ebdd28fea
CurrentServiceObjectiveName   : DW300c
RequestedServiceObjectiveId   : 284f1aff-fee7-4d3b-a211-5b8ebdd28fea
RequestedServiceObjectiveName :
ElasticPoolName               :
EarliestRestoreDate           :
Tags                          :
ResourceId                    : /subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/
                                resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/mynewserver-20171113/databases/mySampleDataWarehouse
CreateMode                    :
ReadScale                     : Disabled
ZoneRedundant                 : False

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 your data warehouse. To learn more about Azure SQL Data Warehouse, continue to the tutorial for loading data.