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

Question 1

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?

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

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 2

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 3

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 4

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 5

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 6

You are planning a deployment of Azure SQL Database for a mission-critical application. The application requires a high level of resilience to regional failures. You need to ensure that data is replicated synchronously across two Azure regions and that automatic failover occurs with zero data loss. Which deployment option should you choose?

Accepted Answer

SQL Server on Azure VMs with Always On Availability Groups.. SQL Server on Azure VMs with Always On Availability Groups can be configured with synchronous-commit mode across Azure regions, ensuring zero data loss (RPO=0) and automatic failover. In contrast, Azure SQL Database and Azure SQL Managed Instance only support asynchronous replication across regions via failover groups or geo-replication, which cannot guarantee zero data loss. General Purpose and Hyperscale tiers also lack synchronous cross-region replication.

Answer

Azure SQL Database on General Purpose tier with active geo-replication.

Answer

Azure SQL Database on Hyperscale tier with named replicas.

Answer

Azure SQL Managed Instance on Business Critical tier with failover groups.

Question 7

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.

Question 8

You are designing a disaster recovery solution for an Azure SQL Database in the Business Critical tier. You require automatic failover to a secondary region with zero data loss. Which configuration should you use?

Accepted Answer

Create a failover group with the Business Critical tier.. The Business Critical tier in Azure SQL Database supports failover groups, which provide automatic failover across regions with zero data loss by using synchronous replication at the primary region and asynchronous replication to the secondary region. Failover groups ensure that all committed transactions are preserved during a failover, meeting the zero data loss requirement for disaster recovery.

Answer

Configure geo-replication between two servers.

Answer

Enable read scale-out in a secondary region.

Answer

Use active geo-replication with manual failover.

Question 9

Which TWO actions are required to implement Azure SQL Database automatic failover with zero data loss?

Accepted Answer

Create an auto-failover group.. Option A is correct because an auto-failover group is the Azure SQL Database feature that provides automatic failover of a database or group of databases to a secondary region, with a listener endpoint that redirects connections without application changes. Option D is correct because zero data loss requires synchronous replication to the secondary, which is only supported in the Premium and Business Critical service tiers (and their equivalent Hyperscale/General Purpose configurations do not provide synchronous geo-replication with zero RPO). Active geo-replication (B) is asynchronous and therefore can incur data loss, so it does not meet the zero data loss requirement. Read scale-out (C) only offloads read-only workloads to replicas and does not provide failover. Multiple readable secondaries (E) is not a valid standalone Azure SQL Database configuration for automatic failover with zero data loss.

Answer

Configure active geo-replication.

Answer

Enable read scale-out.

Answer

Configure multiple readable secondaries.

Question 10

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Option A is correct because Azure SQL Managed Instance supports FILESTREAM and FileTable, but they are disabled by default and require specific configuration (enabling the FILESTREAM option and provisioning a file share/storage), so they must be explicitly planned for during migration. Option B is correct because SQL Managed Instance supports cross-database queries with three-part names within the same instance, unlike Azure SQL Database; however, this capability depends on all referenced databases residing on the same managed instance, so it is a key planning consideration. Option E is correct because SQL Server Agent is available on SQL Managed Instance and supports PowerShell job steps via the PowerShell subsystem, but the agent must be enabled and the PowerShell subsystem configured, making it an important migration consideration. Option C is not marked correct because SSIS is not hosted inside SQL Managed Instance; SSISDB and package execution require Azure-SSIS Integration Runtime in Azure Data Factory, so it is not a native Managed Instance consideration in the same way. Option D is not marked correct because contained databases are supported on SQL Managed Instance and do not represent a special migration blocker or planning constraint compared to the marked options.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 11

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

Accepted Answer

Service tier and performance level of the database.. The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Answer

Details of table schemas.

Answer

List of backup retention policies.

Answer

List of users and their permissions.

Question 12

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

Accepted Answer

Use active geo-replication with a readable secondary in another Azure region. Active geo-replication provides an asynchronous replica in a secondary region with an RPO of up to 5 seconds and supports manual failover with an RTO of approximately 30 seconds, meeting the strict RPO and RTO requirements for disaster recovery. Zone-redundant configuration (Option C) offers high availability within a single region but does not protect against regional outages, so it is not a disaster recovery solution. Failover groups (Option B and D) have an RTO of about 1 hour, exceeding the 30-second requirement. Therefore, Option A is correct.

Answer

Use a failover group with a secondary in a paired region

Answer

Deploy the database with zone-redundant configuration in the primary region

Answer

Deploy the database with an auto-failover group to a secondary region

Question 13

Your Azure SQL Database is running in the General Purpose tier. You notice that read queries are experiencing high latency. You need to improve read performance without changing the compute size. What should you implement?

Accepted Answer

Create columnstore indexes on the tables. Creating columnstore indexes can improve read performance by compressing data and enabling batch processing for analytical queries, which reduces I/O and latency without requiring a change in compute size or service tier. Read scale-out (option B) is not available on the General Purpose tier, so it cannot be implemented without upgrading to a higher tier, which might be considered a change beyond compute size. Hyperscale (option A) changes the service tier and may not directly address read latency. In-memory OLTP (option D) is optimized for transactional workloads, not general read queries.

Answer

Upgrade to the Hyperscale service tier

Answer

Enable read scale-out

Answer

Use in-memory OLTP for the tables

Question 14

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

Accepted Answer

Use Stretch Database to migrate cold data to Azure Blob Storage. Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Answer

Partition the table by date and compress each partition

Answer

Use page compression on the table

Answer

Delete old data and store it in Azure Blob Storage as CSV files

Question 15

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

Accepted Answer

Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.. Setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Answer

Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.

Answer

Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.

Answer

Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.