Introduction to Azure SQL Managed Instance

Microsoft currently offers two built-in methods of running production SQL Server databases in Azure. The first of them relies on the ability of Infrastructure-as-a-Service (IaaS) Azure virtual machines to host a variety of on-premises workloads, including SQL Server instances. The second one leverages Platform-as-a-Service (PaaS)-based Azure SQL Database. With the introduction of Azure SQL Managed Instance service, which is in public preview at the time of publishing this article, you have a third option, which combines the benefits of its two predecessors. This article provides an overview of the main characteristics of this new service.

Due to its PaaS nature, Azure SQL Database eliminates all of operating system-related maintenance tasks, such as patching or backups. It also minimizes the majority of SQL Server-level management responsibilities, allowing you to focus on database-specific design and development. Other important benefits of Azure SQL Database include built-in high availability as well as considerably simplified implementation of vertical scaling and disaster recovery. However, the extra layer of abstraction that hides the complexity associated with these tasks also limits the degree of manageability. As the result, Azure SQL Database is not fully compatible with databases hosted on full-fledged instances of SQL Server (which you can implement by using SQL Server instances running within Azure virtual machines), lacking support for features such as SQL Server Agent, Profiler, Database Mail, Common Language Runtime, Cross-database queries, distributed partition views, or linked servers. This, in turn, commonly hinders migration to Azure SQL Database, forcing customers to resort to the lift-and-shift approach. Another common customer complaint concerns the ability to provision an Azure SQL database directly into an Azure virtual network, although, at this point, with the introduction of Service Endpoints, it is possible to establish direct communication between Azure virtual machines on a virtual network and an Azure SQL Database without exposing its public endpoint to the Internet (for more information regarding this functionality, refer to one of our earlier articles published on this forum).

None of these limitations are applicable to Azure SQL Managed Instance. The new offering delivers a combination of features, which, on one hand, provide traditional IaaS capabilities, such as deployment into a virtual network or support for SQL Server Agent (for a full comparison between SQL Server, Azure SQL Database, and Azure SQL Managed Instance, refer to Microsoft Docs) and, on the other, simplify management and maintenance through PaaS-based, platform-provided services. In particular, you benefit from built-in high availability (which, incidentally, prevents you from implementing Always On Availability Groups containing Azure SQL Managed Instance databases), as well as automated patching and automatic backups combined with point-in-time restores.

During the preview, Azure SQL Managed Instance is available in a single General Purpose service tier, which allows you to choose between 8, 16, and 24 vCPUs with up to 8 TB of Premium storage, providing up to 7500 IOPS. While you do not have direct access to the underlying database files (which affects your ability to reference physical file system paths when operating within the managed instance), you can customize their number for each database. A single Azure SQL Managed Instance can contain multiple databases of up to 4 TB in size (the aggregate size of databases cannot exceed the 8 TB instance size limit).

Azure SQL Managed Instance supports the security and auditing features available in both SQL Server and Azure SQL Database, including Always Encrypted, Dynamic Data Masking, and row-level security (for more information regarding these features, refer to our earlier articles). In addition, you can take advantage of the Managed Instance Threat Detection available from Azure Security Center. To authenticate, you can use either SQL Authentication or Azure Active Directory-based authentication. This makes it possible to authenticate to an Azure SQL Managed Instance using on-premises Active Directory (AD) credentials, as long as the AD domain and an Azure AD tenant are integrated. Given the increased compatibility between Azure SQL Managed Instance and SQL Server, you might be able to perform database migration through backup and restore. Alternatively, you have the option of carrying out the migration by using Azure Database Migration Service (for more information regarding this process, refer to the Microsoft Docs).

In our upcoming articles, we will step through the provisioning of an Azure SQL Managed Instance and will explore its capabilities in more detail.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles