DP-300 sample exam questions

15 exam-style questions for Microsoft DP-300: Administering Azure SQL Solutions, with answers and explanations. Take them as a timed practice exam first if you want a score out of 1000.

Take the free practice exam DP-300 exam guide
  1. Question 1Single choiceTopic 1
    You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime.
    What should you do?
    1. A
      Configure Transaction Log Shipping.
    2. B
      Implement Always On availability groups.
    3. C
      Configure transactional replication.
    4. D
      Import a BACPAC.
    Show answer

    Correct answer: C

    Why

    Transactional replication can push changes continuously from on-premises SQL Server to an Azure SQL Database subscriber, so you cut over with minimal downtime. Log shipping and availability groups cannot target Azure SQL Database, and importing a BACPAC requires an offline window.

  2. Question 2Single choiceTopic 2
    You have an Azure subscription that contains a logical SQL server named Server1. The master database of Server1 contains a user named User1.
    You need to ensure that User1 can create databases on Server1.
    Which database role should you assign to User1?
    1. A
      db_owner
    2. B
      dbmanager
    3. C
      dbo
    4. D
      db_ddladmin
    Show answer

    Correct answer: B

    Why

    No community answer. The dbmanager role in the master database of an Azure SQL logical server grants permission to create and manage databases. db_owner and db_ddladmin are database-level roles in each database and do not allow creating databases, and dbo is a user, not a role.

  3. Question 3HotspotTopic 2
    HOTSPOT

    You have an Azure subscription.

    You need to deploy a logical SQL server by using PowerShell. The solution must ensure that the logical SQL server can create Azure AD users and provide Transparent Data Encryption (TDE) with a customer-managed key.

    How should you complete the command? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    First parameter after -Tags- assignidentity / -federatedclientID / -keyid
    Parameter before the key vault key URL- assignidentity / -federatedclientID / -keyid
    Show answer
    • First parameter after -Tags - assignidentity
    • Parameter before the key vault key URL -keyid
    Why

    -AssignIdentity gives the logical server a managed identity, which it uses to read Microsoft Entra directory data for creating Entra users and to access Key Vault. -KeyId sets the Key Vault key URI as the TDE protector for customer-managed TDE. -FederatedClientId is for cross-tenant scenarios.

  4. Question 4Single choiceTopic 3
    You have an Azure Databricks resource.
    You need to log actions that relate to changes in compute for the Databricks resource.
    Which Databricks services should you log?
    1. A
      clusters
    2. B
      jobs
    3. C
      DBFS
    4. D
      SSH
    5. E
      workspace
    Show answer

    Correct answer: A

    Why

    Databricks diagnostic logs for the clusters service record cluster create, edit, resize, start and terminate actions, which are the compute changes. Jobs, DBFS, SSH and workspace logs capture other activities.

  5. Question 5Single choiceTopic 4
    You have an instance of SQL Server on Azure Virtual Machines that has a database named DB1.
    You plan to implement Azure SQL Data Sync for DB1.
    Which isolation level should you configure?
    1. A
      SERIALIZABLE
    2. B
      SNAPSHOT
    3. C
      READ UNCOMMITTED
    4. D
      READ COMMITTED
    Show answer

    Correct answer: B

    Why

    Azure SQL Data Sync requires snapshot isolation to be enabled on the hub and all member databases, including SQL Server on Azure VMs, so you configure ALLOW_SNAPSHOT_ISOLATION ON. Serializable, read uncommitted and read committed are not the required setting.

  6. Question 6Drag and drop: orderTopic 4
    DRAG DROP

    You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures.

    You need to ensure that SQL Server Agent jobs running on SQL1 can notify administrators when a failure occurs.

    Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

    Arrange 3 of these in the correct order:

    • Create a Database Mail account.
    • Create a profile named AzureManagedInstance_dbmail_profile.
    • Create a profile named application_dbmail_profile.
    • Enable email notifications upon failure.
    • Enable pager notifications upon failure.
    Show answer
    1. Create a Database Mail account.
    2. Create a profile named AzureManagedInstance_dbmail_profile.
    3. Enable email notifications upon failure.
    Why

    On a managed instance, SQL Server Agent sends mail only through a Database Mail profile named AzureManagedInstance_dbmail_profile. First create the mail account, then create that profile with the account in it, and then enable email notifications for the job. Pager notifications are not supported.

  7. Question 7Single choiceTopic 5
    You have an Azure SQL managed instance that hosts multiple databases.
    You need to configure alerts for each database based on the diagnostics telemetry of the database.
    What should you use?
    1. A
      Azure SQL Analytics alerts based on metrics
    2. B
      SQL Health Check alerts based on diagnostics logs
    3. C
      SQL Health Check alerts based on metrics
    4. D
      Azure SQL Analytics alerts based on diagnostics logs
    Show answer

    Correct answer: D

    Why

    No community answer. For SQL Managed Instance databases, Azure SQL Analytics supported alerts based on diagnostics logs sent to Log Analytics, whereas metric-based alerts were not available for managed instance databases. SQL Health Check is unrelated. Azure SQL Analytics has since been retired.

  8. Question 8HotspotTopic 5
    HOTSPOT

    You have an Azure subscription.

    You need to deploy a logical SQL server by using an Azure Resource Manager (ARM) template. The solution must ensure that the server will allow inbound connectivity from any Azure resource.

    How should you complete the template? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    "startIpAddress":"" / "0.0.0.0" / "255.255.255.255"
    "endIpAddress":"" / "0.0.0.0" / "255.255.255.255"
    Show answer
    • "startIpAddress": "0.0.0.0"
    • "endIpAddress": "0.0.0.0"
    Why

    A firewall rule whose start and end addresses are both 0.0.0.0 is the special AllowAllWindowsAzureIps rule. It allows connections from any Azure service or resource. A range up to 255.255.255.255 would instead open the server to the whole internet.

  9. Question 9Multiple responseTopic 6
    You are planning disaster recovery for the failover group of an Azure SQL Database managed instance.
    Your company's SLA requires that the database in the failover group become available as quickly as possible if a major outage occurs.
    You set the Read/Write failover policy to Automatic.
    What are two results of the configuration? Each correct answer presents a complete solution.
    NOTE: Each correct selection is worth one point.
    1. A
      In the event of a datacenter or Azure regional outage, the databases will fail over automatically.
    2. B
      In the event of an outage, the databases in the primary instance will fail over immediately.
    3. C
      In the event of an outage, you can selectively fail over individual databases.
    4. D
      In the event of an outage, you can set a different grace period to fail over each database.
    5. E
      In the event of an outage, the minimum delay for the databases to fail over in the primary instance will be one hour.
    Show answer

    Correct answer: A, E

    Why

    With the Automatic read/write failover policy, the managed instance failover group fails over automatically after a regional or datacenter outage, but only after the grace period, whose minimum is one hour. Failover is not immediate, applies to all databases in the group together, and one grace period covers the group.

  10. Question 10HotspotTopic 6
    HOTSPOT

    You plan to deploy an Always On failover cluster instance (FCI) on Azure virtual machines.

    You need to provision an Azure Storage account to host a cloud witness for the deployment.

    How should you configure the storage account? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    Type:Premium block blobs / Premium file shares / Standard general-purpose v2
    Replication:Geo-redundant storage (GRS) / Locally-redundant storage (LRS) / Read-access geo-redundant storage (RA-GRS)
    Show answer
    • Type: Standard general-purpose v2
    • Replication: Locally-redundant storage (LRS)
    Why

    A cloud witness keeps only a tiny blob, so it needs a standard general-purpose v2 account, not premium storage. Microsoft recommends LRS, or ZRS where it is available, because geo-replicated storage can serve stale data after failover.

  11. Question 11HotspotTopic 7
    HOTSPOT

    You have an Azure subscription that contains a resource group named RG1. RG1 contains an instance of SQL Server on Azure Virtual Machines named SQ1.

    You need to use PowerShell to enable and configure automated patching for SQ1. The solution must include both SQL Server and Windows security updates.

    How should you complete the command? To answer, select the appropriate options in the answer area.

    NOTE: Each correct selection is worth one point.

    Get-AzVM -ResourceGroupName "RG1" -Name "SQ1" |Get-AzVMSQLServerExtension / Set-AzVMExtension / Set-AzVMSqlServerExtension
    -SQLManagementType-Full / -Lightweight / -NoAgent
    Show answer
    • Get-AzVM -ResourceGroupName "RG1" -Name "SQ1" | Set-AzVMSqlServerExtension
    • -SQLManagementType -Full
    Why

    Set-AzVMSqlServerExtension applies the auto-patching settings through the SQL IaaS Agent extension. Automated patching needed full management mode. Management modes have since been retired, and Microsoft now recommends Azure Update Manager over automated patching.

  12. Question 12Single choiceTopic 7
    You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1.

    You need to configure SQL1 to use mixed mode authentication.

    Which procedure should you run?
    1. A
      sp_addremotelogin
    2. B
      xp_instance_regwrite
    3. C
      sp_change_users_login
    4. D
      xp_grant_login
    Show answer

    Correct answer: B

    Why

    No community answer. The authentication mode is stored in the registry LoginMode value, so you run xp_instance_regwrite to set LoginMode to 2 (mixed mode) and restart SQL Server, which is what SSMS does behind the scenes. The other procedures manage logins or user mappings.

  13. Question 13Single choiceTopic 9
    You need to implement a solution to notify the administrators. The solution must meet the monitoring requirements.
    What should you do?
    1. A
      Create an Azure Monitor alert rule that has a static threshold and assign the alert rule to an action group.
    2. B
      Add a diagnostic setting that logs QueryStoreRuntimeStatistics and streams to an Azure event hub.
    3. C
      Add a diagnostic setting that logs Timeouts and streams to an Azure event hub.
    4. D
      Create an Azure Monitor alert rule that has a dynamic threshold and assign the alert rule to an action group.
    Show answer

    Correct answer: D

    Why

    The case study's monitoring requirement is to alert administrators only on significant deviations in usage patterns, which an Azure Monitor alert rule with a dynamic threshold (machine-learned baseline) and an action group provides. Static thresholds alert on fixed values, and diagnostic settings to Event Hubs do not notify anyone.

  14. Question 14Single choiceTopic 12
    What should you implement to meet the disaster recovery requirements for the PaaS solution?
    1. A
      Availability Zones
    2. B
      failover groups
    3. C
      Always On availability groups
    4. D
      geo-replication
    Show answer

    Correct answer: B

    Why

    Failover groups replicate the PaaS Azure SQL databases to a secondary region with automatic failover and a listener endpoint that does not change, meeting the disaster recovery requirements. Availability Zones protect only within a region, geo-replication alone needs manual failover and connection changes, and Always On AGs are for VMs.

  15. Question 15Multiple responseTopic 21
    You need to identify the cause of the performance issues on SalesSQLDb1.
    Which two dynamic management views should you use? Each correct answer presents part of the solution.
    NOTE: Each correct selection is worth one point.
    1. A
      sys.dm_pdw_nodes_tran_locks
    2. B
      sys.dm_exec_compute_node_errors
    3. C
      sys.dm_exec_requests
    4. D
      sys.dm_cdc_errors
    5. E
      sys.dm_pdw_nodes_os_wait_stats
    6. F
      sys.dm_tran_locks
    Show answer

    Correct answer: C, F

    Why

    No community answer. SalesSQLDb1 is an Azure SQL database, so you use sys.dm_exec_requests to see running requests, waits and blocking sessions, and sys.dm_tran_locks to see the locks involved. The sys.dm_pdw_* and compute node views are for Synapse dedicated SQL pools, and dm_cdc_errors covers change data capture.