Study guide

5.4 Relational Data Protection Design

Exam AZ-305: Designing Microsoft Azure Infrastructure Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Orientation

1.1 AZ-305 Current Exam Facts & Format 1.2 Candidate Profile, Prerequisites & the AZ-104 Requirement 1.3 Skills Outline, Domain Weights & How to Use This Guide

2 Chapter 2: Logging, Monitoring & Diagnostics Design

2.1 Logging Solution Design 2.2 Log Routing Design 2.3 Azure Monitor Metrics & Alerting Design 2.4 Application Insights & Diagnostic Monitoring Design

3 Chapter 3: Authentication & Authorization Design

3.1 Authentication Solution Design 3.2 Identity Management Design 3.3 Authorization for Azure Resources 3.4 Authorization for On-Premises Resources 3.5 Secrets, Certificates & Key Management Design

4 Chapter 4: Governance, Compliance & Identity Governance Design

4.1 Management Group & Subscription Hierarchy Design 4.2 Resource Group Organization & Tagging Strategy Design 4.3 Compliance Management Design 4.4 Identity Governance Design

5 Chapter 5: Relational Data Storage Design

5.1 Relational Data Storage Selection 5.2 Database Service & Compute Tier Design 5.3 Database Scalability Design 5.4 Relational Data Protection Design

6 Chapter 6: Semi-Structured & Unstructured Data Storage Design

6.1 Semi-Structured Data Storage Design 6.2 Unstructured Data Storage Design 6.3 Storage Tier & Cost-Performance Trade-off Design 6.4 Unstructured Data Protection & Durability Design

7 Chapter 7: Data Integration & Analytics Design

7.1 Data Integration Pipeline Design 7.2 Event-Based & Real-Time Data Integration Design 7.3 Data Analytics Platform Design

8 Chapter 8: Backup & Disaster Recovery Design

8.1 Recovery Objectives Design: Calculating and Meeting RTO/RPO 8.2 Azure Site Recovery Architecture for Hybrid Workloads 8.3 Compute Backup & Recovery Design 8.4 Database Backup & Recovery Design 8.5 Unstructured Data Backup & Recovery Design

9 Chapter 9: High Availability Design

9.1 Compute High Availability: Availability Sets & Availability Zones 9.2 Compute High Availability: Load Balancing & VM Scale Set Design 9.3 Relational Data High Availability Design 9.4 Semi-Structured & Unstructured Data High Availability Design

10 Chapter 10: Compute Solution Design

10.1 Compute Requirements Analysis & Virtual Machine-Based Solution Design 10.2 Container-Based Solution Design (AKS, Container Apps, Azure Container Instances) 10.3 Serverless-Based Solution Design (Azure Functions, Logic Apps) 10.4 Batch Processing Solution Design (Azure Batch, HPC Workloads)

11 Chapter 11: Application Architecture Design

11.1 Messaging Architecture Design (Service Bus, Queue Storage) 11.2 Event-Driven Architecture Design (Event Grid, Event Hub) 11.3 API Integration & Application Caching Design (API Management, Azure Cache for Redis, CDN) 11.4 Application Configuration Management & Automated Deployment Design (App Configuration, DevOps Pipelines, Deployment Slots)

12 Chapter 12: Migration Design

12.1 Migration Assessment: Cloud Adoption Framework & On-Premises Evaluation 12.2 IaaS & PaaS Workload Migration Design 12.3 Database Migration Design (Data Migration Service, Database Migration Assistant) 12.4 Unstructured Data Migration Design (AzCopy, Data Box, Storage Migration Service)

13 Chapter 13: Network Solution Design

13.1 Internet Connectivity Design 13.2 Hybrid Connectivity Design 13.3 Network Security Optimization Design 13.4 Network Performance Optimization & Load-Balancing/Routing Design

14 Chapter 14: Final Review, Exam Strategy & Career Path

14.1 Cross-Domain Scenario Practice: Combining Identity, Data, Continuity & Infrastructure Decisions 14.2 Common AZ-305 Decision Traps 14.3 Final Study Plan & Practice Assessment Strategy 14.4 After AZ-305: Retake Policy, Renewal & Career Path

5.4 Relational Data Protection Design

Key Takeaways Explain This Topic AI

Transparent Data Encryption (TDE) protects data at rest and is on by default; customer-managed TDE (BYOK) with keys in Azure Key Vault is required whenever the organization, not Microsoft, must control and revoke encryption keys.

Always Encrypted protects specific columns so that even privileged database administrators cannot see plaintext — TDE alone does not satisfy that requirement, since a logged-in DBA can still query plaintext under TDE.

Dynamic Data Masking obfuscates query results for non-privileged users but is a display-layer control, not encryption, and does not meet strict compliance access requirements on its own.

Point-in-time restore covers 1-35 days of operational recovery; Long-Term Retention (LTR) covers up to 10 years of compliance-driven backup retention — these solve different problems and are not interchangeable.

Auditing can be scoped at the server or database level and streamed to Storage (low-cost long-term retention), Log Analytics, or Event Hub (near-real-time/SIEM); Microsoft Defender for SQL adds vulnerability assessment and threat detection on top.

Last updated: July 2026

Why This Topic Matters

