DP-300 Plan and implement data platform resources Practice Test 3 — 15 Questions

Question 1

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?

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

Accepted Answer

Use online index rebuild with resumable operation.. 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 2

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.. 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 3

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

Accepted Answer

Increase the frequency of transaction log backups on the source database to keep the log small.. Increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Answer

Switch to offline migration mode to avoid log growth issues.

Answer

Pause the migration and request a higher network bandwidth from the ISP.

Answer

Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.

Question 4

You are designing a database solution for an e-commerce application that requires high read throughput with sub-5 ms latency. The application runs on Azure VMs. You need to choose between Azure SQL Database and Azure SQL Managed Instance. Which factor most strongly supports choosing Azure SQL Database over SQL Managed Instance?

Accepted Answer

Azure SQL Database supports Hyperscale tier with sub-5 ms read latency on local SSDs.. The Hyperscale tier of Azure SQL Database uses a distributed architecture with local SSD-based buffer pool extensions on compute nodes, enabling sub-5 ms read latency for data cached locally. This directly meets the e-commerce application's requirement for high read throughput with low latency, whereas Azure SQL Managed Instance relies on remote premium page blobs for storage, which introduces higher latency for reads that are not cache-resident.

Answer

Azure SQL Database offers a wider range of built-in instance-level features.

Answer

Azure SQL Database allows databases up to 100 TB in size.

Answer

Azure SQL Database supports active geo-replication with read-scale secondaries.

Question 5

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

Accepted Answer

Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.. TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Answer

Use Azure Data Studio to import the certificate directly from the local machine store during migration.

Answer

Disable TDE on the source database, migrate the backup, then enable TDE on the target.

Answer

Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.

Question 6

A company plans to deploy a new application on Azure VMs that requires a highly available SQL Server database with automatic failover and readable secondaries. The database size is 1 TB. Which deployment option meets these requirements with the least administrative overhead?

Accepted Answer

Azure SQL Managed Instance in the Business Critical tier.. Azure SQL Managed Instance in the Business Critical tier provides built-in high availability with automatic failover and readable secondaries via Always On Availability Groups, while requiring minimal administrative overhead. The 1 TB database size is supported in the Business Critical tier (up to 4 TB), and the managed platform handles patching, backups, and failover orchestration automatically, unlike IaaS-based solutions.

Answer

Azure SQL Database in the General Purpose tier.

Answer

SQL Server on Azure VMs with Always On Availability Groups.

Answer

Azure SQL Database in the Hyperscale tier.

Question 7

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

Accepted Answer

The database is experiencing memory pressure due to concurrent queries exceeding available memory.. RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants. This is a classic sign of memory pressure, not CPU or IO throttling.

Answer

There is a blocking chain due to unoptimized queries.

Answer

The disk IOPS limit is being reached, causing queuing.

Answer

The DTU limit is being reached, causing CPU throttling.

Question 8

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

Accepted Answer

Create secondary server in another region, then configure the secondary database, then initiate replication, then verify the replication link.. Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Answer

Create secondary server in another region, then initiate replication, then configure the secondary database, then verify the replication link.

Answer

Initiate replication, then create secondary server in another region, then configure the secondary database, then verify the replication link.

Answer

Create secondary server in another region, then configure the secondary database, then verify the replication link, then initiate replication.

Question 9

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

Accepted Answer

Identify high CPU queries, then Analyze execution plans, then Check for missing indexes, then Implement fixes, then Monitor performance.. Start by identifying high CPU queries, analyze plans, check for missing indexes, implement fixes, then monitor.

Answer

Identify high CPU queries, then Check for missing indexes, then Analyze execution plans, then Implement fixes, then Monitor performance.

Answer

Analyze execution plans, then Identify high CPU queries, then Check for missing indexes, then Implement fixes, then Monitor performance.

Answer

Identify high CPU queries, then Analyze execution plans, then Implement fixes, then Check for missing indexes, then Monitor performance.

Question 10

Match each Azure SQL Database service tier to its description.

Accepted Answer

General Purpose: Balanced performance for most cloud workloads with compute and storage separation.. These are the main service tiers in Azure SQL Database, each designed for different performance and scalability needs.

Answer

General Purpose: High I/O and low latency with multiple read-only replicas.

Answer

Business Critical: Scalable storage up to 100 TB with auto-scaling compute.

Answer

Hyperscale: Balanced performance for small to medium workloads with compute and storage separation.

Question 11

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

Accepted Answer

Query Performance Insight: Provides intelligent query analysis with performance history and recommendations.. Azure SQL Database provides several performance tuning features. Query Performance Insight offers query-level analysis, Automatic Tuning automatically applies index and plan changes, Intelligent Insights gives proactive diagnostics, and Database Advisor provides configuration recommendations. Common confusions involve swapping the automatic tuning and insight features.

Answer

Query Performance Insight: Automatically applies performance improvements like index creation and plan forcing.

Answer

Automatic Tuning: Provides proactive performance monitoring with root cause analysis and diagnostics.

Question 12

You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?

Accepted Answer

Use Azure Database Migration Service (DMS) with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.

Answer

Use the SQL Server Import/Export Wizard to copy data.

Answer

Export the database as a BACPAC file and import it into the managed instance.

Answer

Use transaction log shipping from the source to the target.

Question 13

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

Accepted Answer

Configure an Entra ID administrator for the Azure SQL logical server.. To enforce Microsoft Entra ID authentication for all connections to Azure SQL Database, you must first configure an Entra ID administrator at the logical server level. This step establishes the server’s trust relationship with the Entra ID tenant, enabling token-based authentication using OAuth 2.0. Without this administrator, Entra ID authentication cannot be used, and only SQL authentication would be available.

Answer

Enable the Azure SQL Database firewall to allow only Entra ID IP addresses.

Answer

Create a contained database user mapped to an Entra ID identity.

Answer

Set the database to read-only mode.

Question 14

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

Accepted Answer

Use the Azure portal to scale up the DTU or vCore service tier.. Scaling up the DTU or vCore service tier in the Azure portal is a dynamic scaling operation that typically completes within minutes and does not require application downtime. Azure SQL Database supports online scaling, meaning the database remains available during the transition, with only a brief connection failover at the end. This directly addresses the need to increase performance during peak hours with minimal disruption.

Answer

Create a new database on a higher tier and copy data manually.

Answer

Enable read scale-out to offload read workloads.

Answer

Implement horizontal sharding across multiple databases.

Question 15

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

Accepted Answer

Use Azure Data Box to physically transfer the data.. Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Answer

Use transaction log shipping to continuously sync changes.

Answer

Export as BACPAC and upload to Azure Blob Storage, then import.

Answer

Use Azure Database Migration Service (DMS) online migration.