The Azure SQL DB Managed Instance public preview is open, although it may take a week or two for new applications to get their new VMs. To start your application process now, go into your Azure portal and Create an Azure SQL Managed Instance. There are preview terms at the top – fill out the form to accept that now, because they’re going through a big queue, and you’re not my first reader. (You’re still my favorite, though.)
Do that, then come back here and start reading – because you’ve got some planning and thinking to do. The preview deployment process has gotchas. Read the documentation carefully. It’s not hard by any means, but you have to be really detail-oriented. (The company Slack room was colorful yesterday.)
First, read the documentation tutorial, Create an Azure SQL Database Managed Instance. It looks like an easy pictorial walkthrough, but do not follow it yet. Read the rest of this first because there’s some huge gotchas. You do not wanna screw this up because your first deployment is going to take 24 hours or more:
Mine really did, too:
That pictorial tutorial is great, but keep reading – there’s details that matter.
How to configure a network for Azure SQL Managed Instance – here’s where the hurt is gonna come in: with Azure SQL DB, you have a fancypants portal that just lets you open a firewall to specific IPs and let you connect directly in. That is not the case with MIs – read this post to understand how you’re going to be doing networking.
Even when you read that post, what it doesn’t explain is how to open access to your on-premises SQL Server Management Studio to be able to connect. That’s an even bigger process with more steps.
You might think, “I’ll just deploy a VM to that same subnet that my MI is in,” but as of this posting, that doesn’t work – all VM deployments into that subnet fail with a cryptic error:
Instead, here’s what worked for me:
- Follow the MI setup instruction carefully – if you’re in a hurry, follow the instructions even though you may end up with too-large of a subnet. If you’re a networking pro with time, use a small subnet mask (like a /32). I’m not going to explain that here given what I wanna cover, but TCP/IP people will understand what I’m getting at. (Welcome to the cloud, here’s your networking manual.)
- Create another virtual network and another subnet in the same region (like if your MI is in South Central US, create another virtual network in South Central US, with a subnet in it, with different IPs from your MI subnet.
- In the MI subnet, create a peering that points to your VM subnet.
- In the VM subnet, create a peering that points to your MI subnet.
- Deploy a VM in the VM subnet – this is where you’re going to remote desktop in and run SSMS or SOS. From that VM, you should be able to connect to your MI once it’s available.
You can prepare now for when your preview goes live.
If you’re not savvy with networking, start by playing with the same concept using two Azure VMs. Create two virtual networks, two subnets, and peering routes between them. Put a VM in each subnet, and make sure that they can see each other. Yes, this is going to cost you some money to spin up, but I’d much rather have you learn the hard lesson with a couple of $3/day VMs as opposed to a $30/day MI that takes a day to spin up, and then you borked the setup and you have to start over, spending $30/day and waiting a day each time you goof it up.
If you’re part of a company or you’re networking-savvy, start reading how to configure a VNet for MIs. There are some really big gotchas here: no network security group, no other cloud services in it, and you only have 1 option for a route table. To be clear, I don’t have any problem with these requirements – Microsoft is doing some ambitious stuff in here, spinning up and tearing down VMs in order to handle the service, and they need IP address space to do it:
But that’s a story for another blog post.
Next, you probably wanna restore a SQL Server database into your Managed Instance.