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

Question 1

You are configuring an Azure SQL Database to support a new application. The application requires that the database be able to scale compute resources automatically based on demand and that it can be paused during periods of inactivity to reduce costs. You also need to ensure that the database supports zone redundancy for high availability. Which two actions should you perform? (Choose two.)

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

Accepted Answer

Configure the database to use the serverless compute tier.. The serverless compute tier provides automatic scaling and the ability to pause during inactivity, meeting the cost and scaling needs. Enabling zone redundancy ensures high availability across availability zones. Together, these actions satisfy all requirements: auto-scaling, pausing, and zone redundancy. Other options either lack auto-scaling, pausing, or zone redundancy support.

Answer

Configure the database to use the Business Critical service tier with local storage.

Answer

Configure the database to use the Hyperscale service tier.

Answer

Enable active geo-replication.

Question 2

You manage an Azure SQL Managed Instance that hosts a database used by a line-of-business application. The application requires the database to remain online during planned maintenance and to automatically fail over to a secondary instance in a different Azure region if the primary region fails. You need to implement a solution that provides both a zone-redundant configuration and a cross-region failover group. What should you do?

Accepted Answer

Configure the instance with the Business Critical service tier and enable zone redundancy, then create an auto-failover group with a secondary instance in the paired region.. Business Critical is the only Azure SQL Managed Instance service tier that supports zone redundancy, and it maintains multiple replicas to keep the database online during planned maintenance. An auto-failover group extends this to a secondary managed instance in another region and provides a listener endpoint for automatic failover. The combination satisfies both the zone-redundant and cross-region failover requirements.

Answer

Configure the instance with the General Purpose service tier and enable zone redundancy, then create an auto-failover group with a secondary instance in the paired region.

Answer

Configure the instance with the Business Critical service tier and enable zone redundancy, then create a geo-secondary by using the Azure portal.

Answer

Configure the instance with the Business Critical service tier and enable zone redundancy, then configure active geo-replication on each database in the instance.

Question 3

You are migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration preserves these features and minimizes downtime. You plan to use the Azure Database Migration Service (DMS). What should you do to configure the target environment before migration?

Accepted Answer

Deploy an Azure SQL Managed Instance, create the necessary databases, and migrate SQL Server Agent jobs using the Data Migration Assistant (DMA).. Azure SQL Managed Instance natively supports cross-database queries and SQL Server Agent jobs. Before migration, you must deploy the Managed Instance and create the target databases. SQL Server Agent jobs are not migrated by DMS, so you need to use Data Migration Assistant (DMA) or scripts to migrate them. The other options either suggest unnecessary configurations, use an incompatible platform, or misunderstand the support for these features in Managed Instance.

Answer

Deploy an Azure SQL Managed Instance with the General Purpose service tier and enable cross-database queries by setting the database compatibility level to 150.

Answer

Deploy an Azure SQL Managed Instance with the Business Critical service tier and configure a service endpoint for the source network.

Answer

Deploy an Azure SQL Database elastic pool and configure cross-database queries using external tables.

Question 4

You are deploying a new Azure SQL Database. The application that will use the database requires that all connections be encrypted and that the database be accessible only from a specific Azure virtual network. You need to configure the network security. What should you do?

Accepted Answer

Create a private endpoint for the Azure SQL Database and disable public access.. A private endpoint provides a private IP address for the Azure SQL Database within your virtual network, and disabling public access ensures that the database is not exposed to the internet. This meets the requirements for encrypted connections and access restricted to a specific virtual network. Other options either leave the public endpoint accessible or do not provide the same level of isolation.

Answer

Configure a firewall rule to allow the virtual network's subnet and enable 'Allow Azure services and resources to access this server'.

Answer

Enable 'Deny public network access' on the Azure SQL server and configure a firewall rule for the virtual network's public IP range.

Answer

Use service endpoints for the Azure SQL Database and set the firewall to allow only that subnet.

Question 5

You are designing a disaster recovery strategy for an Azure SQL Database that is part of a business-critical application. The database is currently in the General Purpose service tier and uses geo-replication to a secondary region. You need to ensure that the secondary database can be used for read-only queries during normal operations and that failover can be performed with minimal data loss. You also want to minimise administrative overhead for failover. Which two actions should you include in your strategy? (Choose two.)

Accepted Answer

Configure active geo-replication for the Azure SQL Database. Active geo-replication and auto-failover groups both provide cross-region replication with readable secondaries and support for failover with minimal data loss. Active geo-replication allows manual failover and readable secondaries. Auto-failover groups add automated failover and listener endpoints, reducing administrative overhead. Together, they meet the requirements for read-only access during normal operations and minimal data loss during failover.

Answer

Create a read-scale out replica using the Business Critical tier

Answer

Enable zone-redundant configuration for the database

Answer

