DP-300: Azure Database Administrator Associate Practice Exam

What the DP-300 exam covers

• Plan and Implement Data Platform Resources151 questions
• Implement a Secure Environment140 questions
• Monitor, Configure, and Optimize Resources140 questions
• Configure and Manage Automation of Tasks125 questions
• Plan and Configure HA and DR Environment143 questions

Free DP-300 practice test questions

A sample of 10 questions with answers and explanations. A free account includes a fixed set of 20 practice questions for this exam, retryable anytime. Paid access unlocks the full 699-question bank.

1. Question 1Plan and Implement Data Platform Resources

   A mid-sized e-commerce company expects unpredictable traffic spikes during seasonal sales events. The database team needs to deploy multiple Azure SQL databases that share compute and storage resources to reduce costs while handling variable workloads. Which Azure SQL deployment option should the team choose?

   • ADeploy each database as a standalone Azure SQL Database with the General Purpose tier
   • BDeploy a single Azure SQL Managed Instance and create all databases inside it
   • CDeploy SQL Server on an Azure Virtual Machine with multiple databases
   • DDeploy all databases within an Azure SQL Database elastic poolCorrect
   ✓ Correct answer: D

   An elastic pool is the optimal choice for this scenario because it allows multiple databases to share a pool of compute and storage resources at a lower cost than provisioning individual databases. The elastic pool's dynamic resource allocation automatically adjusts compute capacity across databases based on their real-time workload demands, making it ideal for handling unpredictable traffic spikes. This provides cost efficiency while ensuring that each database receives adequate resources when needed during seasonal peaks.

   Why the other options are wrong

   • ADeploy each database as a standalone Azure SQL Database with the General Purpose tier is incorrect because standalone databases cannot share resources, resulting in higher costs and potential underutilization during low-traffic periods when you're still paying for full capacity.
   • BDeploy a single Azure SQL Managed Instance and create all databases inside it is incorrect because while Managed Instance can host multiple databases, it is optimized for on-premises compatibility scenarios rather than cost-effective resource sharing for variable workloads in SaaS multi-tenant architectures.
   • CDeploy SQL Server on an Azure Virtual Machine with multiple databases is incorrect because VMs require manual management of compute scaling, backup coordination, and security patching, increasing operational overhead without the automatic resource allocation benefits of elastic pools.
2. Question 2Plan and Implement Data Platform Resources

   Azure SQL Managed Instance supports both the vCore purchasing model and the DTU purchasing model.

   • AFalseCorrect
   • BTrue
   ✓ Correct answer: A

   Azure SQL Managed Instance uses only the vCore purchasing model based on virtual cores and does not support the DTU (Database Transaction Unit) model. The vCore model provides more granular control over compute resources, allows independent scaling of compute and storage, and supports Azure Hybrid Benefit for license cost savings. The DTU model bundles compute, storage, and I/O into fixed packages and is available only for Azure SQL Database single databases and elastic pools, not for Managed Instance deployments. The statement is false because the DTU purchasing model is exclusive to Azure SQL Database, while Managed Instance exclusively uses the vCore-based model.

   Why the other options are wrong

   • BTrue is incorrect. The statement is false. Azure SQL Managed Instance uses only the vCore purchasing model based on virtual cores and does not support the DTU (Database Transaction Unit) model. The vCore model provides more granular control over compute resources, allows independent scaling of compute and storage, and supports Azure Hybrid Benefit for license cost savings.
3. Question 3Implement a Secure EnvironmentSelect all that apply

   You need to classify and protect sensitive data in an Azure SQL Database. Which two features should you use together to discover and label sensitive columns? (Choose two.)

   • AMicrosoft Purview Information Protection integrationCorrect
   • BTransparent Data Encryption of the storage layer
   • CSQL Server Data Tools schema projects in Visual Studio
   • DData Discovery and ClassificationCorrect
   ✓ Correct answer: A, D

   Discovering and labeling sensitive columns combines the engine-level classifier with enterprise labeling. Data Discovery and Classification scans the database, recommends which columns hold sensitive information, and stores classification metadata on those columns. Microsoft Purview Information Protection integration lets you apply consistent organization-wide sensitivity labels to that data so protection and policy follow the data. Used together they provide both detection and standardized labeling across the estate.

   Why the other options are wrong

   • BTDE encrypts data at rest at the storage layer and performs no discovery or column-level classification of sensitive data.
   • CSSDT is a schema development and deployment environment, not a data discovery or classification feature.
