Blog Post

Azure SQL Blog
8 MIN READ

Optimize cost of SQL Managed Instances with new stop-start capability

UrosMilanovic's avatar
UrosMilanovic
Icon for Microsoft rankMicrosoft
Nov 16, 2022

Global crises and uncertain times always bring the cost as the topic, and people start to think how they can optimize their spendings. Questions like “How can I spend less for my resource” or “How can I improve TCO of my environment” start to be more frequent. Dev/test environments are the main candidates for optimization, and one of the most voted feature requests for SQL Managed Instance is the ability to stop the instance once it is not in use – mainly outside of the working hours and during the weekends.
 
We are excited to announce that with the November 2022 Feature Wave release, all General Purpose SQL Managed Instances with the Feature Wave enabled get access to the start/stop capability as a tool to cost optimize their environments.
 
So far, customers could get discounted compute prices through Reserved Instance pricing. With start-stop capability in place, you can now further reduce the compute costs regardless of Reserved Instance pricing purchased or not.
 

Start-Stop feature overview

 

Stopping an instance is like deallocating a virtual machine. When an instance is in a stopped state, you're no longer billed for compute and licensing costs while still billed for storage and backup storage. Instead of deleting the instance and recreating it again, you can now use start-stop feature and get your instance back online with the same data and configuration after a period of inactivity.
 
 

What happens when instance is stopped

 

Stop operation consists of a foreground and background phase. Once the stop is initiated, the instance enters the stopping state, and in the foreground the operation:
  • Drop external connections
  • Disables connectivity
  • Disables billing
  • Moves instance to the stopped state
Once the instance is in stopped state, the background operation phase continues further to:
  • Take necessary backups
  • Terminate processes
  • Deallocate compute
 
As the stop operation is split into foreground and background phases, from the customer perspective, the operation completes very fast, typically taking around 5 minutes to stop the instance.
 

What happens when instance is started

 

Start operation does not have foreground and background part and appears as one flow. Once the start operation is initiated, the instance enters the starting state and the system:
 
  • Allocates compute through virtual cluster buildout or resize
  • Starts the processes
  • Enables connections
  • Enables billing
 
This means that after starting the instance, you have a “cold start” without previously cached data. Start operation typically takes around 20 minutes to complete.
 

Optimizing cost

 

Stopped instances don't get billed for vCores or the SQL license, they are only charged for data storage and backup storage. Eliminating billing for vCores and licenses while the instance is in a stopped state frees vCores to be used by the Azure Hybrid Benefit (AHB) and reduces the use of Reserved Instances (RI).
 
As in dev/test subscription types there is no charge for SQL License, almost 100% of the cost comes from the vCores and you immediately save 1/3 of the full cost.
 
Let’s take an example of 4vCores instance running on Standard Series hardware in Central US region (full pricing details can be found on Azure SQL Managed Instance pricing page).
 
Full price: $825.28/month
Price on dev/test subscription: $533.38/month
 
Without start/stop capability in place, you would pay for the remaining 2/3 of the SQL Managed Instance cost for 100% of the time even though you are not using it. But now, you can pay according to your usage. If for example your team working hours are from 8AM until 6PM (10 hours), and your team does not work during the weekends, this is in total 50 out of 168 hours in one week. By leveraging the start/stop feature, your vCores charges would further be reduced by ~70%.
 
Full price: $825.28/month
Price on dev/test subscription: $533.38/month
Price on dev/test subscription with start-stop used: ~160$/month
 
If you want to optimize further, you can purchase a Reserved Instance pricing for 1 year or 3 years period. This pricing will further reduce the vCores costs. Let’s see how this pricing impacts the calculation above.
 
Full price: $825.28/month
Price on dev/test subscription: $533.38/month
Price on dev/test subscription with 1 year RI pricing: ~$346/month
Price on dev/test subscription with start-stop used: ~160$/month
Price on dev/test subscription with 1 year RI pricing and start-stop used: ~$104/month
 

Reserved instance pricing

 

