Design data storage solutions · Topic 2 of 5
Database Scalability and Data Protection
AZ-305 database scale and security: scale up, read replicas and sharding, failover groups, TDE, Always Encrypted, dynamic data masking, ledger and auditing.
- 5 min
- Scale up for one database, replicas for reads, elastic pools for many tenants, sharding only when one database cannot cope.
- Failover groups give stable listener endpoints. Zone redundancy gives local resilience with RPO 0.
- TDE is on by default. Always Encrypted hides data from DBAs. Masking is presentation only. Ledger proves integrity.
What it is
Once you have chosen a database, you design how it scales as load grows and how data is protected: encrypted, masked, audited and recoverable. Scaling can be vertical (bigger compute), horizontal for reads (replicas) or horizontal for data (sharding). Protection spans encryption at rest, in use and in transit, access control and auditing.
How it works
Scalability options
| Need | Option |
|---|---|
| More CPU, memory or IOPS for one database | Scale up (change tier or vCores). Hyperscale and serverless scale with little downtime |
| Offload reporting and read queries | Read scale out: Business Critical's included readable secondary, Hyperscale named replicas (up to 30), or geo replicas |
| Many tenants of uneven load | Elastic pool |
| Data too big or write heavy for one database | Sharding with the elastic database tools (shard map), or partitioning in Hyperscale |
| Automatically adjust to load | Serverless compute, or autoscale in Cosmos DB |
| Move to a different region for latency | Active geo replication or a failover group with a readable secondary |
Prefer read replicas for reporting over scaling the primary. Sharding increases the number of databases and the complexity, so use it only when a single database cannot meet write or size demands.
Availability and replication
- Zone redundant configuration spreads replicas over availability zones. It is supported in General Purpose, Business Critical and Hyperscale vCore tiers (and Premium in DTU) and targets recovery in under about 30 seconds with an RPO of 0 within the region.
- Failover groups replicate to a secondary region and give read write and read only listener endpoints that do not change on failover, so apps need no connection string change. A group can fail over many databases together.
- Active geo replication replicates single databases to up to four readable secondaries.
- Backups are automatic: point in time restore, with long term retention (LTR) for up to 10 years.
Data protection
| Control | What it does |
|---|---|
| Transparent data encryption (TDE) | Encrypts data and logs at rest. On by default with service managed keys. Use a customer managed key in Key Vault or Managed HSM when required |
| TLS | Encrypts data in transit. Enforce a minimum TLS version |
| Always Encrypted | Column level encryption in the client. The server never sees plaintext or keys, so even DBAs cannot read it. Secure enclaves allow richer queries on encrypted data |
| Dynamic data masking | Hides sensitive values from non privileged users in query results. It is not encryption and does not stop a determined user |
| Row level security | Filters rows by the user |
| Microsoft Entra authentication | Central identity, MFA and managed identities, instead of SQL logins |
| Microsoft Defender for SQL | Vulnerability assessment and threat detection |
| Auditing | Writes audit logs to storage, Log Analytics or Event Hubs |
| Ledger | Tamper evident tables with cryptographic proof, for audit and integrity |
| Private endpoint and firewall | Remove public access |
| Data discovery and classification | Labels sensitive columns |
Choosing: use TDE for general encryption at rest. Use Always Encrypted when the database administrator or cloud operator must not see the data. Use masking to limit what support staff see on screen. Use ledger when the requirement is proof that records were not altered.
Choosing quickly
| Requirement | Answer |
|---|---|
| Reporting must not slow the OLTP database | A read replica (Business Critical readable secondary or Hyperscale named replica) |
| Apps must keep one connection string after a regional failover | A failover group listener |
| DBAs must not see card numbers | Always Encrypted |
| Support staff see only the last four digits | Dynamic data masking |
| Prove records were not tampered with | Ledger |
| Customer holds the encryption key | TDE with a customer managed key |
A worked scenario
A payments company has a primary database in West Europe and wants reporting without load, regional failover with no code change, and card numbers hidden even from administrators. The architect uses Business Critical with the readable secondary for reports, a failover group to North Europe with listener endpoints, Always Encrypted with secure enclaves on the card column, dynamic data masking for support users, and auditing to a Log Analytics workspace. TDE uses a customer managed key in Key Vault.
What to remember for the exam
- Scale up for one database, replicas for reads, elastic pools for many tenants, sharding only when one database cannot cope.
- Failover groups give stable listener endpoints. Zone redundancy gives local resilience with RPO 0.
- TDE is on by default. Always Encrypted hides data from DBAs. Masking is presentation only. Ledger proves integrity.
- Long term backup retention can reach 10 years.
- Entra authentication with managed identities replaces SQL logins and stored passwords.
Dynamic data masking is not a security boundary. Use Always Encrypted when the data must be unreadable to privileged users.
How the exam asks about this
- "Administrators must not be able to read the data." Always Encrypted.
- "Fail over to another region without changing connection strings." A failover group.
- "Offload heavy reports from the primary." A read replica.
- "Show only the last four digits to the help desk." Dynamic data masking.
Common mistakes
Treating masking as encryption.
Sharding too early. Replicas or Hyperscale may be enough.
Pointing apps at a geo replica endpoint. Use failover group listeners.
Forgetting key access for customer managed TDE. The database loses access if the key is removed.
Check yourself
Which feature hides data from database administrators?
Always Encrypted.
What gives a stable connection string across a regional failover?
A failover group listener endpoint.
What is TDE's default key?
A service managed key, with a customer managed key option.
Topic 2 of 5 in Design data storage solutions
Test yourself on Design data storage solutions
The full Microsoft AZ-305 question bank has 755 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.