A quick one today. In my first article on Azure SQL Data Warehouse (ASDW), 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:
- You can increase the compute of SQL Data Warehouse during periods of heavy demands. Heavy demands 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
- 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. The following video shows you how to scale using the Azure Portal. But you can also scale the data warehouse using the following T-SQL code:
ALTER DATABASE ContosoRetailDW MODIFY (service_objective = 'DW100');
The service_objective defines the scale of the data warehouse. When DW100 is selected, think of this as one SQL instance being provisioned as a node to host the data warehouse. When DW6000 is selected, 60 SQL instances are provisioned to host the data warehouse. We will go into this in more depth in a separate article. This example should provide you with the concept of how ASDW scales though at this stage.
As an alternative, you can also use 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 your business operation is only 9am – 5pm? Or perhaps during the holiday season, your organization is in shutdown, 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. The storage of the data is a lot cheaper than the compute. More details regarding the procing of the compute versus the storage aspects of ASDW can be found here. 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 pause the service when you need it, and resume it at the same DWU when you need it again. Giving you control of the performance and the costs of the Solution.
