DP-300 Practice Questions 1 — 50 Questions

Question 1

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

Accepted Answer

Active geo-replication with failover group. Active geo-replication with a failover group is the correct choice because it provides automatic, customer-managed failover to a secondary region with an RPO of 5 seconds. The failover group orchestrates the failover of multiple databases simultaneously and supports automatic failover policies, meeting the requirement for zero manual intervention. Standard geo-replication does not support automatic failover, and other options do not provide cross-region disaster recovery.

Answer

Standard geo-replication

Answer

Local redundancy with automatic failover

Answer

Read-scale out

Question 2

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

Accepted Answer

Set the backup retention period to 35 days in the backup settings. Option C is correct because Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Answer

Create a backup policy in Azure Backup Vault

Answer

Enable geo-redundant storage (GRS)

Answer

Configure long-term retention (LTR) policy

Question 3

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

Accepted Answer

Set zoneRedundant to true and configure an auto-failover group with a secondary region. Option C is correct because Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Answer

Set collation to SQL_Latin1_General_CP1_CS_AS

Answer

Set zoneRedundant to true

Answer

Change storageAccountType to RA-GRS

Question 4

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

Accepted Answer

Migrate to Azure SQL Managed Instance as it supports SQL Agent and cross-database queries within the same instance.. Azure SQL Managed Instance is the correct target because it provides full support for SQL Server Agent, including proxies, and enables cross-database queries within the same instance. Unlike Azure SQL Database, Managed Instance maintains instance-level scope, allowing queries that reference other databases in the same instance without requiring external data sources or elastic queries.

Answer

Migrate to SQL Server on Azure Virtual Machines for full control.

Answer

Migrate to Azure SQL Database elastic query to handle cross-database queries.

Answer

Migrate to Azure SQL Database instead to reduce costs.

Question 5

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

Accepted Answer

Use online index rebuild with resumable operation.. Option B is correct because online index rebuild with resumable operation allows the index to be rebuilt while the table remains available for reads and writes, minimizing downtime. The resumable feature lets you pause and resume the rebuild if it is interrupted, which is critical for large tables that are frequently updated. This approach reduces performance impact compared to offline rebuilds and avoids blocking issues associated with other online methods.

Answer

Schedule an offline index rebuild during off-peak hours.

Answer

Disable and rebuild indexes manually during maintenance window.

Answer

Use online index reorganize with low priority locks.

Question 6

Which THREE of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database?

Accepted Answer

Use the Data Migration Assistant (DMA) to assess and migrate.. Option B is correct because the Data Migration Assistant (DMA) is a Microsoft tool specifically designed to assess on-premises SQL Server databases for compatibility issues and then perform a seamless migration of schema and data to Azure SQL Database. DMA handles both assessment and migration in a single workflow, making it a valid and recommended option for migrating to Azure SQL Database.

Answer

Use SQL Server Integration Services (SSIS) to migrate schema and data.

Answer

Use the Azure portal's 'Import database' wizard directly from on-premises.

Question 7

You are managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

Accepted Answer

Update all statistics on the table.. The query is performing a clustered index scan, which means SQL Server is reading all rows in the table. Outdated statistics can cause the optimizer to choose a scan instead of a more efficient seek. Updating all statistics on the table (option B) provides the optimizer with fresh distribution information, potentially allowing it to choose a better execution plan that avoids the scan, thereby reducing query execution time without adding indexes.

Answer

Increase the service tier of the Azure SQL Database to provide more resources.

Answer

Rebuild the clustered index to reduce fragmentation.

Answer

Update the statistics on the nonclustered index only.

Question 8

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

Accepted Answer

sys.query_store_query_stats. A is correct because sys.query_store_query_stats aggregates runtime statistics per query across all plans and time intervals, making it the ideal view to identify queries with the highest total resource consumption (e.g., CPU, I/O, duration) over the collected week. Query Store captures historical execution data, and this view provides the cumulative metrics needed to pinpoint the most resource-intensive queries for performance bottleneck analysis.

Answer

sys.query_store_plan

Answer

sys.dm_exec_query_stats

Answer

sys.dm_db_resource_stats

Question 9

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

Accepted Answer

Manually drop the problematic index using a DROP INDEX command.. Option D is correct because manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Answer

Use the Azure portal to revert all automatic tuning recommendations for the past week.

Answer

Manually create the missing indexes that were dropped by automatic tuning.

Answer

Disable automatic tuning for the entire database.

Question 10

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Accepted Answer

Create appropriate indexes on the columns used in joins and filters.. An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Answer

Disable parallelism for the query using the MAXDOP 1 hint.

Answer

Increase the DTU or vCore count of the database.

Answer

Rewrite the query using table hints to force a specific join order.

Question 11

A company runs a critical Azure SQL Database in the West US region. To meet a Recovery Point Objective (RPO) of 5 seconds and a Recovery Time Objective (RTO) of 30 seconds during a regional outage, which deployment option should be used?

Accepted Answer

Deploy the database in the Business Critical tier with zone-redundant configuration in West US. The Business Critical tier with zone-redundant configuration provides synchronous replication of data across three availability zones within the same Azure region, ensuring zero data loss (RPO=0) and automatic failover within seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds during a regional outage because zone redundancy protects against zone-level failures, and the failover is automatic and fast. In contrast, other options either introduce asynchronous replication (which cannot guarantee an RPO of 5 seconds) or rely on cross-region failover that exceeds the required RTO.

Answer

Use an Auto-Failover Group with a secondary in West US 2

Answer

Enable geo-zone-redundant backup storage and perform point-in-time restore

Answer

Configure Active Geo-Replication with a readable secondary in East US

Question 12

A company has an Azure SQL Managed Instance in the East US region. They need to implement disaster recovery with automatic failover to a paired region. The solution must minimize data loss to less than 5 seconds. Which feature should they use?

Accepted Answer

Configure an Auto-Failover Group with a secondary instance in West US. Option C is correct because Auto-Failover Groups for Azure SQL Managed Instance provide automatic failover to a paired region (West US) with a replication lag target of less than 5 seconds when using the Readable Secondary option. This feature uses synchronous replication at the commit level to minimize data loss, meeting the RPO requirement of <5 seconds.

Answer

Active Geo-Replication with a secondary in East US 2

Answer

Enable geo-redundant backup storage and restore to West US

Answer

Set up a Failover Group with manual failover to a secondary in West US

Question 13

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Accepted Answer

Create an Always On Availability Group with an availability group listener. An Always On Availability Group (AG) with a listener provides high availability with automatic failover at the database level without requiring shared storage. This solution meets the requirement for automatic failover and minimizes downtime during planned maintenance by allowing manual failover to a synchronized secondary replica with minimal disruption.

Answer

Configure Log Shipping to a secondary VM

Answer

Deploy a Failover Cluster Instance using Azure Shared Disks

Answer

Use Database Mirroring with automatic failover

Question 14

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Accepted Answer

Automatic failover capability. Option A is correct because auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.

Answer

Recovery Point Objective (RPO) of 1 second

Answer

Support for SQL Server Authentication

Question 15

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.

Question 16

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

Question 17

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Question 18

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

Question 19

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

Question 20

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.