DP-300 Practice Test 27 — 15 Questions

Question 1

You need to create a new Azure SQL Database for a development environment. The development team requires the ability to restore the database to any point in time within the last 7 days. Which backup policy configuration should you choose?

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

Accepted Answer

Use the default backup retention of 7 days. The default backup retention for Azure SQL Database is 7 days, which meets the requirement to restore to any point in time within the last 7 days without additional configuration. Point-in-time restore (PITR) is enabled by default with this retention period, allowing automatic backups and restores within that window. No further backup policy changes are needed for this development environment.

Answer

Configure long-term retention (LTR) for 90 days

Answer

Configure point-in-time restore for 35 days

Answer

Configure point-in-time restore for 1 day

Question 2

You are a database administrator for a global SaaS provider. You have multiple Azure SQL Databases in a single logical server. You need to implement a disaster recovery strategy that provides automatic failover across Azure regions with the lowest possible RPO and RTO for critical databases. The solution must not require any application code changes. What should you implement?

Accepted Answer

Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.. Auto-failover groups provide automatic failover across Azure regions with the lowest possible RPO (typically 5 seconds) and RTO (around 1 minute) for critical databases, without requiring any application code changes. By setting the grace period to 1 hour, you allow the system to tolerate transient outages before triggering failover, which balances availability and data loss tolerance. Including all critical databases in the group ensures consistent failover behavior and prevents split-brain scenarios.

Answer

Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.

Answer

Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.

Answer

Use active geo-replication for each critical database and configure a custom health check to trigger failover.

Question 3

Your company plans to migrate several on-premises SQL Server databases to Azure SQL Database. The databases are used by different applications, each with varying performance requirements. You need to choose a deployment option that provides the most cost-effective solution while allowing for independent scaling of each database. Which option should you choose?

Accepted Answer

Create an Azure SQL Database elastic pool and add databases to the pool.. Azure SQL Database elastic pools allow multiple databases to share a fixed set of resources (eDTUs or vCores), enabling cost savings by pooling underutilized databases while providing the ability to independently scale each database's resource consumption within the pool. This meets the requirement for independent scaling and cost-effectiveness, as opposed to single databases that each require their own dedicated resources.

Answer

Install SQL Server on Azure Virtual Machines and host all databases on a single instance.

Answer

Migrate all databases to a single Azure SQL Managed Instance.

Answer

Deploy each database as a single Azure SQL Database with its own DTU-based tier.

Question 4

You have an Azure SQL Database configured with active geo-replication. You need to automate the failover process in the event of a regional outage, ensuring minimal data loss and automatic failback when the primary region recovers. What should you implement?

Accepted Answer

Configure an auto-failover group with a grace period of 1 hour.. Azure SQL Database auto-failover groups provide automated failover and failback with a grace period for data loss tolerance. Option B is wrong because it does not automate failover. Option C is wrong because manual failover does not provide automatic failback. Option D is wrong because ScheduledExecutorService is a manual workaround that does not integrate with Azure failover groups.

Answer

Set up a PowerShell script that checks primary database health and initiates failover.

Answer

Use a ScheduledExecutorService in a Java application to monitor and failover.

Answer

Enable geo-replication and manually trigger failover when needed.

Question 5

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Accepted Answer

Failover of multiple databases in a single group.. Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Answer

Support for manual failover.

Answer

Readable secondary replicas for read-scale workloads.

Answer

Synchronous data replication to the secondary.

Question 6

You are the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

Accepted Answer

Configure a SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region.. A SQL Server Always On availability group with a synchronous-commit replica in a secondary Azure region provides automatic failover with zero data loss (RPO of 0 seconds) and can meet the 30-minute RTO by enabling fast, automated failover to the secondary region. This solution eliminates the need for manual restore processes and ensures continuous data synchronization, directly addressing the 4-hour outage caused by slow geo-restore.

Answer

Implement Azure Site Recovery to replicate the VM to a secondary region.

Answer

Set up log shipping to a secondary SQL Server in a different region and perform manual failover.

Answer

Increase the frequency of transaction log backups to every 5 minutes and use geo-restore.

Question 7

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

Accepted Answer

Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Answer

Export to BACPAC and import using SqlPackage

Answer

Backup and restore using URL with log shipping

Answer

Transactional replication from on-premises to Azure SQL Database

Question 8

You are managing an Azure SQL Database that runs a critical business application. The database experiences a predictable surge in read-only queries every night at 2:00 AM. You need to configure automatic scaling to handle this surge without manual intervention. What should you do?

Accepted Answer

Configure autoscale settings on the Azure SQL Database using Azure Automation runbooks triggered by a metric alert.. Azure SQL Database does not natively support automatic scaling based on load. To achieve this, you must use Azure Automation runbooks triggered by a metric alert (e.g., DTU or CPU percentage) to programmatically scale the database's service tier up or down. This approach allows you to handle the predictable nightly surge without manual intervention, as the runbook can be scheduled or triggered by a threshold alert.

