Relational Data: Choosing Your SQL Platform
SQL Database, SQL Managed Instance, SQL on VMs, PostgreSQL, or MySQL? The right choice depends on compatibility needs, management overhead, and migration complexity.
Choosing your relational data platform
Think of it like choosing where to live. A fully managed apartment (SQL Database) means someone else handles maintenance — but you can’t knock down walls. A managed house (SQL Managed Instance) gives you more control while still handling the plumbing. Building your own house (SQL on VMs) means total freedom — and total responsibility.
Azure offers five main relational platforms: Azure SQL Database, SQL Managed Instance, SQL Server on VMs, Azure Database for PostgreSQL, and Azure Database for MySQL. The exam tests your ability to pick the right one based on requirements.
The Azure SQL decision tree
This is the most important comparison table in AZ-305 data storage:
| Factor | Azure SQL Database | SQL Managed Instance | SQL Server on Azure VMs |
|---|---|---|---|
| Service model | PaaS — fully managed database | PaaS — managed instance (near 100% SQL Server compatibility) | IaaS — full SQL Server on a VM you manage |
| SQL Server compatibility | ~95% — some features unavailable (SQL Agent, cross-DB queries) | ~99% — SQL Agent, cross-DB queries, CLR, linked servers | 100% — full SQL Server, any version/edition |
| OS access | None | None | Full — RDP into the VM |
| Patching | Automatic | Automatic | You manage (or use Azure Update Manager) |
| High availability | Built-in (99.99% SLA) | Built-in (99.99% SLA) | You configure (Always On AG, failover clustering) |
| Max database size | 100 TB (Hyperscale) | 16 TB | Disk-limited (64 TB+) |
| Cross-database queries | Only via elastic query (limited) | Yes — native | Yes — native |
| SQL Agent jobs | No — use Elastic Jobs or Logic Apps | Yes | Yes |
| Migration path | Requires app changes if using unsupported features | Near-seamless lift-and-shift from on-prem SQL | Direct lift-and-shift, no changes needed |
| Cost model | DTU or vCore, serverless option | vCore only | VM cost + SQL license (or Azure Hybrid Benefit) |
| Best for | New cloud-native apps, microservices, single databases | Migrating on-prem SQL with minimal changes | Apps needing full OS/SQL control, legacy versions |
🏗️ Priya’s migration decision: GlobalTech has 200+ SQL Server databases across 15 on-prem servers. Many use SQL Agent jobs, cross-database queries, and CLR assemblies. Priya recommended SQL Managed Instance because:
- Near 100% compatibility means minimal application changes
- SQL Agent, cross-DB queries, and CLR all work natively
- Azure manages patching and HA — reducing ops burden during migration
- Azure Database Migration Service can do online migration (near-zero downtime)
Exam tip: The magic words that point to each option
SQL Database: “new application,” “single database,” “serverless,” “hyperscale,” “microservice per database”
SQL Managed Instance: “migrate from on-prem,” “SQL Agent jobs,” “cross-database queries,” “CLR,” “linked servers,” “minimal code changes”
SQL on VMs: “specific SQL version required,” “OS-level access needed,” “third-party software on same server,” “SSIS/SSRS on same machine,” “full control”
PostgreSQL and MySQL on Azure
Not everything runs on SQL Server. Azure provides fully managed options for open-source databases:
| Factor | Azure Database for PostgreSQL (Flexible Server) | Azure Database for MySQL (Flexible Server) |
|---|---|---|
| Engine | PostgreSQL (community edition) | MySQL (community edition) |
| Best for | Complex queries, JSON/GIS data, extensions, analytics | Web applications, CMS (WordPress, Drupal), simple CRUD |
| Extensions | Rich — PostGIS, pg_cron, pgvector (AI embeddings) | Limited extension model |
| HA options | Zone-redundant HA with automatic failover | Zone-redundant HA with automatic failover |
| Read replicas | Up to 5 replicas, cross-region supported | Up to 5 replicas, cross-region supported |
| Compute tiers | Burstable, General Purpose, Memory Optimized (stop/start supported) | Burstable, General Purpose, Memory Optimized (stop/start supported) |
| Migration from on-prem | Azure Database Migration Service, pg_dump/restore | Azure Database Migration Service, mysqldump |
🚀 Marcus’s platform choice: NovaSaaS’s multi-tenant platform uses PostgreSQL Flexible Server because:
- Rich JSON support (JSONB) for tenant-specific schema flexibility
- PostGIS extension for their location-based features
- pgvector extension for AI-powered semantic search
- Row-level security for multi-tenant data isolation
Design decision: When to choose open-source over SQL Server
Choose PostgreSQL or MySQL when:
- The application is already built on that engine (don’t migrate databases for fun)
- You need specific extensions (PostGIS, pgvector, TimescaleDB)
- Cost sensitivity — open-source licensing is cheaper (no SQL Server license)
- Cross-platform portability matters (avoid vendor lock-in)
Choose Azure SQL when:
- Existing SQL Server investment (tools, expertise, stored procedures)
- Deep Microsoft ecosystem integration (Power BI direct query, SSRS)
- .NET application stack (Entity Framework, ADO.NET optimised for SQL Server)
Knowledge check
🏗️ GlobalTech is migrating an on-premises SQL Server 2019 application that uses SQL Agent jobs for nightly ETL, cross-database queries joining data from 3 databases, and CLR stored procedures for custom business logic. The goal is minimal application changes. Which platform should Priya recommend?
🚀 NovaSaaS is building a new multi-tenant SaaS platform. The application needs JSONB columns for flexible tenant schemas, PostGIS for location features, and pgvector for AI-powered search. The team has strong PostgreSQL expertise. Which platform should Marcus recommend?
🎬 Video coming soon
Next up: You’ve chosen your platform — now let’s optimise it — Database Performance & Scalability.