AZ-305 ยท Relational data design
Picking a relational service: SQL Database, Managed Instance, SQL Server on VMs or open source
Azure SQL Database vs Azure SQL Managed Instance vs SQL Server on Azure VMs: compatibility, instance-level features, management effort, open-source alternatives (Azure Database for PostgreSQL / MySQL flexible server).
Key points
- Azure SQL Database (PaaS): database or elastic pool, serverless, Hyperscale up to 128 TB; no SQL Server Agent, CLR or cross-database queries.
- Azure SQL Managed Instance (PaaS): instance in your VNet, near-full compatibility (Agent, CLR, Database Mail, linked servers, native restore); lift-and-shift; up to 16 TB.
- SQL Server on Azure VMs (IaaS): 100% compatibility, OS access, FILESTREAM.
- Open source: PostgreSQL or MySQL flexible server.
Source: https://learn.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview
Practice questions
Which Azure SQL option gives you administrative access to the operating system that runs SQL Server?
- Azure SQL Database
- Azure SQL Managed Instance
- SQL Server on Azure VMs
- Azure SQL Database Hyperscale
Show answer
Answer: C. SQL Server on Azure VMs
SQL Server on Azure VMs is IaaS: you control the Windows or Linux guest and the SQL Server instance. Azure SQL Database and Azure SQL Managed Instance are PaaS services where Microsoft manages the OS, so you never get OS-level access there.
Which capabilities are available in Azure SQL Managed Instance but NOT in Azure SQL Database?
- SQL Server Agent
- Database Mail
- FILESTREAM
- Common language runtime (CLR)
- Elastic query
Show answer
Answer: A. SQL Server Agent, B. Database Mail, D. Common language runtime (CLR)
SQL Server Agent, Database Mail and CLR integration are supported in Managed Instance and missing from SQL Database (which offers elastic jobs instead of Agent). FILESTREAM is supported in neither service, so it needs SQL Server on a VM. Elastic query is the reverse case: a SQL Database feature that Managed Instance does not offer, because it has native cross-database queries.
Because Azure SQL Managed Instance has near-full SQL Server compatibility, an application that stores documents with FILESTREAM can move to it without changes.
Show answer
Answer: False
False. FILESTREAM is one of the few SQL Server features that Managed Instance does not support (Azure SQL Database lacks it too). An application that depends on FILESTREAM either needs SQL Server on Azure VMs or must be changed, for example to keep files in Blob storage.
Practice 8 questions on this topic in Bitzu
Short lessons, instant explanations and spaced review of what you get wrong. Free on iOS and Android.
