The Scalability of Azure SQL Data Warehouse
By Chris Testa-O’Neill, Features Engineer at Microsoft.
Chris Testa-O’Neill is a Features Engineer in the Analytics and Data Science team at Microsoft. He also runs SQLBits, which takes place in the UK every spring!
A quick one today. In the first post on Azure SQL Data Warehouse, we introduced the key business benefits of the service. Namely, the ability to scale on demand, and being able to pause and restart the service at will.
The ability to scale a SQL Data Warehouse on demand provides several benefits:
- 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 compute during these peak periods, and then reduce compute when the demand is not there.
- You can control the cost of the data warehouse. The ability to change compute at will also enables you to control the 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 with which you can scale a SQL Data Warehouse. The following video shows how you can scale using the Azure Portal:
Alternatively, you can also scale the data warehouse using the following TSQL code:
ALTER DATABASE ContosoRetailDW
MODIFY (service_objective = ‘DW100’);
You can even use PowerShell to scale the data warehouse:
Set-AzureRmSqlDatabase `
–ResourceGroupName “RG_name” `
–ServerName “SRV_name” `
–DatabaseName “DB_name” `
-RequestedServiceObjectiveName “DW100”
Your organisation may face a period where no access to the data held in a data warehouse is required. Perhaps, during the holiday season, your organisation 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 both performance and costs.