The Scalability of Azure SQL Data Warehouse

Play The Scalability of Azure SQL Data Warehouse
Sign in to queue


A quick one. In the first post of Azure SQL Data Warehouse, we introduced the key business benefits of the service. Namely the ability to scale on demand, and the pause and restart the service at will.

The ability to scale a SQL Data Warehouse on demand provides several benefits

  1. You can increase the compute of SQL Data Warehouse during periods of heavy demand. Heavy demand can either include a period of sustained data loads, or periods of heavy querying. You can scale up the compute during these peak periods, and then reduce the compute when the demand is not there
  2. You can control the cost of the data warehouse. The ability to change the compute at will, also enable you to control to costs of the data warehouse. Pricing can be found at the following page. Note that the price is broken down into the cost of the compute, and the cost of storing the data.

There are several methods to scale a SQL Data Warehouse. You can scale using the Azure Portal. But you can also scale the data warehouse using the following TSQL code:

MODIFY (service_objective = 'DW100');
Finally, PowerShell could be used to scale the data warehouse:
Set-AzureRmSqlDatabase `
–ResourceGroupName "RG_name" `
–ServerName "SRV_name" `
–DatabaseName "DB_name" `
-RequestedServiceObjectiveName "DW100"


Your organization may face a period where no access is required to the data held in a data warehouse. Perhaps during the holiday season, your organization informs the staff that IT maintenance is being performed over the holidays and that access to data and network resources is not guaranteed. In this case, you can pause the Azure SQL Data Warehouse. This means that you won't be charged for the compute aspect of the warehouse, only the storage aspect.  You can pause the service using the Azure Portal as shown in the following video, or you can use the following PowerShell code:

Suspend-AzureRmSqlDatabase `
–ResourceGroupName "RG_name" `
–ServerName "SRV_name" `
–DatabaseName "DB_name"
You can resume the Azure SQL Data Warehouse with similar PowerShell Code:

Resume-AzureRmSqlDatabase `
–ResourceGroupName "RG_name" `
–ServerName "SRV_name" `
-DatabaseName "DB_name"

These simple steps can be used to help you scale and pause the service when you need it. Giving you control of the performance and the costs of the Solution.



Download this episode

The Discussion

Add Your 2 Cents