Azure Data Factory: Autoscale Azure SQL Database From A Pipeline

In this tutorial, Austin Libal, a trainer at Pragmatic Works, demonstrates a powerful technique to optimize both cost and performance when running ETL processes in Azure. By dynamically scaling an Azure SQL Database up before execution and down afterward, organizations can significantly reduce expenses while maintaining high performance during heavy workloads.

Understanding the Need for Scaling

Azure SQL Databases operate on a pricing model based on compute resources, measured in DTUs (Database Transaction Units). A database configured at a lower tier, such as Basic, is cost-effective but may not provide sufficient performance for intensive ETL workloads.

Austin explains that instead of permanently running a database at a higher tier, users can:

• Scale the database up before running ETL processes
• Execute the required workload
• Scale the database back down after completion

This approach ensures that users are billed at higher rates only when necessary, optimizing both cost and efficiency.

Using Stored Procedures for Database Scaling

The first step in this process involves creating stored procedures within the Azure SQL Database:

• Scale Up Procedure: Modifies the database tier (e.g., from Basic to Standard S3)
• Scale Down Procedure: Reverts the database back to its original lower tier

Austin highlights that scaling operations in Azure SQL are performed online, meaning they can occur without downtime. This allows ETL processes to run seamlessly without interrupting database availability.

Building the Azure Data Factory Pipeline

To automate this process, Austin uses Azure Data Factory to create a pipeline that orchestrates the scaling and ETL workflow. The pipeline includes several key components:

1. Stored Procedure Activity

   This activity triggers the stored procedure responsible for scaling the database up. It connects to the Azure SQL Database via a linked service.

2. Until Activity (Control Flow)

   The pipeline uses an Until loop to wait until the database has fully scaled to the desired compute tier (e.g., S3) before proceeding.

3. Lookup Activity

   Inside the loop, a lookup query retrieves the current compute size of the database. This information is used to determine whether scaling has completed.

4. Wait Activity

   An optional wait step (e.g., 15 seconds) prevents excessive polling and reduces unnecessary load on the system.

Using Expressions to Monitor Scaling

Austin demonstrates how to configure an expression within the Until activity to evaluate whether the database has reached the desired tier. The logic compares the current compute size retrieved from the lookup activity with the target value (S3).

Once the condition is met, the pipeline continues execution, ensuring that ETL processes only begin when sufficient resources are available.

Executing and Monitoring the Pipeline

When the pipeline is executed in debug mode, users can observe the scaling process in real time. Outputs from the lookup activity reveal whether the database is still in the Basic tier or has transitioned to S3.

Austin notes that scaling time depends on the size of the database, typically taking about one minute per gigabyte of data. Once scaling is complete, the ETL process can proceed efficiently.

Scaling the Database Back Down

After the ETL process finishes, a second stored procedure is triggered to scale the database back down to its original tier. This ensures that higher compute costs are incurred only during active processing.

Austin also suggests adding validation logic to confirm that the database has successfully scaled down, especially when working with higher-cost tiers.

Key Benefits of This Approach

• Cost Savings: Pay for higher performance only when needed
• Improved Performance: Run ETL processes faster with increased compute power
• Automation: Fully automated pipeline eliminates manual intervention
• Scalability: Easily adaptable for different workloads and database sizes

Final Thoughts

Austin Libal emphasizes that this method is one of several ways to manage database scaling in Azure. Alternatives include using web activities or embedding logic directly within stored procedures. However, this approach provides a straightforward and accessible solution, especially for those new to Azure Data Factory.

For those looking to deepen their knowledge, Pragmatic Works offers additional training through boot camps and on-demand learning platforms, covering Azure SQL Database, Azure Data Factory, and Azure Synapse Analytics.

By implementing dynamic scaling strategies, users can achieve a balance between performance and cost—making their data workflows more efficient and scalable.

 Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Azure Data Factory and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.