DP-300 Practice Test 2 — 30 Questions

Question 1

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

Accepted Answer

The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.. The current configuration uses a backup interval of 12 hours, which means automated backups are taken only every 12 hours. Point-in-time restore (PITR) in Azure SQL Database relies on full, differential, and transaction log backups to allow restoration to any second within the retention period. With a 12-hour backup interval, transaction log backups are not frequent enough to achieve 1-minute granularity; the minimum granularity is limited by the frequency of log backups, which defaults to every 5-10 minutes but can be configured. A 12-hour interval indicates that log backups are not being taken frequently enough, thus preventing 1-minute restore granularity.

Answer

The retention period is too short to support point-in-time restore.

Answer

The policy must specify a minimum retention of 30 days for point-in-time restore.

Answer

Geo-redundant backup must be disabled for point-in-time restore to work.

Question 2

A company is deploying Azure SQL Managed Instance and needs to ensure that the failover group provides automatic failover with zero data loss during a regional outage. The secondary region is 500 miles away. Which data replication mode should be configured?

Accepted Answer

Synchronous replication. Azure SQL Managed Instance failover groups require synchronous replication to guarantee zero data loss during automatic failover. Synchronous replication ensures that transactions are committed on both the primary and secondary replicas before acknowledging the commit, so no data is lost even if the primary region fails. This mode is mandatory for failover groups with the 'automatic failover' and 'zero data loss' requirements, despite the 500-mile distance which introduces latency.

Answer

Geo-replication

Answer

Snapshot replication

Answer

Asynchronous replication

Question 3

Match each Azure SQL Database service tier to its description.

Question 4

Match each Azure SQL Database backup type to its description.

Question 5

Match each Azure SQL Database migration tool to its description.

Question 6

Match each Azure SQL Database security feature to its purpose.

Question 7

Match each Azure SQL Database high availability feature to its description.

Question 8

Match each Azure SQL Database performance tuning feature to its description.

Question 9

Match each Azure SQL Database command to its function.

Question 10

Match each Azure SQL Database error code to its meaning.

Question 11

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Accepted Answer

Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.. Option B is correct because REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Answer

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Answer

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Answer

Rebuild the entire database by creating a new database and copying data.

Question 12

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

Accepted Answer

Use active geo-replication and plan manual failover.. Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Answer

Use auto-failover groups with read-write failover policy.

Answer

Use geo-restore from geo-redundant backups.

Answer

Use long-term retention (LTR) backups with daily frequency.

Question 13

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

Accepted Answer

Set PITR retention to 35 days and configure a long-term retention (LTR) policy to keep weekly backups for 7 years.. Azure SQL Managed Instance's point-in-time restore (PITR) retention is capped at 35 days, so to meet the 90-day restore requirement, you must combine a 35-day PITR policy with a long-term retention (LTR) policy that keeps weekly full backups for 7 years. LTR allows you to restore to any point within the LTR window by using full, differential, and log backups, satisfying both the 90-day point-in-time restore and the 7-year auditing compliance.

Answer

Set point-in-time restore (PITR) retention to 90 days.

Answer

Set PITR retention to 90 days and configure geo-replication to achieve the 7-year retention.

Answer

Configure geo-redundant backup storage with a retention of 90 days.

Question 14

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

Accepted Answer

Use an Auto-Failover Group with the secondary in the paired region. Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Answer

Restore a geo-redundant backup to the secondary region

Answer

Configure Active Geo-Replication to the secondary region

Answer

Create a named replica in the secondary region and fail over manually

Question 15

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Accepted Answer

Internal Load Balancer. A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 16

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

Accepted Answer

Network connectivity between the replicas is blocked on port 5022. The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Answer

The availability group listener is not configured correctly

Answer

The primary instance encountered a critical error and failed over automatically

Answer

The cluster has lost quorum

Question 17

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 18

A company has an Azure SQL Managed Instance that is experiencing high CPU usage. The DBA observes that a specific query is causing high compile time due to parameter sniffing. The query is executed frequently with varying parameter values. Which approach should the DBA use to reduce CPU usage without changing the T-SQL code?

Accepted Answer

Enable forced parameterization for the database. Forced parameterization (Option D) converts literal values in the query to parameters, allowing SQL Server to reuse cached execution plans across different parameter values. This reduces CPU usage by eliminating frequent recompilations caused by parameter sniffing, without requiring any changes to the T-SQL code itself.

Answer

Create a plan guide to force a specific plan

Answer

Use optimize for ad hoc workloads

Answer

Add query store and force the last good plan

Question 19

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Accepted Answer

Cross-database queries (three- and four-part names). Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Answer

Transparent Data Encryption (TDE)

Answer

Elastic pools

Question 20

A DBA is migrating a large on-premises database to Azure SQL Database using the Data Migration Assistant (DMA). The migration fails with an error indicating that the source database contains cross-database queries. What is the best remediation?

Accepted Answer

Refactor the application to eliminate cross-database queries or use elastic query. Azure SQL Database does not support cross-database queries natively. The Data Migration Assistant (DMA) blocks migrations that rely on such queries because the PaaS service lacks the necessary server-level context. The correct remediation is to refactor the application to eliminate cross-database dependencies or use elastic query, which provides a read-only, schema-bound mechanism to query remote databases via external data sources and external tables.

Answer

Use elastic database query to reference external tables

Answer

Create a linked server in Azure SQL Database

Answer

Migrate to Azure SQL Managed Instance instead

DP-300 Practice Test 2 — 30 Questions

Exhibit

DP-300 Practice Test 2 — 30 Questions

Exhibit