DP-300: Azure Database Administrator Associate Practice Exam

What the DP-300 exam covers

• Plan and Implement Data Platform Resources138 questions
• Implement a Secure Environment128 questions
• Monitor, Configure, and Optimize Resources125 questions
• Configure and Manage Automation of Tasks122 questions
• Plan and Configure HA and DR Environment137 questions

Free DP-300 sample questions

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

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

   You need to deploy an Azure Database for PostgreSQL instance that provides built-in high availability with zone-redundant deployment. Which deployment option should you use?

   • AAzure Database for PostgreSQL - Hyperscale (Citus)
   • BPostgreSQL on Azure Virtual Machines
   • CAzure Database for PostgreSQL - Single Server
   • DAzure Database for PostgreSQL - Flexible ServerCorrect
   ✓ Correct answer: D

   Flexible Server is the managed PostgreSQL deployment option that provides built-in, zone-redundant high availability. When zone-redundant HA is enabled, Azure provisions a standby replica in a different availability zone within the same region and handles automatic failover, so you get managed HA without configuring replication yourself. This makes Flexible Server the correct choice when you need built-in high availability with zone redundancy.

   Why the other options are wrong

   • AHyperscale (Citus), now Azure Cosmos DB for PostgreSQL, is a distributed/sharded option for horizontal scale-out; it is not the standard answer for built-in zone-redundant HA of a single PostgreSQL instance.
   • BPostgreSQL on Azure Virtual Machines requires you to manually configure replication and failover; Azure does not provide built-in managed high availability at the database layer for IaaS VMs.
   • CSingle Server is a single-zone, retiring deployment option without built-in zone-redundant HA; Microsoft is migrating customers from Single Server to Flexible Server.
3. Question 3Implement a Secure Environment

   You are configuring network security for an Azure SQL Database. You want to allow access only from a specific Azure Virtual Network subnet. Which feature should you use?

   • AVirtual network service endpoints with virtual network rulesCorrect
   • BAzure Application Gateway
   • CNetwork Security Groups applied directly to the SQL Database
   • DIP firewall rules
   ✓ Correct answer: A

   Virtual network service endpoints combined with VNet rules provide the mechanism to restrict Azure SQL Database access to specific VNet subnets. You enable a service endpoint for SQL on the target subnet, which optimizes routing and ensures traffic to the SQL service uses the Azure backbone. Then you configure a virtual network rule on the SQL server that permits connections from that endpoint. This combination provides network-level access control for Azure resources, ensuring only traffic from the specified subnet can reach the database. The service endpoint routing improves performance and security by keeping traffic within Azure's network.

   Why the other options are wrong

   • BAzure Application Gateway is incorrect because Application Gateway is a reverse proxy for HTTP(S) traffic; it doesn't provide network-level database access control.
   • CNetwork Security Groups applied directly to the SQL Database is incorrect because NSGs cannot be directly attached to SQL Database resources; SQL Database access is controlled through firewall rules and VNet rules.
   • DIP firewall rules is incorrect because IP firewall rules restrict based on IP address; they don't provide the capability to restrict to a specific VNet subnet or use service endpoint routing.
4. Question 4Monitor, Configure, and Optimize Resources

   A database administrator discovers that an Azure SQL Database has significant index fragmentation after heavy insert and delete operations. Which T-SQL command should they run to reorganize a specific index online?

   • ADROP INDEX IX_OrderDate ON Sales.Orders
   • BALTER INDEX IX_OrderDate ON Sales.Orders REBUILD
   • CALTER INDEX IX_OrderDate ON Sales.Orders REORGANIZECorrect
   • DDBCC DBREINDEX('Sales.Orders', 'IX_OrderDate')
   ✓ Correct answer: C

   ALTER INDEX ... REORGANIZE defragments an index by physically reordering leaf-level pages, and it is always an online, minimally logged operation that holds no long blocking locks, which suits a database experiencing ongoing activity. It is the recommended choice for moderate fragmentation and to reorganize a specific index online. The alternatives either drop the index, can take the index offline, or use a deprecated command.

   Why the other options are wrong

   • ADROP INDEX removes the index entirely rather than defragmenting it, which would harm query performance instead of reorganizing the index.
   • BALTER INDEX ... REBUILD recreates the index and, unless WITH (ONLINE = ON) is specified (and the tier supports it), can take the index offline and is more resource-intensive than a reorganize.
   • DDBCC DBREINDEX is a deprecated command retained only for backward compatibility and is not supported in Azure SQL Database, so it is not the correct way to reorganize an index online.
