Home / Documentation / Azure / Reference / Databases / Azure SQL Managed Instance

Azure SQL Managed Instance Reference

Introduction

Azure SQL Managed Instance is a fully managed relational database service built on the SQL Server engine. It provides the broadest SQL Server compatibility in the cloud, along with automatic patching, backups, high availability, and performance monitoring. This makes it an ideal choice for lift-and-shift scenarios where you want to migrate existing SQL Server databases to Azure without significant application changes.

It offers a fully managed platform as a service (PaaS) that supports most of the features of SQL Server Agent and on-premises SQL Server, along with the benefits of a fully managed cloud service. This includes high compatibility with on-premises SQL Server, automated patching and updates, built-in high availability, and integrated security features.

Key Features

  • High SQL Server Compatibility: Supports most of the features of SQL Server, including SQL Server Agent, CLR, Service Broker, and distributed transactions.
  • Managed Service: Handles infrastructure management, including patching, backups, high availability, and disaster recovery.
  • Hybrid Cloud Support: Provides consistent instance and database management for hybrid environments.
  • Built-in High Availability: Offers automatic failover and recovery capabilities without user intervention.
  • Advanced Security: Integrates with Azure Active Directory, Azure Key Vault, and offers features like Transparent Data Encryption (TDE) and Always Encrypted.
  • Scalability: Offers different service tiers and compute sizes to meet varying performance needs.
  • Network Isolation: Deployed within an Azure virtual network (VNet), providing enhanced security and connectivity options.

Note: While compatibility is very high, some minor differences may exist compared to on-premises SQL Server. Always test your applications thoroughly.

Architecture

Azure SQL Managed Instance is built on a dedicated, isolated instance of the SQL Server engine running on Azure infrastructure. Each instance is deployed within a customer-assigned virtual network (VNet) subnet, ensuring network isolation and private connectivity. This architecture provides a true instance-level deployment that closely mirrors an on-premises SQL Server environment.

The managed service layer handles all the operational tasks, including:

  • Infrastructure provisioning and maintenance
  • Operating system and SQL Server patching
  • Automated backups and restore operations
  • High availability and disaster recovery
  • Performance monitoring and tuning

Deployment Options

Azure SQL Managed Instance can be deployed in various configurations to suit different needs:

  • General Purpose: Offers a balanced compute and storage option for most common workloads, with built-in high availability and disaster recovery.
  • Business Critical: Provides the highest performance, availability, and redundancy for mission-critical applications, leveraging Always On Availability Groups.

When creating a managed instance, you will configure:

  • Service tier (General Purpose or Business Critical)
  • Compute size (vCores)
  • Storage size
  • VNet and subnet for deployment
  • Instance collation and time zone

Networking

SQL Managed Instance instances are deployed within an Azure Virtual Network (VNet). This provides:

  • Network Isolation: Instances are private and accessible only from within their VNet or through configured connectivity methods like VPN Gateway or ExpressRoute.
  • Private IP Address: Each instance is assigned a private IP address, allowing for direct connection from other resources within the VNet.
  • Connectivity Options: Supports connections from on-premises networks via VPN Gateway or ExpressRoute, enabling hybrid cloud scenarios.
  • Managed Instance Link: Facilitates replication between SQL Managed Instance and SQL Server on-premises.

Tip: Ensure your VNet and subnet configurations allow sufficient IP address space for the managed instance, as it requires multiple IP addresses for its operations.

Security

Security is a paramount concern for Azure SQL Managed Instance. It incorporates robust security features:

  • Network Security: Deployed in a VNet for isolation, with network security groups (NSGs) to control inbound and outbound traffic.
  • Authentication: Supports SQL authentication and Azure Active Directory (Azure AD) authentication, including multi-factor authentication.
  • Authorization: Role-based access control (RBAC) for managing permissions on the instance and its databases.
  • Data Encryption:
    • Transparent Data Encryption (TDE): Encrypts data at rest by default.
    • Always Encrypted: Protects sensitive data from unauthorized access, even from database administrators, by encrypting data in client applications.
    • Dynamic Data Masking: Obfuscates sensitive data in query results for non-privileged users.
  • Threat Detection: Advanced Threat Protection monitors for suspicious activities, potential vulnerabilities, and anomalous database access patterns.

Management

Azure SQL Managed Instance simplifies database management through its PaaS nature and integrated tools:

  • Automated Operations: Handles patching, backups, and availability management automatically.
  • Azure Portal: A web-based interface for creating, configuring, monitoring, and managing instances and databases.
  • SQL Server Management Studio (SSMS): The familiar tool for connecting to and managing your SQL Managed Instance databases.
  • Azure Data Studio: A modern, cross-platform database tool for developers and administrators.
  • PowerShell and Azure CLI: Scripting and automation capabilities for managing resources programmatically.
  • Monitoring: Performance metrics, query performance insights, and error logs are available through Azure Monitor.

Performance

SQL Managed Instance offers flexible performance options through its service tiers and compute sizes:

  • Service Tiers: General Purpose and Business Critical offer different levels of performance and availability.
  • Compute: Available in different vCore configurations to match workload demands.
  • Storage: Scalable storage options with varying performance characteristics.
  • Managed Instance Link: For hybrid scenarios, it allows low-latency replication between on-premises SQL Server and SQL Managed Instance.

Important: Choose the service tier and compute size that best matches your application's performance requirements and budget.

Pricing

Pricing for Azure SQL Managed Instance is based on several factors:

  • Service Tier: General Purpose and Business Critical have different pricing models.
  • Compute: The number of vCores allocated.
  • Storage: The amount of provisioned storage.
  • Backup Storage: The amount of storage used for backups.
  • Data Transfer: Standard data transfer costs apply.

Azure Hybrid Benefit and Reserved Instances can provide significant cost savings for long-term commitments.

View detailed pricing information on the Azure pricing page.

Tutorials

Get started with Azure SQL Managed Instance through our comprehensive tutorials:

API Reference

Explore the APIs and SDKs for managing Azure SQL Managed Instance: