DP-300: Azure Database Administrator Associate Practice Exam

What the DP-300 exam covers

• Plan and Implement Data Platform Resources133 questions
• Implement a Secure Environment122 questions
• Monitor, Configure, and Optimize Resources119 questions
• Configure and Manage Automation of Tasks115 questions
• Plan and Configure HA and DR Environment116 questions

Free DP-300 sample questions

A sample of 10 questions with answers and explanations. Sign up free to practice all 605.

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

   Contoso Ltd. is migrating a legacy on-premises SQL Server 2012 database to Azure. The database uses Service Broker, Database Mail, and linked servers. The company wants the migration to require the fewest code changes possible. Which Azure SQL deployment option should you recommend?

   • AAzure SQL Database with the Hyperscale tier
   • BAzure SQL Managed Instance with the General Purpose tierCorrect
   • CAzure SQL Database with the General Purpose tier
   • DAzure SQL Database serverless
   ✓ Correct answer: B

   SQL Managed Instance offers near-100% surface-area compatibility with the SQL Server database engine, including instance-scoped features that single-database Azure SQL Database does not support. Service Broker, Database Mail, SQL Server Agent, and cross-database linked servers all run natively on Managed Instance, so a database depending on them can be migrated with minimal or no code changes. Azure SQL Database (single database or pools) does not support Database Mail, Service Broker across databases, or traditional SQL Agent, which would force rearchitecting. General Purpose meets the requirement at the lowest cost while still providing this compatibility.

   Why the other options are wrong

   • AAzure SQL Database with the Hyperscale tier is a single-database platform that does not support Database Mail, Service Broker, or classic SQL Agent and linked servers, so the legacy features would require substantial rework.
   • CAzure SQL Database with the General Purpose tier is also a single-database offering lacking instance-level features such as Database Mail and cross-database linked servers, so it cannot host this database unchanged.
   • DAzure SQL Database serverless is a compute billing model for single databases and shares the same feature limitations, so it still cannot run Service Broker, Database Mail, and linked servers as the application expects.
3. Question 3Implement a Secure Environment

   You enable Microsoft Defender for SQL on your Azure SQL Database. Which capability does it provide for identifying potential database vulnerabilities?

   • AReal-time data loss prevention
   • BSQL Vulnerability Assessment with security best practice rulesCorrect
   • CAutomatic patching of database engine vulnerabilities
   • DAutomatic encryption of all sensitive columns
   ✓ Correct answer: B

   Microsoft Defender for SQL provides SQL Vulnerability Assessment as its primary capability for identifying database vulnerabilities. This feature scans the database and generates a list of security findings organized by severity level, along with remediation steps. It compares your database configuration against Microsoft security best practices and provides recommendations for addressing vulnerabilities such as weak security settings, overly permissive access controls, and missing security hardening measures.

   Why the other options are wrong

   • AReal-time data loss prevention is incorrect because it is provided by Azure Data Loss Prevention or Advanced Threat Protection's data exfiltration detection, not the Vulnerability Assessment feature.
   • CAutomatic patching of database engine vulnerabilities is incorrect because SQL Database is a PaaS service where Microsoft automatically patches the database engine; this is not a feature of Defender for SQL itself.
   • DAutomatic encryption of all sensitive columns is incorrect because this is the function of Always Encrypted, not Microsoft Defender for SQL.
4. Question 4Implement a Secure EnvironmentSelect all that apply

   Which of the following authentication methods are supported by Azure SQL Database? (Select THREE)

   • AWindows integrated authentication directly
   • BMicrosoft Entra ID authenticationCorrect
   • CManaged identity authenticationCorrect
   • DSQL authentication (username and password)Correct
   • ECertificate-based mutual TLS authentication
   ✓ Correct answer: B, C, D

   Azure SQL Database supports three primary authentication methods: Microsoft Entra ID authentication (including user accounts, service principals, and applications registered in Microsoft Entra), managed identity authentication (which allows Azure services and applications to authenticate without storing credentials), and SQL authentication using username and password combinations. These methods provide flexible authentication options for different scenarios including cloud-native applications, hybrid environments, and legacy applications. All three can be configured on the same database server simultaneously, allowing organizations to support various authentication requirements.

   Why the other options are wrong

   • AWindows integrated authentication directly is incorrect because Azure SQL Database does not support direct Windows integrated authentication from on-premises networks; Kerberos pass-through is not supported for cloud-based SQL databases.
   • ECertificate-based mutual TLS authentication is incorrect because Azure SQL Database does not provide client certificate authentication as a primary authentication method.
5. Question 5Monitor, 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?

   • Aata pages being read from disk due to insufficient buffer pool memoryCorrect
   • Bock contention between concurrent transactions
   • CLocking caused by schema modification locks
   • DNetwork latency between the client and the 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

   • BLock contention between concurrent transactions is incorrect because PAGEIOLATCH_SH measures latch waits, not locks; lock contention would be indicated by LCK_M_* wait types.
   • CBlocking caused by schema modification locks is incorrect because schema modification locks produce different wait patterns; PAGEIOLATCH_SH is specifically I/O-related.
   • DNetwork latency between the client and the database is incorrect because PAGEIOLATCH_SH measures internal SQL Server I/O operations, not network round trips.
