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
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.
To see which subscription you are using, run 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.
Sign in to the Azure portal.
Click SQL data warehouses in the left page of the Azure portal.
Select mySampleDataWarehouse from the SQL data warehouses page. This opens the data warehouse.
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.
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.
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
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.