3.1.1.1. Design for Azure SQL Database
3.1.1.1. Design for Azure SQL Database
💡 First Principle: A fully managed, enterprise-grade relational database service abstracts away infrastructure management, enabling developers to focus on application logic while benefiting from built-in scalability, high availability, and security.
Choosing an Azure SQL deployment option:
Work down this tree rather than starting from the service you know. Managed Instance exists for the lift-and-shift of an instance, not merely a database — its selling point is the surface area SQL Database omits. SQL Server on a VM is the answer only when something outside the database forces it, such as a vendor supporting one specific build.
Scenario: You are designing a new e-commerce platform that will be hosted on Azure. The platform requires a highly available, scalable, and secure relational database. You anticipate unpredictable transaction volumes but need consistent performance.
Azure SQL Database is a fully managed relational database service in Azure, based on the Microsoft SQL Server engine.
Key Design Considerations:
- Scalability:
- Select between vCore and DTU purchasing models to match workload and budget. vCore offers more granular control over compute resources.
- Serverless tier enables automatic scaling and pausing for variable workloads, optimizing cost.
- Hyperscale supports rapid growth, up to 100 TB per database, with highly scalable storage and compute.
- High Availability:
- Built-in high availability with automatic failover and redundancy (for PaaS, SQL Database automatically provides 99.99% availability SLA).
- Geo-replication enables readable secondary databases in other Regions for disaster recovery and read-scaling.
- Deployment across Availability Zones provides resilience against datacenter failures within a Region.
- Security:
- Network isolation via Virtual Network (VNet) integration and Private Link to restrict public access.
- Transparent Data Encryption (TDE) protects data at rest. With customer-managed keys — the TDE protector held in your own Azure Key Vault — the organization controls key creation, rotation and revocation; with service-managed keys, custody stays with Microsoft.
- Microsoft Entra ID authentication and role-based access control (RBAC) for access control.
- Always Encrypted encrypts selected columns on the client, so even database administrators never see plaintext; Dynamic Data Masking only hides values in query results and encrypts nothing.
- Compliance:
- Auditing writes database events to a storage account or Log Analytics as an access trail, and long-term backup retention keeps full backups for up to 10 years beyond the point-in-time restore window (up to 35 days).
Service tiers and pooling decide most scenarios:
| Choice | What it gives you | Choose it when |
|---|---|---|
| General Purpose | Compute separated from remote storage; a failover attaches the storage to new compute, which takes tens of seconds | Most business workloads at moderate cost |
| Business Critical | An Always On availability group of local-SSD replicas: the fastest local failover, and one replica readable at no extra cost through read scale-out (connect with ApplicationIntent=ReadOnly) | Latency-sensitive OLTP, or reporting that must come off the primary within the same region |
| Hyperscale | Distributed storage for very large databases, fast restores largely independent of size, and separately added read replicas | Databases heading toward the 100 TB range or growing fast |
| Elastic pool | Many databases share one pool of eDTUs or vCores, each with a per-database minimum and maximum | Multi-tenant SaaS where databases burst at different times |
Zone redundancy spreads the replicas across Availability Zones so the database survives a datacenter loss inside the region. Active geo-replication and failover groups are the cross-region (disaster recovery) tools, not a way to offload in-region reads. Every failover drops open connections for a few seconds, so applications need retry logic for transient errors.
⚠️ Common Pitfall: Choosing the DTU model for a workload with specific CPU and memory requirements. The vCore model provides more transparency and control over the underlying resources, making it easier to right-size for performance and cost.
Key Trade-Offs:
- Serverless vs. Provisioned: The Serverless tier is cost-effective for intermittent, unpredictable workloads but may have a "cold start" latency for the first request after a pause. Provisioned tiers offer consistent performance at a fixed cost. For a predictable cycle — quiet weekdays, busy weekends — scheduled scaling of provisioned compute (for example an Automation runbook that changes the service objective before and after the peak) usually beats both paying for peak all week and serverless, whose savings come from genuinely idle periods.
Reflection Question: How does designing for Azure SQL Database, considering its scalability models (e.g., Serverless, Hyperscale), built-in high availability features (geo-replication, Availability Zones), and security options, fundamentally ensure a reliable, enterprise-grade database solution that meets the demands of transactional workloads in the cloud?