Configure long-term retention (LTR) for the database

Question 6

You are tasked with deploying an Azure SQL Database that will host a mission-critical application. The application requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 seconds in the event of a regional outage. The database must also support read-only workloads in the secondary region to offload reporting queries. You need to implement a disaster recovery solution that meets these requirements at the lowest cost. What should you do?

Accepted Answer

Configure a failover group with automatic failover policy and a readable secondary in the paired region.. Failover groups provide automatic failover with an RTO of less than 30 seconds and asynchronous replication with an RPO of less than 5 seconds. They also allow the secondary database to be readable, enabling reporting offload. This combination meets the strict RPO and RTO requirements for a regional outage at a lower management overhead than active geo-replication, which requires manual failover.

Answer

Deploy the database in the Business Critical service tier with zone redundancy and rely on zone failover.

Answer

Configure active geo-replication with a readable secondary in the paired region.

Answer

Enable geo-redundant backup storage and perform a geo-restore to the paired region during an outage.

Question 7

You are designing an Azure SQL Database solution for a new application that requires a database with 8 vCores, 32 GB of memory, and the ability to scale storage independently up to 4 TB. You need to minimize cost while ensuring that the database can handle a workload with high transaction log throughput. Which service tier and hardware configuration should you choose?

Accepted Answer

Hyperscale with the standard-series hardware.. Hyperscale is designed for large databases and high transaction log throughput, with independent scaling of compute and storage. It supports up to 100 TB of storage, so 4 TB is easily accommodated. The standard-series hardware provides a cost-effective option compared to Business Critical. General Purpose with Fsv2 may not provide the required log throughput, and Business Critical with M-series is more expensive.

Answer

General Purpose with the Fsv2-series hardware.

Answer

General Purpose with the DC-series hardware.

Answer

Business Critical with the M-series hardware.

Question 8

You are planning to deploy an Azure SQL Database elastic pool to host multiple databases for a SaaS application. The databases have varying and unpredictable usage patterns, and you want to optimize cost while maintaining performance. Which two actions should you perform when configuring the elastic pool? (Choose two.)

Accepted Answer

Configure per-database minimum and maximum DTU or vCore settings to control resource consumption.. Configuring per-database minimum and maximum resource limits prevents resource contention and ensures fair sharing among databases in the pool. Monitoring usage and adjusting the pool's allocation allows you to optimize cost and performance over time. These two actions are essential for managing an elastic pool with unpredictable workloads.

Answer

Enable auto-scaling for the elastic pool to automatically adjust resources based on demand.

Answer

Set the elastic pool's service tier and compute size based on the combined peak resource requirements of all databases.

Answer

Create each database with its own dedicated service tier and then add them to the pool.

Question 9

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 10

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 11

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

Accepted Answer

Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.. Azure SQL Managed Instance is the correct target because it provides full support for SQL Server Agent, including proxies, and enables cross-database queries within the same instance. Unlike Azure SQL Database, Managed Instance maintains instance-level scope, allowing queries that reference other databases in the same instance without requiring external data sources or elastic queries.

Answer

Migrate to SQL Server on Azure Virtual Machines for full control.

Answer

Migrate to Azure SQL Database elastic query to handle cross-database queries.

Answer

Migrate to Azure SQL Database instead to reduce costs.

Question 12

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

Accepted Answer

Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.. TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Answer

Use Azure Data Studio to import the certificate directly from the local machine store during migration.

Answer

Disable TDE on the source database, migrate the backup, then enable TDE on the target.

Answer

Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.

Question 13

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

Accepted Answer

The database is experiencing memory pressure due to concurrent queries exceeding available memory.. RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants. This is a classic sign of memory pressure, not CPU or IO throttling.

Answer

There is a blocking chain due to unoptimized queries.

Answer

The disk IOPS limit is being reached, causing queuing.

Answer

The DTU limit is being reached, causing CPU throttling.

Question 14

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

Accepted Answer

Create secondary server in another region, then configure the secondary database, then initiate replication, then verify the replication link.. Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Answer

Create secondary server in another region, then initiate replication, then configure the secondary database, then verify the replication link.

Answer

Initiate replication, then create secondary server in another region, then configure the secondary database, then verify the replication link.

Answer

Create secondary server in another region, then configure the secondary database, then verify the replication link, then initiate replication.

Question 15

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Accepted Answer

Identify high CPU queries, then Analyze execution plans, then Check for missing indexes, then Implement fixes, then Monitor performance.. Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Answer

Identify high CPU queries, then Check for missing indexes, then Analyze execution plans, then Implement fixes, then Monitor performance.

Answer

Analyze execution plans, then Identify high CPU queries, then Check for missing indexes, then Implement fixes, then Monitor performance.

Answer

Identify high CPU queries, then Analyze execution plans, then Implement fixes, then Check for missing indexes, then Monitor performance.