Feature comparison of Azure SQL Database, Azure SQL Managed Instance and SQL Server on a VM

Published Feb 11 2022 06:54 AM 5,441 Views
Microsoft

When migrating from on-premises to the Azure cloud, the choice of the SQL service can be challenging. The options available, the differences between them, and the implications of choosing each are often difficult to grasp.
As such, this blog series aims to help guide you through the journey of choosing the service, the service tiers within this, as well as help with migration options. In this first blog post, we will start with a feature comparison of the 3 services that usually constitute the migration choices: Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VM.

The below article was inspired by this comparison. However, as it was missing the SQL Server on Azure VM option, I decided that adding it will provide a more complete view of the options.

Of course, when choosing, besides a feature comparison, we need to look at other factors, such as performance, cost, manageability, etc. For example, the performance of an Azure SQL Database can be more variable due to its multitenant architecture, compared to the SQL on a VM or Managed Instance options where you have a dedicated host.

Below is a feature comparison of Azure SQL Database, Azure SQL Managed Instance, and SQL Server on a VM. Cloud technologies are continuously changing, and as such this comparison is true as of its writing.

Feature 

Azure SQL Database 

Azure SQL Managed Instance 

SQL Server on Azure VM 

Updates and patching 

Automated patching and version updates. 

Automated patching and version updates. 

Only Windows and SQL Server updates marked as Important or Critical are installed. Other SQL Server updates, such as service packs and cumulative updates that are not marked as Important or Critical, must be installed manually. 

Always Encrypted 

Yes - see Cert store and Key vault 

Yes - see Cert store and Key vault 

Yes 

Always On Availability Groups 

99.99-99.995% availability is guaranteed for every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database 

99.99.% availability is guaranteed for every database and can't be managed by user. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. Use Auto-failover groups to configure a secondary SQL Managed Instance in another region. SQL Server instances and SQL Database can't be used as secondaries for SQL Managed Instance. 

High-availability is built-in only for the VM. For the SQL Server running inside the VM, the user needs to design and maintain the High-Availability setup. For options - see here. 

Attach a database 

No 

No 

Yes 

Auditing 

Yes. The .xel log files are stored in Azure Blob storage. 

Yes. The .xel log files are stored in Azure Blob storage. 

Yes. Events are stored on the file system or Windows event logs. 

Azure Active Directory (Azure AD) authentication 

Yes. Azure AD users only. 

Yes. Including server-level Azure AD logins. 

No 

BACKUP command 

No, only system-initiated automatic backups - see Automated backups 

