Optimize your Azure SQL Managed Instance cost with Microsoft Azure Well-Architected Framework

[Edit: March 2023] We have a new blog post talking about pricing optimization options: How to do more with less in Azure SQL Managed Instance.

Let’s start with some basics…

Cost optimization is one of the most common requirements for cloud workloads. How to implement a cost optimization framework will depend on the type of service, scenario, purchase model, and a few other aspects of Your environment.  It’s worth noting that, when it comes to frameworks, Microsoft has been developing its own framework for Azure. The Azure Well-Architected Framework (WAF) is a set of guiding tenets that can be used to improve the quality of a workload. The framework consists of five pillars of architecture excellence:

• Cost Optimization
• Operational Excellence
• Performance Efficiency
• Reliability
• Security

Following the best practices and the specific business priorities that are relevant to you and your cloud journey, you can effectively and consistently optimize your workload costs against Azure. You can find more information about Microsoft Azure Well-Architected Framework by going to:

• Introducing the Microsoft Azure Well-Architected Framework | Azure Blog and Updates | Microsoft Azure
• Microsoft Azure Well-Architected Framework - Microsoft Azure Well-Architected Framework introduction | Microsoft Docs

 

Now, after You have grown a little more with the WAF, it’s time to start the journey. Diving deeper down the cost optimization pillar we can identify several areas:

• Right resources, the right size
• Aim for scalable costs |Pay for consumption
• Keep within the cost constraints
• Monitor and optimize

 

These four areas are your anchor for the further steps. The thing to keep in mind is that, as any other framework, the WAF  only provides broad recommendations. To implement it, You will need concrete details pertinent to a particular solution, workload, and even the service level. With this in mind, let’s go one level deeper and focus on the cost optimization aspect for Azure SQL Managed Instance.

 

The next level starts here…

Right resources, right size…and the right location

The first and one of the most important steps in cost optimization is to choose the right resources that are aligned with Your business goals and can deliver the required performance of the planned workload. An inappropriate or misconfigured service can adversely impact the cost.

With SQL Managed Instance You can choose between General Purpose (GP) and Business Critical (BC) tiers. Obviously, there is a price difference between these two tiers because each tier was designed differently.

 

The General Purpose service tier is based on the separation of computing and storage, while the Business Critical service tier model is based on a cluster of database engine processes. The choice between these architectural models affects the availability, reliability, performance, and cost. In the GP tier, Azure Blob Storage transparently replicates database files and guarantees no data loss. Business Critical, on the other hand, relies on the fact that there is always a quorum of available database engine nodes which ensure minimal performance impact on your workload even during maintenance activities. Of course, there are other differences between the two tiers which affect their bottom line pricing, e.g. storage latency between 5 and 10 m in GP and 1-2 ms SSD storage in BC.

The SKU Recommendations feature which is available in Data Migration Assistant tool allows you to identify the minimum recommended Azure SQL Managed Instance SKU based on performance counters collected from the computer(s) hosting your databases. Make sure to check how easily  identify the right Azure SQL Database SKU for your on-premises database (Data Migration Assistant)

If You need more details about the SQL MI tiers please visit:

• General Purpose service tier - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs
• Business Critical service tier - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs
• High availability - Azure SQL Database and SQL Managed Instance | Microsoft Docs

Below you can find a sample comparison between the same SQL MI configuration running in GP and BC tiers.

 

 Figure 1 General Purpose Sample Pricing vs Business Critical Sample Pricing

 

Once you identify the appropriate service tier, there are few more things to consider which can allow reducing cost:

 

• Check service prices in different Azure datacenter locations. Azure has regions located all over the world. Prices between regions for the same service can be different. Below You can find a sample comparison of SQL MI cost deployed in different regions in Europe with the following configuration : General Purpose, 4 vCores, Pay-as-you-go:

 Figure 2 Sample SQL MI cost per Azure region in Europe comparison

 

 

• Deploy the database/instances in the same region as your application to prevent data transfer costs. Deploying SQL MI in one region and other services, like App Service, in another region, can generate additional cross-region data transfer costs. Although in most cases this cost will not be significant, it’s worth following the pattern of keeping the related services in the same region.

Figure 3 Sample bandwidth cost for 1 TB

 

• Building a multi-region service when the service levels don't require high-availability or geo-redundancy will increase the cost without a reasonable business justification. There are many options to increase the availability of your solution. One of them is multi-region deployment. In the case of SQL MI You can achieve this by using Auto-failover groups - SQL Managed Instance.  Auto-failover groups are superior to having a secondary instance deployed in another region as a fully paid instance, which doubles the cost of the solution.

 Figure 4 Failover groups - secondary replica is fully paid

 

As an alternative approach, if you still need geo-region deployment with reduced costs you can consider using Auto-failover groups - SQL Managed Instance with secondary instance configuration lower than primary one – in this case make sure performance of secondary instance is enough to follow the primary instance synchronization needs and scale it up if needed.

 

