DP-300 Plan and implement data platform resources — Timed 20 Questions

Question 1

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

20 minute time limit — choose an answer to begin.

Accepted Answer

Active geo-replication with failover group. Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Answer

Standard geo-replication

Answer

Local redundancy with automatic failover

Answer

Read-scale out

Question 2

You are planning a deployment of Azure SQL Database for a mission-critical application. The application requires a high level of resilience to regional failures. You need to ensure that data is replicated synchronously across two Azure regions and that automatic failover occurs with zero data loss. Which deployment option should you choose?

Accepted Answer

SQL Server on Azure VMs with Always On Availability Groups.. SQL Server on Azure VMs with Always On Availability Groups can be configured with synchronous-commit mode across Azure regions, ensuring zero data loss (RPO=0) and automatic failover. In contrast, Azure SQL Database and Azure SQL Managed Instance only support asynchronous replication across regions via failover groups or geo-replication, which cannot guarantee zero data loss. General Purpose and Hyperscale tiers also lack synchronous cross-region replication.

Answer

Azure SQL Database on General Purpose tier with active geo-replication.

Answer

Azure SQL Database on Hyperscale tier with named replicas.

Answer

Azure SQL Managed Instance on Business Critical tier with failover groups.

Question 3

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

Accepted Answer

Use active geo-replication with a readable secondary in another Azure region. Active geo-replication provides an asynchronous replica in a secondary region with an RPO of up to 5 seconds and supports manual failover with an RTO of approximately 30 seconds, meeting the strict RPO and RTO requirements for disaster recovery. Zone-redundant configuration (Option C) offers high availability within a single region but does not protect against regional outages, so it is not a disaster recovery solution. Failover groups (Option B and D) have an RTO of about 1 hour, exceeding the 30-second requirement. Therefore, Option A is correct.

Answer

Use a failover group with a secondary in a paired region

Answer

Deploy the database with zone-redundant configuration in the primary region

Answer

Deploy the database with an auto-failover group to a secondary region

Question 4

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 5

You are deploying a new Azure SQL Database that will be used by an application requiring high availability and automatic failover across Azure regions. The RPO must be less than 5 seconds, and the RTO must be less than 1 minute. You also need to ensure that the secondary region can be used for read-only workloads during normal operation. Which solution should you implement?

Accepted Answer

Use active geo-replication with a readable secondary in a different Azure region.. Active geo-replication with a readable secondary in a different Azure region meets the requirements because it provides an asynchronous replication mechanism with an RPO of less than 5 seconds and an RTO of less than 1 minute during failover. The secondary database is fully readable for read-only workloads, supporting the application's need for read-scale in the secondary region during normal operation.

Answer

Use an auto-failover group with a readable secondary in a different region.

Answer

Use Azure SQL Database backup to a second region and restore on demand.

Answer

Use an auto-failover group with a read-scale secondary in the same region.

Question 6

You are designing a disaster recovery strategy for an Azure SQL Database that supports a critical OLTP application. The database is currently deployed in the East US region. You need to ensure that failover occurs automatically with minimal data loss in the event of a regional outage. Which deployment option should you recommend?

Accepted Answer

Configure auto-failover groups between East US and West US.. Auto-failover groups in Azure SQL Database provide automatic, synchronous replication to a secondary region and enable automatic failover with minimal data loss (RPO of a few seconds). Active Geo-Replication alone requires manual failover, which does not meet the automatic failover requirement. Geo-restore has an RPO of up to 1 hour, and manual failover groups still require manual initiation.

Answer

Enable Active Geo-Replication with a readable secondary in West US.

Answer

Deploy a failover group with manual failover.

Answer

Use geo-restore to recover the database in West US.

Question 7

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

Accepted Answer

Azure SQL Database Hyperscale with geo-replication. Azure SQL Database Hyperscale with geo-replication is the correct choice because it provides a single writable primary endpoint while supporting geo-distributed readable secondaries with low-latency reads for global users. Hyperscale's distributed architecture allows fast scaling and read scale-out, and geo-replication enables asynchronous replication to multiple regions, meeting the OLTP workload's low-latency read requirement without compromising the single write endpoint.

Answer

Azure SQL Database with active geo-replication

Answer

Azure SQL Managed Instance with failover groups

Answer

Azure SQL Database with failover groups

Question 8

You are a database administrator for a company that runs a large e-commerce platform on Azure SQL Database. The platform experiences unpredictable traffic spikes during flash sales, causing performance degradation. You need to implement a solution that automatically scales the database resources based on demand without manual intervention. Which feature should you use?

Accepted Answer

Hyperscale service tier. The Hyperscale service tier (Option D) is correct because it provides near-instantaneous, automatic scaling of compute and storage resources in response to workload demand, without requiring manual intervention. For unpredictable traffic spikes during flash sales, Hyperscale's architecture decouples compute from storage, allowing compute nodes to scale up or down independently and transparently, while its buffer pool and page server design handle high concurrency and large data volumes efficiently.

Answer

Elastic pool

Answer

Manual scaling using Azure CLI

Answer

Serverless compute tier

Question 9

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

Accepted Answer

Configure an auto-failover group with a readable secondary in East US. Auto-failover groups with a readable secondary in East US provide automatic and manual failover capabilities. For Business Critical tier, the secondary is readable and uses asynchronous replication, typically achieving an RPO of 5 seconds or less. This meets the requirement for minimal data loss. Active geo-replication also uses asynchronous replication with similar RPO but requires manual failover and does not support automatic failover. Zone redundancy protects against within-region failures, not regional outages. Geo-restore has a higher RPO and requires manual recovery.

Answer

Configure active geo-replication to East US

Answer

Enable zone redundancy for the database

Answer

Configure geo-restore from the West US database

