How to create an Azure SQL Managed Instance How to setup and configure a SQL Managed Instance on the Azure Cloud Portal: a great way to create, host and mantain SQL Databases in the cloud

How to create an Azure SQL Managed Instance

Providing ourselves with a SQL Managed Instance on the Azure Cloud Portal can be a great way to create, host and mantain SQL Databases in the cloud.

As we most likely already know, putting our DBMS on Azure gives us the chance to have our SQL Server workloads running as either a hosted infrastructure (IaaS) or a hosted service (PaaS): the first option is great if we want to handle the database maintenance tasks by ourselves - such as apply patches, take backups, and so on; the second option is preferable if we do want to delegate such operations to Azure. However, regardless of the chosen path, we're going to have a scalable database service with full redundancy and no single point of failure guarantees, plus a lot of other performance and data security benefits. The downsides, as we can easily guess, are mostly two: the additional cost and the fact that we're going to have our data located elsewhere, which can be a major issue in terms of privacy and data protection in certain scenarios.

In the first part of this guide we'll quickly summarize the main differences between the IaaS and PaaS approaches; right after that, we'll extensively explain how to configure a SQL Managed Instance (IaaS), leaving the PaaS alternative to another post that we'll be publishing within the next few weeks.

Introduction

The SQL Managed Instance is one of the three main approaches available on Azure for hosting SQL databases: the other two options are SQL Databases and SQL Virtual Machines. As we can easily expect, all of these options have their pros and cons in terms of costs, performance, security, customization and complexity. Let's briefly summarize how each one of them works and their benefits:

  • SQL Database: A fully-managed SQL database engine based on SQL Server Enterprise Edition. This option allows us to setup and manage one or more single relational databases hosted in the Azure cloud with a Platform-as-a-Service (PaaS) usage and billing model: more specifically, we can define it a Database-as-a-Service (DBaaS) approach. This option provides built-in high availability, intelligence, and management, hence being great for those who wants a versatile solution without the hassle of having to configure, manage and pay a whole server host.
  • SQL Managed Instance: A dedicated SQL Managed Instance on Azure, a scalable database service providing near 100% compatibility with a standard SQL Server instance, featuring a Infrastructure as a Service (IaaS) usage and billing model. This option provides all of the PaaS benefits of the previous one (SQL Database) but adds some additional features and capabilities, such as: Linked Servers, Service Brokers, Database Mail, full Azure Virtual Network support, multiple databases with shared resources, and so on.
  • SQL Virtual Machine: A fully-managed SQL Server consisting of a Windows or Linux Virtual Machine with a SQL Server instance installed on top of it. This approach, which also adopts a Infrastructure as a Service (IaaS) usage and billing model, offer full administrative control over the whole SQL Server instance and the underlying OS, thus being the most complex and customizable one. The most significant difference from the other two options (SQL Database and SQL Managed Instance) is that SQL Server VMs also allow full control over the database engine: we can choose when to start maintenance/patching, change the recovery model, pause/start the service, and so on.

For further info regarding the pros and cons of the Azure options described above, we strongly suggest to read this guide.

How to create an Azure SQL Managed Instance

All of these options are good enough and, although very different in terms of overall costs, can be activated free of charge: SQL Database is arguably the cheapest one because it's free for 12 months, thanks to the trial subscription plan offered by Azure, as long as we keep its size under 250GB; either SQL Managed Instance and SQL Virtual Machine are rather expensive, since they both provide a virtualized IaaS, but they can be activated for free (at least for few weeks) with the 200€ provided by that same Azure trial subscription plan.

SQL Managed Instance

Let's see how we can setup a SQL Managed Instance.

Let's start by visiting the following URL:

Which will bring us to the web page that will allow us to create an Azure SQL Managed Instance:

How to create an Azure SQL Managed Instance

Click the Start free button and create a new account.

If you already have a valid MS account you can definitely use it, but you should only do that if you are sure you want to use the free Azure trial on it: if that's not the case, consider creating a new one.

After a brief registration form (and/or login phase) you'll be redirected to the Azure portal.

It goes without saying that, if the account we've logged in with has already spent its free period or has an active paid subscription plan, we'll be gracefully bounced back:

How to create an Azure SQL Managed Instance

Eventually, after having sorted everything out, we should be able to access the Azure portal (portal.azure.com) in all of its glory:

How to create an Azure SQL Managed Instance

Once there, click to the Create a resource button to access the Azure Marketplace: search for an entry called Azure SQL Managed Instance, then click Create to add it.

IMPORTANT: be careful to not pick the SQL Database entry instead, which is the one for creating the SQL Server Virtual Machine - the option #1 which we talked about early on.

How to create an Azure SQL Managed Instance

During the process we'll be also asked to create our very first Azure Tenant (unless we already have one): that's basically a virtual organization that owns and manages a specific set of Microsoft cloud services. Tenants are identified by unique URIs with the following format: <TenantName>.onmicrosoft.com: just give it a suitable name and go ahead.

Configuring the Instance

As soon as we click the Create button we'll be asked to configure our SQL Managed Instance with a wizard-like interface splitted into the following tabs:

  • Basics: Subscription type, instance name, admin username and password, service tier, and so on
  • Networking: Network connectivity method and firewall rules
  • Additional settings: Collation and Time Zone
  • Review + Create: Review and confirm all of the above

