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

Question 1

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 2

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 3

You need to deploy an Azure SQL Database that complies with a regulatory requirement to encrypt data at rest using a customer-managed key stored in Azure Key Vault. The database will be used by a line-of-business application. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE) with customer-managed key. Transparent Data Encryption (TDE) with a customer-managed key (CMK) in Azure Key Vault encrypts the database at rest, meeting the regulatory requirement for customer-controlled encryption keys. TDE performs real-time I/O encryption and decryption of data files, logs, and backups, and when configured with a CMK (also known as Bring Your Own Key or BYOK), the encryption key is stored and managed in Azure Key Vault, giving the customer full control over key rotation and revocation.

Answer

Azure Defender for SQL

Answer

Dynamic Data Masking

Answer

Always Encrypted

Question 4

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has multiple logins, jobs, and SQL Server Agent alerts. You need to choose a migration method that minimizes downtime and preserves all server-level objects. Which approach should you recommend?

Accepted Answer

Use Azure Database Migration Service with online migration. Azure Database Migration Service (DMS) with online migration is the correct choice because it supports minimal downtime by continuously synchronizing changes from the source SQL Server to the target Azure SQL Managed Instance until cutover. It also automatically migrates server-level objects such as logins, jobs, and SQL Server Agent alerts, which are critical for preserving the full environment. This method is designed for large databases (2 TB) and provides a managed, resilient migration pipeline.

Answer

Export to BACPAC and import to Managed Instance

Answer

Use transactional replication

Answer

Back up to URL and restore to Managed Instance

Question 5

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. Option B is correct because 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 6

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

Accepted Answer

Azure SQL Database with active geo-replication and failover group. Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Answer

Azure SQL Database in Hyperscale tier

Answer

Azure SQL Database with zone-redundant configuration

Answer

Azure SQL Managed Instance with failover group

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. Option C is correct because 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

Your organization has multiple Azure SQL Databases in an elastic pool. You need to monitor the performance of the pool and identify which databases are consuming the most resources. You want to view historical resource usage for the past month. Which tool should you use?

Accepted Answer

Azure SQL Analytics (Azure Monitor). Azure SQL Analytics (Azure Monitor) is the correct tool because it provides built-in monitoring and alerting for elastic pools, including historical resource usage (CPU, DTU, storage) for up to 30 days. It aggregates data across all databases in the pool and allows you to drill down into individual database consumption, making it ideal for identifying top resource consumers over the past month.

Answer

Azure portal metrics for the elastic pool

Answer

SQL Server Profiler

Answer

Query Performance Insight

Question 9

You are tasked with designing a disaster recovery plan for an Azure SQL Database. The database is mission-critical and must have a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 minutes in case of a regional failure. Which configuration should you implement?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with failover groups is the correct choice because it provides a continuous, synchronous replication mechanism that can achieve an RPO of 5 seconds and an RTO of 30 minutes during a regional failure. The failover group automates the process of failing over to a readable secondary database in a paired region, meeting both the stringent RPO and RTO requirements without manual intervention.

Answer

Long-term backup retention

Answer

Automated backups with geo-restore

Answer

Zone-redundant configuration

Question 10

You are planning to migrate a 500 GB on-premises SQL Server database to Azure SQL Managed Instance. The database has high transaction volume and cannot tolerate more than 15 minutes of downtime. Which migration methods should you consider? (Choose TWO.)

Accepted Answer

Azure Database Migration Service (online mode). Azure Database Migration Service (DMS) online mode is correct because it uses continuous change data capture (CDC) to replicate ongoing transactions from the source to the target, allowing the source database to remain operational during migration. This minimizes downtime to a final cutover window, which can be under 15 minutes when properly orchestrated.

Answer

Back up to URL and restore

Answer

Export to BACPAC and import

Answer

Log shipping

Question 11

You are designing an Azure SQL Database solution for a SaaS application that will host multiple tenants. Each tenant has a dedicated database. You need to ensure that tenants are isolated and resource usage is optimized. Which two features should you use? (Choose TWO.)

Accepted Answer

DTU-based purchasing model. Option B (DTU-based purchasing model) is correct because it provides a simple, pre-configured bundle of compute, storage, and I/O resources, which is ideal for SaaS environments with multiple dedicated databases where predictable performance and isolation are needed. Option C (Elastic pool) is correct because it allows multiple databases to share a fixed pool of DTU resources, optimizing cost and resource utilization across tenants while maintaining logical isolation.

Answer

Serverless compute tier

Answer

Hyperscale service tier

Answer

vCore-based purchasing model

Question 12

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Accepted Answer

Always Encrypted. Always Encrypted is correct because it encrypts sensitive columns like credit card numbers at the client-side, ensuring the data remains encrypted at rest, in transit, and during processing. This prevents unauthorized users, including database administrators, from viewing the plaintext data, while authorized applications with the column encryption key can decrypt it transparently.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-level security

Question 13

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

Accepted Answer

Configure active geo-replication to a secondary in East US using a lower service tier.. Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Answer

Deploy an auto-failover group with a secondary in East US using the same service tier.

Answer

Deploy a zone-redundant database in West US with a failover group to East US.

Answer

Use a failover group with a secondary in East US and enable zone redundancy on both.

Question 14

Your company plans to migrate multiple on-premises SQL Server databases to Azure SQL Managed Instance. You need to select the appropriate migration method that minimizes downtime and supports automated schema and data validation. The source databases are running SQL Server 2019 Enterprise Edition. Which tool should you recommend?

Accepted Answer

Use Azure Database Migration Service with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Managed Instance, and it provides built-in schema and data validation to ensure consistency before cutover. This aligns with the requirement for automated validation and minimal downtime.

Answer

Use the Log Replay Service to continuously replicate transaction logs.

Answer

Set up transactional replication from on-premises to Azure SQL Managed Instance.

Answer

Export the databases as BACPAC files and import them into Azure SQL Managed Instance.

Question 15

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

Accepted Answer

Business Critical tier with zone redundancy enabled. The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Answer

Hyperscale tier with zone redundancy

Answer

Standard tier with failover group

Answer

Basic tier with active geo-replication