Azure SQL database vs Azure SQL Managed Instance

Question

I want to understand the difference between Azure SQL database and Azure SQL Managed Instance. On a brief level, I understood that Azure SQL managed instance is to be used when we need compatibility with on prem SQL database, but didnt get any good satisfactory explanation around the two offerings. Also, in what instance to use each.

Answer 1

Feature comparison is the way to go.

From my experience with SQL MI, Scaling options are not very flexible in terms of cost for multi tenant scenarios. For example, the disk I/O and throughput are driven by the file size, so you need to preallocate the disks to make it fall under certain premium disk category to get most out of it, if you want to stay on General Purpose service tier. On the other hand Azure SQL databases with elastic pools would scale truly on demand.

The second important consideration for us is DR - SQL MI supports failover group, which gets best of RPO,RTO comparing to active geo replication of Azure SQL databases at a lesser cost. So finally, it depends on your requirements.

Answer 2

About Azure SQL Database.

Azure SQL Database is a relational database-as-a-service hosted in Azure that falls into the industry category of Platform-as-a-Service (PaaS).

Best for modern cloud applications that want to use the latest stable SQL Server features and have time constraints in development and marketing. A fully managed SQL Server database engine, based on the latest stable Enterprise Edition of SQL Server. SQL Database has two deployment options built on standardized hardware and software that is owned, hosted, and maintained by Microsoft.

When using SQL Database, you pay-as-you-go with options to scale up or out for greater power with no interruption. SQL Database has some additional features that are not available in SQL Server, such as built-in high availability (local redundancy, zone redundancy and Read Scale Out for Premium tiers), artificial intelligence (Automatic Tuning, automatic creation and dropping of indexes, automatic query plan correction), management (automatic backups, automatic updates/upgrades), security (Intelligent Threat Detection), and Data Governance (data classification).

Azure SQL Database offers the following deployment options:

1. As a single database with its own set of resources managed via a logical SQL server. A single database is similar to a contained database in SQL Server.
2. An elastic pool, which is a collection of databases with a shared set of resources managed via a logical server. Single databases can be moved into and out of an elastic pool. This option is optimized for multi-tenant SaaS application pattern. Elastic pools provide a cost-effective solution for managing the performance of multiple databases that have variable usage patterns.

About Managed Instance

Azure SQL Managed Instance falls into the industry category of Platform-as-a-Service (PaaS), and is best for most migrations from SQL Server on-premise to Azure, Azure SQL MAnaged instance is lift-and-shift ready.

Best for new applications or existing on-premises applications that want to use the latest stable SQL Server features and that are migrated to the cloud with minimal changes. An instance of SQL Managed Instance is similar to an instance of the Microsoft SQL Server database engine offering shared resources for databases and additional instance-scoped features.

SQL Managed Instance supports database migration from on-premises with minimal to no database change. This option provides all of the PaaS benefits of Azure SQL Database but adds capabilities that were previously only available in SQL Server VMs. This includes a native virtual network and near 100% compatibility with on-premises SQL Server. Instances of SQL Managed Instance provide full SQL Server access and feature compatibility for migrating SQL Servers to Azure.

Azure SQL Database provides less compatibility with SQL Server than Azure SQL Managed Instance.