DP-300
Free DP-300 practice questions
10 questions from our DP-300 practice tests, each with the correct answer and a written explanation. Read them with the answers shown, or switch to quiz mode and answer first.
Question 1 of 10, Plan and implement data platform resources
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?
- AAzure SQL Database in a vCore-based elastic pool with elastic jobs replacing SQL Agent
- BSQL Server 2022 on an Azure virtual machine registered with the SQL IaaS Agent extension
- CAzure SQL Managed Instance in the General Purpose service tier, migrated with Database Migration Service Correct answer
- DAzure SQL Database single database in the Hyperscale service tier
Answer and 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, Plan and implement data platform resources
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?
- ALog Replay Service restoring the backups from Blob Storage onto the managed instance Correct answer
- BAzure Data Factory copy activities that move each table from SQL Server into the managed instance
- CTransactional replication with the managed instance configured as a publisher
- DA BACPAC export imported into the managed instance through the Azure portal
Answer and 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, Implement a secure environment
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?
- AAlways Encrypted with randomized encryption and give billing access to the column master key
- BRow-level security with a filter predicate that hides rows from support agents
- CDynamic data masking on the columns and grant UNMASK to the billing team Correct answer
- DTransparent data encryption with a customer-managed key so agents cannot read the files
Answer and 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, Implement a secure environment
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?
- ADynamic data masking on the TenantId column plus a CHECK constraint that compares the column to the value returned by SESSION_CONTEXT
- BA separate view per tenant that filters on TenantId, with INSTEAD OF triggers that reject inserts for other tenants
- CA security policy with only a filter predicate that calls an inline function comparing TenantId to SESSION_CONTEXT
- DA security policy with a filter predicate and an AFTER INSERT block predicate, both calling an inline function that compares TenantId to SESSION_CONTEXT Correct answer
Answer and 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, Monitor, configure, and optimize database resources
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?
- AOPTIMIZE_FOR_AD_HOC_WORKLOADS = ON Correct answer
- BLEGACY_CARDINALITY_ESTIMATION = ON for the database
- CPARAMETER_SNIFFING = OFF
- DELEVATE_ONLINE = WHEN_SUPPORTED
Answer and 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, Monitor, configure, and optimize database resources
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?
- ASQL Insights
- BQuery Performance Insight Correct answer
- CDatabase advisor recommendations
- DResource health for the database
Answer and 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, Configure and manage automation of tasks
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?
- Aaz deployment group what-if with the Bicep file and its parameters Correct answer
- Baz bicep build to transpile the file
- Caz deployment group validate with the Bicep file and parameter file
- DUse a shared access signature token for the target servers stored in a database-scoped credential
Answer and 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, Configure and manage automation of tasks
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?
- AA metric alert on the failed connections metric with a dynamic threshold
- BA Defender for SQL vulnerability assessment rule with a baseline
- CAn activity log alert scoped to the logical server for sign-in failures
- DA log search alert rule that runs the query with a threshold of 20 Correct answer
Answer and 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, Plan and configure a high availability and disaster recovery (HA/DR) environment
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?
- AZone-redundant configuration on the Business Critical tier
- BGeo-restore from geo-redundant backups into another region
- CActive geo-replication with one readable secondary in the paired region
- DA failover group with the read-write and read-only listener endpoints Correct answer
Answer and 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, Plan and configure a high availability and disaster recovery (HA/DR) environment
Which recovery model must a SQL Server database on an Azure VM use to support transaction log backups and point-in-time restore?
- ABULK_LOGGED for all workloads
- BSIMPLE with differential backups
- CSIMPLE
- DFULL Correct answer
Answer and 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.