Azure SQL Database Managed Instance: A Completely Unofficial Primer
If you haven’t heard about Azure SQL Database Managed Instance, or have heard of it but aren’t quite sure what it’s all about, here’s my completely unofficial primer to get you off the ground. I have attempted to describe things using “normal” English so that we don’t get bogged down in technical or marketing speak, let me know I’ve managed to pull this off (it’s trickier than it seems). It also means that in places I’ll almost certainly over-simplify and may omit mentioning some features for the sake of brevity. Which means you should read up the official documentation if you want to get the complete picture – I’ve provided some links at the end of this article to get you started.
With those caveats out of the way, let’s begin with an assumption that you’re aware of SQL Server as an on-premises database service ie you install it on your local network and do all the sizing, administration, security etc yourself. This is of course the traditional way of doing things and on the face of it there’s nothing wrong with it. However as I’m sure you’re aware there is another way.
For some time Microsoft has offered Azure SQL Database, a cloud-based fully-managed relational database service. SQL Database has many advantages over a locally-installed version of SQL Server such as the ability to scale up and down easily according to need, financially-backed uptime, built-in intelligent optimisation, advanced security, geo-availability, near zero administration and more. While SQL Database is highly-successful and widely used there can be some challenges if you want to move your locally-installed on-premises SQL Server databases to the Azure SQL Database service. Let me highlight two specific challenges that are particularly relevant if you need to migrate a large number of apps and corresponding databases from an on-premises implementation:
- App compatibility: You may need to update, refactor or in some cases re-write apps that use the database in order to get the full benefit of SQL Database
- No support for virtual networks, known as VNETs. Let’s not get into VNETs just yet except to say that a VNET allows you a completely isolated and secure method of connecting to a cloud service ie with a VNET you can really lock-down access in a way that’s more difficult to do without a VNET
And here’s where, magician-like, we unveil Azure SQL Database Managed Instance. But what is it exactly? Managed Instance – as we’ll call it for short – isn’t a new service as such, it’s a third deployment option within Azure SQL Database sitting alongside the two existing deployment options (for the record these existing options are single instance databases and so-called elastic pools but let’s not get into that here as it’s out of scope for this article. If you’re interested there is lots more information here).
It’s called “Managed Instance” for a reason – essentially it’s a cloud-based version of an on-premises SQL Server instance. Ok, so what does that mean? Well, databases in a SQL Database Managed Instance share the resources allocated to the managed instance and all the databases in the instance are managed collectively. Think of it as a way of grouping SQL Databases together in the cloud so that they share resources and management.
So far so good, but what other advantages are there? I’m going to point out two significant ones that relate directly to the challenges highlighted above:
- Very, very high levels of compatibility with the programming model of an on-premises SQL Server Enterprise, going all the way back to SQL Server 2005. For those of you who are interested this includes things like CLR support, SQL agents, native backup and restore and a whole lot more. You are very likely to find that once you move your local SQL Server database to a Managed Instance your apps will simply work without any changes, a huge advantage. And not just work: They will benefit from the scalability, security, geo-availability and almost zero-admin you get from a cloud service
- Support for VNET. There’s that VNET thing again, so let’s examine it a bit more closely. VNET is a big deal because, as alluded to above, it gives you options for connecting to the database that are completely isolated and dedicated to a customer. Before VNET there would be a public endpoint that, even though it still required passwords and other credentials, was not a viable option for large institutions like banks or healthcare who need completely isolated access. Now that each Managed Instance is a VNET customers can connect their on-premises app to it via VPN or Express Route Gateway (if you’re not familiar with Express Route Gateway it uses 3rd parties to broker the connection without using the Internet). And of course web apps have a similar capability for isolated, secure connectivity.
This all sounds good and indeed it is but we’ve skimmed over something and I need to return to it: data migration. If you only have one local database then it probably isn’t a big deal to migrate it to the cloud – for example you could use the native backup facility in SQL Server, then connect to your Managed Instance and restore it (again natively as SQL Database Managed Instances support native backup and restore). But if you have hundreds or thousands of databases, possibly some of which are using non-Microsoft database products, then you should take a look at the excellent Azure Database Migration Service. We have recently announced Azure Database Migration Services support for Managed Instance. It’s out of scope of this article but you can take a look here for more information: https://datamigration.microsoft.com/
There are a couple of other things you should be aware of:
- There are actually two Managed Instance options that provide different levels of compute, storage and availability: General Purpose which is best suited to data applications with basic IO and availability requirements; And Business Critical which is best suited to data applications with fast IO and high availability requirements. You can read more here
- Sizing: If you have a local SQL Server Database then you will be used to estimating the processing and storage requirements using physical hardware such as the number and type of processor cores required. To help you use a similar approach for Managed Instances you can use virtual cores (vCores) which represent virtual equivalents of local CPU cores. While this article isn’t focused on licencing there is something that’s worth mentioning: If you’re a SQL Enterprise Edition customer with Software Assurance you get 4 cores in the cloud for every 1 core you own on-premises for workloads moving to the General Purpose Managed Instance. Obviously this will be of enormous help if you’re moving virtualised applications to Managed Instance as you won’t need to purchase incremental vCores. For lots more on pricing and sizing take a look here.
There is of course tons more and if you’re interested take a look at the official blog post that provides good pointers to more information including how to get started, and the official documentation here.
