DP-300 Practice Test 26 — 15 Questions

Question 1

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Zone redundancy enabled on Business Critical tier.. Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Answer

General Purpose service tier.

Answer

Read scale-out.

Answer

Geo-redundant backup storage.

Question 2

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

Accepted Answer

Use the Azure portal to scale up the DTU or vCore service tier.. Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Answer

Create a new database on a higher tier and copy data manually.

Answer

Enable read scale-out to offload read workloads.

Answer

Implement horizontal sharding across multiple databases.

Question 3

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

Accepted Answer

Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance. Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Answer

Perform a native backup and restore to Azure SQL Managed Instance

Answer

Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database

Answer

Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database

Question 4

You are deploying Azure SQL Database for a new application. You need to ensure that connections from Azure services use a private IP address and do not traverse the public internet. What should you configure?

Accepted Answer

Use Azure Private Link for Azure SQL Database. Azure Private Link creates a private endpoint in a virtual network, mapping the Azure SQL Database logical server to a private IP address within that VNet. Traffic from Azure services (e.g., VMs, App Service) to the database uses Microsoft's backbone network via the private endpoint, never traversing the public internet. This meets the requirement for private, non-internet-routed connectivity.

Answer

Enable Virtual Network service endpoints for Azure SQL Database

Answer

Configure the Azure SQL Database firewall to allow Azure services

Answer

Deploy Azure SQL Database inside a virtual network

Question 5

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

Accepted Answer

Enable Microsoft Defender for SQL on the Azure SQL logical server.. Microsoft Defender for SQL must be enabled at the logical server level before any security alerts or vulnerability assessments can be applied to the Azure SQL Database. This server-level enablement activates the Defender for SQL features (including vulnerability assessment and threat detection) for all databases under that server, making it the prerequisite step.

Answer

Configure Microsoft Intune to manage access.

Answer

Run a vulnerability assessment scan from the Azure portal.

Answer

Configure Microsoft Purview to scan the database.

Question 6

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

Accepted Answer

Enable Microsoft Entra ID authentication and assign managed identities to the applications.. Microsoft Entra ID authentication allows Azure SQL Database to trust tokens issued by Entra ID for managed identities. By assigning a managed identity to each application, the application can acquire an access token from Azure Managed Identity endpoints and present it to the database without ever storing credentials in connection strings. This eliminates the need for passwords or connection string secrets.

Answer

Use Always Encrypted with column master key in Azure Key Vault.

Answer

Configure firewall rules to allow application IP addresses.

Answer

Enable SQL Server authentication and create a login for each application.

Question 7

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

Accepted Answer

Azure Defender for SQL and SQL Auditing. Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Answer

Azure Purview and vulnerability assessment

Answer

Microsoft Sentinel and SQL Auditing

Answer

SQL Server auditing and vulnerability assessment

Question 8

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

Accepted Answer

Enable read scale-out and configure the application to use read-only intent.. Read scale-out in Azure SQL Database allows you to offload read-only workloads to a readable secondary replica by setting the application connection string's `ApplicationIntent=ReadOnly`. This reduces load on the primary replica, which is essential for high read throughput in an e-commerce catalog scenario. Entity Framework Core can use this by specifying `ReadOnly` in the connection string or via a custom interceptor.

Answer

Enable Query Performance Insights and create indexes for frequent queries.

Answer

Configure Active Geo-Replication with a readable secondary in a different region.

Answer

Enable automatic tuning to force parameterization of queries.

Question 9

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

Accepted Answer

Use Azure Database Migration Service with online mode after removing FileTables. Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Answer

Set up log shipping to an Azure SQL Database

Answer

Perform an offline migration using Azure Database Migration Service

Answer

Export a BACPAC file from the source and import it to Azure SQL Database

Question 10

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

Accepted Answer

Use the serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay.. The serverless compute tier with auto-pause enabled and a 1-hour auto-pause delay is the most cost-effective choice for a non-production database used only during business hours. Serverless automatically pauses the database after 1 hour of inactivity (e.g., overnight and weekends), charging only for compute during active periods and storage at all times. This aligns perfectly with the 9 AM–5 PM weekday usage pattern, minimizing cost while ensuring the database is available when needed.

Answer

Use the Hyperscale tier with a minimum of 1 vCore and disable auto-pause.

Answer

Use the provisioned General Purpose tier with 2 vCores and disable auto-pause.

Answer

Use the provisioned Business Critical tier with 1 vCore and enable auto-pause.

Question 11

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Accepted Answer

Execute sys.sp_query_store_force_plan with the query_id and plan_id.. `sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Answer

Disable and re-enable Query Store to reset the plans.

Answer

Execute sys.sp_query_store_set_hints to add a query hint.

Answer

Use sys.dm_db_tuning_recommendations to apply a plan.

Question 12

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

Accepted Answer

Microsoft Entra authentication, firewall rules, and managed identity. It satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Answer

Always Encrypted, VNet service endpoints, and SQL authentication

Answer

Transparent Data Encryption, IP firewall rules, and connection strings

Answer

Azure Defender for SQL, firewall rules, and service principal

Question 13

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

Accepted Answer

The deployment will fail because db1 is not in a recoverable state.. The ARM template creates a new database by copying from a source database. Azure SQL Database requires the source database to be in an online and healthy state to perform a copy operation. Since db1 is in a failed state due to a storage issue, it is not accessible for copying, so the deployment will fail.

Answer

It will create an empty database because the source is not accessible.

Answer

It will delete db1 and create db2 as a replacement.

Answer

It will create a new database by recovering db1 to its last known good state.

Question 14

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.

Question 15

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

Accepted Answer

Create a Microsoft Entra ID administrator for the Azure SQL logical server.. Option A is correct because every Azure SQL logical server must have a Microsoft Entra ID administrator provisioned (via the server's Microsoft Entra admin setting) before Entra authentication can be used to manage and connect to the database. Option C is correct because applications should authenticate using Microsoft Entra ID with MFA, leveraging token-based authentication (for example, Microsoft.Data.SqlClient with Authentication=Active Directory Interactive/Password or managed identity) to enforce strong, centralized identity controls. Option E is correct because contained database users mapped to Microsoft Entra ID principals (CREATE USER ... FROM EXTERNAL PROVIDER) allow Entra logins to access the database without requiring server-level logins, which is the recommended pattern for Entra-authenticated database access. Option B is not required: disabling SQL authentication is optional and not mandatory for enabling Entra ID authentication, and it can break existing tooling or break-glass scenarios. Option D is not relevant here because TDE provides encryption at rest for data files and does not implement Entra ID authentication for database access.

Answer

Disable SQL Server authentication after migrating to Entra ID.

Answer

Enable Transparent Data Encryption (TDE).