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

[UrosMilanovic]

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!

Index article: Azure SQL Managed Instance – introduction to #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.