Features and Capabilities of SQL Managed Instance enabled by Azure Arc

Grein
09/19/2024

SQL Managed Instance enabled by Azure Arc share a common code base with the latest stable version of SQL Server. Most of the standard SQL language, query processing, and database management features are identical. The features that are common between SQL Server and SQL Database or SQL Managed Instance are:

Language features - Control of flow language keywords, Cursors, Data types, DML statements, Predicates, Sequence numbers, Stored procedures, and Variables.
Database features - Automatic tuning (plan forcing), Change tracking, Database collation, Contained databases, Contained users, Data compression, Database configuration settings, Online index operations, Partitioning, and Temporal tables (see getting started guide).
Security features - Application roles, Dynamic data masking (Get started with SQL Database dynamic data masking with the Azure portal), Row Level Security
Multi-model capabilities - Graph processing, JSON data, OPENXML, Spatial, OPENJSON, and XML indexes.

RDBMS High Availability

Feature	SQL Managed Instance enabled by Azure Arc
Always On failover cluster instance¹	Not Applicable. Similar capabilities available.
Always On availability groups	Business Critical service tier.
Basic availability groups	Not Applicable. Similar capabilities available.
Minimum replica commit availability group	Business Critical service tier.
Clusterless availability group	Yes
Backup database	Yes - `COPY_ONLY` See BACKUP - (Transact-SQL)
Backup compression	Yes
Backup mirror	Yes
Backup encryption	Yes
Back up to Azure to (back up to URL)	Yes
Database snapshot	Yes
Fast recovery	Yes
Hot add memory and CPU	Yes
Log shipping	Not currently available.
Online page and file restore	Yes
Online indexing	Yes
Online schema change	Yes
Resumable online index rebuilds	Yes

¹ In the scenario where there is a pod failure, a new SQL Managed Instance will start up and re-attach to the persistent volume containing your data. Learn more about Kubernetes persistent volumes here.

RDBMS Scalability and Performance

Feature	SQL Managed Instance enabled by Azure Arc
Columnstore	Yes
Large object binaries in clustered columnstore indexes	Yes
Online nonclustered columnstore index rebuild	Yes
In-Memory OLTP	Yes
Persistent Main Memory	Yes
Table and index partitioning	Yes
Data compression	Yes
Resource Governor	Yes
Partitioned Table Parallelism	Yes
NUMA Aware and Large Page Memory and Buffer Array Allocation	Yes
IO Resource Governance	Yes
Delayed Durability	Yes
Automatic Tuning	Yes
Batch Mode Adaptive Joins	Yes
Batch Mode Memory Grant Feedback	Yes
Interleaved Execution for Multi-Statement Table Valued Functions	Yes
Bulk insert improvements	Yes

RDBMS Security

Feature	SQL Managed Instance enabled by Azure Arc
Row-level security	Yes
Always Encrypted	Yes
Always Encrypted with Secure Enclaves	No
Dynamic data masking	Yes
Basic auditing	Yes
Fine grained auditing	Yes
Transparent database encryption	Yes
User-defined roles	Yes
Contained databases	Yes
Encryption for backups	Yes
SQL Server Authentication	Yes
Microsoft Entra authentication	No
Windows Authentication	Yes

RDBMS Manageability

Feature	SQL Managed Instance enabled by Azure Arc
Dedicated administrator connection	Yes
PowerShell scripting support	Yes
Support for data-tier application component operations - extract, deploy, upgrade, delete	Yes
Policy automation (check on schedule and change)	Yes
Performance data collector	Yes
Standard performance reports	Yes
Plan guides and plan freezing for plan guides	Yes
Direct query of indexed views (using NOEXPAND hint)	Yes
Automatically maintain indexed views	Yes
Distributed partitioned views	Yes
Parallel indexed operations	Yes
Automatic use of indexed view by query optimizer	Yes
Parallel consistency check	Yes

Programmability

Feature	SQL Managed Instance enabled by Azure Arc
JSON	Yes
Query Store	Yes
Temporal	Yes
Native XML support	Yes
XML indexing	Yes
MERGE & UPSERT capabilities	Yes
Date and Time datatypes	Yes
Internationalization support	Yes
Full-text and semantic search	No
Specification of language in query	Yes
Service Broker (messaging)	Yes
Transact-SQL endpoints	Yes
Graph	Yes
Machine Learning Services	No
PolyBase	No

Tools

SQL Managed Instance enabled by Azure Arc supports various data tools that can help you manage your data.

Tool	SQL Managed Instance enabled by Azure Arc
Azure portal	Yes
Azure CLI	Yes
Azure Data Studio	Yes
Azure PowerShell	No
BACPAC file (export)	Yes
BACPAC file (import)	Yes
SQL Server Data Tools (SSDT)	Yes
SQL Server Management Studio (SSMS)	Yes
SQL Server PowerShell	Yes
SQL Server Profiler	Yes

At this time, use the insiders build of Azure Data Studio.

Unsupported Features & Services

The following features and services are not available for SQL Managed Instance enabled by Azure Arc.

Area	Unsupported feature or service
Database engine	Merge replication
	Stretch DB
	Distributed query with 3rd-party connections
	Linked Servers to data sources other than SQL Server and Azure SQL products
	System extended stored procedures (XP_CMDSHELL, etc.)
	FileTable, FILESTREAM
	CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set
	Buffer Pool Extension
SQL Server Agent	SQL Server agent is supported but the following specific capabilities are not supported: Subsystems (CmdExec, PowerShell, Queue Reader, SSIS, SSAS, SSRS), Alerts, Managed Backup
High Availability	Database mirroring
Security	Extensible Key Management
	AD Authentication for Linked Servers
	AD Authentication for Availability Groups (AGs)

Deila með