How to automate the rightsizing process of sql single db (DTU)?

Padmanabhan M 220 Reputation points

I need to automate the process of rightsizing sql single db (DTU) based on the workloads to optimize the cost. And also need to check if there will be any downtime and how to handle it. And also this should be rightsized in the non usage hours only because the queries won't be affected. How to do these all?

Also provide if there are any official docs, articles or blogs based on this automation process which can handle the downtime also.

  1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @Padmanabhan M

    Azure SQL Single Database (DTU model) does not provide native auto-rightsizing. A common approach is to combine Azure Monitor, Azure Automation Runbooks, and PowerShell to automate scaling decisions.

    Recommended approach:

    1. Use Azure Monitor to collect DTU%, CPU%, Data IO, and query performance metrics.
    2. Create an Azure Automation Runbook that evaluates utilization thresholds and executes Set-AzSqlDatabase to scale up or down.
    3. Schedule the runbook during non-business hours to minimize application impact.
    4. Implement application retry logic because scaling operations may briefly disconnect active sessions.

    For example:

    • Average DTU > 80% for 7 days > Scale up
    • Average DTU < 30% for 14 days > Scale down

    Official documentation:

    If this is a new deployment, consider moving to the vCore serverless model because DTU databases do not support native autoscaling.

  2. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @Padmanabhan M
    I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

  3. Padmanabhan M 220 Reputation points

    Hi @Pilladi Padma Sai Manisha

    You said schedule during non-business hours — but how exactly do I identify non-business hours programmatically? Is there a metric or query to detect zero or near-zero active sessions/connections at runtime, rather than hardcoding a time window?

    Can we connect through microsoft teams?

  4. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @Padmanabhan M,

    That's a good question. Instead of hardcoding a time window, you can determine non-business hours dynamically by checking database activity before triggering the rightsizing operation.

    You can use Azure Monitor metrics (DTU %, CPU %) along with SQL DMVs to identify periods of low activity. For example, your automation can proceed only when utilization remains below a defined threshold for a certain duration and there are very few active sessions or running queries.

    You can query active sessions using:

    An Azure Automation runbook can evaluate these conditions first and execute Set-AzSqlDatabase only when the database is relatively idle; otherwise, it can skip the run and retry later.

    Useful references:

Sign in to comment

1 answer

  1. AI answer

    Rightsizing a single Azure SQL Database that uses the DTU model can be automated by combining Azure monitoring with scheduled automation that scales the database up or down outside business hours.

    1. Decide how to monitor and evaluate utilization
    • Use the built‑in monitoring for Azure SQL Database to track DTU usage and query performance over time. For guidance on monitoring and tuning, see the Monitor and performance tuning guidance linked from the “New DBA in the cloud” article.
    • Analyze historical trends to determine thresholds that indicate “running hot” (for example, consistently high DTU usage and long‑running queries) versus “under‑utilized” during peak hours.
    • Azure SQL Database also provides Query Performance Insights and automatic tuning recommendations that help understand workload patterns and performance issues. These can guide what “right size” looks like for the workload.
    1. Automate scaling using Azure Automation
    • Azure SQL Database supports dynamic scaling of a single database up or down after the initial DTU/vCore choice. Scaling can be done via:
      • Transact‑SQL (ALTER DATABASE)
      • Azure portal
      • PowerShell
      • Azure CLI
      • REST API as described in the single‑database scaling article.
    • Azure Automation is designed to run scheduled, repeatable tasks and already has Az PowerShell cmdlets available. It can:
      • Call Set-AzSqlDatabase (or equivalent) to change the DTU service tier/compute size.
      • Optionally query metrics or DMVs before scaling to confirm that current time is within the desired “non‑usage” window and that utilization patterns justify scaling.
    • Typical pattern:
      1. Create an Automation account.
      2. Use a managed identity for authentication to Azure SQL resources.
      3. Author a PowerShell runbook that:
        • Checks current time against a defined schedule (for example, nights/weekends).
        • Optionally reads utilization history (from Azure Monitor or Query Store) to decide whether to scale up or down.
        • Calls the appropriate Az.Sql cmdlet or REST API to update the database DTU tier.
      4. Attach a schedule to the runbook so it runs only during non‑usage hours.
    • Azure Automation can also combine SQL commands and Az cmdlets in the same runbook, so it is possible to perform pre‑ or post‑checks directly against the database if needed.
    1. Handle downtime during scaling
    • Azure SQL Database supports dynamic scaling with minimal downtime, but there is a brief switchover period where connectivity is lost.
    • This interruption can be mitigated by implementing retry logic in the application so transient connection failures during the scale operation are retried.
    • Because the requirement is to scale only during non‑usage hours, schedule the Automation runbooks in those windows so that any brief connectivity loss does not affect users.
    1. Cost and performance optimization features to complement rightsizing
    • Azure SQL Database provides:
      • Performance monitoring and optimization via Query Performance Insights and automatic tuning (SQL Database Advisor). Auto‑tuning can automatically create/drop indexes and force plans, improving performance without manual intervention.
      • Cost optimization recommendations based on utilization history across databases in a server. These recommendations can help decide when and how far to scale down.
    • For some workloads, consider the vCore model or the serverless compute tier (which provides compute autoscaling and per‑second billing) as an alternative to manual DTU rightsizing.
    1. High‑level implementation outline
    • Step 1: Use monitoring and Query Performance Insights to collect at least a few weeks of utilization data and identify non‑usage hours.
    • Step 2: Define DTU tiers for “peak” and “off‑peak” based on observed utilization and performance.
    • Step 3: Implement an Azure Automation runbook that:
      • Runs at the start of off‑peak hours to scale down.
      • Runs at the end of off‑peak hours (before business starts) to scale back up.
    • Step 4: Ensure application retry logic is in place to handle the brief connectivity loss during scaling.
    • Step 5: Periodically review Azure SQL cost‑optimization recommendations and performance metrics, and adjust the schedule or target tiers as workload evolves.

    Official documentation and articles in the context that cover these building blocks:

    • Dynamic scaling of Azure SQL Database and the brief downtime behavior.
    • Managing Azure SQL Database with Azure Automation and Az PowerShell cmdlets.
    • Post‑migration monitoring, performance, and cost optimization guidance, including Query Performance Insights, automatic tuning, and cost recommendations.
    • Single‑database scaling options and the alternative serverless compute tier.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.
Sign in to answer

Your answer