This chapter covers designing relational database solutions on Azure, focusing on Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs. It is critical for the AZ-305 exam, as data storage is a major domain (25-30% of questions), and relational databases are a core component. You will learn how to choose the right deployment option, design for high availability and disaster recovery, optimize performance through indexing and partitioning, and secure data. Expect 5-7 exam questions on relational database design, including scenario-based choices among PaaS, IaaS, and managed instance options.
Jump to a section
Imagine a large public library with millions of books. The library has a card catalog—a set of drawers filled with index cards, each listing a book's title, author, and location (section, shelf, and position). When a patron wants a book, they look up the card catalog to find the exact location, then walk directly to that shelf and retrieve the book. Without the card catalog, the patron would have to walk through every aisle, inspecting each book spine until they find the desired one—a process that could take hours. The card catalog is the library's index. Now, consider a library that stores books in multiple rooms (like database shards). The card catalog must indicate not only the shelf but also which room. If the catalog is outdated or missing cards, the patron might walk to the wrong room or never find the book. This mirrors how Azure SQL Database indexes work: they provide a direct path to data rows, avoiding full table scans. Clustered indexes physically order the data (like books arranged by Dewey Decimal), while nonclustered indexes are separate structures pointing to the data (like the card catalog). Just as a library might have multiple card catalogs (by title, author, subject), a table can have multiple nonclustered indexes. But each catalog takes physical space and must be maintained when new books arrive—similar to index maintenance overhead. If a library is very large, it might split into branches (database sharding), requiring a union catalog to locate books across branches—analogous to a distributed database index.
Overview of Azure Relational Database Options
Azure offers three primary relational database services: Azure SQL Database (fully managed PaaS), Azure SQL Managed Instance (PaaS with near 100% SQL Server compatibility), and SQL Server on Azure VMs (IaaS). The AZ-305 exam tests your ability to recommend the appropriate service based on requirements like compatibility, management overhead, scalability, and hybrid scenarios.
Azure SQL Database (PaaS)
Azure SQL Database is a fully managed relational database-as-a-service. It abstracts the underlying OS and SQL Server instance, providing built-in high availability, automatic backups, and patching. Key features: - Service tiers: General Purpose (balanced), Business Critical (low latency, high resilience), and Hyperscale (up to 100 TB, fast scaling). - Compute tiers: Provisioned (dedicated resources) or Serverless (auto-pause, cost effective for intermittent workloads). - Elastic pools: Shared resources among multiple databases, ideal for SaaS multi-tenant apps with varying usage. - Geo-replication: Active geo-replication (readable secondaries in different regions) and auto-failover groups (automatic failover at database or group level). - Backup retention: Configurable from 7 to 35 days (Long-Term Retention up to 10 years).
Azure SQL Managed Instance
SQL Managed Instance provides near 100% compatibility with on-premises SQL Server, including SQL Server Agent, CLR, Service Broker, and cross-database queries. It runs in a dedicated subnet of your VNet, enabling secure connectivity and integration with Azure services. Key features: - Instance-level features: Supports SQL Server Log Shipping, Database Mail, and linked servers. - Networking: Deployed in a VNet with a subnet delegated to Microsoft.Sql/managedInstances. Requires a route table and network security group. - Service tiers: General Purpose (up to 8 TB) and Business Critical (up to 4 TB, with in-memory OLTP). - Migration: Use Azure Database Migration Service (DMS) or native backup/restore.
SQL Server on Azure VMs (IaaS)
This option gives full control over the SQL Server instance and OS. You manage patching, backups, and high availability. It is best for lift-and-shift migrations with minimal changes. Key considerations: - Licensing: Use Azure Hybrid Benefit to reuse existing SQL Server licenses. - Storage: Premium SSD or Ultra Disk for high IOPS; configure tempdb on local SSD for performance. - High Availability: SQL Server Always On Availability Groups (requires multiple VMs and a load balancer) or Failover Cluster Instances (shared storage). - Disaster Recovery: Azure Site Recovery or log shipping to another region.
Choosing the Right Option
The exam presents scenarios with trade-offs. Use this decision framework: - Need full SQL Server compatibility (e.g., CLR, SQL Agent, cross-database queries)? Choose SQL Managed Instance or SQL on VM. - Want minimal management overhead? Choose Azure SQL Database (PaaS). - Need to run third-party software on the same VM? Choose SQL on VM. - Require massive scalability (over 4 TB) or fast scaling? Choose Azure SQL Database Hyperscale. - Have existing SQL Server licenses to save cost? Use Azure Hybrid Benefit with any option.
High Availability and Disaster Recovery
Designing for HA/DR is a key exam objective (AZ-305 objective 2.2). For Azure SQL Database: - Local HA: Built-in at all tiers (3 replicas in General Purpose, 4 in Business Critical). - Zone-redundant configuration: For Business Critical tier, replicas can be placed in different availability zones within a region. - Geo-replication: Asynchronous replication to a secondary region. Can be used for failover (manual or via auto-failover groups). - Auto-failover groups: Manage multiple databases as a group; provide read-write and read-only listener endpoints. Failover is automatic based on grace period (default 1 hour).
For SQL Managed Instance: - Local HA: Built-in with 3 replicas (General Purpose) or 4 (Business Critical). - Zone-redundant: Supported for Business Critical tier. - DR: Use auto-failover groups (preview for managed instance) or manual geo-restore.
For SQL on VM: - HA: Always On Availability Groups (requires Windows Server Failover Cluster, load balancer for listener). - DR: Azure Site Recovery or log shipping to another region.
Performance and Scalability
Indexing: Clustered indexes determine physical order; nonclustered indexes speed up queries but add overhead. Use columnstore indexes for analytics.
Partitioning: Table partitioning (horizontal) improves manageability and performance for large tables. In Azure SQL Database, use partition functions and schemes.
Sharding: Split data across multiple databases (e.g., using Elastic Database tools). Useful for SaaS applications.
Query Performance Insight: Built-in tool in Azure SQL Database to identify top resource-consuming queries.
Database Engine Tuning Advisor: Available in SQL Managed Instance and SQL on VM.
Security
Network security: Use VNet service endpoints (Azure SQL Database) or VNet injection (Managed Instance). For SQL on VM, use NSGs.
Authentication: Azure AD authentication (supported for all options). Managed identities for PaaS.
Encryption: Transparent Data Encryption (TDE) with service-managed or customer-managed keys (Azure Key Vault). Always Encrypted for column-level encryption.
Auditing: Azure SQL Auditing (for PaaS and Managed Instance) or SQL Server Audit (for SQL on VM).
Advanced Threat Protection: Built-in for Azure SQL Database and Managed Instance; for SQL on VM, use Azure Defender.
Migration Strategies
Azure Database Migration Service (DMS): Supports online (minimal downtime) and offline migrations for SQL Server, Oracle, MySQL, PostgreSQL, etc.
Backup and restore: Native backup (.bak) to Azure Blob Storage, then restore to SQL Managed Instance or SQL on VM.
Data Migration Assistant (DMA): Assess compatibility issues before migration.
SQL Server Integration Services (SSIS): Use Azure-SSIS Integration Runtime in Azure Data Factory.
Monitoring and Management
Azure Monitor: Collect metrics and logs. Use Log Analytics for query analysis.
SQL Insights: Pre-built workbooks for monitoring performance, wait stats, and deadlocks.
Automatic tuning: Azure SQL Database can automatically create/drop indexes and force plans based on query performance.
Elastic jobs: Schedule T-SQL scripts across multiple databases (PaaS).
Cost Optimization
Reserved capacity: Reserve compute for 1 or 3 years (up to 33% discount).
Serverless: Pay per second for compute, auto-pause during inactivity.
Azure Hybrid Benefit: Use existing SQL Server licenses to reduce cost (applies to all options).
Elastic pools: Share resources among databases to reduce cost for low-usage databases.
Exam Traps
Trap 1: Choosing SQL Managed Instance when only basic compatibility is needed. Managed Instance is more expensive and has networking constraints (must be in a VNet). If the scenario doesn't require instance-level features, Azure SQL Database is usually better.
Trap 2: Assuming all Azure SQL Database tiers support zone-redundancy. Only Business Critical tier supports it.
Trap 3: Thinking that Always On Availability Groups on Azure VMs does not require a load balancer. It does (Azure Load Balancer or internal load balancer) for the listener.
Trap 4: Confusing geo-replication (asynchronous) with auto-failover groups (automated failover). Auto-failover groups use geo-replication but add group management and automatic failover.
Trap 5: Believing that Hyperscale tier supports zone-redundancy. It does not currently (as of 2025).
Assess compatibility and requirements
Begin by evaluating the existing SQL Server features used: SQL Agent jobs, CLR assemblies, Service Broker, cross-database queries, etc. If the application relies on instance-level features, SQL Managed Instance or SQL on VM is required. Also consider scalability needs: if data volume exceeds 4 TB, consider Hyperscale (Azure SQL Database) or SQL on VM with appropriate storage. Assess management overhead tolerance: PaaS reduces DBA tasks. Use the Azure Data Migration Assistant (DMA) to check compatibility. This step determines which Azure relational service is viable.
Design high availability and disaster recovery
For Azure SQL Database, configure geo-replication or auto-failover groups for DR. Choose zone-redundant deployment if Business Critical tier is selected. For SQL Managed Instance, use auto-failover groups (preview) or manual geo-restore. For SQL on VM, implement Always On Availability Groups with at least two replicas in different availability zones or regions. Ensure the DR target region is paired and meets RPO/RTO requirements. Test failover procedures regularly.
Choose service tier and compute sizing
Select the appropriate service tier based on performance and availability needs. For OLTP workloads with low latency, use Business Critical. For general purpose workloads, use General Purpose. For large databases (up to 100 TB) and fast scaling, use Hyperscale. Choose provisioned compute for predictable workloads, serverless for intermittent ones. Use the DTU or vCore model; vCore provides more flexibility. Estimate DTU/vCore requirements using performance baselines from on-premises or Azure Monitor.
Configure networking and security
For Azure SQL Database, enable VNet service endpoints or private endpoints for secure connectivity. Configure firewall rules to allow client IPs. For SQL Managed Instance, deploy in a VNet with a delegated subnet; ensure route table and NSG allow required traffic (port 1433, 11000-11999 for redirect). For SQL on VM, use NSGs to restrict access. Enable Azure AD authentication and managed identities. Implement TDE with customer-managed keys if required. Set up auditing and Advanced Threat Protection.
Optimize performance with indexing and partitioning
Analyze query patterns to design indexes. Create clustered indexes on primary keys; add nonclustered indexes for frequent search columns. Consider columnstore indexes for data warehousing. For large tables, implement table partitioning to improve query performance and manageability. Use partition functions and schemes. Monitor index fragmentation and rebuild/reorganize as needed. Use Query Performance Insight in Azure SQL Database to identify slow queries and missing indexes.
Scenario 1: SaaS Multi-Tenant Application with Elastic Pools
A SaaS company hosts hundreds of customer databases, each less than 5 GB, with sporadic usage. They need cost-effective management and isolation. They choose Azure SQL Database with elastic pools. Each pool contains 10-50 databases, sharing a set of vCores. This reduces costs by 30-50% compared to individual databases. They use auto-failover groups for DR to a paired region. Common mistake: not monitoring pool eDTU utilization; if one database spikes, it can affect others. They set up alerts for high eDTU usage and scale the pool accordingly. They also use serverless for databases with very low usage to auto-pause and save compute costs.
Scenario 2: Lift-and-Shift Migration for a Financial Application
A bank has a critical SQL Server 2016 application using SQL Agent jobs, Service Broker, and cross-database queries. They want minimal code changes. They choose Azure SQL Managed Instance because it offers near 100% compatibility. They use Azure Database Migration Service for an online migration with minimal downtime. They configure Business Critical tier for high availability with zone-redundancy. They set up auto-failover groups for DR to another region. They use Azure Hybrid Benefit to reduce licensing costs. Challenge: Managed Instance requires a dedicated subnet with specific routing; they had to coordinate with network team to ensure proper NSG rules (allow inbound from Azure load balancer on port 1433 and 11000-11999). They also enable TDE with customer-managed keys stored in Azure Key Vault.
Scenario 3: High-Throughput OLTP with SQL on VM
An e-commerce company needs low-latency transactions (sub-10 ms) and full control over SQL Server configuration (e.g., tempdb on local SSD). They choose SQL Server on Azure VMs. They deploy two VMs in an availability set (or availability zones) with Always On Availability Groups. They use Premium SSD v2 disks for data and logs, and local SSD for tempdb. They configure an internal load balancer for the availability group listener. For DR, they use log shipping to a VM in another region. Common pitfall: not sizing the VMs correctly; they initially used D-series VMs but had to upgrade to E-series for memory-intensive workloads. They also set up Azure Monitor alerts for disk queue length and SQL Server wait stats.
AZ-305 Objective 2.2: Design for relational database solutions
The exam tests your ability to recommend the right Azure relational database service based on requirements. Key objective codes: - 2.2.1: Recommend a database service tier (General Purpose, Business Critical, Hyperscale). - 2.2.2: Recommend a database solution for high availability and disaster recovery (geo-replication, auto-failover groups, zone-redundancy). - 2.2.3: Recommend a database solution for scalability (elastic pools, sharding, read scale-out). - 2.2.4: Recommend a database migration strategy (online vs offline, DMS, backup/restore).
Common Wrong Answers:
Choosing Azure SQL Database for a scenario requiring SQL Agent jobs. Many candidates see 'fully managed' and assume it supports all SQL Server features. Reality: Azure SQL Database does not support SQL Agent; SQL Managed Instance does.
Selecting General Purpose tier for a latency-sensitive OLTP workload. Candidates might think GP is sufficient, but Business Critical provides lower latency (via In-Memory OLTP and faster storage).
Using geo-replication as the only DR strategy without considering RTO. Geo-replication is asynchronous, so RPO could be up to 5 seconds. For stricter RPO, use auto-failover groups with manual failover or Business Critical tier.
Assuming all tiers support zone-redundancy. Only Business Critical (for Azure SQL Database and Managed Instance) supports zone-redundant configuration. General Purpose does not.
Exam Numbers and Values:
Backup retention: 7-35 days (configurable), long-term up to 10 years.
Hyperscale max size: 100 TB.
Business Critical replica count: 4 (1 primary + 3 secondary).
General Purpose replica count: 3.
Auto-failover group grace period: 1 hour (default), can be set to 0-24 hours.
DTU vs vCore: vCore model is more flexible; DTU is simpler.
Edge Cases:
Cross-database queries: Not supported in Azure SQL Database (single database). Use elastic query or managed instance.
Linked servers: Not supported in Azure SQL Database; supported in Managed Instance and SQL on VM.
CLR: Not supported in Azure SQL Database; supported in Managed Instance and SQL on VM.
How to Eliminate Wrong Answers:
If the scenario mentions 'SQL Server Agent', 'CLR', or 'linked servers', eliminate Azure SQL Database.
If the scenario requires 'zone-redundancy', eliminate General Purpose tier.
If the scenario requires 'sub-10 ms latency', choose Business Critical.
If the scenario has 'intermittent workload', choose serverless compute.
If the scenario has 'multiple databases with varying loads', consider elastic pools.
For instance-level features (SQL Agent, CLR, cross-db queries), choose SQL Managed Instance or SQL on VM.
For minimal management overhead, choose Azure SQL Database (PaaS).
Business Critical tier offers zone-redundancy and lowest latency; General Purpose does not.
Hyperscale supports up to 100 TB and fast scaling, but no zone-redundancy.
Auto-failover groups provide automated DR with configurable grace period (default 1 hour).
Use Azure Hybrid Benefit to save on licensing costs for any option.
Elastic pools share resources among databases, reducing cost for multi-tenant SaaS.
Always On AG on Azure VMs requires a load balancer for the listener.
Backup retention: 7-35 days (configurable), long-term up to 10 years.
Serverless compute auto-pauses databases after 1 hour of inactivity, saving costs.
These come up on the exam all the time. Here's how to tell them apart.
Azure SQL Database (PaaS)
Fully managed: automatic backups, patching, HA.
No instance-level features (SQL Agent, CLR, etc.).
Built-in geo-replication and auto-failover groups.
Scaling can be done with minimal downtime (up to Hyperscale).
Cost includes licensing; Azure Hybrid Benefit can reduce costs.
SQL Server on Azure VMs (IaaS)
Full control over OS and SQL Server configuration.
Supports all SQL Server features (SQL Agent, CLR, SSIS, etc.).
Requires manual setup of HA/DR (Always On AG, log shipping).
Scaling requires VM resizing or adding more VMs (downtime).
You manage licensing; Azure Hybrid Benefit available.
Mistake
Azure SQL Database supports all SQL Server features.
Correct
Azure SQL Database lacks instance-level features like SQL Agent, CLR, Service Broker, and cross-database queries. SQL Managed Instance provides near 100% compatibility.
Mistake
General Purpose tier provides zone-redundant HA.
Correct
Only Business Critical tier supports zone-redundant configuration. General Purpose uses locally redundant storage and three replicas within the same datacenter.
Mistake
Geo-replication provides automatic failover with zero data loss.
Correct
Geo-replication is asynchronous, so there is potential data loss (RPO up to 5 seconds). Auto-failover groups provide automatic failover but still have asynchronous replication.
Mistake
Hyperscale tier supports zone-redundancy.
Correct
As of 2025, Hyperscale does not support zone-redundancy. It uses page servers and log service that are not zone-aware.
Mistake
SQL on VM does not require a load balancer for Always On AG.
Correct
Always On Availability Groups on Azure VMs require an Azure Load Balancer (internal or public) to route traffic to the primary replica via the listener.
Reveal each answer, then mark whether you got it right. Score 60%+ to unlock the next chapter.
Choose Azure SQL Database when you do not need instance-level features like SQL Agent, CLR, or cross-database queries, and you want the lowest management overhead. It is also better for elastic pools and hyperscale scaling. SQL Managed Instance is for high compatibility with on-premises SQL Server, especially for applications that rely on instance-scoped features.
Geo-replication is an asynchronous replication feature that creates readable secondaries in another region. Auto-failover groups build on geo-replication by grouping multiple databases and providing automatic failover based on a grace period (default 1 hour). Auto-failover groups also provide read-write and read-only listener endpoints.
No. Zone-redundant configuration is only available for the Business Critical tier in Azure SQL Database and SQL Managed Instance. General Purpose uses locally redundant storage within a single datacenter.
Use Azure Database Migration Service (DMS) for online migration. DMS supports continuous data sync from on-premises to Azure SQL Database or Managed Instance. For SQL on VM, you can use log shipping or backup/restore with minimal downtime. Assess compatibility first with DMA.
Hyperscale supports up to 100 TB of data. It is designed for large databases that need fast scaling and high throughput. Note that it does not support zone-redundancy.
Serverless compute automatically pauses the database after 1 hour of inactivity (configurable). When a connection request comes in, it resumes within 30 seconds. You pay per second for compute and storage. It is ideal for intermittent workloads like dev/test.
The default is 7 days, but you can configure it up to 35 days. For long-term retention, you can store backups for up to 10 years using long-term retention policies.
You've just covered Designing Relational Database Solutions — now see how well it sticks with free AZ-305 practice questions. Full explanations included, no account needed.
Done with this chapter?