# Free DP-300 practice questions

> 10 free DP-300 practice questions with the correct answer and an explanation.

Page: https://devopscodelab.com/exams/microsoft-dp-300/free-practice-questions/
Practice exams: https://devopscodelab.com/exams/microsoft-dp-300/

### Question 1 of 10

A retailer is moving a 3 TB SQL Server 2019 database to Azure. The application uses cross-database queries, SQL Server Agent jobs, and Service Broker, and the team wants a fully managed PaaS service with no operating system to patch. Which deployment option should they choose?

- A. Azure SQL Database in a vCore-based elastic pool with elastic jobs replacing SQL Agent
- B. SQL Server 2022 on an Azure virtual machine registered with the SQL IaaS Agent extension
- C. Azure SQL Managed Instance in the General Purpose service tier, migrated with Database Migration Service
- D. Azure SQL Database single database in the Hyperscale service tier

**Answer:** C

**Explanation:** Managed Instance is PaaS yet keeps instance-scoped features such as cross-database queries, SQL Server Agent and Service Broker. Azure SQL Database (single or pooled, any tier) does not support Service Broker or cross-database queries in the same way, so Hyperscale and the elastic pool fail the compatibility requirement. A SQL Server VM supports everything but is IaaS, so the team would still patch the operating system.

### Question 2 of 10

A DBA must migrate a 2 TB SQL Server 2017 database to Azure SQL Managed Instance with minimal downtime. Full and log backups already land in Azure Blob Storage every 15 minutes. The DBA wants to restore from those files continuously and cut over when ready. Which service should be used?

- A. Log Replay Service restoring the backups from Blob Storage onto the managed instance
- B. Azure Data Factory copy activities that move each table from SQL Server into the managed instance
- C. Transactional replication with the managed instance configured as a publisher
- D. A BACPAC export imported into the managed instance through the Azure portal

**Answer:** A

**Explanation:** Log Replay Service continuously restores full, differential and log backups from Blob Storage to Managed Instance and completes with a final cutover, giving minimal downtime. A BACPAC import is an offline, slow operation for 2 TB. The managed instance would need to be a subscriber, not a publisher, to receive data, and Data Factory copies data but not the full database schema and objects in a log-consistent way.

### Question 3 of 10

A table stores customer email addresses and phone numbers. Support agents must see only partially obscured values such as aXXX@XXXX.com, while the billing team must see full values. No application changes are allowed. What should you implement?

- A. Always Encrypted with randomized encryption and give billing access to the column master key
- B. Row-level security with a filter predicate that hides rows from support agents
- C. Dynamic data masking on the columns and grant UNMASK to the billing team
- D. Transparent data encryption with a customer-managed key so agents cannot read the files

**Answer:** C

**Explanation:** Dynamic data masking obfuscates column values in query results for users without UNMASK, needs no application change, and can be lifted for specific users. Always Encrypted would require client driver configuration and would show ciphertext rather than a partial mask. Row-level security hides whole rows rather than masking values, and TDE protects files at rest but returns clear data to any authorized query.

### Question 4 of 10

A multi-tenant database stores a TenantId column in every table. Each application connection sets SESSION_CONTEXT('TenantId'). You must ensure queries return only the caller's rows and that the application can never insert rows for another tenant. Which approach meets both requirements?

- A. Dynamic data masking on the TenantId column plus a CHECK constraint that compares the column to the value returned by SESSION_CONTEXT
- B. A separate view per tenant that filters on TenantId, with INSTEAD OF triggers that reject inserts for other tenants
- C. A security policy with only a filter predicate that calls an inline function comparing TenantId to SESSION_CONTEXT
- D. A security policy with a filter predicate and an AFTER INSERT block predicate, both calling an inline function that compares TenantId to SESSION_CONTEXT

**Answer:** D

**Explanation:** Row-level security filter predicates silently remove rows the caller should not read, and block predicates such as AFTER INSERT stop writes that would violate the predicate. A filter predicate alone still allows inserting rows for another tenant. Masking does not filter rows, and per-tenant views with triggers are brittle, easy to bypass through the base table, and costly to maintain.

### Question 5 of 10

An Azure SQL Database receives thousands of different single-use ad hoc queries, and the plan cache is filled with plans that are never reused. Which database scoped configuration reduces this memory waste?

