Azure SQL Database Managed Instance is a fully managed, Azure-hosted version of SQL Server. Belonging to the Azure SQL Database family, it provides near 100% compatibility to SQL Server (SQL Agent, cross-database joins etc), while offering the benefits of PaaS such as automated backups, patching and upgrades. And, it can live inside a Virtual Network (vNet) with private IP addresses.

In this series of posts, I’ll show you how to;

  1. Create a Managed Instance
  2. Configure connectivity through a Point-to-site VPN
  3. Connect to your Managed Instance from your desktop with SQL Server Management Studio, and
  4. Perform an on-line migration of an On-Prem SQL Server database to your Managed Instance using Azure Database Migration Service

Why Managed Instance?

Before we start, let’s briefly cover what Managed Instance is, and why you might want to use it, compared to other options.

Until Managed Instance, Azure SQL Database was the PaaS offering for SQL Server workloads in Azure, offering automatic backups, patching and other management benefits. But when it came to migrating existing SQL Server applications, there were a number of things that needed redevelopment, such as SQL Server Agent jobs and cross-database joins. While there are ways to accomplish these things, though Azure Automation and Elastic Queries, additional effort is required for migration of existing SQL Server workloads.

With Managed Instance, the migration barrier is almost non-existent with an almost 100% compatibility with the traditional SQL Server product. As such, Managed Instance is the logical choice for migrating existing SQL Server workloads to PaaS. In addition to SQL Server compatibility, Managed Instance can run inside a vNet with a private IP address, while still allowing the benefits of PaaS such as auto-backups, patching and upgrades. Further, through Azure Hybrid Benefit, you can re-use your on-prem SQL Server licenses to reduce the operating costs of SQL Server in Azure.

So why not Managed Instance?

With all these great benefits, why wouldn’t you use Managed Instance? It’s a great question. There’s 3 main options for running SQL Server in Azure; Virtual Machines, SQL Database (single database) and Managed Instance. I believe this is the sweet spot for each;

Virtual Machines; You need control of the operating system. There are some situations where you need to install custom software on the same machine that runs SQL Server. Managed Instance doesn’t provide visibility of the underlying OS (by design to reduce admin overheads), so in these cases, a Virtual Machine makes the most sense. You can find best practices for running SQL Server in a VM here

Azure SQL Database (single database); You’re developing a cloud native application. If you’re developing an application from scratch, you probably don’t need the full surface area of SQL Server. Further, SQL Database (single database) starts from about $5 per month, whereas the minimum price for Managed Instance is currently around $1,000 per month for an 8 core instance if you bring your own SQL licenses. For applications developed from scratch with unknown usage, SQL Database (single database) allows you to start small and scale as required for the most economical outcome.

Azure SQL Database Managed Instance; Migrating existing SQL Server workloads to the cloud. If you want to mass-migrate legacy applications with minimal effort, but still take advantage of the benefits of PaaS, then Managed Instance is the standout choice.

In the next post, I’ll show you how to provision a Managed Instance and connect to it using SQL Server Management Studio from your desktop.