DP-300 Plan and implement data platform resources — 30 Questions

Question 1

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?

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

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 2

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?

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

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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?

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 13

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 14

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 15

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

Question 16

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

Accepted Answer

Configure a VNet service endpoint and a firewall rule for the VNet subnet. A VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Answer

Use a private endpoint only

Answer

Configure a point-to-site VPN

Answer

Set the firewall to allow Azure services and resources to access the server

Question 17

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

Accepted Answer

Set the backup retention period to 35 days in the backup settings. Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Answer

Create a backup policy in Azure Backup Vault

Answer

Enable geo-redundant storage (GRS)

Answer

Configure long-term retention (LTR) policy

Question 18

Which TWO of the following are valid methods to migrate an on-premises SQL Server database to Azure SQL Database?

Accepted Answer

Export the database as a BACPAC file and import into Azure SQL Database. A BACPAC file is a logical backup that contains the database schema and data in a compressed format. Azure SQL Database supports importing BACPAC files directly via the Azure portal, PowerShell, or the SqlPackage utility, making this a straightforward and fully supported migration method.

Answer

Configure transactional replication to replicate changes to Azure SQL Database

Answer

Use log shipping to continuously copy transaction logs

Answer

Restore a native SQL Server backup to Azure SQL Database

Question 19

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Accepted Answer

Cross-database queries (three- and four-part names). Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Answer

Transparent Data Encryption (TDE)

Answer

Elastic pools

Question 20

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

Accepted Answer

Set zoneRedundant to true and configure an auto-failover group with a secondary region. Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Answer

Set collation to SQL_Latin1_General_CP1_CS_AS

Answer

Set zoneRedundant to true

Answer

Change storageAccountType to RA-GRS