How to automatically pause and resume Azure SQL Data Warehouse to optimize costs

Azure SQL Data Warehouse is a cloud-based data warehouse-as-a-service available on the Azure platform. The massively parallel processing architecture allows distributing query computations over numerous nodes, which ensures results are obtained very efficiently even for large volumes of data.

However, SQL Data Warehouse can be an expensive resource. The exact price depends on the chosen resource tier, but fees can exceed $65’000 USD/mo for the highest tiers (6000 DWU).

A simple way to save a significant amount of money is to pause Azure SQL Data Warehouses during off hours. This is especially useful with non-production Warehouses, or Warehouses that are used at specific times of the week or month.  When a Warehouse is paused, the only incurred costs are for data storage, which is relatively inexpensive in comparison to the cost of the Warehouse itself.

In this article, we’ll show how you can automatically pause and resume Azure SQL Data Warehouses using Netreo. To further optimize the usage, you can automatically scale them according to the demand. Read this article to learn more.

To automatically pause and resume Azure SQL Data Warehouse follow these steps:

1. Run Netreo Setup Wizard to connect to an Azure environment

If you aren’t using Netreo yet, request a demo, and our team can help you through the process. Ensure that Netreo detected Azure SQL Data Warehouses in your subscription. Learn more about setup here.

2. Define automation rules to pause and resume Azure SQL Data Warehouses

Netreo can execute pause and resume operations according to a schedule, for example, every day at specific time, on weekends, or on given days in the month. Learn more about schedules here. In this example, we’ll trigger those actions every day to ensure Warehouses are active only during the work day, i.e. from 9 am to 5 pm.

You need to define two actions that pause and resume Azure SQL Data Warehouses, respectively:

• Open Configuration dialog for the Azure SQL Data Warehouse resource.
• In the “Actions” tab add a new “PauseForTheNight” action that will execute the “AzureSqlWarehousePause” command at a specific time (screenshot below). Create a schedule entry that defines the time when this action will be executed, i.e. every day at approximately 5 pm.
• In the “Actions” tab add a new action that will execute a “ResumeForTheDay” action that utilizes “AzureSqlWarehouseResume” command at a specific time (screenshot below). Create a schedule defining when the resource should be activated, i.e. every day at approximately 9 am.

A similar approach can be used to automatically execute operations on other resources, for example to shutdown and restart VMs.

To optimize costs even more, see how you can auto-scale Warehouses according to the demand.

Find Out how Netreo can help with all Azure monitoring needs. Request a Demo Today