Data-related questions in AZ-305 are some of the most scenario-heavy. They test not just your knowledge of services, but whether you can choose the right database/storage solution for specific workloads. Many candidates fall into traps by confusing SQL options, storage tiers, or Cosmos DB consistency levels.
This capsule highlights the common pitfalls, best practices, and exam strategies.
Common Pitfalls
1. Mixing up SQL Database Options
-
SQL Database = cloud-native, lightweight, PaaS.
-
SQL Managed Instance = lift-and-shift, near-full SQL compatibility.
-
SQL on VMs = full control, legacy workloads.
Exam Trap: If scenario requires SQL Agent jobs → Managed Instance or SQL on VM, not SQL Database.
2. Confusing Storage Services
-
Blob = unstructured data (images, logs).
-
File = SMB-based file shares.
-
Table = NoSQL key-value.
-
Queue = simple messaging.
Exam Trap: If scenario says “replace on-prem file server” → Azure Files, not Blob.
3. Misunderstanding Cosmos DB Consistency
-
Strong = strict correctness.
-
Session = balance (default).
-
Eventual = fastest but least reliable.
Exam Trap: If requirement is “global DB with guaranteed correctness” → Strong consistency, not Session.
4. Backup Confusion
-
PITR = short-term recovery.
-
LTR = years-long retention for compliance.
Exam Trap: If requirement is “retain backups for 7 years” → Long-Term Retention, not PITR.
5. Encryption Misuse
-
TDE = DB-level encryption.
-
Always Encrypted = column-level encryption.
-
Key Vault = customer-managed keys.
Exam Trap: If requirement says “DBAs must not see sensitive fields” → Always Encrypted, not TDE.
6. Partitioning Errors
-
Poor partition key in Cosmos DB = hot partition = throttling.
-
Exam may hide this in a scenario.
Best Practices
-
Always start with business requirements (availability, latency, compliance).
-
Use Cosmos DB when global distribution + NoSQL are needed.
-
Use SQL Hyperscale for very large OLTP workloads.
-
Use Elastic Pools for multiple unpredictable workloads.
-
Secure all data with TDE by default, extend with CMK in Key Vault.
-
Use Blob lifecycle policies to save costs (Hot → Cool → Archive).
-
Choose partition keys carefully to balance workload.
Quick Recall Table
| Requirement | Best Solution |
|---|---|
| Cloud-native relational DB | Azure SQL Database (PaaS) |
| Lift-and-shift SQL with cross-DB queries | SQL Managed Instance |
| Full control with legacy dependencies | SQL on VMs |
| Unstructured storage (videos, images) | Blob Storage |
| On-prem-like SMB shares | Azure Files |
| Global low-latency NoSQL DB | Cosmos DB |
| Protect DB backups by default | Transparent Data Encryption (TDE) |
| Prevent DBAs from seeing sensitive fields | Always Encrypted |
| 7–10 year DB retention | Long-Term Retention (LTR) |
| Avoid Cosmos DB hot partition | Good partition key selection |
Exam Tips
-
“Which SQL option supports SQL Agent jobs?” → Managed Instance or SQL on VM.
-
“Which storage service replaces legacy SMB file servers?” → Azure Files.
-
“Which Cosmos DB consistency model balances performance and correctness?” → Session.
-
“Which SQL backup feature supports compliance retention for 10 years?” → LTR.
-
“Which encryption option protects sensitive customer data at the column level?” → Always Encrypted.
What to Expect in the Exam
-
Direct Q: “Which storage service is best for semi-structured IoT telemetry data?” → Table Storage or Cosmos DB (depending on scale).
-
Scenario Q: “E-commerce app requires globally distributed DB with multi-master writes.” → Cosmos DB.
-
Scenario Q: “Company wants to migrate SQL workloads with minimal code change but still needs SQL Agent jobs.” → SQL Managed Instance.
-
Scenario Q: “Bank requires compliance backups for 7 years.” → SQL LTR.
-
Trick Q: “TDE ensures DBAs cannot read SSNs.” → False (Always Encrypted).