The purpose of this article is to discuss what Azure SQL Database Managed Instance is. This will then lay a foundation to explore the capabilities of this deployment model. We will discuss some advantages it can bring to your infrastructure with a cross comparison against on-premises SQL Server. Finally, we look at some important networking configuration that you will need to be aware of when evaluating it.
What is Azure SQL Database Managed Instance?
Azure SQL Database is Microsoft’s relational database service that operates in Microsoft Azure as Platform as a Service (PaaS) where you have a choice of the following deployment options:
Their newest offering, Managed Instances provides near 100% compatibility with the latest SQL Server on-premises (Enterprise Edition) Database Engine. It became available for public preview in March 2018 and it is seen as a very attractive deployment model but the question is why?
I see many customers benefiting from this because of three main reasons. Firstly, it is easy to lift and shift the database due its near 100% compatibility with on-premises SQL Server, coupled with the fact that native restore commands work, thus allowing for easy migrations. All this is possible whilst still being a fully managed PaaS offering. Finally, a lot of thinking and design has gone into the architecture, allowing for an isolated and secure cloud environment for databases hosted within a Managed Instance.
Managed Instance can be deployed on two hardware generations (Gen4 and Gen5). Hardware generations have different characteristics that are described in the following table:
There is no right or wrong answer here when selecting the Gen type, this is a decision that you need to make. Once you have decided the hardware type you then need to confirm that service tier you desire.
Managed Instance is available in two service tiers:
- General Purpose: Designed for applications with typical performance and IO latency requirements.
- Business Critical (preview): Designed for applications with low IO latency requirements and minimal impact of underlying maintenance operations on the workload.
Quite simply, if you know that you need low I/O latency and high I/O throughput then Business Critical would make the most sense because this is based on fast SSD storage I/O subsystem. If your business requirements dictate that you need more than one replica and read scale capability, then again Business Critical will be the most natural fit. However, be aware of the capacity requirements for this because it will generally consume four times more capacity than a General Purpose tier database instance due to the extra replicas.
Native Backup Benefit
As mentioned in the introduction you have the ability to restore a backup of your database from Azure storage into a Managed Instance. This is groundbreaking, where in the past we have had to rely on tools and BACPACs to migrate databases into Azure. To do this obviously requires some configuration and TSQL code but the benefit of this feature makes it worthwhile. At a high level you will need the latest version of SSMS (SQL Server Management Studio), a credential that uses a shared access signature to where the backup is located (Azure Storage) and some TSQL code for the actual restore.
Below shows the TSQL statements required, please note keys and secrets have been obfuscated for the purpose of this article.
Connect to the source server and issue the commands to create a credential and a full backup into Azure storage.
-- Create the credential CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/backup] WITH IDENTITY = 'SHARED ACCESS SIGNATURE' , SECRET = 'your secret' --Backup BACKUP DATABASE [TestDB] TO URL = 'https://mystorage.blob.core.windows.net/backup/test.bak'
Then connect to the Managed Instance.
— Create the credential
CREATE CREDENTIAL [https://mystorage.blob.core.windows.net/backup]
WITH IDENTITY = ‘SHARED ACCESS SIGNATURE’
, SECRET = ‘your secret’
–Restore the backup
RESTORE DATABASE [TestDB] FROM URL =
Once you have restored the database do not forget that this is still a fully managed service so Microsoft will take care of all the backups and consistency checks for you. Please note, backups from a Managed Instance can only be restored to another Managed Instance. They cannot be restored to an on-premises SQL Server or to an Azure SQL Database single server or an elastic pool.
This is a great time to discuss the other benefits that Managed Instances will give you.
The administration costs of this solution are very low. Remember we have just restored a SQL Server backup file and still we have key benefits built into the product. These benefits are what makes this technology more advantageous than classic on-premises SQL Servers.
- Automatic software patching
- Managed full, differential and log backups (already mentioned)
- Built-in High Availability with 99.99% uptime SLA
How does Microsoft achieve this 99.99% SLA? They actually have two different architectures depending on the service tier that you select. Let’s assume you provision a database that uses the Business Critical service tier. High availability is implemented using technology similar to SQL Server Availability Groups. Every database is a cluster of database nodes with one primary database that is accessible for customer workload and three secondary processes containing copies of data. The primary node constantly pushes the changes to secondary nodes in order to ensure that the data is available on secondary replicas if the primary node crashes for any reason.
The General Purpose tier uses the idea of a stateless compute layer for the sqlservr.exe and a stateful data layer for data files. With this concept of layers if there is an issue with (for example) the hardware the Azure Service Fabric will move the stateless SQL Server process to another stateless compute node and the data/log files are attached to a newly initialized SQL Server process.
Differences with Singleton Azure SQL Database?
Everything mentioned above also applies to the traditional single Azure SQL Databases. You might be asking what are the key differences that Managed Instances offer over the single Azure SQL Database deployment model? The features that you can use within a Managed Instance but not Azure SQL Database are listed below:
- Change data capture
- Cross database transactions
- Distributed partition views
- Filegroups for your database
- Resource governor
However please be aware that the following features are still not supported:
- R – services
- Policy Based Management
- Trace flags
Network Configuration – Preparation is Key
An absolute requirement for Managed Instances is that it must be deployed within a virtual network. You must be network savvy to setup this so it is advisable to prepare well for this section or seek help from a cloud administrator, which in an enterprise business will very likely be the case. This step is so important because it enables the following scenarios:
- Connection to a Managed Instance directly from an on-premises network
- Connecting a Managed Instance to linked server or another on-premises data store
- Connecting a Managed Instance to other Azure resources
There are further important requirements of a dedicated subnet within the virtual network and mandatory inbound / output ports that you also need to setup, all is well documented by official Microsoft documentation.
All this work is required to allow for security isolation from other tenants within the Azure Cloud. If you decide that you need to connect an on-premises application to the Managed Instance, then you will need ExpressRoute or a site to site VPN for this. Obviously, things are the simplest if the application is inside the same virtual network. Study the below image, this image shows the different choices (1-7) available when connecting application and services to a Managed Instance, it is a great way to summarize this section.
To conclude you can see what a compelling option Azure SQL Database Managed Instance is. Having near to 100% compatibility with your on-premises SQL Server is enough reason on its own to migrate to it. The migration process of backing up and restoring via Azure Storage will bring smiles to all data professionals across the globe. From a business perspective, after completing all the networking pre-requisites you will have complete secure isolation from other tenants in Azure whilst having a flexible capacity model where you can scale where you see fit whilst benefiting from all that a PaaS offering gives you.
By Arun Sirpal (Microsoft MVP), Editorial Contributor
Copyright © 2018 Arun Sirpal: The right of Arun Sirpal to be identified as the author of this work has been asserted in accordance with the Copyright, Designs and Patents Act 1988. All rights reserved. No part of this publication may be reproduced, stored or introduced into a retrieval system, or transmitted, in any form, or by any means (electronic, mechanical, photocopying, recording or otherwise) without the prior written permission of the publisher, Ballard Chalmers. If consent is given, please give credit. Requests to re-print should be sent to [email protected].
About the author
Arun Sirpal is a Microsoft MVP with over 11 years experience with Microsoft Data Platform Technologies – from SQL Server (Physical and Virtual) to Azure. His articles have been published on SQL Server Central and Microsoft TechNet. Arun is a member of Microsoft’s Azure Advisors and SQL Advisors groups, as well as an accomplished Microsoft Certified Solutions Expert on the Data Platform.