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

[UrosMilanovic]

[Edit: Video on SQL MI Stop-Start.]

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

Currently supported tools for executing or scheduling start and stop of managed instance are Azure Portal and API. 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

At the moment there are no start-stop commands in PowerShell and Azure CLI library. Until we have this commands published, you can use any tool to execute API calls. PowerShell is one of the tools and here are some ready to use scripts.

First, we need to set parameters of instance and define API variables. These parameters and variables must be defined no matter if you are using a manual stop and start commands, or you're creating a schedule.

# ===============================================================
# SQL Managed Instance - Start/Stop feature examples
#
# Execute in Azure Cloud Shell PowerShell
# (C) 2022 Managed Instance product group
# ===============================================================

# ===============================================================
# USER CONFIGURABLE VALUES
# ===============================================================

$SubscriptionId = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$SqlMIName = "<SQL MI NAME HERE>"
$RgName = "<RG NAME HERE>"

# ===============================================================
# DO NOT MODIFY THE SCRIPT BELOW - NOT USER CONFIGURABLE
#  ===============================================================

# Constants
$UriPrefix = "https://management.azure.com/subscriptions/" + $SubscriptionId + "/resourceGroups/" + $RgName + "/providers/Microsoft.Sql/managedInstances/"
$UriSuffix = "?api-version=2021-08-01-preview"
$instanceGetUri = $UriPrefix + $SqlMIName + $UriSuffix

# Main
Write-Host "Login to Azure subscription $SubscriptionID ..."
Write-Host "Logging to Azure subscription"
# Login-AzAccount
Select-AzSubscription -SubscriptionName $SubscriptionID

Write-Host "Getting the profile information ..."
$azContext = Get-AzContext
$azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
$profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile)

# Get authentication token
Write-Host "Getting authentication token for REST API call ..."
$token = $profileClient.AcquireAccessToken($azContext.Subscription.TenantId)
$authHeader = @{'Content-Type'='application/json';'Authorization'='Bearer ' + $token.AccessToken}

Manual stop

######## STOP SECTION ########
##############################

# Define Stop SQL Managed Instance URI
Write-Host "Generating URI for stopping SQL Managed instance $SqlMIName in resource group $RgName"
$instanceStopUri = $UriPrefix + $SqlMIName + "/stop " + $UriSuffix

# Invoke API call to start the operation
$stopInstanceResp = Invoke-WebRequest -Method Post -Headers $authHeader -Uri $instanceStopUri
Write-Host "Instance Stop operation triggered:`n" $stopInstanceResp