Answer

Create a read replica and redirect read queries to it during the surge.

Answer

Manually scale up the database service tier before 2:00 AM each day.

Answer

Move the database to an Elastic Database Pool and rely on its built-in autoscaling.

Question 9

Refer to the exhibit. An administrator configures an Azure Cache for Redis instance with zone redundancy and a replica in a different region. What is the current state of the geo-replication setup?

Accepted Answer

The geo-replication is partially configured; the East US replica is still being provisioned.. One replica shows 'Provisioning' status, indicating it is still being set up. The geo-replication link is not yet fully established until both replicas are 'Ready'.

Answer

The zone redundancy is enabled but geo-replication is not configured.

Answer

The geo-replication failed because the SKU does not support it.

Answer

The geo-replication is fully functional and ready for failover.

Question 10

Refer to the exhibit. An administrator wants to ensure that during a regional outage, the failover group automatically fails over without data loss if possible. What is the current configuration gap?

Accepted Answer

The backup storage redundancy is Local, which does not provide geo-redundancy for backups.. Automatic failover with data loss grace period is set to 60 minutes. The backup redundancy is Local, which means backups are stored only in the primary region. If a regional disaster occurs, backups might be lost. Geo-redundant backups would be needed to recover in the secondary region without data loss.

Answer

The failover policy is set to Automatic, but the grace period is too long.

Answer

The secondary managed instance mi2 is in the same region as the primary.

Answer

The read-only endpoint failover policy is Disabled, which prevents read-only traffic after failover.

Question 11

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

Accepted Answer

Enable Microsoft Defender for Cloud on the subscription or resource. Microsoft Defender for Cloud provides advanced threat protection for Azure SQL Managed Instance at the subscription or resource level. Enabling it on the subscription or the specific resource activates threat detection capabilities, including alerts for SQL injection, brute-force attacks, and anomalous access patterns, without requiring additional services.

Answer

Deploy Microsoft Sentinel and connect the SQL Managed Instance

Answer

Configure Microsoft Purview Data Map

Answer

Enable Azure SQL Database auditing

Question 12

You are designing a disaster recovery solution for a mission-critical Azure SQL Database that requires a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds. Which configuration should you recommend?

Accepted Answer

Failover group with automatic failover and data loss threshold of 5 seconds.. Failover groups with automatic failover policy can achieve an RPO of 5 seconds (when the data loss threshold is set to 5 seconds) and an RTO of approximately 30 seconds. Active geo-replication (Option B) requires manual failover, which cannot meet the 30-second RTO. Geo-restore (Option C) has an RPO of 1 hour and RTO of 12 hours. Zone-redundant databases (Option D) only protect against within-region failures, not region-level disasters.

Answer

Active geo-replication with manual failover.

Answer

Geo-restore of automated backups.

Answer

Zone-redundant database in the primary region.

Question 13

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Azure SQL Managed Instance supports zone-redundant deployment for the Business Critical tier, which replicates the database across multiple Azure availability zones within the same region to protect against datacenter-level failures. Option C is correct because Azure SQL Managed Instance has built-in Always On Availability Groups technology that provides automatic failover and high availability at no additional cost, leveraging a quorum-based cluster under the hood.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering

Question 14

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

Accepted Answer

Azure SQL Auditing with storage account destination.. Azure SQL Auditing with a storage account destination is the correct choice because it tracks database events, including schema changes (DDL operations), and writes audit logs to Azure Blob Storage for long-term retention. This meets the requirement to audit all schema changes and store logs durably, as storage accounts provide configurable retention policies.

Answer

Advanced Threat Protection with email alerts.

Answer

Query Store with 'Data Flush Interval' set to 1 minute.

Answer

SQL Vulnerability Assessment with recurring scans.

Question 15

Your organization uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that a specific user can only read data from the Sales schema. The user should not be able to modify any data. What should you do?

Accepted Answer

Create a contained user mapped to the Microsoft Entra identity, then grant SELECT on the Sales schema to the user, and deny SELECT on all other schemas.. It creates a contained database user mapped to the Microsoft Entra identity, then explicitly grants SELECT on the Sales schema. This ensures the user can read data only from that schema. Denying SELECT on all other schemas removes any implicit permissions (such as those from the public role) and enforces the least-privilege principle. This approach is schema-level permission control, which is more granular than fixed database roles.

Answer

Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role.

Answer

Create a contained user mapped to the Microsoft Entra identity and add the user to the db_denydatawriter role.

Answer

Create a contained user mapped to the Microsoft Entra identity and add the user to the db_datareader role, then deny SELECT on all schemas except Sales.