DP-800 secure, optimize and deploy

Secure, optimize, and deploy database solutions is the other 35–40% domain in DP-800, and it is the widest. It covers data security and compliance, performance tuning, CI/CD with SQL Database Projects, and integration with Azure services such as Data API builder. Questions usually describe a requirement and ask which feature meets it, or show a symptom and ask for the cause.

Implement data security and compliance

Feature	What it does	What it does not do
Always Encrypted	Encrypts columns client-side; the server never sees plaintext	Does not allow all operations on encrypted data; deterministic encryption allows equality, randomized does not
Column-level encryption	Encrypts values with keys managed in the database	Keys live on the server side, so it protects less from administrators
Dynamic Data Masking	Masks values in query results for users without `UNMASK`	Is not encryption; masked columns can be inferred by filtering
Row-Level Security	Filters rows, and blocks writes, by predicate	Does not hide columns
Object permissions	`GRANT`, `DENY`, schemas and roles control access	Cannot restrict individual rows

Row-Level Security uses an inline table-valued function as a predicate and a security policy to apply it. A filter predicate affects reads, updates and deletes; a block predicate stops writes that violate it. Use SESSION_CONTEXT when many users share one application login.

Also expect auditing (record who did what), passwordless access with Microsoft Entra authentication and managed identities instead of stored passwords, managed identity for calling model endpoints, and securing GraphQL, REST and MCP endpoints with authentication and role-based permissions.

Optimize database performance

Configuration: pick service tier or compute size and settings to match the workload.
Isolation levels: READ COMMITTED avoids dirty reads; row versioning options and SNAPSHOT reduce reader-writer blocking at the cost of tempdb or version store use; SERIALIZABLE is strictest and blocks most.
Diagnosing slow queries: read the execution plan for scans, key lookups, spills and estimate mismatches; use DMVs for live state; use Query Store to see plan history and force a previous plan after a regression; use Query Performance Insight in Azure SQL for top resource consumers.
Blocking versus deadlock: blocking is one session waiting for another and clears when the first finishes. A deadlock is a lock cycle in which the engine kills one victim. Fix deadlocks by accessing objects in a consistent order and keeping transactions short.

Implement CI/CD with SQL Database Projects

A SQL Database Project holds the schema as source files. The SDK-style project builds into a data-tier package that you deploy to a target; the deployment compares the model with the target and generates the change script.

Testing: unit tests for procedures and functions, integration tests against a deployed copy.
Reference data: keep static lookup values in source control, typically as idempotent post-deployment scripts.
Source control: branches, pull requests and conflict resolution; keep secrets out of the repository and use a secret store or pipeline secrets.
Schema drift: detect when the live database differs from the project, then decide whether to update the project or revert the database.
Pipeline controls: branch policies, approvals before production, protected environments and code owners for sensitive files.

Integrate SQL solutions with Azure services

Data API builder (DAB) exposes tables, views and stored procedures as REST and GraphQL endpoints from a JSON configuration file. You configure entities, permissions per role, caching, pagination, filtering and relationships, and deploy it as a container or other hosting option. Add Application Insights and Log Analytics through Azure Monitor for telemetry.

To react to data changes, choose by need:

Option	Fits when
Change Tracking	You need to know which rows changed, not the old values
Change data capture	You need before and after values from the log
Change event streaming	You want changes streamed out as events
Azure Functions SQL trigger binding	You want code to run on changed rows
Azure Logic Apps	You want low-code workflows around a change

Sample questions

Question 1. A support team must see customer email addresses as masked values in query results, but a small data-quality group must see them in full. The data does not need to be hidden from database administrators. What is the simplest solution?

A. Always Encrypted with randomized encryption
B. Row-Level Security with a filter predicate
C. Dynamic Data Masking with UNMASK granted to the data-quality group
D. DENY SELECT on the column for the support team

Show answer

Answer: C

Dynamic Data Masking masks the column for most users, and granting UNMASK to the data-quality group shows them full values. Always Encrypted would hide the data from the server and from administrators, which is more than required and limits query features. Row-Level Security filters rows, not column values. Denying SELECT on the column removes the data entirely instead of masking it.

Want more questions like this? Full DP-800 practice tests →

Question 2. After a deployment, a frequently used query suddenly runs 20 times slower. Query Store shows the same query now uses a new plan, and the previous plan was fast. What should you do first?

A. Drop and recreate all indexes on the table
B. Force the previous plan in Query Store
C. Restart the database instance
D. Raise the isolation level to SERIALIZABLE

Show answer

Answer: B

Query Store keeps plan history and lets you force the earlier good plan, which fixes the regression immediately while you investigate the cause. Dropping all indexes would harm other queries. Restarting the instance clears caches but does not stop the optimizer from choosing the bad plan again. Raising the isolation level to SERIALIZABLE increases blocking and does not address plan choice.

Want more questions like this? Full DP-800 practice tests →

Question 3. Your team keeps its database schema in a SQL Database Project in GitHub. Last week an administrator added an index directly in production. You want to find such out-of-band changes automatically. What should the pipeline do?

A. Run a drift check that compares the production database with the project model
B. Run the unit tests against the production database
C. Add the index to a reference data script
D. Squash the commits before each release

Show answer

Answer: A

Comparing the live database with the project model detects schema drift, and the pipeline can fail or alert when differences appear. Running unit tests checks logic, not whether the live schema matches the source. Reference data scripts load lookup values and do not detect structure changes. Squashing commits only rewrites history and has no relation to production state.

Want more questions like this? Full DP-800 practice tests →