Free DP-300 Practice Questions

Question 1

A ticket-reservation query in Azure SQL Database slows sharply at 09:10. Query Store shows that query_id 84 changed from plan_id 301 to plan_id 417 at that time. Representative executions take 90 ms with plan 301 and 1,800 ms with plan 417; testing against current data confirms the difference. The required indexes still exist, resource utilization is below service limits, and other queries are unaffected. Application code cannot be changed during the incident. What is the most targeted way to restore performance?

Accepted Answer

Force plan 301 for query 84 in Query Store and verify that runtime improves.

Answer

Increase the database's compute size while retaining plan 417.

Answer

Clear all cached plans in the database to trigger fresh compilation.

Answer

Take the application offline for a full rebuild of the query's indexes.

Question 2

A software vendor is moving a SQL Server application to Azure without rewriting its database code. Several procedures use three-part names to query two databases, and scheduled work consists entirely of T-SQL SQL Server Agent jobs. No component requires operating-system access, and an assessment found no other compatibility blockers. The operations team will not manage operating-system patches or database-engine servicing. Which destination meets these constraints?

Accepted Answer

Azure SQL Managed Instance

Answer

SQL Server on an Azure virtual machine

Answer

Azure SQL Database, placing both databases in one elastic pool

Answer

SQL database in Microsoft Fabric within a shared workspace

Question 3

A compensation application stores salary amounts in Azure SQL Database. Database administrators must manage the database without access to plaintext salaries or the application's encryption keys. The application must also issue parameterized salary-range queries; downloading and decrypting the entire table for client-side filtering is prohibited. The database supports VBS enclaves, uses compatibility level 160, and the application has an enclave-capable driver. Which column-protection design supports the required filtering?

Accepted Answer

Randomized Always Encrypted with enclave-enabled keys and a VBS enclave.

Answer

Deterministic Always Encrypted with externally held keys and no enclave.

Answer

Randomized Always Encrypted with externally held keys and no enclave.

Answer

Transparent Data Encryption with a customer-managed key in Azure Key Vault.

Question 4

At 10:14 UTC, an erroneous DELETE commits in an Azure SQL database and removes completed orders. It is discovered at 10:25 UTC after valid transactions have continued in unrelated rows. Those transactions must be retained. The affected keys are known, and those rows were unchanged between 10:13 UTC and the deletion. A point-in-time restore to 10:13 UTC is available, and reinserting the deleted rows will not conflict with subsequent work. Change Tracking, but not Change Data Capture, is enabled on the table. Select the recovery plan that restores the orders without discarding valid work.

Accepted Answer

Restore to 10:13 UTC as a new database, then copy the verified missing orders into production.

Answer

Stop production and replace it with a database restored to 10:13 UTC.

Answer

Fail over to the caught-up geo-secondary and recover the deleted orders there.

Answer

Use Change Tracking to retrieve each deleted order's prior values and reinsert the complete rows into production.

Question 5

A SQL Server Agent job on Azure SQL Managed Instance loads staging data in step 1 and writes a completion record in step 2. The latest history shows:

Step 1: Failed
Step 2: Succeeded
Overall job: Succeeded

E-mail is configured for 'When the job fails,' and the operator receives failure messages from other jobs through the same mail configuration. Make one change so a failed load ends this job and triggers its configured notification.

Accepted Answer

Set step 1's failure action to 'Quit the job reporting failure.'

Answer

Notify the operator whenever the job completes.

Answer

Increase step 1's retries, keeping its failure transition.

Answer

Restart Agent to reload the mail profile.

Question 6

An Azure App Service connects to an Azure SQL database using its managed identity. The Entra-backed database user [stock-reader] exists, authentication succeeds, and the identity has Azure Contributor access to the logical server. SELECT against reporting.vw_StockAvailability fails with error 229. The application must read this view but must not read the other views in the reporting schema. The view has an intact ownership chain, and no DENY applies. Choose the narrowly scoped permission change.

Accepted Answer