5. Question 5Configure and Manage Automation of Tasks

   A database administrator at Lamna Healthcare needs to automate the deployment of database schema changes across development, staging, and production Azure SQL Databases. They want version-controlled, repeatable deployments integrated with Azure DevOps. Which approach should they use?

   • ARestore production backups to lower environments
   • BUse Azure Database Migration Service for each deployment
   • CManually execute ALTER scripts on each environment
   • DDACPAC deployments using SqlPackage in Azure DevOps pipelinesCorrect
   ✓ Correct answer: D

   For automated, version-controlled database schema deployments across multiple environments, DACPAC (Data-Tier Application Package) deployments using SqlPackage is the industry-standard approach integrated with Azure DevOps. DACPACs are source-control-friendly packages containing schema definitions that can be version-controlled in Git, deployed consistently across dev, staging, and production, and integrated into CI/CD pipelines. SqlPackage provides the command-line tooling to automate deployment with incremental schema comparison, enabling safe deployments with minimal downtime. Restoring backups overwrites production data, Database Migration Service is designed for migration scenarios rather than schema versioning, and manual scripts are not repeatable or version-controlled.

   Why the other options are wrong

   • ARestore production backups to lower environments is incorrect because this approach does not maintain schema version control and overwrites development and staging data with production data.
   • BUse Azure Database Migration Service for each deployment is incorrect because DMS is designed for migrating databases from on-premises to Azure or between Azure services, not for managing schema deployments across environments.
   • CManually execute ALTER scripts on each environment is incorrect because manual deployments are not repeatable, version-controlled, or integrated with CI/CD pipelines, creating risk of inconsistencies.
6. Question 6Plan and Configure HA and DR EnvironmentSelect all that apply

   You are planning backup storage redundancy for an Azure SQL Database. Which TWO backup storage redundancy options are available? (Choose two.)

   • AGeo-redundant storage (GRS)Correct
   • BLocally redundant storage (LRS)Correct
   • CUltra disk redundant storage
   • DPremium SSD redundant storage
   ✓ Correct answer: A, B

   Azure SQL Database backup storage redundancy offers two primary options. Geo-redundant storage (GRS) replicates backup copies to a geographically paired region, providing protection against regional outages and enabling geo-restore capabilities. Locally redundant storage (LRS) stores backup copies within the same region across multiple datacenters, providing lower-cost redundancy suitable for scenarios where regional redundancy is not required. You can select which redundancy option to use when configuring backup settings at the server level.

   Why the other options are wrong

   • CUltra disk redundant storage is incorrect because this is a storage redundancy option for Azure virtual machine disks, not for SQL Database backups.
   • DPremium SSD redundant storage is incorrect because it refers to VM disk storage options, not backup storage redundancy options for SQL Database.
7. Question 7Plan and Configure HA and DR Environment

   What happens to the connection endpoint when an Azure SQL Database auto-failover group performs a failover to the secondary region?

   • AApplications must manually update their connection strings to the new server
   • BThe original endpoint stops working and cannot be used
   • CThe endpoint DNS record is updated to point to the new primary, so applications using the listener endpoint automatically reconnectCorrect
   • DA new endpoint is created and all applications must be reconfigured
   ✓ Correct answer: C

   An auto-failover group exposes stable listener endpoints rather than tying clients to a specific server. When failover promotes the secondary, the failover group updates the DNS records for the read-write (and read-only) listeners to resolve to the new primary, so applications using those endpoints simply reconnect to the right server with no configuration change. This DNS abstraction is what makes failover transparent to the application. The other options describe behaviors that do not occur with listener endpoints.

   Why the other options are wrong

   • AApplications having to manually update their connection strings is the behavior of plain active geo-replication, not failover groups, whose listener endpoints handle redirection automatically.
   • BThe original endpoint stopping working and becoming unusable is incorrect; the same listener endpoint continues to function and simply resolves to the new primary after the DNS update.
   • DA new endpoint being created so all applications must be reconfigured does not happen; the listener endpoints remain constant and only their DNS resolution target changes.
