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

Question 1

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

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

Accepted Answer

Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.. Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Answer

Use Azure Database Migration Service offline migration with automatic cutover.

Answer

Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.

Answer

Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.

Question 2

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

Accepted Answer

Managed Instance. Azure SQL Managed Instance provides the highest level of resource isolation among the listed options. It runs as an isolated instance with its own dedicated set of CPU, memory, and storage resources, and it is deployed into a dedicated virtual network, ensuring network isolation as well. This makes it ideal for scenarios requiring strict tenant separation, such as an e-commerce application handling sensitive customer data. In contrast, a single database in Azure SQL Database shares some underlying resources at the logical server level and does not offer the same degree of network isolation.

Answer

Elastic pool

Answer

Hyperscale

Answer

Single database

Question 3

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

Accepted Answer

Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key. Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE. This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Answer

Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE

Answer

Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio

Answer

Disable TDE on the source database before migration, then enable it after migration

Question 4

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

Accepted Answer

Initiate a planned failover to the secondary region. A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Answer

Enable read scale-out on the primary database

Answer

Initiate a forced failover to the secondary region

Answer

Increase the service tier of the primary database

Question 5

You are deploying an Azure SQL Database for a new application that requires consistent performance and low latency. The database will be accessed by users across multiple Azure regions. Which deployment option should you use?

Accepted Answer

Single database with Active Geo-Replication. Active Geo-Replication for a single database enables continuous data synchronization to readable secondary replicas in different Azure regions, providing low-latency read access for globally distributed users and supporting manual failover for write availability. This meets the requirement for consistent performance and low latency across multiple regions without the orchestration overhead of failover groups.

Answer

Managed Instance with failover groups

Answer

Single database in one region

Answer

Elastic pool in one region

Question 6

You need to create an Azure SQL Database that automatically scales storage up to 4 TB. Which service tier should you select?

Accepted Answer

Hyperscale. Hyperscale service tier automatically scales storage up to 100 TB, and it meets the requirement of automatically scaling up to 4 TB. Business Critical offers a fixed provisioned storage limit (up to 4 TB) but does not auto-scale; it requires manual adjustments. Standard and Basic have lower maximum storage limits (1 TB and 2 GB respectively) and also do not auto-scale.

Answer

Standard

Answer

Basic

Answer

Business Critical

Question 7

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for a new workload?

Accepted Answer

Lift-and-shift migration of on-premises databases. Option B is correct because Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises SQL Server databases, offering near-100% compatibility with the SQL Server engine, including features like CLR, Service Broker, and linked servers, whereas Azure SQL Database requires schema and code changes for many workloads. Option C is correct because cross-database queries (three-part naming across databases on the same instance) are natively supported in Azure SQL Managed Instance, while Azure SQL Database generally requires elastic queries or external tables to query across databases. Option E is correct because SQL Server Agent is fully supported in Azure SQL Managed Instance for scheduling jobs, whereas Azure SQL Database does not include SQL Server Agent and instead relies on Elastic Database Jobs or Azure Automation. Option A is not a valid differentiator because both services can be reached via private endpoints, and static IP addresses are not a defining selection factor between them. Option D is not a valid differentiator because both Azure SQL Database and Azure SQL Managed Instance provide automated backups by default.

Answer

Need for a static IP address

Answer

Both support automated backups

Question 8

Which THREE are features of Azure SQL Database Hyperscale?

Accepted Answer

Storage up to 100 TB. Option C is correct because Azure SQL Database Hyperscale supports storage that can grow up to 100 TB (and beyond with additional configurations), far exceeding the standard 1-8 TB limits of other Azure SQL tiers. Option D is correct because Hyperscale uses snapshots of the underlying page-blob storage layer, enabling near-instantaneous backups regardless of database size, since no data copy is required. Option E is correct because Hyperscale's architecture separates compute from storage, allowing compute resources to be scaled up or down quickly (often within minutes) without moving data. Option A is not correct because zone redundancy is not enabled by default in Hyperscale; it must be explicitly configured, and many Hyperscale deployments use a single primary replica with local redundancy. Option B is not correct because Hyperscale does not offer an unlimited log rate; transaction log throughput is bounded by the provisioned compute and storage subsystem, and log generation rates are still subject to service limits.

Answer

Zone redundant compute by default

Answer

Unlimited log rate

Question 9

You are a database administrator for a large financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases, FinanceDB, is 2.5 TB in size and runs a critical application that requires high availability with automatic failover. The application uses database mail and SQL Agent jobs. During migration, you need to minimize downtime. Which deployment option should you choose?

Accepted Answer

Deploy an Azure SQL Managed Instance in a failover group with a secondary in another region. Azure SQL Managed Instance supports database mail and SQL Agent jobs natively, and deploying it in a failover group with a secondary in another region provides automatic failover and high availability while minimizing downtime during migration. The 2.5 TB size is within Managed Instance limits, and the failover group enables a controlled, continuous-sync replication to the secondary, allowing a swift cutover with minimal data loss.

Answer

Deploy an Azure SQL Database Hyperscale with geo-replication

Answer

Deploy an Azure SQL Managed Instance with zone-redundant configuration

Answer

Deploy a single Azure SQL Database with Active Geo-Replication

Question 10

You are migrating a 10-TB SQL Server database to Azure SQL Database Hyperscale. The source database has multiple filegroups and uses full-text indexing. The migration must be completed with minimal downtime. Which migration method should you use?