The default settings are fine for the most part: just be sure to select a service tier with enough capacity and hardware resources for the workload you reasonably expect: however, keep in mind that you can change it after deployment without issues. In our example, we've picked a General Purpose service tier with the minimum possible amount of vCores and storage:

How to create an Azure SQL Managed Instance

Furthermore, be sure to enable external access from the Internet, to that we'll be able to connect to our Database from all our environments.

How to create an Azure SQL Managed Instance

Wait a minute: isn't that a major security issue? What if our databases contains personal or sensitive data?

As a matter of fact, it actually is: allowing public access from the Internet is something we should always avoid, unless we're playing with open data for testing, demonstrative or tutorial purposes... Which is precisely what we're doing right now.

Also, be sure to select a collation and a timezone suitable to your language & country.

How to create an Azure SQL Managed Instance

Once you've set up all of these options, go to the Review + Create table to check that everything has been properly set.

If everything looks good, click the Create button to start provisioning the managed instance.

The deployment task can take some minutes, therefore you'll have to be patient: once it's done, you'll be able to access the managed instance and configure it further, OR directly connect to it using the public IP address (screenshot below, near the top-right corner) that MS Azure will assign to it.

 

However, before connecting to the SQL Server, we should definitely spend a couple more minutes to understand how we can do that from any external machine (outside MS Azure) without giving up too much in terms of security.

Connecting to a SQL Managed Instance

Azure SQL Managed Instance provides a private endpoint to allow connectivity from inside its virtual network: if you need to connect to your SQL Managed Instance from within any MS Azure service - including Azure-hosted VMs, Web Applications published on App Services, and so on - this is the best option in terms of security, as it provides maximum isolation.

However, there are scenarios where you need to provide a public endpoint connection, for example when you need to connect to your SQL managed instance from external VMs or web farms: that's precisely why we've setup a public endpoint in the previous paragraph: since we've done that, MS Azure will make our SQL Managed Instance publicly accessible from the web.

It goes without saying that, should you go for the public endpoint route, you'll open your managed instance to external threats: for that very reason, you'll have to carefully configure the inbound connection rules to minimize the risk of unauthorized access to your data.

This task can be done by selecting the Security > Networking option from the left menu and properly configure the public endpoint, as well as retrieving the host name and TCP port.

How to create an Azure SQL Managed Instance

Right after doing that, you need to allow public endpoint traffic on the managed instance's network security group (NSG). In order to do that, navigate to the Overview tab and select the Virtual network/subnet link, which will take you to the Virtual network configuration page.

Once there, select the Subnets tab on the left configuration pane, and take a look to the name of the SECURITY GROUP for your managed instance.

How to create an Azure SQL Managed Instance

Go back to the Azure main dashboard and select Network Security Groups to find your NSG with that same name. Select it to go into that NSG configuration page: from there, select the Inbound security rules tab, and then Add a rule that has higher priority than the deny_all_inbound rule with the following settings:

Source Any IP address or Service tag See below for details on this
Source port ranges * Leave this to * (any) as source ports are usually dynamically allocated and as such, unpredictable
Destination Any Leaving destination as Any to allow traffic into the managed instance subnet
Destination port ranges 3342 Scope destination port to 3342, which is the managed instance public TDS endpoint
Protocol TCP SQL Managed Instance uses TCP protocol for TDS
Action Allow Allow inbound traffic to managed instance through the public endpoint
Priority 1000 Make sure this rule is higher priority than the deny_all_inbound rule

The most important setting in terms of security is the Source parameter: if the external VM or device or app that needs to connect to the SQL Managed Instance has a fixed IP address (or a suitable address range), it can be wise to put that IP address or netmask there instead of leaving Any, as it will limit potential threats from unauthorized third-party sources.

After having configured the inbound rule with the above settings, you can find the database connection string by going back to the SQL Managed Instance configuration page and select the Settings > Connection strings tab. Inside the connection string you will find the Managed Instance host name in the same <mi_name>.public.<dns_zone>.database.windows.net format we've seen before and the expected 3342 TCP port.

Conclusion

That's it, at least for now: we hope that this post will help both ASP.NET developers and System Administrators to create a SQL Managed Instance on MS Azure and connect to it from any external client, web application or virtual machine.

If you want to know more about .NET Core and Angular check out the ASP.NET Core 5 and Angular book, available as paperback and e-book.

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

2 Comments on “How to create an Azure SQL Managed Instance How to setup and configure a SQL Managed Instance on the Azure Cloud Portal: a great way to create, host and mantain SQL Databases in the cloud

  1. You can’t access a SQL managed instance privately the way you showed. You took a screen capture of a virtual machine, not the managed instance. There is no accessible IP address you can connect to directly and an Azure SQL Managed Instance doesn’t spin up a Virtual Machine that is managed in any way that other VMs are. Almost everything you explained about managed instances themselves are wrong, such as, it has no “native virtual network”, as you assign it a subnet for an existing vNet. Also, there is no “custom private IP addressing” with these. You get no visilibity into how the IPs for the subnet are assigned to the components. Managed instances inherently have private endpoints built into the solution that requires Azure DNS enabled on the vNET for internal control plane routing.

    1. Hello there, thank you for your feedback: as a matter of fact the content was already amended a while ago, but for some strange reasons the outdated version was still in Cloudflare cache. I think we’ve managed to fix that now.

Leave a Reply

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


The reCAPTCHA verification period has expired. Please reload the page.

This site uses Akismet to reduce spam. Learn how your comment data is processed.