- A. OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON
- B. LEGACY_CARDINALITY_ESTIMATION = ON for the database
- C. PARAMETER_SNIFFING = OFF
- D. ELEVATE_ONLINE = WHEN_SUPPORTED

**Answer:** A

**Explanation:** OPTIMIZE_FOR_AD_HOC_WORKLOADS stores a small compiled plan stub on first execution and caches the full plan only when the query runs again. Legacy cardinality estimation changes estimates, disabling parameter sniffing affects plan choices, and ELEVATE_ONLINE affects DDL operations, none of which reduce single-use plan bloat.

### Question 6 of 10

An Azure SQL Database shows high CPU. You want to see which queries consumed the most CPU over the last 24 hours, with their query text, using only the Azure portal. Which feature should you open?

- A. SQL Insights
- B. Query Performance Insight
- C. Database advisor recommendations
- D. Resource health for the database

**Answer:** B

**Explanation:** Query Performance Insight in the portal is built on Query Store and lists top resource-consuming queries by CPU, duration or execution count with query text. Advisor recommendations list suggested indexes and plan fixes rather than consumption. Resource health reports platform availability events. SQL Insights was a separate monitoring solution that has been retired and was never the built-in top-query view.

### Question 7 of 10

An elastic job must connect to target databases without storing passwords. Security requires that no database-scoped credential holds a secret. How should you configure authentication for the elastic job agent?

- A. az deployment group what-if with the Bicep file and its parameters
- B. az bicep build to transpile the file
- C. az deployment group validate with the Bicep file and parameter file
- D. Use a shared access signature token for the target servers stored in a database-scoped credential

**Answer:** A

**Explanation:** Elastic jobs support a user-assigned managed identity for the agent; you create a database user FROM EXTERNAL PROVIDER for it in the targets, so no secret is stored. A database-scoped credential with a password still holds a secret. The SQL Server Contributor Azure role manages resources but grants no data-plane access in the databases, and SAS tokens are for storage, not database logins.

### Question 8 of 10

A KQL query in a Log Analytics workspace detects failed logins to Azure SQL from audit logs. The team wants a notification when more than 20 failures occur in 10 minutes. What should be created?

- A. A metric alert on the failed connections metric with a dynamic threshold
- B. A Defender for SQL vulnerability assessment rule with a baseline
- C. An activity log alert scoped to the logical server for sign-in failures
- D. A log search alert rule that runs the query with a threshold of 20

**Answer:** D

**Explanation:** Log search alert rules run a KQL query on a schedule and fire when the result crosses a threshold, which matches a custom audit query. A metric alert can work on the platform metric but cannot use the custom KQL logic over audit data, activity log alerts capture control-plane operations, and vulnerability assessment baselines do not monitor logins.

### Question 9 of 10

An Azure SQL Database must survive a full regional outage. The application needs a connection string that does not change after failover, and both the read-write and read-only workloads must be redirected automatically. What should you configure?

- A. Zone-redundant configuration on the Business Critical tier
- B. Geo-restore from geo-redundant backups into another region
- C. Active geo-replication with one readable secondary in the paired region
- D. A failover group with the read-write and read-only listener endpoints

**Answer:** D

**Explanation:** Failover groups provide listener endpoints for read-write and read-only traffic that keep pointing at the right replica after failover. Active geo-replication alone requires the application to change connection strings. Geo-restore has a longer recovery time and creates a new database name, and zone redundancy protects against a zone failure, not a regional outage.

### Question 10 of 10

Which recovery model must a SQL Server database on an Azure VM use to support transaction log backups and point-in-time restore?

- A. BULK_LOGGED for all workloads
- B. SIMPLE with differential backups
- C. SIMPLE
- D. FULL

**Answer:** D

**Explanation:** The FULL recovery model keeps the log until it is backed up, enabling log backups and point-in-time restore to any moment. SIMPLE truncates the log automatically, so even with differential backups you can restore only to backup points, and BULK_LOGGED cannot restore to a point inside a log backup that contains minimally logged operations.

Ready for a timed run? Take the free 15-question DP-300 mini exam: https://devopscodelab.com/app/exams/microsoft-dp-300/mini/

DevOpsCode Lab is not affiliated with Microsoft. DP-300 and related names are trademarks of their owners. We write every question ourselves; these are not real exam questions.
