SQL MI pools: optimize costs with small 2-vcore instances | Data Exposed

Key Takeaways

SQL manage instance pools are generally available. Learn all about the benefits of how you can save money and increase your isolation this week on data [Music] exposed. Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed. Today I'm joined by Georgie. Uh thanks so much for joining us today. Can you kick us off by telling us a little bit about what you do? Hi Anna, great to be here. My name is Georgie and I'm a product manager in Azure SQL managed instance team. We're big Azure SQL family and today I'd like to talk a little bit about SQL managed instance pools. Awesome. Cool. This is something I'm really excited about. These have been around for a while. I think recently there's been some announcements that we're going to talk about, but let's get right into it. Jordan, tell me a little bit like what are SQL and MI instance pools and why should people care about them? Yes, thank you. Well, SQL managed instance is Azure SQL service that is ideal for all customers that want to move their SQL server workloads to the cloud. Now, SQL managed instance pool present a deployment option that enables provisioning a very small 2V core instances which is great for cost-ffective reasons obviously also for faster instance provisioning and for centrally managed provisioning that I'm going to talk a little bit about uh today. Uh instance pools support not only two vcore instances they uh support various sizes up to 80 vores of CLM. So you can combine those instances in the pool. Now what's really a difference from a single instances beside the fact that it's a pool? Uh already with the provisioning the customer see that with traditional SQL managed instance or singleton SQL uh managed instance the provisioning experience is really one step. So immediately the customer gets provisioned compute infrastructure as well as SQL MIA. With instance pools it's a two-step. First the customers provision compute infrastructure and then they uh uh provision uh managed instances as a separate step. Now uh MI uh pools come with lots of flexibility. For example, you can create an instance pool. You can create an a new SQL my into the pool. You can move uh a singleton external SQL my into that pool. You can then decide for example to resize the pool instance or to resize the instance pool altogether. You can then of course populate further instances into that pool or then decide to move out one of those instances out of the pool. You can delete pool instances and also decide to resize or downsize the pool if you don't need such a capacity. Nice. So you have a lot of flexibility. Yeah, exactly. Basically uh you you you don't end up in a dead end. Now the question is when to use instance pools. Of course with small CMIS the cost effective 2V core option really provides for a lot of consolidation uh opportunities. For example, already with SQL MI, some of those four VCore CQMIS uh can probably run on a smaller 2V core instances, especially if those work loads are not uh mission critical. Also, and perhaps even more importantly, 2V core SQL MIS present a great target to migrate and modernize SQL VMs to the past service such as SQL MI. Actually in many purchasing scenarios due to licensing conditions SQL MIS can be even lower cost than SQL VMs. And of course when migrating uh SQL server from on premises um especially when you have for example data exit this uh granularity to var granularity that SQL min provides uh gives much more um uh options when you're trying to map your small SQL server workloads to small SQL MIS. Awesome. Makes sense. One question for you on cost optimization. Is the cost different or is the big difference that you can just kind of deploy less and get more? Is it just a matter of utilizing those resources or are there actual cost differences? It's it's the second. So the the business model for CQLI is uh uh per VCore. That doesn't change. The vcore uh uh price stays the same. It's just the fact that now you can you don't need to provision four vores. If you don't need them, you uh provision for example two vores. So it's basically 50% of the price. Nice. Thank you. So the second use case uh is about centralized provisioning. So especially with big uh uh corporations and big customers centralized platforms teams are normally the ones who manage the procurement also SQL server procurement uh that set up the environment networking security uh roles etc and and generally make sure that the compliance is is ensured. In such a cases, instant pools are great because you can uh uh do all that manage procurement, set up the environment, networking, security, etc. And then when apps, app teams uh uh require uh ad hoc SQL servers, uh they can they can get that and in in a fast and compliant manner. And the third use case is about SAS fees. So many uh software vendors uh they provision or or they they have such a uh software architecture that as a database they use SQL server and for many of them they don't really need a big SQL servers. So these two vcore instances are also good and why is a instance of pyro diagram here important because in many of those solutions um the customers are using actually cross database queries and things like SQL agent and that's why manage instance you to its high uh uh SQL surface area compatibility to SQL server uh uh the great choice. Now I will um describe an example that you can do two things at once. Um and uh that's a situation where you have for example three 4B core SQL mice like today. So you can at the same time both optimize cost and increase isolation. So first we can take those two 4B core instances and move them into the pool and resize. So basically saving 50% of the cost and the third one uh we can assume that it has 10 databases. We can split those 10 databases. So instead of them being in the same instance on four v cores, we can move them into the pool onto the small two two v core instances and increase effectively isolation while uh uh on the total decreasing the costs. Now how do you do that in Azure? In PowerShell moving an instance into a pool. It's a single line command where you specify uh in instance pool and uh the uh instance size and then you can see in the Azure portal that those two instances are in the pool. We can then continue in the portal to create a pool a third managed instance in that pool. The experience is the same like we have today for singleton managed instances. Of course uh you enter a instance name and then very important you switch the button that says belongs to an instance pool. You can choose existing pool and further when you configure it instance you will see that option to vcore compute option that is exclusive property of pulled instances. For the rest is pretty much the same. Uh the only thing we need uh uh to do now to complete this example is to create uh u to select an admin. After we did that, we can just leave all defaults and create that instance. The pool managed instance is faster. The provisioning time is faster than for a singleton to managed instance. So in up to 10 minutes, the instance will be provisioned. If we go now to the resource and select on the instance pool property, we'll see that our instance pool now has the third instance provision. We'll now just repeat uh the the same process for the fourth instance and we'll end up with a pool that has four 2V core instances. Now for the final uh phase of our example, we want to move those 10 databases and redistribute across two instances. We'll move database. We'll use database move feature. Select five databases from the sort database and select the target instance that we just made in the pool. We'll kick off the move. It's a two-step process. In the first uh step, uh there is a data seeding. Once this is completing, we'll select again those 10 uh 10 uh databases that are now ready for move completion. And once we complete, we'll see that those uh five databases will become gray. That means they're no longer in the source instance. If we switch now to the managed instance that in a pool that is a target database a target instance for those databases we'll see those five databases and we'll repeat the same process for the remaining five databases from the source instance move them in a target instance and we completed our example. So I'll just uh recap uh what we quickly saw through the portal. Before we had three instances with 12 v cores uh in total and afterwards we had four instances with 8 v core in total. So we achieved both cost optimization as well as increased isolation in this case. Nice. That's awesome. This is a a great scenario and it's great to see how easy it is and how uh how seamless you guys have kind of made this process of of optimizing and increasing network isolation. So there's a couple different use cases you might have here. Um one question for you is uh like some people may be familiar with elastic pools and Azure SQL database uh similar kind of concepts I think where you can you know kind of provision a pool and then resource share or cost optimize similarly like what's your advice for people when they're thinking about hey is an instance pool the right thing for me or an elastic pool with Azure SQL DB. Yeah thank you that's that's an important question. Um, elastic pools in SQL database world are great when you want to use a SQL database or single database as a paradigm as architectural paradigm and when you want to combine them uh and sort of time multiplex them so they can share the same resources uh uh because the isolation uh level is fairly low when it comes to the compute but on the other side it's a great cost So if you can um if your workloads have such a scenario, this is a fantastic way to uh optimize cost. On the instance pool aside, there is uh isolation is pretty high. So there is no sharing of the same compute between instances. So it's a more uh for the uh scenarios when you need a manage instance paradigm to the SQL server instance paradigm and when you want to save the cost. So you say okay this is the smallest instance I can provision is to be core. Awesome. Yeah that totally makes sense and and also from the like you mentioned a scenario earlier around migrating um and for many reasons SQL manage instance may be easier to migrate SQL agent was one thing you mentioned or uh cross DB querying. So maybe you landing in in SQL MI and um that makes a lot of sense. Um any final things? I think you mentioned this was generally available or is it generally available and uh any tips for folks who are just getting started? Yeah, it is generally available. We're very happy about it since November uh 24. So if you want to learn more, I provided here uh two links. One is for CQMI pools and the other is for the other feature that I showcase in this example that is a great a great mesh to the instance pool when you're trying to consolidate and redistribute. And this is a DB copy of Awesome. Well, great. Thanks so much, Georgia, for coming on the show. I learned a lot about instance pools, and I'm sure our viewers did as well. Uh viewers, if you like this episode, go ahead, give it a like, leave us a comment to let us know what you plan to do or what you're already doing with managed instance pools, which are generally available. Uh we'll put those links in the description for you to go learn more. And we hope to see you next time on Data Exposed. [Music]