Question 10

You have an Azure SQL Database that experiences performance degradation due to high concurrency and blocking. You need to implement a solution that offloads reporting queries to a read-only copy of the database without impacting the primary workload. What should you configure?

Accepted Answer

Enable read scale-out on the database by adding a secondary replica. Adding the database to an auto-failover group creates a readable secondary replica that can offload read-only reporting queries without affecting the primary. The secondary is kept in sync via asynchronous replication (or synchronous if within the same region for Premium/Business Critical tiers), providing a read-only copy suitable for reporting. This approach works across all service tiers and does not require upgrading. While read scale-out (Option D) is ideal for read offloading in Business Critical or Hyperscale tiers, auto-failover groups offer a comparable solution that is always available.

Answer

Add the database to an auto-failover group and use the readable secondary for reporting

Answer

Create a database snapshot and use it for reporting

Answer

Configure a geo-replica and use it for reporting

Question 11

Which TWO Azure SQL Database features can help you scale out read-intensive workloads without modifying application code?

Accepted Answer

Geo-replication with readable secondary. Geo-replication with readable secondary (Option B) is correct because it creates a continuously synchronized read-only replica in a different Azure region. Applications can connect to this secondary using the same connection string with 'ApplicationIntent=ReadOnly', offloading read-intensive workloads without code changes. Read scale-out (Option E) is correct because it provides a built-in, read-only replica of the primary database within the same region. The read-only traffic is automatically routed to the replica when the connection string includes 'ApplicationIntent=ReadOnly', also requiring no application code modifications. Both features enable scaling out reads while maintaining the same connection string.

Answer

Database sharding

Answer

In-memory OLTP

Answer

Elastic pool

Question 12

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

Accepted Answer

Hyperscale. Hyperscale service tier automatically scales storage up to 100 TB, and it meets the requirement of automatically scaling up to 4 TB. Business Critical offers a fixed provisioned storage limit (up to 4 TB) but does not auto-scale; it requires manual adjustments. Standard and Basic have lower maximum storage limits (1 TB and 2 GB respectively) and also do not auto-scale.

Answer

Standard

Answer

Basic

Answer

Business Critical

Question 13

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Assign a Microsoft Entra ID user or group as the Azure SQL Database admin. Assigning a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B) is the only required action to enable Microsoft Entra ID authentication for an Azure SQL Database. This establishes the administrative identity for managing authentication. Setting the authentication mode to 'Azure AD Only Authentication' (Option C) is optional and enforces that only Azure AD identities can connect, but it is not required to enable Microsoft Entra ID authentication. Creating contained database users (Option A) is a subsequent step after enabling authentication, and registering an application (Option E) is not required for enabling authentication.

Answer

Create contained database users mapped to Microsoft Entra ID identities

Answer

Set the database's authentication mode to 'Azure AD Only Authentication'

Answer

Deploy the database in Azure SQL Managed Instance

Answer

Register the application in Microsoft Entra ID

Question 14

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 15

You are a database administrator for a healthcare company that uses Azure SQL Database for its patient management system. The database is currently 500 GB and uses the General Purpose tier in the West US region. The application experiences high read traffic during business hours, and you need to improve read performance without increasing costs significantly. You also need to ensure that read queries are isolated from write workloads. The application supports read-only routing using connection strings. You have the following options:

A. Create a secondary replica in the same region using active geo-replication and configure the application to use the secondary for read-only queries.
B. Upgrade the database to the Business Critical tier and use the built-in readable secondary.
C. Implement read scale-out by adding a readable secondary replica in the same region using the built-in read scale-out feature of the General Purpose tier.
D. Create an elastic pool with multiple databases and distribute read queries across them.

Which option should you choose?

Accepted Answer

Implement read scale-out in General Purpose tier.. Active geo-replication allows you to create a readable secondary replica in the same region, which can be used to offload read-only queries from the primary. The application can use connection strings with ApplicationIntent=ReadOnly to automatically route read queries to the secondary, isolating them from write workloads. This improves read performance without upgrading to a higher-cost tier, as the General Purpose tier does not natively support readable secondaries within the same region.

Answer

Create a secondary replica using active geo-replication and configure read-only routing.

Answer

Upgrade to Business Critical tier and use its readable secondary.

Answer

Create an elastic pool and distribute read queries.

Question 16

A company is deploying Azure SQL Managed Instance and needs to ensure that the failover group provides automatic failover with zero data loss during a regional outage. The secondary region is 500 miles away. Which data replication mode should be configured?

Accepted Answer

Synchronous replication. Azure SQL Managed Instance failover groups require synchronous replication to guarantee zero data loss during automatic failover. Synchronous replication ensures that transactions are committed on both the primary and secondary replicas before acknowledging the commit, so no data is lost even if the primary region fails. This mode is mandatory for failover groups with the 'automatic failover' and 'zero data loss' requirements, despite the 500-mile distance which introduces latency.

Answer

Geo-replication

Answer

Snapshot replication

Answer

Asynchronous replication

Question 17

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 18

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Accepted Answer

Enable forced parameterization for the database. Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Answer

Create a plan guide to force a specific plan

Answer

Use optimize for ad hoc workloads

Answer

Add query store and force the last good plan

Question 19

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

Accepted Answer

Transparent data encryption (TDE) and TLS 1.2. Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Answer

Dynamic data masking and row-level security

Answer

Azure Active Directory authentication and firewall rules

Answer

Always Encrypted and transparent data encryption (TDE)

Question 20

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Accepted Answer

Refactor the application to eliminate cross-database queries or use elastic query. Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Answer

Use elastic database query to reference external tables

Answer

Create a linked server in Azure SQL Database

Answer

Migrate to Azure SQL Managed Instance instead