- Home
- Microsoft FastTrack
- FastTrack for Azure
- Feature comparison of Azure SQL Database, Azure SQL Managed Instance and SQL Server on a VM
- Subscribe to RSS Feed
- Mark as New
- Mark as Read
- Bookmark
- Subscribe
- Printer Friendly Page
- Report Inappropriate Content
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. |
|
Yes - see Cert store and Key vault |
Yes - see Cert store and Key vault |
Yes |
|
|
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. |
|
|
No |
No |
Yes |
|
|
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. |
|
|
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. |
|
|
No, only system-initiated automatic backups - see Automated backups |
Yes. Automated Backup (starting SQL Server 2014), and Manual backups are available. |
||
|
Most - see individual functions |
Yes - see Stored procedures, functions, triggers differences |
Yes |
|
|
Yes, but just from Azure Blob storage as a source. |
Yes, but just from Azure Blob Storage as a source - see differences. |
Yes |
|
|
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. |
|
|
Yes, for S3 tier and above. Basic, S0, S1, S2 are not supported. |
Yes |
Yes |
|
|
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 |
|
|
Yes. See constraints here. |
Yes |
||
|
No |
Yes, but without access to file system in CREATE ASSEMBLY statement - see CLR differences |
Yes |
|
|
Yes, but only Azure Key Vault and SHARED ACCESS SIGNATURE are supported - see details |
Yes |
||
|
Yes |
Yes |
||
|
No |
Yes, within the instance. See Linked server differences for cross-instance queries. |
Yes |
|
|
No |
Yes |
Yes |
|
|
No |
Deprecated and not recommended to use |
||
|
No |
No |
Yes |
|
|
Most - see individual statements |
Yes |
||
|
Most - see individual statements |
Yes |
||
|
Database only |
Yes |
Yes |
|
|
No |
Yes |
Yes |
|
|
Cross-database and cross-instance distributed transactions are supported. |
Yes |
||
|
Most - see individual statements |
Yes |
Yes |
|
|
Most - see individual DMVs |
Yes |
||
|
Yes, with the required RDBMS type. |
No |
No |
|
|
No |
Yes |
||
|
Yes |
Yes |
Yes |
|
|
Yes |
|||
|
No |
No |
Yes |
|
|
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 |
|
|
No |
Yes |
||
|
Yes, but third-party word breakers are not supported |
Yes |
||
|
Most - see individual functions |
Yes - see Stored procedures, functions, triggers differences |
Yes |
|
|
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 |
|
|
Most - see individual elements |
Yes |
||
|
Yes |
No |
In SQL Server 2022 (16.x) |
|
|
Yes. Only to SQL Server and SQL Database without distributed transactions. |
Yes |
||
|
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 |
|
|
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 |
|
|
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 |
||
|
No, only Full Recovery model is supported. |
No, only Full Recovery model is supported. |
Yes |
|
|
No |
Yes |
Yes |
|
|
No |
No |
Yes |
|
|
No |
Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences |
Yes |
|
|
No |
Yes, only to SQL Database, SQL Managed Instance and SQL Server. See T-SQL differences |
Yes |
|
|
Yes, only to import from Azure Blob storage. |
Yes |
||
|
Most - see individual operators |
Yes |
||
|
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 |
||
|
No |
Yes |
Yes |
|
|
No |
Yes, see Machine Learning Services in Azure SQL Managed Instance |
Yes |
|
|
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. |
|
|
No |
Yes |
Yes |
|
|
No |
Yes |
||
|
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. |
||
|
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. |
|
|
No |
No |
Yes |
|
|
No |
Yes |
||
|
No |
Yes |
||
|
Most - see individual statements |
Yes |
||
|
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 |
||
|
Yes |
|||
|
Most - see individual functions |
Yes - see Stored procedures, functions, triggers differences |
Yes |
|
|
Some - see individual stored procedures |
Yes - see Stored procedures, functions, triggers differences |
Yes |
|
|
Some - see individual tables |
Yes |
||
|
Some - see individual views |
Yes |
||
|
Yes. Limited to 24 GB/vCore (96 - 1,920 GB) and currently available instance storage size. |
Yes |
||
|
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 |
|
No |
Yes, but only limited set of global trace flags. See DBCC differences |
Yes |
|
|
Yes, transactional and snapshot replication as push subscriber only |
Yes |
||
|
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 |
|
|
No |
No |
Yes |
|
|
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.