4. Question 4Monitor, Configure, and Optimize Resources

   You are analyzing wait statistics for an Azure SQL Database using sys.dm_db_wait_stats. You observe a high number of PAGEIOLATCH_SH waits. What does this typically indicate?

   • AData pages being read from disk due to insufficient buffer pool memoryCorrect
   • BLock contention between concurrent transactions competing for the same rows
   • CBlocking caused by concurrent schema modification (Sch-M) locks
   • DNetwork latency on the round trip between the client and database
   ✓ Correct answer: A

   PAGEIOLATCH_SH (shared page I/O latch) waits occur when SQL Server must wait to read data pages from disk into the buffer pool. This wait type specifically indicates I/O bottlenecks caused by insufficient buffer pool memory, forcing the database engine to perform physical disk reads rather than accessing pages already cached in memory. The "SH" indicates a shared latch, which is acquired when reading data. High PAGEIOLATCH_SH waits suggest the working set is larger than available memory, requiring frequent disk I/O operations.

   Why the other options are wrong

   • BPAGEIOLATCH_SH is a latch, not a lock; lock contention surfaces as LCK_M_* wait types instead.
   • CSchema modification locks produce lock waits, whereas PAGEIOLATCH_SH is specifically a data-page I/O wait.
   • DPAGEIOLATCH_SH measures internal storage I/O for buffer pages, not client network round trips.
5. Question 5Configure and Manage Automation of Tasks

   You need to create an Azure Automation runbook that connects to an Azure SQL Database and executes a T-SQL script. What type of credential should the runbook use to authenticate without storing passwords?

   • AA system-assigned managed identity for the Automation accountCorrect
   • BA hardcoded connection string in the runbook script code
   • CAn Azure Automation Run As account using a certificate
   • DA stored SQL authentication credential in a runbook asset
   ✓ Correct answer: A

   Azure Automation requests a token for the Automation account's managed identity at run time, so no secret is stored in the runbook, in an Automation asset, or in the account's configuration. The runbook passes that access token on the connection, and Azure SQL Database validates it against Microsoft Entra ID and maps it to a contained database user created for the identity with CREATE USER ... FROM EXTERNAL PROVIDER. Because the token is short lived and issued per request there is nothing to rotate and nothing to leak, and the permissions are granted to that identity rather than to a login shared with anything else.

   Why the other options are wrong

   • BHardcoding a connection string embeds credentials in the runbook, which is insecure and unnecessary with a managed identity.
   • CRun As accounts were retired on 30 September 2023 in favour of managed identities, and even while supported they required certificate lifecycle management rather than being passwordless.
   • DStoring SQL credentials as an Automation asset still requires secret management and is less secure than a managed identity.
6. Question 6Plan and Configure HA and DR Environment

   Nod Publishers runs a SQL Server Always On Availability Group across two Azure VMs in different Availability Zones. They need to add a third replica in a different Azure region for disaster recovery. How should you configure the replication mode for the remote replica?

   • AAsynchronous commit mode with automatic failover
   • BSynchronous commit mode with manual failover
   • CSynchronous commit mode with automatic failover
   • DAsynchronous commit mode with manual failoverCorrect
   ✓ Correct answer: D

   A disaster-recovery replica in a distant Azure region should use asynchronous commit mode so the primary does not wait for acknowledgment across high-latency inter-region links, preserving primary transaction throughput. It should also use manual failover, because failing over to another region is a deliberate disaster decision that an administrator should evaluate and trigger rather than have the cluster do automatically. The two local replicas in the Availability Zones, by contrast, would typically use synchronous commit to support automatic local failover.

   Why the other options are wrong

   • AAsynchronous commit mode with automatic failover is not a valid combination, because Always On permits automatic failover only with synchronous-commit replicas that are kept fully synchronized; an asynchronous replica can never be an automatic failover target.
   • BSynchronous commit mode with manual failover is unsuitable for the remote region, because synchronous commit forces the primary to wait for the distant replica to harden each transaction, and the inter-region latency would degrade primary performance.
   • CSynchronous commit mode with automatic failover is wrong for the remote replica, because synchronous commit imposes the cross-region latency penalty on the primary and automatic failover across regions removes the deliberate administrator control a DR site requires.
7. Question 7Monitor, Configure, and Optimize ResourcesSelect all that apply

   Queries on an Azure SQL Database intermittently accumulate RESOURCE_SEMAPHORE waits, and under concurrency some fail with query-memory-grant timeouts. You want to see which executing requests are asking for large query-memory grants and how much grant memory is currently available. Which TWO dynamic management views should you query? (Choose two.)

   • Asys.dm_exec_query_memory_grantsCorrect
   • Bsys.dm_exec_query_resource_semaphoresCorrect
   • Csys.dm_os_memory_cache_counters
   • Dsys.dm_resource_governor_workload_groups
   ✓ Correct answer: A, B

   sys.dm_exec_query_memory_grants lists each executing request that requested or received a query-memory grant, including requested and granted kilobytes and whether it is still waiting. sys.dm_exec_query_resource_semaphores shows each semaphore's target and available memory and how many grants are waiting. Read together, they reveal whether oversized grants and a saturated semaphore are driving the RESOURCE_SEMAPHORE waits. The cache-counter and Resource Governor views describe entirely different memory areas.

   Why the other options are wrong

   • Csys.dm_os_memory_cache_counters reports memory used by internal caches such as the plan cache, not per-request query-memory grants or semaphore availability.
   • Dsys.dm_resource_governor_workload_groups exposes Resource Governor group configuration and stats; Resource Governor is unavailable in single-database Azure SQL Database and shows no individual pending grants.