Reserved instance pricing (reserved capacity) is applied for the vCores and hours emitted. When an instance eligible for reserved pricing is stopped, reserved pricing is automatically redirected to another instance, if one exists. As such, the start and stop feature can be used to overprovision reserved instance pricing.
 
Note: Reservation discounts are use it or lose it, so if you don't have matching resources for any hour, then you lose the reservation quantity for that hour. You cannot carry forward unused reserved hours.
 
Example: assume that the SQL Managed Instance reserved capacity you buy is for a 16 vCores SQL Managed Instance and the rest of the reservation attributes match the running SQL Managed Instances (region, hardware generation).
 
1. You run two 8 vCores instances from 8am to 2pm.

 

2. You run another two 8vCores instances from 2pm to 8pm

 

 

Feature limitations

 

You cannot stop instances that:
 
  • Have ongoing operation (like ongoing restore, vCores scaling, etc...)
  • Part of failover group setup
  • Part of Managed Instance link setup
While an instance is in stopped state, it is not possible to change any configuration parameters and instance has to be started to change any instance properties.
 
Stopped instances don't get billed for vCores and the SQL license, they are only charged for storage and backup storage. However, vCores and license billing is charged for every hour started, meaning that at 12:01, you will be charged for the entire hour, even if the instance is stopped within the hour.
 
From the backup perspective, while the instance is in a stopped state it is not possible to take backups. Therefore, if you have long-term backups configured and you have yearly backups in place, if the instance is stopped during a defined period to take a yearly backup, the backup will be skipped. It is advised to keep the instance up and running during the yearly backups period.
 
It is not possible to cancel the start or stop operation once it is initiated.
 

Start-Stop action types

 

There are two ways to stop and start an instance - either manually on-demand, or with a schedule.
 

Manual commands

 

Manual commands immediately trigger the stop or start action. Manual commands are good for instances that have larger periods of inactivity without regular patterns, or for testing purposes. In addition, Azure Automation Schedules or any custom solution that creates customized and more flexible schedules that cannot be achieved by using the built-in stop/start scheduler in SQL Managed Instance.
 

Scheduled commands (start-stop scheduler)

 

You can also create a schedule with one or more multiple points of time when a start or stop action is triggered. Scheduling commands are a good fit for instances that have regular patterns, such as starting an instance every working day at 8am, and then stopping it at 5pm, and then starting at 7am and stopping at 11am on a weekend. Scheduling your commands eliminates the need for custom solutions or Azure Automation Schedules to create stop/start schedules. With this approach you can create “working hours” for your SQL Managed Instance.
 
Scheduled items represent points in time when start or stop events are initiated, not when the instance is up and running. So when creating a schedule, take the operation duration into account. For example, if you want to have your instance up and running at 08:00 AM, define a schedule item that initiates the start operation at 07:40 AM.
 

How to stop and start the instance

Update: Aug 2023

Stop-start capability has expanded its tooling coverage and you can now use Azure Portal, Powershell, Azure CLI or API to execute stop and start or configure the schedules. You can use any tool that can execute API calls to script the actions. You can visit official documentation page that provides an overview of Instance stop and start (preview).
 

Azure Portal

 

The following screenshots display the position of start and stop related actions in Azure Portal.
 
Manual commands
 
 
Scheduler
 

PowerShell

Define parameters

$SubscriptionId = "<SubscriptionID>"
$SqlMIName = "<Sql-MI-name>"
$RgName = "<ResourceGroup>"

# Login-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionID
 
Manual stop
Stop-AzSqlInstance -Name $SqlMIName -ResourceGroupName $RgName

 

Manual start

Start-AzSqlInstance -Name $SqlMIName -ResourceGroupName $RgName

 

Create or update schedule

$newSchedule = [System.Collections.ArrayList]::new()

$newScheduleMonday = New-AzSqlInstanceScheduleItem -StartDay Monday -StopDay Monday -StartTime "09:00" -StopTime "17:00"
$newSchedule.add($newScheduleMonday)

$newScheduleTuesday = New-AzSqlInstanceScheduleItem -StartDay Tuesday -StopDay Tuesday -StartTime "09:00" -StopTime "17:00"
$newSchedule.add($newScheduleTuesday)

