This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
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. |
|
|
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. |
||
|
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. |
No |
|
|
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 (Preview) for S3 tier and above. Basic, S0, S1, S2 are not supported. |
Yes |
Yes |
|
|
No, default server collation SQL_Latin1_General_CP1_CI_AS is always used. |
Yes, can be set when the instance is created and can't be updated later. |
Yes |
|
|
Yes |
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 |
|
|
No - see Elastic transactions (distributed transactions I think – not elastic) Cross DB transactions inside the MI do not need TDC and are supported |
Yes |
||
|
Most - see individual statements |
Yes |
Yes |
|
|
Most - see individual DMVs |
Yes |
||
|
Yes, with required RDBMS type. |
No |
No |
|
|
No |
Yes |
||
|
Yes |
Yes |
Yes |
|
|
Yes |
|||
|
No |
No |
Yes |
|
|
Primary file group only |
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 |
|
|
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 (Preview) |
|
|
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 |
||
|
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 |
||
|
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 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. |
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. 24-GB size per vCore for entire GP tier and limited by instance size on BC tier |
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 |
|||
|
Yes - General Purpose, Business Critical, and Hyperscale (in preview) service tiers only |
Yes (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!