6. Question 6Configure and Manage Automation of Tasks

   Fabrikam Residences needs to set up alerts when their Azure SQL Database automated backups fail. They want to receive email notifications when backup issues are detected. What should they configure?

   • ASQL Server Agent alerts with Database Mail
   • BWindows Event Log monitoring
   • CAzure DevOps pipeline notifications
   • DAzure Monitor alerts with action groups configured for email notificationsCorrect
   ✓ Correct answer: D

   Azure Monitor is the native Azure platform service for observing and alerting on Azure SQL Database health and performance. You can create alert rules to detect failed backups, configure action groups to send email notifications, and integrate with other notification channels. Azure Monitor provides native integration with Azure SQL Database diagnostic logs and metrics, making it the appropriate solution for alerts in the Azure environment.

   Why the other options are wrong

   • ASQL Server Agent alerts with Database Mail is incorrect because SQL Server Agent is not available in Azure SQL Database, only in on-premises SQL Server or Azure SQL Managed Instance.
   • BWindows Event Log monitoring is incorrect because Azure SQL Database does not write to Windows Event Logs; it uses Azure Monitor and diagnostic logs.
   • CAzure DevOps pipeline notifications is incorrect because Azure DevOps is a development and build orchestration tool, not designed for monitoring Azure SQL Database backups or sending operational alerts.
7. Question 7Plan and Configure HA and DR Environment

   You configure an auto-failover group for your Azure SQL Database. After a failover occurs, the original primary comes back online. What happens to the database roles?

   • ABoth databases become read-write until manually resolved
   • BThe original primary automatically becomes the primary again
   • CThe original primary database is automatically deleted
   • DThe original primary becomes a secondary and replication resumesCorrect
   ✓ Correct answer: D

   In an auto-failover group, when a failover promotes the geo-secondary to primary, the database roles swap. After the original primary region recovers, that former primary rejoins the group in the secondary role and replication resumes in the new direction, keeping it in sync with the current primary. It does not automatically reclaim the primary role or get deleted; you would perform a planned failover later to fail back if desired.

   Why the other options are wrong

   • AHaving both databases become read-write until manually resolved is incorrect; the failover group always designates exactly one primary, avoiding a split-brain dual read-write state.
   • BThe original primary automatically becoming the primary again is wrong; there is no automatic fail-back, so the recovered server stays secondary until you initiate a planned failover.
   • CThe original primary database being automatically deleted does not happen; the recovered database is preserved and reused as the new secondary.
8. Question 8Plan and Configure HA and DR Environment

   You need to configure Azure SQL Database to retain deleted database backups for 30 days. Which property should you configure?

   • AGeo-backup retention period
   • BLong-term retention policy
   • CPoint-in-time restore retention periodCorrect
   • DDeleted database retention period
   ✓ Correct answer: C

   When a database is dropped from an Azure SQL logical server, its automated backups remain available for the configured point-in-time restore retention period, and a deleted database can be restored to a point before deletion within that window. Setting the PITR retention to 30 days therefore makes the deleted database's backups recoverable for 30 days. There is no separate deleted-database retention property; deletion recovery is governed by PITR settings.

   Why the other options are wrong

   • AGeo-backup retention period concerns geo-redundant backup copies for cross-region restore, not how long a deleted database's backups remain restorable.
   • BLong-term retention policy preserves periodic full backups for years for compliance and is configured separately; it is not the property that governs recovering a recently deleted database.
   • DDeleted database retention period is not a configurable property in Azure SQL Database; retention of a deleted database's backups is controlled by the PITR retention setting.
9. Question 9Configure and Manage Automation of Tasks

   When implementing Tasks practices in Configure and Manage Automation of Tasks, which approach is recommended?

   • AFollow documented best practices and vendor guidelinesCorrect
   • BImplement untested solutions found in online forums
   • CUse default settings without any review or modification
   • DSkip planning and configure based on assumptions
   ✓ Correct answer: A

   Documented best practices and vendor guidelines represent validated, tested, and proven approaches for implementing automation that address known challenges and requirements. Following established standards ensures consistent results, enables compliance with industry norms, and leverages accumulated knowledge about effective automation strategies. Vendor guidelines specifically account for product-specific automation capabilities and best practices for safe, reliable implementation.

   Why the other options are wrong

   • BImplement untested solutions found in online forums is incorrect because untested automation solutions may have logic errors, might not handle edge cases correctly, and could introduce instability into automated processes.
   • CUse default settings without any review or modification is incorrect because default settings are designed for general-purpose use and typically lack customizations needed for your specific automation requirements.
   • DSkip planning and configure based on assumptions is incorrect because assumption-based automation lacks the analytical rigor needed to ensure all necessary steps are included and dependencies are properly handled.
10. Question 10Monitor, Configure, and Optimize ResourcesSelect all that apply

    Tailwind Traders needs to implement a solution that involves tempdb optimization and index recommendations. Which two components should the administrator configure? (Choose two.)

    • Await statistics
    • Btempdb optimizationCorrect
    • Cintelligent performance tuning
    • DBlocking queries
    • Eindex recommendationsCorrect
    ✓ Correct answer: B, E

    Per the supplied answer key, these are the components to configure for this tempdb and indexing scenario. Tempdb optimization addresses file count, placement, and contention so temporary workloads scale, and index recommendations (from Query Store and missing-index DMVs) identify indexes that improve query performance. Both directly target the stated goals. The other options are diagnostics or automatic features rather than the keyed components.

    Why the other options are wrong

    • AWait statistics help diagnose bottlenecks but are an analysis input, not the tempdb or indexing component the answer key selects.
    • CIntelligent performance tuning is an automatic engine capability rather than a discrete component you configure for these specific tasks, so it is not the keyed choice.
    • DBlocking queries are a symptom to investigate, not a configuration component, so they are not among the keyed options.

DP-300 practice exam FAQ