Microsoft Azure – Azure SQL Managed Instance Deployment Options
In this article, we will take a deeper look at the differences between Azure SQL Managed Instance and Azure SQL Database. When you’re going to deploy a Managed Instance, you’re first going to have some choices to make. You can deploy a single instance, which is like a single instance of SQL Server, get these capabilities of your surface area, of your virtual networks, all the management capabilities, and then you can deploy databases with that instance.
This type of deployment can take time as Azure is deploying an entire Ring for your instance. We have this option now in preview called an instance pool, where you can pre-provision the instance, which makes an instance pool deployment much faster. It’s a more cost-efficient migration scenario because, for example, you’re going to be the host of smaller numbers of cores in these instance pool situations.
Service Tiers -Managed Instance:
Currently, in public preview, you start with an instance, you pick an instance pool, and then you’re going to dive into a service tier choices for deploying Managed Instance.
When we look at the service tier for Managed Instance, it’s a pretty easy decision. There are two options available:
Now, most of the users run on general-purpose, but some others have really extreme requirements that they need around recovery or latency and they want to take advantage of a Readable secondary. Those are the main decision points between general-purpose and business-critical.
Now, when it comes to purchasing options, there’s only one model, the vCore model, so you don’t have to make a choice there. This is going to let you scale your compute independently from your storage. Now, when we talk about general-purpose, it’s critical to talk about Remote storage. It means that your data and log files are going to be stored in remote storage where your TempDB is going to be running on a local SSD. To compare that with business-critical, everything is going to be running on local SSD.
Memory & Performance Decisions:
When we talk about IOPS, and this might be a new term to you, this is IO per second. You’re going to get better IOPS from business-critical. Additionally, in-memory OLTP is only available in the business-critical tier, if something you’re application or workload requires, you’re definitely going to want to consider the business-critical tier.
Finally, what happens in the backend is Azure basically deploys an availability group for you. Azure creates four replicas for you, and one of those Azure actually allows you to leverage as a readable secondary.
If you want to separate your read and write workloads, this can be done with business-critical. For Azure SQL Managed Instance, you really have two options: general purpose, which is going to be best for most of the users, and business-critical for if you need low latency, the readable secondary, or really fast recovery.
Difference between Azure SQL Managed Instance and Azure SQL Database:
Azure SQL Managed Instance Azure SQL Database Single Instance Instance Pool Single Database Elastic Pool SQL Server surface area(majorly) Pre-provision compute resources for migration Hyperscale storage(100TB+) Resource sharing between multiple databases for price optimization Native virtual network support Enable cost-efficient migration Serverless compute Simplified performance management for multiple databases Fully managed Service Ability to host smaller instances (2Vcore) Fully managed service Fully managed service Currently in public preview