Features and Capabilities of SQL Managed Instance enabled by Azure Arc
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 instance1 | Not Applicable. Similar capabilities available. |
| Always On availability groups2 | Business Critical service tier. |
| Basic availability groups 2 | Not Applicable. Similar capabilities available. |
| Minimum replica commit availability group 2 | 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 |
1 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) |
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for