8. Question 8Implement a Secure Environment

   How does a private endpoint differ from a VNet service endpoint when securing access to an Azure SQL Database?

   • AIt gives the server a private IP in your VNetCorrect
   • BIt keeps the public IP but hides the port
   • CIt removes the need for a private DNS zone
   • DIt encrypts the connection at the storage layer
   ✓ Correct answer: A

   A VNet service endpoint extends your virtual network identity to the Azure SQL service over the Azure backbone, but the server still uses its public IP and firewall rules. A private endpoint instead projects the logical server into your VNet as a network interface with a private IP, so traffic never touches the public endpoint and the resource is reachable from peered VNets and on-premises networks. Private endpoints therefore provide stronger isolation than service endpoints.

   Why the other options are wrong

   • BA service endpoint does not hide a port; it keeps the public IP and controls access by virtual network, not by assigning a private IP.
   • CA private endpoint actually requires a private DNS zone for FQDN resolution rather than removing the need for one.
   • DNeither feature adds storage-layer encryption; both are network access controls, and encryption in transit is handled by TLS.
9. Question 9Plan and Configure HA and DR Environment

   Your Azure SQL Database occasionally experiences brief connection drops during Azure planned maintenance, which coincide with your busiest hours. You want to move these maintenance-related failovers to a predictable off-peak time without changing the service tier. What should you configure?

   • AA maintenance window that schedules planned maintenance to off-peak hoursCorrect
   • BA read-only replica so maintenance never touches the primary database
   • CAn auto-failover group so maintenance is redirected to the secondary region
   • DAutomatic tuning to defer index operations until the maintenance window
   ✓ Correct answer: A

   Azure periodically performs planned maintenance that can cause a short reconfiguration or failover. Configuring a maintenance window moves these events to a predictable off-peak block, for example weekday nights, so users are less affected, and it works without upgrading the service tier. A read-only replica and an auto-failover group address read scaling and regional DR, not the timing of planned maintenance, and automatic tuning manages indexes and plans, not maintenance scheduling.

   Why the other options are wrong

   • BA read-only replica offloads reads but does not control when Azure applies planned maintenance to the primary.
   • CAn auto-failover group provides cross-region DR; it does not reschedule routine planned maintenance to off-peak hours.
   • DAutomatic tuning manages indexes and execution plans and has no effect on the timing of platform maintenance.
10. Question 10Plan and Configure HA and DR Environment

    You are designing DR for an Azure SQL Managed Instance using a failover group. A networking requirement applies because the primary and secondary instances are in different regions. Which networking prerequisite must be satisfied for the failover group to function?

    • AThe virtual networks of the two managed instances must be connected (for example via global VNet peering or a VPN/ExpressRoute) with non-overlapping address spacesCorrect
    • BBoth managed instances must be deployed into the same delegated subnet inside one shared virtual network that is stretched across both of the paired regions
    • CPublic data endpoints must be explicitly disabled on both managed instances and all of their private endpoints removed before the failover group can be created
    • DThe two managed instance virtual networks must be configured with overlapping IP address ranges so that cross-region routing between the subnets stays symmetric
    ✓ Correct answer: A

    Because SQL Managed Instance lives inside a virtual network, a cross-region failover group requires network connectivity between the two instances' VNets, typically through global VNet peering or a gateway connection (VPN/ExpressRoute). The address spaces must not overlap so that traffic can route between the instances. This connectivity supports replication and the failover group endpoints.

    Why the other options are wrong

    • BInstances in different regions cannot share a subnet; each needs its own VNet, which are then connected together.
    • CDisabling public endpoints is not a prerequisite; private connectivity between the two VNets is what the failover group requires.
    • DOverlapping address spaces break routing between the VNets; the address spaces must be non-overlapping.

Who this DP-300 practice exam is for

This practice set is for anyone preparing for the DP-300: Azure Database Administrator Associate exam at the intermediate level - from first-time candidates building a foundation to experienced Microsoft practitioners doing a final review before test day. If you learn best by working through realistic questions and reading why each answer is right or wrong, it is built for you.

How to use this DP-300 practice exam

1. Start with the free sample questions above to gauge your current baseline.
2. Read the full explanation on every question, including why each wrong option is wrong.
3. Track your weak domains and focus your study where you are losing the most marks.
4. Once you are scoring consistently well, take a timed, full-length mock exam.
5. Use your readiness score to decide when you are ready to book the real DP-300 exam.

Related Microsoft resources

• DP-300 study guideKey concepts
• Microsoft practice examsAll Microsoft
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• How these questions are written and reviewedMethodology
• Report a problem with a questionCorrections
• DP-420 practice examRelated
• DP-600 practice examRelated
• DP-700 practice examRelated

DP-300 practice exam FAQ