8. Question 8Plan and Implement Data Platform ResourcesSelect all that apply

   Adventure Works runs a mission-critical SQL Server workload that requires OS-level access to install third-party agents and needs to minimize Windows Server and SQL Server licensing costs by reusing existing licenses with active Software Assurance. The team will host the workload on Azure IaaS. Which TWO actions should the administrator take? (Choose two.)

   • AProvision SQL Server on an Azure Virtual MachineCorrect
   • BDeploy the workload to Azure SQL Managed Instance
   • CApply Azure Hybrid Benefit to reuse licenses with Software AssuranceCorrect
   • DPurchase pay-as-you-go SQL Server VM images from the Marketplace
   • EEnable the serverless compute tier to reduce cost
   ✓ Correct answer: A, C

   SQL Server on an Azure Virtual Machine is the IaaS option that grants full OS-level access needed to install third-party agents and provides complete SQL Server compatibility. Azure Hybrid Benefit lets the organization apply existing SQL Server (and Windows Server) licenses that have active Software Assurance to Azure VMs, significantly reducing licensing cost, which directly meets the cost objective.

   Why the other options are wrong

   • BAzure SQL Managed Instance is a PaaS offering that does not provide OS-level access, so third-party agents that require the OS cannot be installed.
   • DPay-as-you-go Marketplace images include the SQL license in the hourly rate and do not reuse existing Software Assurance licenses, so they do not minimize licensing cost.
   • EThe serverless compute tier is an Azure SQL Database feature and does not apply to SQL Server on an Azure VM, so it cannot be enabled for this IaaS workload.
9. Question 9Monitor, Configure, and Optimize Resources

   Alerts for SQL Database is a supported feature when implementing solutions for organizations like Wide World Importers.

   • TrueTrueCorrect
   • FalseFalse
   ✓ Correct answer: True

   True. Alerts for SQL Database is a fully supported feature in Azure SQL Database that enables administrators to configure automated notifications based on performance metrics, resource utilization thresholds, and alert rules. This capability helps Wide World Importers monitor critical database conditions and respond to issues proactively, making it an essential component of database monitoring strategies.

   Why the other options are wrong

   • FalseFalse is incorrect. The statement is true. Alerts for SQL Database is a fully supported feature in Azure SQL Database that enables administrators to configure automated notifications based on performance metrics, resource utilization thresholds, and alert rules.
10. Question 10Plan and Configure HA and DR Environment

    When configuring Managed Instance link at Contoso Ltd, administrators must also consider Azure SQL Database backup as part of the overall architecture.

    • TrueTrueCorrect
    • FalseFalse
    ✓ Correct answer: True

    Managed Instance link is a specialized replication feature that enables near-real-time synchronization of SQL Server data on-premises with Azure SQL Managed Instance. While Managed Instance link provides high availability and disaster recovery capabilities, it does not replace the need for comprehensive backup strategies that comply with organizational data protection requirements. Azure SQL Managed Instance includes automated backup functionality that operates independently of Managed Instance link, providing point-in-time recovery and geo-restore capabilities. Integrating Azure SQL Database backup considerations (including backup retention policies, geo-redundancy options, and recovery targets) into the overall architecture ensures Contoso Ltd maintains compliance with backup and disaster recovery standards. Therefore, administrators must view Azure SQL Database backup as a complementary component of the complete solution when deploying Managed Instance link.

    Why the other options are wrong

    • FalseFalse is incorrect. The statement is true. Managed Instance link is a specialized replication feature that enables near-real-time synchronization of SQL Server data on-premises with Azure SQL Managed Instance.

Related Microsoft resources

• DP-300 study guideKey concepts
• Microsoft practice examsAll Microsoft
• Certification pathWhere this fits
• Certification exam guides & tipsBlog
• Plans & pricingFree & paid
• SC-200 practice examRelated
• AZ-140 practice examRelated
• AZ-900 practice examRelated

DP-300 practice exam FAQ