How to Deploy Your First Azure SQL DB Managed Instance Preview

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:

If your deployment lasts 4 hours or more, call Reddit, because you still have another 20 to go

Mine really did, too:

Kiefer Sutherland is frantically deploying behind the scenes

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:

No sex in the champagne room, no VMs in the Managed Instance subnet

Instead, here’s what worked for me:

  1. 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.)
  2. 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.
  3. In the MI subnet, create a peering that points to your VM subnet.
  4. In the VM subnet, create a peering that points to your MI subnet.
  5. 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:

Nodelayheehoo

But that’s a story for another blog post.

Next, you probably wanna restore a SQL Server database into your Managed Instance.

Previous Post
Why sp_prepare Isn’t as “Good” as sp_executesql for Performance
Next Post
How to Restore a SQL Server Database into Azure SQL DB Managed Instances

7 Comments. Leave new

  • For the networking, if you create additional subnets in the same Azure vNet then you can deploy a VM to that and then you will have connectivity to the Managed Instance on its subnet.

    Reply
    • John – thanks! I was hoping so, but I wasn’t about to break the MI after a 24-hour deployment cycle to find out, hahaha. Finicky thing!

      Reply
      • I had to re-read the networking article a couple of times ahead of the deployment in public preview. There are still some complexities around networking behavior when you have VPN Gateways in play as well as vNet peering.

        General rule of thumb is that so long as nothing else is in the subnet with the MI it should be happy.

        I have taken to setting up dedicated subnets for servers, service endpoints for DB and Storage and then smaller 10.0.x.0/27 subnets for Managed Instances. Seems to be working for me fine at the moment.

        Reply
  • How can we connect to Managed instance from our on promises SSMS?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}