DP-300 Plan and implement data platform resources Practice Test 10 — 15 Questions

Question 1

Match each Azure SQL Database service tier to its description.

Accepted Answer

General Purpose: Balanced performance for most cloud workloads with compute and storage separation.. These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Answer

General Purpose: High I/O and low latency with multiple read-only replicas.

Answer

Business Critical: Scalable storage up to 100 TB with auto-scaling compute.

Answer

Hyperscale: Balanced performance for small to medium workloads with compute and storage separation.

Question 2

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

Accepted Answer

Configure an Entra ID administrator for the Azure SQL logical server.. To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Answer

Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.

Answer

Create a contained database user mapped to an Entra ID identity.

Answer

Set the database to read-only mode.

Question 3

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 4

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 5

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

Accepted Answer

Read scale-out.. Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Answer

Automatic tuning.

Answer

Connection pooling.

Answer

Active geo-replication.

Question 6

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

Accepted Answer

Configure long-term retention (LTR) policy.. Long-term retention (LTR) in Azure SQL Database allows you to retain full database backups for up to 10 years, which meets the compliance requirement for 10-year backup retention. LTR policies are configured separately from point-in-time restore (PITR) and store backups in isolated containers for extended periods, ensuring regulatory compliance.

Answer

Configure point-in-time restore (PITR) retention to 10 years.

Answer

Enable automatic tuning to optimize backups.

Answer

Enable geo-redundant backup storage (GRS).

Question 7

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Accepted Answer

Up to 100 TB of database storage.. Option C is correct because the Hyperscale service tier is designed to scale storage up to 100 TB (and beyond in some configurations), far exceeding the limits of General Purpose and Business Critical tiers, which is a core benefit of its architecture that separates compute from storage. Option D is correct because Hyperscale uses a page-server and log-service architecture that allows additional read replicas to be provisioned quickly and independently of the primary compute, enabling rapid scale-out for read workloads. Option A is incorrect because built-in in-memory OLTP is a feature of the Business Critical tier, not Hyperscale. Option B is incorrect because zone-redundant configuration is not enabled by default in Hyperscale; it is an optional configuration choice. Option E is incorrect because no Azure SQL tier guarantees zero data loss in all scenarios; Hyperscale provides high durability but not an absolute zero-data-loss guarantee across every failure scenario.

Answer

Built-in in-memory OLTP support.

Answer

Zone-redundant configuration by default.

Answer

Zero data loss in all scenarios.

Question 8

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks.. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days.

Answer

4 months.

Answer

4 years.

Question 9

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?

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 10

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database with elastic query

Question 11

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Answer

SQL Vulnerability Assessment

Answer

Advanced Threat Protection

Answer

Microsoft Defender for Cloud

Question 12

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Accepted Answer

DTU-based elastic pool with 200 DTUs. A DTU-based elastic pool offers a cost-effective solution for hosting 50 databases with varying workloads. DTU pools provide a shared resource model (eDTUs) that automatically balances capacity across databases, minimizing cost while meeting baseline performance requirements. Serverless tier (Option C) is not supported for elastic pools, making it invalid. Hyperscale (Option A) is designed for large, highly scalable databases and is overkill and costly. Provisioned vCore-based pool (Option B) with 2 vCores may be insufficient for 50 databases and is generally more expensive than DTU-based pools for variable workloads.

Answer

Hyperscale tier with 4 vCores

Answer

Provisioned tier with General Purpose and 2 vCores

Answer

Serverless tier with General Purpose and auto-pause enabled

Question 13

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Accepted Answer

Query Store with Query Store Hints. Query Store with Query Store Hints is the correct feature because it allows you to identify parameter-sensitive plan regression by analyzing historical execution plans stored in Query Store, and then force a specific plan using a hint without changing application code. This directly addresses the root cause of parameter-sensitive plan issues, where different parameter values lead to suboptimal cached plans.

Answer

Intelligent Insights

Answer

Automatic tuning

Answer

Database Advisor

Question 14

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 15

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

Accepted Answer

VNet service endpoints and firewall rules. To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Answer

Microsoft Entra ID authentication and firewall rules

Answer

Advanced Threat Protection and VNet service endpoints

Answer

Private endpoint and VNet service endpoints