Azure Relational Data Services
Azure SQL DatabaseA fully managed PaaS relational database engine. Provides automatic patching, backups, built-in intelligence for performance tuning, and zone-redundant high availability. Available in three service tiers: General Purpose, Business Critical, and Hyperscale. Supports vCore and DTU-based purchasing models.Azure SQL Managed InstanceA PaaS SQL Server instance with near-complete SQL Server compatibility. Supports SQL Server-specific features (SQL Agent jobs, CLR, SSIS, cross-database queries, replication). Deployed in a managed VNet with private network isolation. Best for lifting-and-shifting on-premises SQL Server with minimal code changes.Elastic PoolA shared compute and storage resource pool for multiple Azure SQL Databases. Cost-effective when databases have variable and complementary usage patterns (multi-tenant SaaS). Not beneficial when all databases have consistently high usage simultaneously.Failover GroupsA managed geo-replication feature that provides a single listener endpoint that automatically redirects to the promoted secondary after failover. Preferred over manual geo-replication because client connection strings remain unchanged.Always EncryptedClient-side encryption where data is encrypted before it reaches the database engine. Column master keys are stored outside the database (in Azure Key Vault or a certificate store). Database administrators cannot view decrypted values — required for PCI DSS and HIPAA scenarios where DBA access must be restricted.Service Tier Selection
| Tier | Key Characteristics | Best For |
|---|---|---|
| General Purpose | Balanced compute, memory, I/O; 99.99% SLA | 95% of business workloads |
| Business Critical | In-memory OLTP (Hekaton); Always-On AGs; 99.995% SLA with zone redundancy | Mission-critical, ultra-low latency |
| Hyperscale | Up to 100 TB; snapshot-based backups; rapid scale | Very large databases, bursty analytics |
High Availability Architecture
Zone Redundancy (regional HA)
- Synchronous replication across 3 availability zones
- Zero data loss (RPO = 0) on zone failure
- 99.995% SLA for Business Critical
Geo-Replication + Failover Groups (DR)
- Asynchronous replication to secondary region (RPO ≈ 5 seconds)
- Failover groups provide automatic health monitoring and single listener endpoint
- RTO ≈ 30 seconds for automatic failover
Design Patterns and Trade-Offs
Single Database vs. Elastic Pool vs. Managed Instance
| Pattern | Use Case | Key Benefit |
|---|---|---|
| Single Database | One application per database | Isolated performance, simple management |
| Elastic Pool | 50–500 tenant databases with variable load | Shared resources reduce cost |
| Sharding | >100 TB or extreme concurrent connections | Unlimited horizontal scale |
| Managed Instance | Legacy SQL Server lift-and-shift | Near-complete SQL Server compatibility |
Purchasing Models
vCore Model (recommended for new deployments)
- Predictable compute, separate storage
- Eligible for Azure Hybrid Benefit (SQL Server license savings)
- Serverless tier: auto-pause during inactivity (dev/test cost savings)
DTU Model (legacy, bundled pricing)
- CPU, memory, I/O bundled in a single unit
- Less transparent than vCore
- May overprovision at scale; vCore is more cost-effective
Encryption Options
Replication and Failover Comparison
| Feature | Zone Redundancy | Geo-Replication | Failover Groups |
|---|---|---|---|
| RPO | Zero | ~5 seconds | ~5 seconds |
| RTO | Automatic (seconds) | ~30 seconds (manual) | ~30 seconds (automatic) |
| Client impact | None | Must change connection string | None (listener endpoint) |
| Best for | Regional HA | Read offload + DR | Production DR |
Always recommend Failover Groups over manual geo-replication for production workloads — the listener endpoint eliminates the need for application connection string changes after failover.
Hands-On: Create Azure SQL Database with Geo-Redundancy
Step 1: Create Azure SQL Database
- Navigate to Azure SQL > Create > Single database
- Configure:
- Server: Create new logical server with Entra ID admin
- Service tier: General Purpose (vCore) or Business Critical
- Compute: Start with 2 vCores; scale as needed
- Enable Zone redundancy in Compute + Storage settings
- Configure networking: Use Private endpoint for production
- Set Backup retention: 7 days default (max 35 days for PITR)
- Review and create
Step 2: Configure Failover Group
- Open Azure SQL Database instance
- Go to Replication > Failover groups
- Click Create failover group
- Select secondary region and server
- Configure:
- Failover policy: Automatic (recommended)
- Grace period: 1 hour (detection time before auto-failover)
- Review and create — replication begins; monitor sync state
Step 3: Enable Zone Redundancy (Existing Database)
- Open database > Compute + storage
- Toggle Zone redundancy: Enable
- Confirm — brief downtime during reconfiguration
- Verify in Properties: Redundancy shows as "Zone-redundant"
Step 4: Review Query Performance Insights
- Open database > Intelligent Performance > Query Performance Insight
- View top queries by CPU, I/O, duration
- Click a query to see execution plan and index recommendations
- Use Automatic tuning to apply recommendations automatically
Step 5: Configure Always Encrypted (SSMS)
- Open SQL Server Management Studio (SSMS)
- Right-click table > Encrypt columns
- Select columns to encrypt (e.g., CreditCardNumber)
- Create column master key stored in Azure Key Vault
- Configure column encryption key
- Complete wizard — column data is encrypted at client
AZ-305 Exam Focus
AZ-305 tests your ability to match SQL service tiers and HA features to specific business requirements. Scenario questions often include RTO/RPO requirements, compliance encryption needs, or multi-tenant SaaS patterns.
Exam Trap
Business Critical Is Not Always Better: Business Critical provides higher SLA and in-memory OLTP — but General Purpose often performs equally well for standard OLTP. Choose tier based on HA requirements and specific features (in-memory OLTP, read replicas for BC tier), not pure performance assumptions.
Exam Trap
Zone Redundancy vs. Geo-Replication Scope: Zone redundancy protects against zone-level failures WITHIN a single region. Geo-replication protects against regional disasters. They address different failure domains and should be combined for complete protection.
Exam Trap
Manual Geo-Replication for Production: Manual geo-replication requires application connection string changes after failover. Failover groups provide a listener endpoint that remains valid before and after failover. Always recommend failover groups for production DR scenarios.
Exam Trap
Elastic Pools for All Multi-Database Scenarios: Elastic pools benefit from variable and complementary usage patterns. If all databases hit peak load simultaneously, they compete for shared resources. Elastic pools are optimal for SaaS with staggered user activity patterns, not for simultaneously peaking workloads.
Exam Tip
Hyperscale for Large Databases: When a scenario mentions database sizes exceeding 4–8 TB with bursty workloads and fast restore requirements — Hyperscale is the answer. Snapshot-based backups enable rapid restore (seconds vs. hours), and the architecture supports up to 100 TB.
Must Memorize
Always Encrypted Use Case: TDE protects data at rest from external attackers but database administrators can still view decrypted values. Always Encrypted encrypts at the client — DBAs get ciphertext and cannot decrypt. Required when the exam scenario mentions restricting DBA visibility into sensitive data (PCI, HIPAA).
Question — click to flip
Q: What is the key difference between zone redundancy and geo-replication in Azure SQL Database?
Question — click to flip
Q: When should you recommend Azure SQL Managed Instance over Azure SQL Database?
Question — click to flip
Q: What advantage do Failover Groups provide over standalone geo-replication?
Question — click to flip
Q: When is an Elastic Pool cost-effective vs. individual databases?
Question — click to flip
Q: How does Always Encrypted differ from Transparent Data Encryption (TDE)?
Question — click to flip
Q: What purchasing model should you recommend for a new Azure SQL Database deployment?