DP-300 Practice Test 3 — 30 Questions

Question 1

Your company has a policy that requires all Azure SQL Databases to be recoverable to a point in time within the last 5 minutes in the event of a user error. What should you configure?

Accepted Answer

Configure point-in-time restore (PITR) with a retention period of at least 5 minutes. Point-in-time restore (PITR) allows restoring a database to any point within the retention period. By configuring a retention period that covers at least the last 5 minutes, you can recover from user errors within 5 minutes. The default retention is 7 days, but you can configure it up to 35 days, so 5 minutes is easily achievable. Option B (zone-redundant configuration) provides high availability within a region but not point-in-time recovery. Option C (active geo-replication) is for disaster recovery to another region. Option D (long-term backup retention) is for backups beyond the PITR retention period, not for short-term recovery.

Answer

Enable zone-redundant configuration

Answer

Set up active geo-replication to a secondary region

Answer

Enable long-term backup retention (LTR)

Question 2

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

Accepted Answer

7 days. Point-in-time restore retention can be set from 1 to 35 days. To restore to any point within 7 days, you need at least 7 days retention. Option A (1 day) is insufficient. Option C (14 days) is more than needed. Option D (3 days) is also insufficient.

Answer

1 day

Answer

14 days

Answer

3 days

Question 3

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

Accepted Answer

Initiate an unplanned failover from the primary to the secondary.. During a full outage, the primary is offline, so a planned failover (which requires connectivity to the primary to synchronize) is not possible. The only option to fail over with minimal data loss is to initiate an unplanned (forced) failover, which will make the secondary the new primary. Although there may be some data loss from transactions not yet replicated, this is the best available option. Deleting the secondary or creating a new secondary does not fail over.

Answer

Delete the secondary database and create a new one in the primary region.

Answer

Initiate a planned failover from the primary to the secondary.

Answer

Create a new secondary database in the same region as the primary.

Question 4

Your company has a SQL Server on Azure VM hosting a critical database. You need to ensure high availability with automatic failover and no data loss during a planned patching event. The solution must minimize cost. Which configuration should you use?

Accepted Answer

Deploy two Azure VMs in an availability set, configure SQL Server Always On availability group with synchronous commit and automatic failover.. Deploying two Azure VMs in an availability set and configuring SQL Server Always On availability group with synchronous commit and automatic failover meets the requirements of high availability with automatic failover and no data loss during planned patching (by performing a manual failover). This solution minimizes cost compared to Azure SQL Database Managed Instance or geo-replication. Option A is incorrect because migrating to Azure SQL Database Managed Instance is a different platform, not SQL Server on Azure VM, and geo-replication is for disaster recovery, not planned patching. Option B is incorrect because log shipping is not automatic and can result in data loss. Option C is incorrect because a single VM with FCI using Storage Spaces Direct does not provide automatic failover for planned patching; it requires a secondary node.

Answer

Migrate to Azure SQL Database Managed Instance and configure geo-replication.

Answer

Configure log shipping with a secondary VM in another region.

Answer

Use a single VM with a failover cluster instance (FCI) using Storage Spaces Direct.

Question 5

Your company uses Azure SQL Database with active geo-replication for disaster recovery. The primary database is in the West Europe region. You need to perform a planned failover to the secondary in North Europe for a maintenance window. Which THREE actions should you take? (Choose three.)

Accepted Answer

After failover, verify that the new secondary (in West Europe) is configured as a readable secondary.. The correct actions are C, D, and E. For a planned failover during a maintenance window, you should initiate a planned failover (no data loss) from the Azure portal or PowerShell (E) to ensure all transactions are synchronized before switching. After failover, verify that the new secondary (in West Europe) is configured as a readable secondary (C) to maintain disaster recovery capabilities. Additionally, update the application connection strings to point to the new primary in North Europe (D). Option A is incorrect because a forced failover could cause data loss and is intended for unplanned outages. Option B is incorrect because seeding of the new secondary happens automatically via geo-replication.

Answer

Initiate a forced failover to ensure the fastest switchover.

Answer

Manually seed the new secondary database after failover.

Question 6

Your company runs a financial application on Azure SQL Managed Instance. The primary instance is deployed in North Europe. You need to implement a disaster recovery solution that meets the following requirements: automatic failover to a secondary region in case of a regional outage; the secondary must be readable during normal operations to serve read-only queries; the RPO must be less than 10 seconds; the RTO must be less than 30 seconds; and you must minimize compute costs by using the smallest possible secondary instance size. The primary instance uses the Business Critical service tier. What should you do?

Accepted Answer

