By: Jared Westover | Updated: 2023-07-05 | Comments (3) | Related: > Azure SQL Managed Instance
Problem
Since all the cool kids are moving to the cloud, I don't want to be left out. Three years ago, I toyed with Microsoft's first Azure PaaS offering of SQL that nearly copied SQL Server 2019. The offering was called a Managed Instance (MI). There's so much to like about a MI versus an Azure SQL DB. Life went on, and it had been a few years since I worked on one. Recently a new opportunity to work with an MI emerged, and I was excited to revisit an old friend. Was it as good as I remember, or has nostalgia for the good old days infected my brain?
Solution
In this article, I'll introduce you to a Managed Instance and why you might want to use one over Azure SQL DB. I'll highlight four features that excite me more than a big cup of coffee in the morning. Finally, let's determine if a MI is right for you. It's crazy talk to think a fully managed SQL solution in Azure might not be for everyone. Disclaimer: Microsoft's not paying me a cent to write about this, but I will accept donations.
What is a Managed Instance?
A Managed Instance is one of the platforms as a service (PaaS) Azure SQL offerings. In the past, if you wanted the full functionality of a MI, an IaaS solution would be your only option. This solution involved creating a virtual server in Azure, running SQL Server. You're still in the cloud but manage everything from an OS perspective. Azure SQL DB is the other PaaS offering that's been around for a while.
The project I worked on didn't fit well into the Azure SQL DB framework. For one, our application had several databases communicating with each via cross-database joins. Hopefully, this admission doesn't cause you to click away. It's not like anyone sets out to have a dozen databases for an application. Additionally, the application relied heavily on the SQL Server Agent. Doesn't everyone rely on the Agent? Also, we needed a lift-and-shift approach. Plus, a team member wrapped up another project where a MI fit perfectly as the solution. They performed extensive testing and found their workload performed significantly better with a MI versus Azure SQL DB.
Using a MI is the closest PaaS SQL Server experience in Azure. This fact means you don't have to worry about upgrading SQL Server, and it includes the Enterprise features. Microsoft states that a SQL MI has nearly 100% compatibility with the latest SQL Server (Enterprise Edition) database engine.
My Favorite Things
We all like different things. Some people enjoy watching horror movies, while others can't get enough of the Marvel universe. One isn't better than the other; they're just different. I've outlined below four things I like the most about revisiting a MI after all these years.
Decreased Creation Time
The first noticeable enhancement when creating a MI is the decreased time it takes. A few years ago, it took anywhere between 4-8 hours. After finishing the setup recently, a co-worker joked about waiting 8 hours, and it would be ready like magic. After 20 minutes, the screen displayed a completion message and was ready. At first, I thought something was wrong. Maybe we neglected to check a box. Like when you rewrite that cursor using set logic, it's faster now, way faster.
Microsoft points out that the 30-minute or less creation applies to the first MI in the subnet. In an article, Uros Randjelovic writes that subsequent creations in the subnet, with the same configuration, will take only 60 minutes instead of the full 4 hours that was previously the case. You can enjoy faster creation times when responding to business needs. I have yet to test creating another MI in the same subnet, but it's on the to-do list.
During my first experience with a MI, someone created them in the wrong region. Unfortunately, there wasn't a way to change that without creating new ones. It was a pain, and the huge wait time compounded the misery. With a decreased creation time, creating one in a test/dev subscription isn't as daunting.
Pause the Compute
One of the best Azure SQL Database and Synapse offerings is the ability to pause the compute. When it's serverless and not in use, you're not paying for the compute, only the storage. On average, storage is cheap compared to compute. You might ask why you would want to do that. For me, it comes down to one thing, and that is to save money. Unless you have unlimited Azure bucks, this is important. Saving money especially applies to development environments. Going to your boss and trying to justify spending several hundred dollars monthly to perform a POC or testing isn't fun.
Microsoft now offers the ability to stop and start the compute on a MI. This functionality was on my wish list while using one a few years ago. As I write this, it's listed as a preview feature for the November 2022 wave. You can create individual stop-and-start schedules. In the example below, I've configured a Monday through Friday pattern where the instance starts at 8:00 am and stops at 6:00 pm. The slider at the bottom indicates my MI is stopped over 70% of the week.
I'm not fond of some things about the current design. First, it would be nice to have the ability to create a stop event without a start. For example, I work on Saturday, turn the MI on at 9:00 am, and work for a few hours. I'm busy and forget to turn it off. There goes my hard-earned money down the drain. You can see from the screenshot below that you can't leave the start option blank.
The second item must be the time it takes to start the instance. Testing on two MIs, resuming, takes around 20-25 minutes. In the grand design, I can live with this. However, if you need to test something in a hurry and it's paused, expect to wait, maybe get a coffee, or go for a walk.
Customizable Maintenance Window
Everybody wants options. Offering one flavor of ice cream at a social event won't win you many friends, but I'll still eat it. The same goes for choosing when maintenance takes place. Defining a maintenance window isn't a new feature. However, it's new since I last worked with a MI. Unlike the TV show choices on Netflix, Microsoft only gives you a few options. You can see from the screenshot below that you have three:
Microsoft claims planned maintenance doesn't cause outages, and I'm inclined to believe them. However, we live in the real world. So choosing when the service is interrupted or completely unavailable is great. This also allows you to configure the maintenance not to run while you perform your own maintenance or load data via an ETL process.
There is also an option to receive advanced notifications of when a planned event will occur. It's nice to let your users know, especially if they work late or on the weekends to complete a deadline. Maybe we'll be given more than the predefined selections in the future.
SQL Server Agent
Anyone who's worked as a full-time or part-time DBA knows the importance of the SQL Server Agent. I needed two things when we shifted an application from on-prem to Azure. One was the ability for cross-database joins. You might say, wait, why are you performing cross-database joins? I don't think anyone sets out to do anything less than their ideal; however, life happens. The second was the SQL Server Agent. The MI supplied both.
What's a SQL Server Agent? Microsoft defines it as a "Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server." For the most part, the Agent functionality with a MI mirrors that of on-premises. I'm sure there are some limitations that I haven't run into yet. When viewing from SSMS, it mirrors an on-premises environment.
There's the ability to create schedules and define operators. Best of all, you have another option to run Ola Hallengren's fantastic maintenance scripts in Azure.
Should You Use a Managed Instance?
Without knowing the specifics of your environment, the answer to the above question is that it depends. No one likes that answer. Come on, just tell me what to do. Okay, if you're moving to Azure and your SQL environment has multiple databases, and you want the feel of SQL Server without the overhead of an IaaS solution, my answer is yes. If you want to move to Azure and have one simple database used a few times a day, then look at a serverless Azure SQL DB.
I'm interested to hear from you. Do you use a MI in your environment? If you answered yes, what's your experience been so far?
Key Takeaways
- A Managed Instance is Microsoft's only PaaS offering that closely mimics SQL Server on-premises.
- With an MI, you can pause compute with the feature wave in November 2022. This enhancement means you can save money, especially in development environments.
- If you're moving to Azure and want a lift and shift experience, try a Managed Instance.
Next Steps
- Bob Ward and Niko Neugebauer are two of the best resources for anything Managed Instance related. Here's a video where they cover all the features at a high level: Inside Azure SQL Managed Instance By Bob Ward and Niko Neugebauer.
- John Minor wrote an excellent article going through the steps to set up a Managed Instance. Even though some screenshots no longer match, the process is the same: Deploy and Configure Azure SQL Managed Instance
- Are you considering moving your on-premises SQL Server to an Azure Managed Instance? Give John Martin's article a read for using native backups: Migrating SQL Server Databases to Azure Managed Instances with Native Backups
- Check out this webinar series on Azure SQL Managed Instance:
- Modernize Your Apps with Azure SQL Managed Instance and Unlock the Benefits of the Cloud
- Optimize your costs with Azure SQL Managed Instance
- The flexibility of hybrid options with Azure SQL Managed Instance
- Set Yourself Up for Performance Success on Azure SQL Managed Instance
- Five ways to improve data security on Azure SQL Managed Instance
About the author
Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-07-05
