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

Question 1

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?

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

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 2

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 3

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. (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 4

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. Column-level security is also correct because it allows administrators to restrict access to specific columns (e.g., credit card numbers) by granting permissions only to authorized users or applications. This ensures that even if a user has access to the database, they cannot query the sensitive columns without explicit permission. Together, Always Encrypted provides encryption and Column-level security provides fine-grained access control, meeting all requirements.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-level security

Question 5

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 6

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 7

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

Question 8

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?

Accepted Answer

Enable auditing to a Log Analytics workspace and integrate with Microsoft Sentinel.. Auditing to a Log Analytics workspace allows centralized collection of audit logs, which can then be integrated with Microsoft Sentinel for real-time analytics, threat detection, and automated alerting on suspicious activities. This meets both the compliance requirement for auditing and the operational need for real-time alerts.

Answer

Enable Microsoft Defender for Cloud and configure SQL vulnerability assessment.

Answer

Enable auditing to a storage account and use Microsoft Intune for monitoring.

Answer

Enable Microsoft Purview Data Map for the managed instance.

Question 9

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

Accepted Answer

Scale up to a higher Hyperscale service objective.. Scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Answer

Configure Query Store hints to force parameterization.

Answer

Enable read scale-out to offload read queries.

Answer

Increase the MAXDOP setting for the database.

Question 10

You are deploying an Azure SQL Database for a development environment. The database will be used for testing and will be deleted after three months. You want to minimize costs while ensuring the database can be restored to any point within the retention period. Which backup retention policy should you configure?

Accepted Answer

Configure short-term backup retention to 7 days.. The scenario specifies a development database that will be deleted after three months, with the requirement to restore to any point within the retention period. The default short-term backup retention for Azure SQL Database is 7 days, which provides point-in-time restore (PITR) capability within that window. Since the database is temporary and cost minimization is key, 7 days meets the requirement without incurring extra storage costs for longer retention or geo-redundancy.

Answer

Configure geo-redundant backup storage.

Answer

Configure short-term backup retention to 14 days.

Answer

Configure long-term backup retention (LTR) for 1 year.

Question 11

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

Accepted Answer

Azure SQL Database serverless.. Azure SQL Database serverless is the correct choice because it automatically scales compute resources based on demand and supports pausing the database when idle, effectively scaling to zero compute to save costs. This aligns perfectly with the requirements of variable traffic and cost optimization without manual intervention.

Answer

Azure SQL Database elastic pool.

Answer

Azure SQL Database Hyperscale.

Answer

Azure SQL Database Business Critical tier.

Question 12

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database is 2 TB in size and has a high transaction rate. You need to minimize downtime during migration. The source server is running SQL Server 2016 Enterprise Edition. Which migration approach 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 for large databases with high transaction rates. It uses continuous data synchronization via change tracking or log shipping to keep the target nearly in sync, allowing a brief cutover window. For a 2 TB database, this avoids the lengthy downtime required by offline methods.

Answer

Export the database as a BACPAC file and import it into the managed instance.

Answer

Perform a full backup and restore to the managed instance, then apply log backups.

Answer

Set up transactional replication from on-premises to the managed instance.

Question 13

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 14

You are planning to migrate a large number of on-premises SQL Server databases to Azure SQL Managed Instance. The migration must be automated and support schema and data validation. Which TWO tools or services should you consider using? (Choose TWO.)

Accepted Answer

Azure Database Migration Service. Azure Database Migration Service (DMS) is the correct choice because it is a fully managed service designed to automate the migration of on-premises SQL Server databases to Azure SQL Managed Instance with minimal downtime. It supports both schema and data validation through built-in assessment, data integrity checks, and cutover capabilities, making it ideal for large-scale automated migrations.

Answer

Azure SQL Migration tool

Answer

SQL Server Management Studio (SSMS)

Answer

BACPAC export and import

Question 15

You are configuring an Azure SQL Database for a financial application that requires high availability and disaster recovery across regions. The database is in the Premium service tier. Which TWO configurations should you implement to meet these requirements? (Choose TWO.)

Accepted Answer

Create a failover group that includes the primary and secondary.. A failover group in Azure SQL Database provides automated, coordinated failover of multiple databases across regions, ensuring high availability and disaster recovery with a defined replication policy and a readable secondary endpoint. Option D is correct because active geo-replication allows you to create up to four readable secondary databases in different Azure regions, enabling manual or automatic failover for cross-region disaster recovery, which is essential for a financial application requiring regional resilience.

Answer

Enable automatic tuning for the database.

Answer

Enable zone redundancy within the primary region.

Answer

Enable Transparent Data Encryption (TDE).