Create a failover group between the primary and a secondary Business Critical instance in a paired region, and use a smaller compute size for the secondary.. A failover group between two Azure SQL Managed Instances in different regions provides automatic failover and a readable secondary. The secondary must be in the Business Critical service tier, matching the primary's tier, but you can use a smaller compute size (fewer vCores) to reduce costs while still supporting read-only workloads. Option A is incorrect because you cannot change the secondary's service tier to General Purpose in a failover group; it must match the primary. Option C is incorrect because the secondary must be in a different region for disaster recovery. Option D is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate DR solution.

Answer

Create a failover group with a General Purpose secondary instance in a different region to reduce costs.

Answer

Deploy a secondary Business Critical instance in the same region and configure a failover group.

Answer

Configure active geo-replication to a secondary managed instance in a different region.

Question 7

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 8

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

Accepted Answer

Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.. To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Answer

Set the 'minimal TLS version' to 1.2 in the database's properties.

Answer

Add a firewall rule to deny connections using TLS 1.0 or 1.1.

Answer

Enable the 'Force encryption' option in the connection string and require TLS 1.2.

Question 9

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Accepted Answer

Query Store to review query execution plans and wait statistics. Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 10

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

Accepted Answer

Autoscale settings for the database. Azure SQL Database can use autoscale settings (in the DTU or vCore model) to automatically adjust resources based on metrics like CPU usage. Option B (Elastic job) is for running scheduled tasks across databases, not scaling. Option C (Elastic pool) is for managing multiple databases with shared resources, but it does not automatically scale a single database. Option D (Automatic tuning) optimizes query performance, not resource scaling.

Answer

Elastic job

Answer

Elastic pool

Answer

Automatic tuning

Question 11

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

Accepted Answer

Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.. The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days. Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Answer

Full backups are taken every 12 hours and retained for 7 days.

Answer

Long-term retention (LTR) is set to 7 days.

Answer

Transaction log backups are taken every 12 hours.

Question 12

You manage an Azure SQL Database in the Business Critical service tier. The database must be highly available within a single region. Which TWO features contribute to this high availability?

Accepted Answer

Multiple synchronous replicas (4 replicas). The correct answers are B and E. In the Business Critical service tier, multiple synchronous replicas (at least 4) provide local high availability within a region. Additionally, enabling zone-redundant configuration distributes these replicas across availability zones within the same region, further improving availability. Options A and D (Active geo-replication and Auto-failover groups) are cross-region disaster recovery features and do not contribute to intra-region high availability. Option C (Always On availability groups) is the underlying technology for the replicas but is not a separate feature; the question asks for features that contribute to high availability, and the replicas themselves are the feature.

Answer

Active geo-replication

Answer

Always On availability groups

Answer

Auto-failover groups

Question 13

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email. Azure Automation runbooks can query fragmentation and send emails via SendGrid. Elastic Database Jobs can check fragmentation but cannot send emails directly. Azure Monitor alerts can trigger on metrics like DTU but not on index fragmentation. Azure Logic Apps can receive data but require custom setup.

Answer

Create an Azure Monitor alert based on fragmentation

Answer

Use Elastic Database Jobs to check fragmentation and send email

Answer

Configure SQL Agent job to send email

Question 14

You are a database administrator for a medium-sized e-commerce company. The company runs its online transaction processing (OLTP) workload on an Azure SQL Database in the General Purpose service tier (DTU-based, S3). The database is used for order processing, inventory management, and customer data. Recently, during peak shopping hours (10 AM to 2 PM), users have reported that order entry forms take several seconds to submit, and inventory queries are timing out. Monitoring shows that DTU usage regularly hits 100% during these hours, with high PAGELATCH_IO waits. You need to resolve the performance issue with minimal cost increase. What should you do?

Accepted Answer

Migrate to the vCore model with Hyperscale service tier. Migrate to the vCore model with Hyperscale service tier. The issue is high DTU usage with PAGELATCH_IO waits, indicating resource contention on I/O. Hyperscale architecture separates compute and storage, eliminating such bottlenecks and providing near-instant scaling for peak loads. Option A (increase storage) does not address DTU limits. Option B (increase to S4) adds more DTUs but retains the same architecture limitations that cause PAGELATCH_IO. Option C (read replica) only offloads read queries, not write-heavy OLTP.

Answer

Increase the max storage size to 1 TB

Answer

Increase the service tier to S4 during peak hours

Answer

Create a read-only replica and offload reporting queries

Question 15

Your company has an Azure SQL Managed Instance in the West US region. You need to configure a disaster recovery solution that provides automatic failover to a secondary region with minimal data loss. The solution must also allow read-only access to the secondary database for reporting purposes. Which option should you use?