$newScheduleWednesday = New-AzSqlInstanceScheduleItem -StartDay Wednesday -StopDay Wednesday -StartTime "07:00" -StopTime "19:00"
$newSchedule.add($newScheduleWednesday)

$newScheduleThursday = New-AzSqlInstanceScheduleItem -StartDay Thursday -StopDay Thursday -StartTime "09:00" -StopTime "17:00"
$newSchedule.add($newScheduleThursday)

$newScheduleFriday = New-AzSqlInstanceScheduleItem -StartDay Friday -StopDay Friday -StartTime "11:00" -StopTime "17:00"
$newSchedule.add($newScheduleFriday)

New-AzSqlInstanceStartStopSchedule -InstanceName $SqlMIName -ResourceGroupName $RgName -TimeZone "Central Europe Standard Time" -ScheduleList $newSchedule

 

Get schedule

$currentSchedule = Get-AzSqlInstanceStartStopSchedule -InstanceName $SqlMIName -ResourceGroupName $RgName
$scheduleItemsList = $currentSchedule.ScheduleList
$scheduleItemsList

 

Delete schedule

Remove-AzSqlInstanceStartStopSchedule -InstanceName $SqlMIName -ResourceGroupName $RgName

 

 

Azure CLI

Define parameters

subscription="<subscriptionId>"
instanceName="<managedInstanceName>"
resourceGroupName="<managedInstanceResourceGroup>"

az account set -s $subscription # ...or use 'az login'

 

Manual stop

az sql mi stop --mi $instanceName -g $resourceGroupName

 

Manual start

az sql mi start --mi $instanceName -g $resourceGroupName

 

Create or update schedule

scheduleItems="[{'startDay':'Monday','startTime':'10:00','stopDay':'Monday','stopTime':'18:00'},{'startDay':'Tuesday','startTime':'10:00','stopDay':'Tuesday','stopTime':'18:00'},{'startDay':'Wednesday','startTime':'12:00','stopDay':'Wednesday','stopTime':'22:00'},{'startDay':'Thursday','startTime':'14:00','stopDay':'Thursday','stopTime':'20:00'},{'startDay':'Friday','startTime':'14:00','stopDay':'Friday','stopTime':'20:00'}]"

timezone="Central Europe Standard Time"

az sql mi start-stop-schedule create --mi "$instanceName" -g "$resourceGroupName" --timezone-id "$timezone" --schedule-list "$scheduleItems"

 

Get schedule

az sql mi start-stop-schedule show --mi "$instanceName" -g "$resourceGroupName"

 

Delete schedule

az sql mi start-stop-schedule delete --mi "$instanceName" -g "$resourceGroupName"

 

Documentation

Powershell

Stop-AzSqlInstance

Start-AzSqlInstance

New-AzSqlInstanceStartStopSchedule

New-AzSqlInstanceScheduleItem

Get-AzSqlInstanceStartStopSchedule

Remove-AzSqlInstanceStartStopSchedule

 

Azure CLI

az sql mi stop

az sql mi start

aq sql mi start-stop-schedule create

aq sql mi start-stop-schedule show

aq sql mi start-stop-schedule update

aq sql mi start-stop-schedule delete

 

API

Managed Instances - Start

Managed Instances - Stop

Start Stop Managed Instance Schedules - Create Or Update

Start Stop Managed Instance Schedules - Get

Start Stop Managed Instance Schedules - Delete

 

Summary

 

In this article we introduced a new feature that enables SQL Managed Instances with General Purpose service tier to be stopped when not in use and started when there is need. This feature comes with two action types. Manual mode that comes handy for ad-hoc actions, irregular patterns, and testing purposes, and a built-in scheduler that helps to create “working hours” for SQL Managed Instance. Next to Azure Hybrid Benefit (AHB) and Reserved Instance pricing (RI), this is an additional tool that helps in optimizing environments usage and saving costs.
 
Stay tuned with #sqlmiops!
 