• If your Business Continuity strategy requires geo-region deployment but there are no strict requirements for auto-failover and some RTO,RPO values can be relaxed consider using backup with geo-replicated storage as well as the geo-restore option, you can also automate the process with a simple script to Restore geo-backup for Azure SQL Managed Instance - Azure SQL Managed Instance. Geo-restore is the most basic disaster-recovery solution available in SQL Database and SQL Managed Instance. It relies on automatically created geo-replicated backups with a recovery point objective (RPO) up to 1 hour and an estimated recovery time of up to 12 hours. Depends on a need this can be a good option to keep Business Continuity policies in place at lower cost.

 

• Overprovisioning storage can generate additional cost. Additional storage can easily be added to running instances even after SQL MI has been deployed. Also, make sure you understand the storage performance limitations as in some scenarios the deployed storage size can be determined based on the performance needs. Make sure to refer to Resource limits - Azure SQL Managed Instance and also Storage performance best practices and considerations for Azure SQL DB Managed Instance.

Note! Remember to choose the service tier based on a thorough analysis rather than a superficial comparison of service prices. Always make a clear analysis of your HA/DR (RTO, RPO), performance, and feature requirements to set up your service accordingly. Please visit the following pages to get more information about SQL MI tiers:

• General purpose and business critical service tiers - Azure SQL Database & SQL Managed Instance 
• High availability - Azure SQL Database and SQL Managed Instance
• General Purpose service tier - Azure SQL Database & Azure SQL Managed Instance
• Business Critical service tier - Azure SQL Database & Azure SQL Managed Instance

 

Aim for scalable costs | Pay for consumption

 

The workload cost should scale with the demand. A key benefit of the cloud is the ability to scale dynamically. You can save costs through automatic/on-demand scaling. Although SQL Managed Instance doesn’t have a built-in autoscaling option, like other services it follows a common cloud pattern in which You can access APIs to turn on/off, scale-up/down, or even drop and re-deploy workloads. Such an approach allows You to manage the overall costs depending on the changing business needs. Although scaling up/down or drop/re-deployment of the service aren’t instantaneous, they can be some of the easiest ways to control SQL Managed Instance cost over longer periods of time. Make sure You check the following links for details and examples of automation  (including progress tracking):

• Scale resources – Azure SQL Database | Microsoft Docs
• Azure SQL Managed Instance with network security group and route tables
• Track progress of SQL Managed Instance create/scale request by UrosMilanovic 
• Automatic storage scaling - ready to use script coming soon. Now you can simply change it even through Azure Portal:

Figure 6 Sample SQL MI Cost Pay-as-you-go vs Reservations

 

Make sure You check more details how to Save compute costs with reserved capacity - Azure SQL Database & SQL Managed Instance

 

Keep within the cost constraints

 

Every design choice has cost implications. Thus, no matter if You are just planning the deployment or the deployment was already done, there are some points to verify and focus on in order to fit within the budget constraints. In the case of SQL Managed Instance, licensing can be one of the most important areas to consider:

 

Cost structure has a huge impact on the ongoing costs. Although we are talking about cloud services we have to follow specific licensing rules. In the case of SQL MI there are few rules of thumb that help you manage cost-effectively:

• Compute is provisioned in virtual cores (vCores). A vCore represents a logical CPU and its main cost factor when choosing Your Managed Instance configuration as you pay for computing (number of cores) and license needed to cover deployed cores – BY DEFAULT LICENSE COST IS TRANSPARENT FOR YOU AS A CUSTOMER AND IS INCLUDED IN THE SERVICE PRICE
• In some cases, You can already have SQL Server licenses in Your organization which can be used to run SQL MI and reduce its cost. SQL Managed Instance is eligible for Azure Hybrid Use Benefit (AHUB), which is a licensing benefit that works by letting you use your on-premises SQL Server licenses with Software Assurance on Azure based on the following pattern:

 

It’s important to remember if you decide to use AHUB  you need to cover whole SQL MI configuration (it isn’t possible to cover only part of vCores used in SQL MI with AHUB and using rest in license-included model). The same rule applies if you plan to scale up your instance – you must have eligible number of SQL license with SA to cover whole instance after scaling up. To find more information about Azure Hybrid Use Benefit, visit Azure Hybrid Benefit – Azure SQL Database & SQL Managed Instance

 

Azure Hybrid Use Benefit for SQL MI can be enabled during or after instance deployment and it’s possible to do it using Azure Portal, PowerShell, CLI, or REST API. Below You can find the sample snapshot from Azure Portal for running SQL MI which shows how to activate AHUB and the potential savings – in this case, 39.6% (value can vary between Azure offering, Azure regions).

 

Figure 7 SQL MI Cost comparison in Pay-as-you-go vs Azure Hybrid Use Benefit

 

Note! One of best practices to reduce cost is instances consolidation with right sizing  to ensure the least amount of vCores is required.

 