#Get the operation ID
$stopInstanceOperationId = ($stopInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."x-ms-request-id"
Write-Host "Stop operation ID:`n" $stopInstanceOperationId

# Get the header from the API response, if status returned is Accepted, all is good
$stopInstanceStatusUri = ($stopInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."Azure-AsyncOperation"
Write-Host "Instance stop operation unique Get-status URI:`n" $stopInstanceStatusUri

# Poll the status of the operation (statuses: InProgress, Succeeded, Failed), continue when Succeeded
$stopInstanceStatusResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri (out-string -inputobject $stopInstanceStatusUri)
Write-Host "Status of the instance stop operation:`n" $stopInstanceStatusResp

# Get the operation result URI
$stopInstanceOperationStatusUri = ($stopInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."Location"
Write-Host "Instance stop operation result unique URI:`n" $stopInstanceOperationStatus

# Check the stop operation result
$stopInstanceOperationStatusResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri (out-string -inputobject $stopInstanceOperationStatusUri)
Write-Host "Status of the instance stop operation:`n" $stopInstanceOperationStatusResp

# Get the SQL Managed Instance and check properties
$getInstanceResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri $instanceGetUri
Write-Host "Instance Get API Response:`n" $getInstanceResp | ConvertFrom-Json

Manual start

######## START SECTION ########
###############################

# Define Start SQL Managed Instance URI
Write-Host "Generating URI for starting SQL Managed instance $SqlMIName in resource group $RgName"
$instanceStartUri = $UriPrefix + $SqlMIName + "/start " + $UriSuffix

# Invoke API call to start the operation
$startInstanceResp = Invoke-WebRequest -Method Post -Headers $authHeader -Uri $instanceStartUri
Write-Host "Instance Start API Response:`n" $startInstanceResp

#Get the operation ID
$startInstanceOperationId = ($startInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."x-ms-request-id"
Write-Host "Stop operation ID:`n" $startInstanceOperationId

# Get the header from the API response, if status returned is Accepted, all is good
$startInstanceStatusUri = ($startInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."Azure-AsyncOperation"
Write-Host "Instance start operation unique Get-status URI:`n" $startInstanceStatusUri

# Poll the status of the operation (statuses: InProgress, Succeeded, Failed), continue when Succeeded
$startInstanceStatusResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri (out-string -inputobject $startInstanceStatusUri)
Write-Host "Status of the instance start operation:`n" $startInstanceStatusResp

# Get the operation result URI
$startInstanceOperationStatusUri = ($startInstanceResp.Headers | ConvertTo-Json | ConvertFrom-Json)."Location"
Write-Host "Instance start operation result unique URI:`n" $startInstanceOperationStatusUri

# Check the start operation result
$startInstanceOperationStatusResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri (out-string -inputobject $startInstanceOperationStatusUri)
Write-Host "Status of the instance start operation:`n" $startInstanceOperationStatusResp

# Get the SQL Managed Instance and check properties
$getInstanceResp = Invoke-WebRequest -Method Get -Headers $authHeader -Uri $instanceGetUri
Write-Host "Instance Get API Response:`n" $getInstanceResp | ConvertFrom-Json

Create or update schedule

######## CREATE OR UPDATE SCHEDULE ########

# Define URI for creating or updating start/stop schedule
Write-Host "Creating start/stop schedule for SQL Managed instance $SqlMIName in resource group $RgName"
$instanceCreateScheduleUri = $UriPrefix + $SqlMIName + "/startStopSchedules/default " + $UriSuffix

# Define schedule to be applied
$requestBody = [pscustomobject]@{
  properties = [pscustomobject]@{
    timeZoneId = "Central European Standard Time"
    description = "This is a schedule for our Dev/Test environment."
    scheduleList = @(
      @{startDay='Monday';startTime='06:00 AM';stopDay='Monday';stopTime='01:00 PM'}
      @{startDay='Monday';startTime='03:00 PM';stopDay='Monday';stopTime='07:00 PM'}
      @{startDay='Tuesday';startTime='09:00 AM';stopDay='Monday';stopTime='05:00 PM'}
      @{startDay='Wednesday';startTime='09:00 AM';stopDay='Monday';stopTime='05:00 PM'}
      @{startDay='Thursday';startTime='09:00 AM';stopDay='Monday';stopTime='05:00 PM'}
      @{startDay='Friday';startTime='03:00 PM';stopDay='Friday';stopTime='04:00 PM'}
    ) 
  }  
}
$instanceScheduleBody = ConvertTo-Json -InputObject $requestBody -Depth 3

# Invoke API call to start the operation
Invoke-WebRequest -Method Put -Headers $authHeader -Uri $instanceCreateScheduleUri -Body $instanceScheduleBody

Get schedule

######## GET SCHEDULE ########

# Define URI for getting start/stop schedule
Write-Host "Getting start/stop schedule for SQL Managed instance $SqlMIName in resource group $RgName"
$instanceScheduleGetUri = $UriPrefix + $SqlMIName + "/startStopSchedules/default " + $UriSuffix

# Invoke API call to start the operation
$scheduleAPIResponse = Invoke-WebRequest -Method Get -Headers $authHeader -Uri $instanceScheduleGetUri
$scheduleAPIResponseContent = $scheduleAPIResponse.Content | ConvertFrom-Json
Write-Host "Defined schedule items list:"
$scheduleAPIResponseContent.properties.scheduleList

Delete schedule

######## DELETE SCHEDULE ########

# Define URI for deleting start/stop schedule
Write-Host "Deleting start/stop schedule for SQL Managed instance $SqlMIName in resource group $RgName"
$instanceScheduleDeleteUri = $UriPrefix + $SqlMIName + "/startStopSchedules/default " + $UriSuffix

# Invoke API call to start the operation
Invoke-WebRequest -Method Delete -Headers $authHeader -Uri $instanceScheduleDeleteUri

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.