The final relational-data bullet, "Recommend a solution for data protection," closes out this functional group by testing encryption, auditing, and data-recovery design for relational stores. This section focuses on protecting data at rest, in use, and from unauthorized access — recovery procedures for restoring a failed database are covered in depth in the Business Continuity chapter (8.4), so here the emphasis is on choosing the right protection mechanism for a given compliance or security requirement.

Encryption at Rest: Transparent Data Encryption

Transparent Data Encryption (TDE) encrypts the entire database, its backups, and its transaction log files at rest, transparently to the application — no application code changes are required. TDE is on by default for new Azure SQL databases. Two key modes matter for design decisions:

Service-managed TDE : Microsoft manages the encryption key entirely; simplest option, no customer action required.

Customer-managed TDE (bring your own key / BYOK) : the organization supplies and controls the TDE Protector , an asymmetric key stored in a customer-owned Azure Key Vault . The key never leaves the vault; Azure SQL wraps the database encryption key with it. This gives the customer control over key rotation, revocation, and audit trail of key usage — the correct recommendation whenever a scenario requires the customer , not Microsoft, to control encryption keys for compliance (common in healthcare, finance, and government requirements).

Protecting Specific Columns: Always Encrypted

Always Encrypted protects sensitive data at the column level (e.g., national ID numbers, payment card numbers) so that the data remains encrypted in memory, in transit, and in query plans — even database administrators with full permissions cannot see the plaintext , because encryption and decryption happen at the client driver, not on the server. The Always Encrypted with secure enclaves variant additionally allows richer server-side operations (pattern matching, range comparisons) on encrypted columns inside a hardware-protected enclave, without exposing plaintext outside it. Use Always Encrypted whenever a scenario says specific columns must be hidden even from privileged database roles — TDE alone does not satisfy that requirement, since TDE only protects data at rest and DBAs can still query plaintext through a normal connection.

Masking and Auditing

Dynamic Data Masking : obfuscates sensitive data in query results for non-privileged users (e.g., showing only the last four digits of a credit card) without changing the underlying stored data. It is a UX/least-privilege control, not a strong security boundary — it does not stop a determined attacker with query access, so it complements rather than replaces encryption.

Auditing : tracks database events (logins, schema changes, data access) and writes them to a chosen destination — an Azure Storage account, a Log Analytics workspace, or an Event Hub — configurable at the server level (applies to every database) or database level (granular, per-database policy). Choose Log Analytics or Event Hub when a scenario requires near-real-time analysis or SIEM integration; choose Storage when the requirement is long-term, low-cost retention of audit logs for compliance evidence.

Microsoft Defender for SQL bundles vulnerability assessment (scans for misconfigurations and missing patches) with advanced threat protection (behavioral analytics that alert on SQL injection, brute-force attempts, and anomalous access patterns). Recommend it whenever a scenario asks for proactive threat detection on top of passive auditing.

Backup Retention as a Protection Control

Backups are also a data-protection design lever, distinct from the disaster-recovery mechanics covered in Chapter 8:

Point-in-time restore (PITR) : automatic full/differential/log backups retained for a configurable 1 to 35 days (1-7 days for Basic tier), letting you restore to any point within that window; new databases default to a 7-day retention.

Geo-restore : because PITR backups are stored on geo-redundant storage by default, you can restore a database into a different Azure region if the primary region is unavailable.

Long-Term Retention (LTR) : for compliance mandates beyond 35 days, LTR stores full backups on a configurable weekly/monthly/yearly policy for up to 10 years , independent of the live database's retention window.

Geo-Replication as a Protection Mechanism

Active geo-replication and auto-failover groups create readable secondary replicas in a different Azure region, protecting relational data against a regional outage. From a data-protection lens (as opposed to the availability-architecture lens covered in Chapter 9.3), the design takeaway is: geo-replication gives you a secondary copy you control the failover of manually (active geo-replication) or automatically behind a stable listener endpoint (auto-failover groups), and either can also serve read-only reporting traffic. Recommend geo-replication/failover groups whenever a compliance or business-continuity requirement calls for a regionally separated, near-real-time copy of relational data, in addition to (not instead of) backups.

Exam Scenarios

Scenario A: A healthcare provider must ensure the organization — not Microsoft — controls and can revoke the encryption keys protecting patient records at rest, for HIPAA compliance. Answer: Customer-managed TDE (BYOK) with the TDE Protector stored in an organization-owned Azure Key Vault.

Scenario B: A payment processor must ensure that even database administrators cannot view plaintext credit card numbers stored in a specific column. Answer: Always Encrypted (with secure enclaves if server-side computation on those columns is also required).

Scenario C: An auditor requires a 7-year retained record of every login and schema change on a production database, plus near-real-time SIEM ingestion. Answer: Auditing configured at the server level, streaming to both an Event Hub (for SIEM) and a Storage account with an LTR-aligned retention policy.

Common Traps

Assuming TDE protects against a privileged database administrator viewing data — it only protects data at rest (files on disk); a logged-in DBA still sees plaintext through a query. Use Always Encrypted for that requirement instead.

Recommending Dynamic Data Masking as a compliance-grade security control — it is a display-layer convenience, not encryption, and does not satisfy "must not be able to access the underlying sensitive data" requirements.

Confusing PITR (short-term, 1-35 days, for operational recovery) with Long-Term Retention (up to 10 years, for compliance archiving) — a scenario asking for multi-year retention needs LTR, not a longer PITR window (which is capped at 35 days).