• An additional cost-saving option is directly related to the scenario in which SQL MI is used. If your workload is not a production one. Consider using  Dev/Test licensing model which is available for SQL Database Managed Instance as part of the dev/test offer. It is a highly cost-effective way to run your development and testing workloads and can easily help save up to 55% off the list price. To make it clear Dev/Test licensing model means you pay only for compute, license cost is reduced to zero. There are three offers that allow reducing SQL MI cost used for development and testing:
  
  
• Individual one: Monthly Azure credits for Visual Studio subscribers

Azure credits are included in your Visual Studio subscription and depend on the Visual Subscription level. When you run out of the credit that’s allotted for the month, you won’t be able to continue using it until it resets the next month.

 Figure 8 Azure Credits in Visual Subscriptions

 

• Enterprise Dev/Test (FOR TEAMS - ENTERPRISE AGREEMENT CUSTOMERS) : Enterprise Dev/Test

This is offer doesn’t require any separate payment, it’s just using the funds already in your Enterprise Agreement. It requires creating a subscription that is marked as Dev/Test or changing the existing one. More details can be found here: Enabling and Creating EA Dev/Test Subscriptions through the EA Portal | Enterprise Azure Portal | Channel 9 (msdn.com).Please remember that only active Visual Studio subscribers with standard subscriptions can use the Azure resources running within an Enterprise Dev/Test subscription. End users can also access the application to provide feedback or perform acceptance tests.

 

• ALL OTHER CUSTOMERS (FOR TEAMS): Pay-As-You-Go Dev/Test

This offer works similar to Enterprise Dev/Test. The difference is that it doesn’t require You to have an Enterprise Agreement in place. This scenario also requires users to have an active Visual Studio subscription to be able to use the Azure resources running within a Dev/Test subscription. 

 

Just to show how the dev/test pricing looks like, below You will find a comparison between the same SQL MI configuration running in standard and dev/test model.

 

 Figure 9 Commercial to Dev/Test cost comparision

 

• Last but not least is taking the advantage of the appropriate subscription offer types. Azure usage rates and billing periods can differ between Enterprise, Web Direct, and Cloud Solution Provider (CSP). It’s worth evaluating this option to make sure your pricing is relevant to your business needs.

 

Monitor and optimize

Resource monitoring can be a great opportunity to optimize costs. Treat it as a process, rather than a point-in-time activity. Conduct regular reviews and forecast the capacity needs so that you can provision resources dynamically and scale with demand – for more information please back to section Right resources, right size…and right location and Aim for scalable costs | Pay for consumption

 

There are few steps which can help you optimize the overall cost:

• collect and visualize key performance metrics to determine the right resource level usage (vCore, memory, IOPS, etc.). Verify if your database requires a specific number of cores, memory, IOPS and use it to determine the right service tier (General Purpose or Business Critical). Follow the best practice in monitoring SQL MI by using solutions like SQL Insights. Make sure to explore monitoring options available for Azure SQL Managed Instance
  
  
• use built-in features like Query Store or index usage statistics to optimize SQL workload which can allow you to optimize the sizing of your SQL MI and help to reduce the ongoing costs: Performance tuning guidance for applications and databases - Azure SQL Database & Azure SQL Managed Instance | Microsoft Docs
  
  
• consider using compression (row, page), column store indexes where possible to keep storage footprint small and reduce storage size needs.
  
  
• define and enforce data retention, archival requirements, or data offloading (e.g. to a storage account) strategy, as not all data typically needs to be available for online processing)
  
  
• reduce the billing charges for excess usage of the backup storage space beyond the free backup storage space provided. You can control the backup consumption using these general approaches:
  • Choosing the backup storage type that is right for you
  • Optimize database backup retention period
  • Maximize your free backup storage space
  • Optimize your apps and workloads
  • Alternative considerations

Dive into details about backup cost optimization by danimir 

Fine tuning backup storage costs on SQL Managed Instance 

Backup storage consumption on Managed Instance explained

 

• proactively use alerting to oversee if a subscription / Resource Group or particular service is using more than planned. Make sure to get more details about Monitoring options available for Azure SQL Managed Instance  and Prevent exceeding Azure budget with forecasted cost alerts | Azure Blog and Updates | Microsoft Azure

 

 

What’s next?

Once you go through the most common cost optimization hints described in this post, remember to treat them as a process. Implement, revisit, and follow them regularly. Also stay tuned as more articles about Azure Well-Architected Framework (WAF) for SQL Managed Instance workload is coming.

 

Disclaimer

Please note that options presented in this article are subject to change. This article reflects the state of cost optimization options available for Azure SQL Managed Instance in March, 2021 but is not limited to them and can changed over time

 

Closing remarks

If you find this article useful, please like it on this page and share through social media.

To share this article, you can use the Share button below, or this short link: https://aka.ms/sqlmi-waf-cost-optimization