Azure SQL Managed Instance Documentation

Azure SQL Managed Instance Overview

Azure SQL Managed Instance is a cloud-native Platform as a Service (PaaS) database offering that combines the broadest SQL Server engine compatibility with the scalability and manageability benefits of Azure. It is ideal for customers looking to migrate their on-premises SQL Server workloads to the cloud with minimal application changes.

Key benefits include:

Full SQL Server Engine Compatibility: Runs almost all features of SQL Server.
Managed Service: Microsoft handles patching, backups, high availability, and disaster recovery.
Hybrid Benefits: Leverage existing SQL Server licenses for cost savings.
VNet Integration: Securely connect to your virtual network.

Key Features

Database Engine

Supports a wide range of SQL Server features including:

T-SQL: Full T-SQL support.
Stored Procedures, Triggers, Functions: All database objects are supported.
Transactions: ACID compliant transactions.
Indexing and Query Optimization: Advanced performance features.

Security

Azure Active Directory Authentication: Centralized identity management.
SQL Server Authentication: Traditional username/password.
Transparent Data Encryption (TDE): Encrypts data at rest.
Always Encrypted: Protects sensitive data.
Network Security: Integration with Azure Virtual Networks, firewall rules, and private endpoints.

High Availability and Disaster Recovery

Built-in High Availability: Automatic failover.
Geo-Replication: Disaster recovery across regions.
Automated Backups: Point-in-time restore.

Getting Started

Create an Instance

You can create an Azure SQL Managed Instance using the Azure portal, Azure CLI, PowerShell, or ARM templates. The process involves selecting a region, pricing tier, compute size, and configuring network settings.

Azure CLI Example:


az sql mi create --resource-group <resource-group-name> \
    --name <managed-instance-name> \
    --location <location> \
    --subnet <subnet-id> \
    --admin-user <admin-login> \
    --admin-password <admin-password> \
    --tier GeneralPurpose \
    --family Gen5 \
    --capacity 8 \
    --collation SQL_Latin1_General_CP1_CI_AS

Connect to an Instance

Connecting to SQL Managed Instance typically involves using SQL Server Management Studio (SSMS), Azure Data Studio, or other SQL Server compatible tools. Ensure your client machine has network connectivity to the instance's VNet.

You can connect using:

Public Endpoint: If enabled, allows connection over the internet (requires specific firewall configuration).
Private Endpoint: Recommended for secure connections within your VNet.
VNet-to-VNet or VPN Gateway: For connections from on-premises or other VNets.

Administration

Security

Implementing robust security measures is crucial. This includes configuring network security groups, firewalls, managing access with Azure AD, and using Transparent Data Encryption.

Refer to the Azure SQL Managed Instance security best practices.

Performance Tuning

Optimize your instance for peak performance by monitoring query execution plans, using appropriate indexes, and tuning database configurations. Azure provides tools like Query Performance Insight.

Backup and Restore

SQL Managed Instance automatically performs daily full backups, with differential and transaction log backups taken frequently. You can configure backup storage redundancy and retention policies.

Restore operations are managed through the Azure portal or T-SQL commands.

API Reference

Explore the Azure SQL Managed Instance REST API for programmatic management of your instances. This includes operations for creating, updating, deleting, and querying instances.

API Endpoint	HTTP Method	Description
`/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/managedInstances`	`PUT`	Creates or updates a managed instance.
`/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/managedInstances/{managedInstanceName}`	`GET`	Retrieves a managed instance.
`/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/managedInstances/{managedInstanceName}`	`DELETE`	Deletes a managed instance.
`/subscriptions/{subscriptionId}/providers/Microsoft.Sql/locations/{location}/managedInstanceAvailableEditions`	`GET`	Lists available service tiers and hardware generations for managed instances in a location.

For detailed information, please visit the Azure REST API documentation.

Tutorials

Follow these tutorials to learn how to perform common tasks:

Troubleshooting

Find solutions to common issues you might encounter with Azure SQL Managed Instance. This section covers performance problems, connectivity issues, and management errors.

Consult the Azure SQL Managed Instance troubleshooting guide for detailed assistance.