Yes, user initiated copy-only backups to Azure Blob storage (automatic system backups can't be initi... 

Yes. Automated Backup (starting SQL Server 2014), and Manual backups are available. 

Built-in functions 

Most - see individual functions 

Yes - see Stored procedures, functions, triggers differences 

Yes 

BULK INSERT statement 

Yes, but just from Azure Blob storage as a source. 

Yes, but just from Azure Blob Storage as a source - see differences. 

Yes 

Certificates and asymmetric keys 

Yes, without access to file system for BACKUP and CREATE operations. 

Yes, without access to file system for BACKUP and CREATE operations - see certificate differences. 

Yes, with access to file system for BACKUP and CREATE operations. 

Change data capture - CDC 

Yes (Preview) for S3 tier and above. Basic, S0, S1, S2 are not supported. 

Yes 

Yes 

Collation - server/instance 

Yes, is set at the database level at creation time and can’t be updated later. 

Yes, can be set when the instance is created and can't be updated later. 

Yes 

Columnstore indexes 

Yes - Premium tier, Standard tier - S3 and above, General Purpose tier, Business Critical, and Hyper... 

Yes 

Yes 

Common language runtime - CLR 

No 

Yes, but without access to file system in CREATE ASSEMBLY statement - see CLR differences 

Yes 

Credentials 

Yes, but only database scoped credentials. 

Yes, but only Azure Key Vault and SHARED ACCESS SIGNATURE are supported - see details 

Yes 

Cross-database/three-part name queries 

No - see Elastic queries 

Yes 

Yes 

Cross-database transactions 

No 

Yes, within the instance. See Linked server differences for cross-instance queries. 

Yes 

Database mail - DbMail 

No 

Yes 

Yes 

Database mirroring 

No 

No 

Deprecated and not recommended to use 

Database snapshots 

No 

No 

Yes 

DBCC statements 

Most - see individual statements 

Yes - see DBCC differences 

Yes 

DDL statements 

Most - see individual statements 

Yes - see T-SQL differences 

Yes 

DDL triggers 

Database only 

Yes 

Yes 

Distributed partition views 

No 

Yes 

Yes 

Distributed transactions - MS DTC 

No - see Elastic transactions 

Cross-database and cross-instance distributed transactions are supported. 

Yes 

DML triggers 

Most - see individual statements 

Yes 

Yes 

DMVs 

Most - see individual DMVs 

Yes - see T-SQL differences 

Yes 

Elastic query (in public preview) 

Yes, with required RDBMS type. 

No 

No 

Event notifications 

No - see Alerts 

No 

Yes 

Expressions 

Yes 

Yes 

Yes 

Extended events (XEvent) 

Some - see Extended events in SQL Database 

Yes - see Extended events differences 

Yes 

Extended stored procedures 

No 

No 

Yes 

Files and file groups 

Primary file group only 

Yes. File paths are automatically assigned, and the file location can't be specified in ALTER DATABASE ADD FILE statement. 

Yes 

Filestream 

No 

No 

Yes 

Full-text search (FTS) 

Yes, but third-party word breakers are not supported 

Yes, but third-party word breakers are not supported 

Yes 

Functions 

Most - see individual functions 

Yes - see Stored procedures, functions, triggers differences 

Yes 

In-memory optimization 

Yes in Premium and Business Critical service tiers. Limited support for non-persistent In-Memory OLTP objects such as memory-optimized table variables in Hyperscale service tier. 

Limited support for non-persistent In-Memory OLTP objects such as memory-optimized table variables in Hyperscale service tier. 

At the time of writing you get one gigabyte of storage for every 125 DTUs or eDTUs.  

Yes in Business Critical service tier 

At the time of writing, Max In-Memory OLTP memory is defined per vCore and series and is between 0.8-4,5 GB per vCore 

Yes 

Language elements 

Most - see individual elements 

Yes - see T-SQL differences 

Yes 

Ledger 

Yes 

No 

In SQL Server 2022 (Preview) 

Linked servers 

No - see Elastic query 

Yes. Only to SQL Server and SQL Database without distributed transactions. 

Yes 

Linked servers that read from files (CSV, Excel) 

No. Use BULK INSERT or OPENROWSET as an alternative for CSV format. 

No. Use BULK INSERT or OPENROWSET as an alternative for CSV format. Track these requests on SQL Managed Instance feedback item 

Yes 

Log shipping 

High availability is included with every database. Disaster recovery is discussed in Overview of business continuity. 

Natively built-in as a part of Azure Database Migration Service (DMS) migration process. Natively built for custom data migration projects as an external Log Replay Service (LRS). 

Not available as High availability solution, because other High availability methods are included with every database and it is not recommended to use Log-shipping as HA alternative. Disaster recovery is discussed in Overview of business continuity. Not available as a replication mechanism between databases - use secondary replicas on Business Critical tier, auto-failover groups, or transactional replication as the alternatives. 

  

Yes 

Logins and users 

Yes, but CREATE and ALTER login statements do not offer all the options (no Windows and server-level Azure Active Directory logins). EXECUTE AS LOGIN is not supported - use EXECUTE AS USER instead. 

Yes, with some differences. Windows logins are not supported and they should be replaced with Azure ... 

Yes 

Minimal logging in bulk import 

No, only Full Recovery model is supported. 

No, only Full Recovery model is supported. 

Yes 

Modifying system data 

No 

Yes 

Yes 

OLE Automation 

No 

No 

Yes 

OPENDATASOURCE 

No 

Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences 

Yes 

OPENQUERY 

No 

Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences 

Yes 

OPENROWSET 

Yes, only to import from Azure Blob storage. 

Yes, only to SQL Database, SQL Managed Instance and SQL Server, and to import from Azure Blob storag... 

Yes 

Operators 

Most - see individual operators 

Yes - see T-SQL differences 

Yes 

PolyBase 

No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function or use an... 

No. You can query data in the files placed on Azure Blob Storage using OPENROWSET function, a linked server that references a serverless SQL pool in Synapse Analytics, or an external table (in public preview) that references a serverless SQL pool in Synapse Analytics or SQL Server. 

Yes 

Query Notifications 

No 

Yes 

Yes 

Machine Learning Services (Formerly R Services) 

No 

Yes, see Machine Learning Services in Azure SQL Managed Instance 

Yes 

Recovery models 

Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available. 

Only Full Recovery that guarantees high availability is supported. Simple and Bulk Logged recovery models are not available. 

Simple, Full and Bulk logged Recovery models are supported. 

Resource governor 

No 

Yes 

Yes 

RESTORE statements 

No 

Yes, with mandatory FROM URL options for the backups files placed on Azure Blob Storage. See Restore... 

Yes 

Restore database from backup 

From automated backups only - see SQL Database recovery 

From automated backups - see SQL Database recovery and from full backups placed on Azure Blob Storage - see Backup differences 

Supports restore from local storage as well as Azure Blob Storage. 

Restore database to SQL Server 

No. Use BACPAC or BCP instead of native restore. 

Restore from lower versions to MI or from MI backups are allowed. In SQL 2022 there is possibility to configure – preview subject to change. 

Restore from lower and same version are allowed. 
With SQL 2022 some possibilities with managed instance might be introduced. 

Semantic search 

No 

No 

Yes 

Service Broker 

No 

Yes, but only within the instance. If you are using remote Service Broker routes, try to consolidate... 

Yes 

Server configuration settings 

No 

Yes - see T-SQL differences 

Yes 

Set statements 

Most - see individual statements 

Yes - see T-SQL differences 

Yes 

SQL Server Agent 

No - see Elastic jobs (preview) 

Yes - see SQL Server Agent differences The key difference here is that the agent cannot access external resources. 

Yes 

SQL Server Auditing 

No - see SQL Database auditing 

Yes with some limitations 

Yes - see Auditing differences 

Yes 

System stored functions 

Most - see individual functions 

Yes - see Stored procedures, functions, triggers differences 

Yes 

System stored procedures 

Some - see individual stored procedures 

Yes - see Stored procedures, functions, triggers differences 

Yes 

System tables 

Some - see individual tables 

Yes - see T-SQL differences 

Yes 

System catalog views 

Some - see individual views 

Yes - see T-SQL differences 

Yes 

TempDB 

Yes. 32-GB size per core for every database. 

Yes. 24-GB size per vCore for entire GP tier and limited by instance size on BC tier 

Yes 

Temporary tables 

Local and database-scoped global temporary tables 

Local and instance-scoped global temporary tables 

Yes 

Time zone choice 

No 

Yes, and it must be configured when the SQL Managed Instance is created. 

 Yes, from the OS settings 

Trace flags 

No 

Yes, but only limited set of global trace flags. See DBCC differences 

Yes 

Transactional Replication 

Yes, Transactional and snapshot replication subscriber only 

Yes, in public preview. See the constraints here. 

Yes 

Transparent data encryption (TDE) 

Yes - General Purpose, Business Critical, and Hyperscale (in preview) service tiers only 
(default, can be disabled) 

Yes (default, can be disabled) 

Yes 

Windows authentication 

No 

No 

Yes 

Windows Server Failover Clustering 

No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. 

No. Other techniques that provide high availability are included with every database. Disaster recovery is discussed in Overview of business continuity with Azure SQL Database. 

Yes 

Stay tuned for the next post!

2 Comments
Co-Authors
Version history
Last update:
‎Feb 17 2022 01:09 AM
Updated by: