Feature comparison of Azure SQL Database, Azure SQL Managed Instance and SQL Server on a VM
Published Feb 11 2022 06:54 AM 8,258 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 file system or Windows event logs.

Azure Active Directory (Azure AD) authentication

Yes. Azure AD users only.

Yes. Including server-level Azure AD logins.

Yes, SQL Server 2022 (16.x) introduces support for Azure Active Directory (Azure AD) authentication, on both Windows and Linux on-premises, and SQL Server on Windows Azure VMs.

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, for S3 tier and above. Basic, S0, S1, S2 are not supported.

Yes

Yes

Collation - server/instance

Yes, it 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 - columnstore indexes are available in Azure SQL Database Premium tiers, Standard tiers - S3 and...

Yes. See constraints here.

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 the 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.

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 (16.x)

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...

Yes, in addition to being able to 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 the 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, Cross-instance service broker message exchange is supported only between Azure SQL Managed Inst...

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, for example, network shares via robocopy.

Yes

SQL Server Auditing

No - see SQL Database auditing

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. Limited to 24 GB/vCore (96 - 1,920 GB) and currently available instance storage size.

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 as push subscriber only

Yes, see the constraints here.

Yes

Transparent data encryption (TDE)

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

Yes, enabled by 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!

3 Comments
Co-Authors
Version history
Last update:
‎Feb 02 2023 08:43 AM
Updated by: