DP-300 Practice Test 7 — 10 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