Domain 2: Secure, optimize, and deploy database solutions (35-40%)
This domain is about building SQL systems that are not just functional, but safe, observable, and deployable at scale.
2.1 Implement data security and compliance
Core controls
| Control | Use it when | Key point |
|---|---|---|
| Always Encrypted | Sensitive data must remain protected even from DB admins | Encryption/decryption happens client-side |
| Column-level encryption | Specific columns need encryption but not full Always Encrypted semantics | Simpler, but different trust boundary |
| Dynamic Data Masking | Hide sensitive values from some users without changing stored data | Masking is obfuscation, not encryption |
| Row-Level Security (RLS) | Different users should only see their own rows | Filter data visibility by predicate |
| Object-level permissions | Restrict who can query tables, views, or procs | Standard least-privilege control |
| Auditing | Need traceability and compliance evidence | Capture access and changes |
Masking vs Encryption
Dynamic Data Masking changes what some users see.
Encryption changes how data is protected at rest or in use.
If the requirement is "DBA must not see plaintext", think Always Encrypted, not masking.
Modern access posture
- Prefer passwordless approaches where supported.
- Use Managed Identity to secure model endpoints and service-to-service access.
- Secure REST, GraphQL, and MCP endpoints the same way you would secure the database itself: authentication, authorization, auditability, and least privilege.
2.2 Optimize database performance
Performance toolkit
| Tool | What it helps with |
|---|---|
| Execution plans | See join strategy, scans, seeks, and operator costs |
| DMVs | Surface live operational metadata and workload behavior |
| Query Store | Historical query performance, regressions, and plan comparison |
| Query Performance Insight | Azure-centric performance visibility |
Common exam themes
- Blocking and deadlocks
- Bad indexing strategy
- Parameter sensitivity / unstable plans
- Wrong isolation level for workload behavior
- Transaction scope too broad
Isolation level mental shortcut
| Need | Direction |
|---|---|
| Highest correctness, more locking | Stronger isolation |
| Better concurrency, tolerate more anomalies | Lighter isolation |
| Preserve integrity under contention | Review transaction design first |
TIP
DP-800 is practical here: the best answer is usually the one that improves performance without sacrificing correctness or weakening security.
2.3 Implement CI/CD by using SQL Database Projects
This section is very Microsoft-specific and likely exam-relevant.
What to know
- Create and validate database models with SQL Database Projects
- Use SDK-style models
- Store reference/static data in source control where appropriate
- Configure branching, PRs, and conflict resolution
- Detect schema drift
- Deploy changes safely and repeatedly
- Manage secrets outside source control
Healthy database DevOps flow
- Model schema in a SQL Database Project.
- Keep changes versioned in Git.
- Validate builds in CI.
- Run unit/integration tests.
- Review drift between desired and target state.
- Deploy with approvals and branch policies.
Deployment governance signals
If the question mentions any of these, expect stronger pipeline controls:
- branch policies
- approvals
- code owners
- authentication tables
- schema drift
- secret rotation
Common Trap
If a scenario needs repeatable deployments and reviewable schema history, do not rely on ad hoc manual SQL scripts as the primary answer. Prefer a SQL Database Project + CI/CD pipeline approach.
2.4 Integrate SQL solutions with Azure services
Data API builder (DAB)
DAB exposes database entities through REST and GraphQL using configuration-first patterns.
| Capability | Why it matters |
|---|---|
| Entity configuration | Controls what gets exposed and how |
| Pagination/search/filtering | Shapes endpoint usability and cost |
| GraphQL relationships | Makes relational models easier to consume |
| Deployment config | Needed for predictable API rollout |
When DAB is the right answer
- You need quick API exposure over existing SQL objects
- You want REST and GraphQL without building a custom app layer from scratch
- You need to expose stored procedures, views, and relationships
Change propagation options
The study guide explicitly calls out:
- Change event streaming (CES)
- Change Data Capture (CDC)
- Change Tracking
- Azure Functions with SQL trigger binding
- Azure Logic Apps
Use these when embeddings, caches, downstream apps, or search indexes must stay in sync with database changes.
Fast Recall
- Always Encrypted protects plaintext from the database side.
- DDM hides values from some users but is not encryption.
- RLS controls who sees which rows.
- Query Store is the default answer for plan regressions/history.
- SQL Database Projects are central to CI/CD and drift detection.
- DAB is the Microsoft answer for configuration-driven REST/GraphQL over SQL.