ADW2: Scalability of Azure SQL Data Warehouse.

A quick one today. 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 warehouseThe 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. The following video shows you how to 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.




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s