Call to action: If you have ideas that you would like to share, feel free to leave comments to this blogpost or contact us using https://aka.ms/contactSQLMI link.
Updated Sep 01, 2023
Version 4.0
  • AlexL's avatar
    AlexL
    Copper Contributor


    Update #2:

    As of 2023-01-20, everything appears to be working on the Basics page when trying to create an instance of SQL MI:

    • Able to see the feature wave notification bubble at the top of the page
    • The filter toggle is available
    • Switching the toggle gets the regions filtered correctly and the list contains the compatible regions only

    I haven't gone through the entire process, but will do so and post here if I encounter anything in the subsequent steps.

  • AlexL's avatar
    AlexL
    Copper Contributor

    Update:

    • Had to upgrade to the Developer support plan in order to submit a technical support request
    • Went back to the page to capture the issue
    • The "Your subscription is ready for November 2022 feature wave" notification at the top was gone 
    • The filter toggle was gone too... :facepalm:
    • Submitted a support request describing the entire process and observations, attached a screenshot of the page with the Feature Wave bits vanished

    Down the rabbit hole...

  • AlexL's avatar
    AlexL
    Copper Contributor

    Yes, we have two Pay-as-You-Go Dev/Test subscriptions, and both exhibit this issue.
    What worries me though is that both of them also had zero quotas by default for Subnet and VCore for all regions, which is odd as it should be 6 and 320 per region according to documentation. Now, the quotas have been granted for one subscription and region through a support ticket, but seeing as that didn't help to gain access to the Nov2022 feature wave, I wonder if these subscriptions had something else missing right from the start.

  • Hi AlexL,

     

    do you have multiple subscriptions eligible for Feature Wave? Do you experience same issue for all of them? This is not expected and the best way for investigation is opening support case and sending relevant details to our engineering team. In case you already opened the case, please include me in the conversation.


    Thanks,

    Uros

  • AlexL's avatar
    AlexL
    Copper Contributor

    NikoNeugebauer Doing exactly that with a Dev/Test subscription, and all Regions are still showing under the "Other" category.
    And once the filter is toggled to show only those compatible with the feature wave, no regions at all appear on the list. The subscription, however, remains visible in the subscription list under "Ready for November 2022 feature wave".
    Is there a specific resource provider that might be missing and would need to be manually registered for the subscription to get this new feature working, or should Microsoft.Sql provider be removed and re-added to pick up latest? - Any advice or pointer would be greatly appreciated as we're completely stuck.

    EDIT: This is what I noticed in the API calls made by the portal while trying to select a region (with the filter toggle being off)

    Any idea why the default Subnet and VCore quotas for a Dev/Test subscription would be zero? - Aren't they supposed to be 6 and 320, respectively?

     

    EDIT2: Quotas increased using a support request, and I'm now able to select the intended region when the toggle is off, but still no regions will be listed as compatible with the feature wave.

  • DavidF_D's avatar
    DavidF_D
    Copper Contributor

    Hi Uros - as of 17 Nov 22 It looks as though I'm not able to choose an Azure region that is available for use for the Nov 2022 feature wave, which seems to be against the guidance that only a few regions are not available as per the Announcement Blog entry and guidance that's appearing in the Azure Portal that it should be possible to create a new SQL MI in a supported region:

    https://techcommunity.microsoft.com/t5/azure-sql-blog/november-2022-feature-wave-for-azure-sql-managed-instance/ba-p/3677741

    From the above article:  "

    Rollout of the November 2022 Feature Wave is happening over the course of several months. The initial rollout for the feature wave will cover:

    • Instances that belong to Azure Dev/Test subscriptions (more details in the Q&A section below)
    • Most public Azure Regions, except: Australia Central 2, Germany West Central and West India"

    I've tried to create a new MI in approximately 8 regions from US, Australia, Canada and Asia Pac.  All of which have indicate on the Networking tab that they aren't available for the 2022 Wave:

    "The selected Azure region is not supported for November 2022 feature wave yet. If you'd like to try the new features, select one of the supported regions on the "Basics" tab."  

    Can you provide an Azure region name that has access to the "November 2022 feature wave" set please?  The error message above indicates that there -are- supported regions, however it's not clear which ones apply.  Many thanks.