This blog post was co-authored by Eldad Hagashi, Software Engineering Manager, and Feng Tan, Software Engineer, Microsoft Education Data Service.
Azure SQL Data Warehouse is Microsoft’s SQL analytics platform, the backbone of your enterprise data warehouse (EDW). The service is designed to allow customers to elastically, and independently, scale compute and storage with massively parallel processing. SQL DW integrates seamlessly with big data stores and acts as a hub to your data marts and cubes for an optimized and tailored performance of your EDW. Azure SQL DW offers guaranteed 99.9% high availability, compliance, advanced security, and tight integration with upstream and downstream services so you can build a data warehouse that fits your needs. Azure SQL DW is the first and only service enabling enterprises to replicate their data everywhere with global availability in more than 30 regions.
Inside of Microsoft, Azure services are used extensively across teams. Clever solutions combining multiple Azure products are the norm across teams such as the OneNote and Education Microsoft team who built an Autoscaler to meet its SQL Data Warehouse compute demand while minimizing its cost.
Willing to share that work with the SQL DW community, the team provide to us (and you!) their solution with a deployable template to use with just 1-click. For the Education Data Service team, their SQL Data Warehouse represents the single source of truth for all their data across Microsoft. Their instance will drive shared outcomes across Engineering, Marketing, Finance, and Sales teams. Their data warehouse solution consists of Data Lake Storage, Data Factory, Data Warehouse, Analysis Services, and Power BI.
SQL Data Warehouse was a good fit for the team for its ability to provide interactive querying and transforming raw data into business value. To derive the best price and performance, the team needed to ensure that their Data Warehouse Units (DWU) value matched their actual demand at any given time. Lower DWU levels allowed for cost savings, but meant business questions would take longer to answer. Higher DWU levels meant higher performance, but their occasional requirements of 3,000 DWUs would mean a much higher monthly cost if left alone. The team needed an intelligent way to manage elasticity with more flexibility than a schedule-based solution. This is the reason they decided to introduce a scaling solution based on system demand that would be hands free, reduce costs, and be non-disruptive to their workload.
The EDU Data Service team’s Autoscaler is comprised of two primary components including a DWU-usage monitor based scaler and a timer-based scaler. The monitor-based scaler uses a pre-defined DWU ladder, which the scaler climbs up and down, based on usage. Over the course of the day, as the scaler notices DWU usage above or beyond usage thresholds, the scaler will move the system up and down the DWU ladder. The timer-based scaler operates as a supplement to scale the system back to a defined baseline before the business day’s operations if the DWU settings drop too low from inactivity.
Autoscaler uses Azure HTTP Webhook Function combined with Azure Monitor and alert notifications. Azure Monitor observes DWU usage for the previous period, typically 30 minutes. When the DWU usage exceeds an upper threshold, such as 80% of the DWU limit, Azure Monitor will send an alert via email and triggers a webhook. This parameter can easily be changed. The webhook HTTP request will trigger Autoscaler to send a REST API request to their data warehouse to scale either up or down the DWU ladder depending on which threshold monitor was fired. Autoscaler then logs the request in the Azure Table for later analysis and quality control. At the beginning of each business day, the timer-scaler will scale the instance to the day’s baseline.
With the use of the Autoscaler, we see that their DWU levels closely match their demand over the course of the day. Over the course a month, the team has saved around approximately 55% per year!
To learn more about Autoscaler, check out our GitHub or deploy now to your own instance! Before deploying, bear in mind that Autoscaler as a solution works specifically with Optimized for Elasticity in architectures that decouples loading, transformation, and querying into discrete jobs. For the EDU team, the use of Azure Data Factory’s retry-logic allows loading and transformation disruption by the Autoscaler. For querying, the use of Azure Analysis Services as a serving layer delivers performance to the team without disruption.
If you need our help for a POC, contact us directly. Stay up-to-date on the latest Azure SQL DW news and features by following us on Twitter @AzureSQLDW.