Accepted Answer

Use the Azure SQL Migration extension in Azure Data Studio with online migration mode. The Azure SQL Migration extension in Azure Data Studio with online migration mode is the correct choice because it supports minimal-downtime migrations for large databases (10 TB) with complex features like multiple filegroups and full-text indexing. It uses the Data Migration Service (DMS) to continuously replicate changes from the source to the target Azure SQL Database Hyperscale, allowing you to cut over with only a brief pause.

Answer

Set up transactional replication from on-premises to Azure

Answer

Use Azure Data Factory to copy data

Answer

Export a BACPAC file and import it to Azure

Question 11

You are deploying Azure SQL Database for a new application that requires predictable performance and minimal latency. You need to choose a service tier that provides dedicated resources and allows scaling independently for compute and storage. Which service tier should you select?

Accepted Answer

Business Critical. The Business Critical service tier is designed for applications requiring predictable performance and minimal latency, as it provides dedicated resources (CPU and memory) and uses local SSD storage for the database. It also supports independent scaling of compute and storage, allowing you to adjust DTUs or vCores without affecting storage capacity, which aligns with the requirement for independent scaling.

Answer

General Purpose

Answer

Basic

Answer

Hyperscale

Question 12

You are designing a disaster recovery strategy for an Azure SQL Database that uses the Business Critical tier. The primary region is East US, and you need to ensure automatic failover with zero data loss in case of a regional outage. The secondary region must be West US. You also need to minimize latency for read-only workloads in the secondary region. What should you implement?

Accepted Answer

Create a failover group with the primary in East US and a readable secondary in West US.. A failover group with a readable secondary in the Business Critical tier provides automatic failover with zero data loss because it uses synchronous replication. The readable secondary in West US minimizes latency for read-only workloads by allowing direct connections to the secondary replica. This meets all requirements: automatic failover, zero data loss, and low-latency reads in the secondary region.

Answer

Create a failover group with the primary in East US and a non-readable secondary in West US using Basic tier.

Answer

Configure active geo-replication with a readable secondary in West US.

Answer

Configure active geo-replication with a readable secondary in West US using Standard tier.

Question 13

You are planning to deploy Azure SQL Database for a new application. The application requires high availability and the ability to scale out read workloads. You need to choose the appropriate deployment option and feature. Which TWO options should you select? (Choose two.)

Accepted Answer

Configure a failover group with a readable secondary.. Option C is correct because a failover group (auto-failover group) provides high availability with automatic failover across regions and exposes a readable secondary endpoint, enabling read scale-out for the application. Option E is correct because active geo-replication creates readable secondary databases in other regions, which directly supports scaling out read workloads while providing a high-availability/disaster-recovery topology. Option A is incorrect because elastic pools only share resources (eDTUs/vCores) among multiple databases and do not by themselves provide high availability or read scale-out. Option B is incorrect because zone redundancy improves availability within a region but does not provide a readable secondary for scaling out reads. Option D is incorrect because a single database with locally redundant backup storage offers only basic backup redundancy and neither high availability across zones/regions nor read scale-out.

Answer

Use an elastic pool to group databases for resource sharing.

Answer

Enable zone redundancy for the database.

Answer

Deploy a single Azure SQL Database with locally redundant backup storage.

Question 14

You are a database administrator for a healthcare company that uses Azure SQL Database for its patient management system. The database is currently 500 GB and uses the General Purpose tier in the West US region. The application experiences high read traffic during business hours, and you need to improve read performance without increasing costs significantly. You also need to ensure that read queries are isolated from write workloads. The application supports read-only routing using connection strings. You have the following options:

A. Create a secondary replica in the same region using active geo-replication and configure the application to use the secondary for read-only queries.
B. Upgrade the database to the Business Critical tier and use the built-in readable secondary.
C. Implement read scale-out by adding a readable secondary replica in the same region using the built-in read scale-out feature of the General Purpose tier.
D. Create an elastic pool with multiple databases and distribute read queries across them.

Which option should you choose?

Accepted Answer

Implement read scale-out in General Purpose tier.. Active geo-replication allows you to create a readable secondary replica in the same region, which can be used to offload read-only queries from the primary. The application can use connection strings with ApplicationIntent=ReadOnly to automatically route read queries to the secondary, isolating them from write workloads. This improves read performance without upgrading to a higher-cost tier, as the General Purpose tier does not natively support readable secondaries within the same region.

Answer

Create a secondary replica using active geo-replication and configure read-only routing.

Answer

Upgrade to Business Critical tier and use its readable secondary.

Answer

Create an elastic pool and distribute read queries.

Question 15

You are planning the storage configuration for a new Azure SQL Managed Instance. The instance will host a database that requires low-latency, high-throughput transaction log writes. You need to choose the appropriate storage type for the transaction log files. What should you do?

Accepted Answer

Place the transaction log files on a Premium SSD managed disk with no caching.. Transaction log files in Azure SQL Managed Instance are automatically placed on Premium SSD storage with caching disabled. This ensures low-latency, high-throughput writes, which are critical for transaction log performance. Premium SSD provides the necessary IOPS and throughput, while no caching avoids unnecessary overhead for write-heavy operations.

Answer

Place the transaction log files on a Ultra Disk with read-only caching enabled.

Answer

Place the transaction log files on a Standard HDD managed disk with read/write caching enabled.

Answer

Place the transaction log files on a Premium SSD managed disk with read-only caching enabled.