Accepted Answer

Configure a failover group between the primary and secondary managed instances.. Failover groups are the native disaster recovery feature for Azure SQL Managed Instance, providing automatic failover, a readable secondary (via read-only intent), and minimal data loss with a synchronization mode that prioritizes data durability. Option A (auto-failover group with active geo-replication) is incorrect because active geo-replication is a separate feature for single databases and elastic pools, not managed instances; failover groups themselves include the auto-failover capability. Option C (log shipping) is not a built-in managed instance feature, does not provide automatic failover, and the secondary is not readable during normal operation. Option D (active geo-replication) is for single databases and elastic pools, not managed instances, and does not provide automatic failover.

Answer

Create an auto-failover group with active geo-replication.

Answer

Use log shipping to a secondary managed instance.

Answer

Enable active geo-replication on the database.

Question 16

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

Accepted Answer

Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.. The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, the resource type is 'backupShortTermRetentionPolicies', which only supports short-term retention properties like retentionDays. Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) belong to a different resource type ('backupLongTermRetentionPolicies'). Including them in the same resource will cause a deployment error. Option B correctly identifies this. Option A is incorrect because the JSON syntax is valid (camelCase is used in ARM templates). Option C is incorrect because diffBackupIntervalInHours is not relevant to this issue. Option D is incorrect because 28 days is within the allowed range for PITR (up to 35 days).

Answer

The JSON syntax is invalid because property names use camelCase instead of PascalCase.

Answer

The diffBackupIntervalInHours should be 12 for a business-critical database.

Answer

The retentionDays value of 28 exceeds the maximum allowed for PITR.

Question 17

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

Accepted Answer

The pause action is not supported for Azure SQL Database.. Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the pause action is not supported for Azure SQL Database, making option D correct. Option A is incorrect because the connection authentication is not the issue here. Option B is incorrect because the recurrence trigger schedule is valid. Option C is incorrect because the API version is not the primary reason; the operation itself is unsupported.

Answer

The SQL connection is not authenticated.

Answer

The recurrence trigger uses a schedule that is not valid.

Answer

The API version specified is outdated.

Question 18

Which TWO statements about Azure SQL Database zone-redundant configuration are true? (Choose two.)

Accepted Answer

It can be enabled during database creation or by updating an existing database.. Options B and D are correct. Zone-redundant configuration can be enabled during database creation or by updating an existing database (B), and it provides resilience within a single Azure region across multiple availability zones (D). Option A is incorrect because zone-redundant configuration does not require a failover group; failover groups are used for geo-replication, not for zone redundancy. Option C is incorrect because zone-redundant configuration is available in both the General Purpose and Business Critical service tiers. Option E is incorrect because the cost impact varies by tier: General Purpose tier does not incur additional cost for zone redundancy, while Business Critical does; thus, the blanket statement that it always incurs additional cost is false.

Answer

It requires a failover group to be configured.

Answer

It is only available in the Business Critical service tier.

Answer

It incurs additional cost compared to non-zone-redundant configurations.

Question 19

Your company uses Azure SQL Database Hyperscale tier for a large data warehouse. You need to implement disaster recovery with the ability to perform point-in-time restore in the secondary region. What is the best approach?

Accepted Answer

Configure geo-replication to a secondary Hyperscale database in the secondary region.. Azure SQL Database Hyperscale tier supports geo-replication (currently in preview), which creates a readable secondary database in a different region. This secondary database also supports point-in-time restore within the secondary region, meeting the requirement for disaster recovery with point-in-time restore capability. Option B is incorrect because auto-failover groups are not supported for Hyperscale databases. Option C is incorrect because geo-restore of automated backups has a higher recovery point objective (RPO) compared to geo-replication. Option D is incorrect as failover groups are not supported for Hyperscale.

Answer

Configure auto-failover groups between regions.

Answer

Use geo-restore of automated backups.

Answer

Create a failover group with manual failover.

Question 20

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

Accepted Answer

Configure zone-redundant availability for the database. Azure SQL Database zone-redundant availability replicates the database across multiple availability zones within the same region, providing high availability in the event of a single zone failure. Option B (active geo-replication) replicates to a different region, which protects against regional outages but not specifically a zone failure. Option C (locally redundant storage) stores data within a single data center, offering no zone-level protection. Option D (read scale-out) provides read-only replicas but does not ensure write availability during a zone failure.

Answer

Use active geo-replication to a different region

Answer

Deploy the database with locally redundant storage

Answer

Configure read scale-out with a secondary replica