AZ-305 · Question #294
AZ-305 Question #294: Real Exam Question with Answer & Explanation
Azure SQL Database Solution for PII Storage --- Dropdown 1: Service Tier and Compute Tier Correct: General Purpose service tier and Serverless compute tier Why General Purpose is correct: General Purpose uses zone-redundant deployments, which maintain availability across multiple
Question
Hotspot Question You are building an Azure web app that will store the Personally Identifiable Information (PII) of employees. You need to recommend an Azure SQL. Database solution for the web app. The solution must meet the following requirements: - Maintain availability in the event of a single datacenter outage. - Support the encryption of specific columns that contain PII. - Automatically scale up during payroll operations. - Minimize costs. What should you include in the recommendations? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Answer:
Options
- __typehotspot
- variantdropdown
Explanation
Azure SQL Database Solution for PII Storage
Dropdown 1: Service Tier and Compute Tier
Correct: General Purpose service tier and Serverless compute tier
Why General Purpose is correct:
- General Purpose uses zone-redundant deployments, which maintain availability across multiple datacenters within a region — satisfying the "single datacenter outage" requirement.
- It's the most cost-effective tier that still provides this HA capability.
Why Serverless compute is correct:
- Serverless automatically scales compute up/down based on workload demand, which directly satisfies the "automatically scale up during payroll operations" requirement.
- It also pauses when idle, significantly minimizing costs during off-peak periods.
Why the alternatives are wrong:
| Option | Why Wrong |
|---|---|
| Business Critical | Offers higher IOPS and in-memory OLTP — overkill here, and significantly more expensive. Fails the "minimize costs" requirement. |
| Hyperscale + Provisioned | Hyperscale is designed for massive databases (up to 100TB). Provisioned compute doesn't auto-scale — you pre-allocate compute. Both violate cost minimization. |
Dropdown 2: Encryption Method
Correct: Always Encrypted
Why Always Encrypted is correct:
- Always Encrypted is the only option that encrypts specific columns — the encryption/decryption happens client-side, meaning even DBAs and the database engine itself cannot see the plaintext data.
- This is precisely designed for protecting sensitive fields like SSNs, salaries, or other PII columns individually — matching "encryption of specific columns."
Why the alternatives are wrong:
| Option | Why Wrong |
|---|---|
| Transparent Data Encryption (TDE) | Encrypts the entire database at rest (data files, backups). It does not provide column-level encryption — a DBA with DB access can still read all plaintext data. |
| SQL Server/database encryption keys | Refers to key management infrastructure (e.g., TDE's Database Encryption Key). This is a mechanism supporting TDE, not a column-level solution. |
Key Concept Summary
| Requirement | Solution |
|---|---|
| Survive single datacenter outage | General Purpose (zone-redundant HA) |
| Encrypt specific PII columns | Always Encrypted (column-level, client-side) |
| Auto-scale for payroll | Serverless compute tier |
| Minimize costs | Serverless (pause when idle) + General Purpose (not premium tiers) |
Topics
Community Discussion
No community discussion yet for this question.