GRANT SELECT ON OBJECT::reporting.vw_StockAvailability TO [stock-reader];

Answer

ALTER ROLE db_datareader ADD MEMBER [stock-reader];

Answer

GRANT SELECT ON SCHEMA::reporting TO [stock-reader];

Answer

Assign the managed identity the Azure Owner role for the entire resource group.

Question 7

A SQL Server 2022 instance on a Linux Azure VM has had no restart or counter reset. During a ten-minute slowdown, the DBA takes two snapshots for the same data file from sys.dm_io_virtual_file_stats:

Start: num_of_reads = 10,000; io_stall_read_ms = 40,000
End: num_of_reads = 12,000; io_stall_read_ms = 124,000

The comparable workload baseline is 4 ms per read. PAGEIOLATCH_SH waits have increased while CPU utilization remains low. Which finding correctly interprets the incident interval?

Accepted Answer

42 ms/read; investigate the physical-read path alongside storage metrics and workload changes.

Answer

10.3 ms/read; investigate physical storage latency.

Answer

7 ms/read; compare physical-read performance with the workload baseline.

Answer

42 ms/read; treat this as tempdb allocation-latch contention and review the number of data files.

Question 8

An online migration from SQL Server to Azure SQL Managed Instance has completed its initial seed through a supported migration workflow. The source remains writable, and several recent checks report zero replication lag. The permitted write outage is five minutes. Rehearsals show that final synchronization and validation fit this window, whereas a fresh full reload takes four hours. A proposed cutover would simply change connection strings while source writes continue. What must replace that proposal to preserve the final transactions?

Accepted Answer

Freeze source writes, synchronize, validate, then redirect clients.

Answer

Switch connections when lag reaches zero, leaving source writes enabled.

Answer

Stop writes for a full backup and four-hour reload, then redirect clients.

Answer

Quiesce source writes, redirect clients immediately, and apply the remaining source changes after target writes begin.

Question 9

A shared Azure SQL database uses a row-level security filter predicate on dbo.Orders to restrict rows by TenantId. A trusted middle tier supplies the tenant identity in session context; callers cannot change it. A tenant 27 session sees only its own rows. However, inserting a row with TenantId 64 succeeds, and changing an existing row's TenantId from 27 to 64 also succeeds. Each row then disappears from that session's queries. Legitimate inserts and updates for tenant 27 must remain allowed. The existing policy needs:

Accepted Answer

AFTER INSERT and AFTER UPDATE block predicates using the tenant-validation function.

Answer

An AFTER UPDATE block predicate only.

Answer

The tenant function in BEFORE UPDATE and BEFORE DELETE block predicates.

Answer

A DENY of the application's INSERT and UPDATE permissions on dbo.Orders to prevent cross-tenant writes.

Question 10

A SQL Server 2022 availability group on Windows Azure VMs uses synchronous commit. Before a planned failover, the secondary reports SYNCHRONIZED and the log-send queue is empty, but its redo queue is large. Recovery rehearsals with this backlog have consistently taken several minutes. The maintenance plan requires no data loss and an RTO of 30 seconds. The primary is healthy and maintenance can be delayed. How should the DBA assess failover readiness?

Accepted Answer

Reduce the redo backlog and retest recovery time before the planned failover.

Answer

Proceed; SYNCHRONIZED means all log records have been redone.

Answer

Use asynchronous commit to avoid the secondary's pending redo work.

Answer

Re-seed the secondary because redo backlog means synchronization failed.

Free DP-300 Practice Questions

Domain 1: Plan and implement data platform resources (15-20%)

Question 1

Question 2

Domain 2: Implement a secure environment (20-25%)

Question 3

Question 4

Question 5

Domain 3: Monitor, configure, and optimize database resources (20-25%)

Question 6

Question 7

Domain 4: Configure and manage automation of tasks (15-20%)

Question 8

Domain 5: Plan and configure a high availability and disaster recovery (HA/DR) environment (20-25%)

Question 9

Question 10

That's 10 of 1,030