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

Question 1

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?

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

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 2

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 3

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 4

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. Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises databases, offering near 100% compatibility with SQL Server features like instance-level configurations, linked servers, and SQL Server Agent. Azure SQL Database, as a Platform-as-a-Service (PaaS) offering, lacks these instance-scoped capabilities, making Managed Instance the preferred choice for migrating existing databases with minimal changes.

Answer

Need for a static IP address

Answer

Both support automated backups

Question 5

Which TWO are valid ways to secure access to an Azure SQL Database?

Accepted Answer

Configure a Virtual Network service endpoint. A Virtual Network service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection. This allows you to secure access by limiting connectivity to only traffic originating from a specific subnet in your VNet, effectively blocking public internet access while using the Azure backbone network.

Answer

Use IPsec VPN from on-premises

Answer

Join the database to an on-premises Active Directory domain

Answer

Disable all firewall rules and rely only on authentication

Question 6

Which THREE are features of Azure SQL Database Hyperscale?

Accepted Answer

Storage up to 100 TB. Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Answer

Zone redundant compute by default

Answer

Unlimited log rate

Question 7

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 8

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

Accepted Answer

Azure SQL Database Hyperscale tier. The Hyperscale tier of Azure SQL Database is designed for workloads up to 100 TB, decouples compute from storage (allowing independent scaling), and uses a log-based architecture with page servers to enable fast restores (typically within minutes, regardless of database size). This directly matches the requirements for 50 TB data, high-frequency inserts, independent compute/storage scaling, and rapid restore times.

Answer

Azure SQL Database Business Critical tier

Answer

Azure SQL Database Standard tier

Answer

Azure SQL Managed Instance Business Critical

Question 9

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

Accepted Answer

Enable Transparent Data Encryption (TDE) using a customer-managed key from Azure Key Vault. Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault is the correct choice because it encrypts the database at rest, including data files and log files, using a key that you control and manage in Azure Key Vault. This meets the requirement for encrypting protected health information (PHI) at rest with a customer-managed key, and TDE automatically ensures that backups are encrypted with the same database encryption key (DEK), which is protected by the customer-managed key.

Answer

Use column-level encryption with a certificate

Answer

Enable Transparent Data Encryption with a service-managed key

Answer

Enable Always Encrypted with keys stored in Azure Key Vault

Question 10

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

Accepted Answer

Use an elastic pool and add databases as needed. Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Answer

Deploy each tenant's database as a single database with reserved capacity

Answer

Use a single Hyperscale database with schema per tenant

Answer

Use a single Azure SQL Managed Instance with multiple databases

Question 11

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 12

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 13

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

Accepted Answer

Migrate the database to Azure SQL Managed Instance and recreate the job using SQL Server Agent.. Azure SQL Managed Instance supports SQL Server Agent, including jobs that run PowerShell scripts via CmdExec or PowerShell job steps. By migrating the database and recreating the job with the same script, you preserve the existing automation with minimal changes. This is the only option that keeps the job execution environment identical to the on-premises setup.

Answer

Migrate the database to Azure SQL Managed Instance and configure an Azure Automation runbook to run the script.

Answer

Migrate the database to Azure SQL Managed Instance and use Elastic Jobs to run the PowerShell script.

Answer

Migrate the database to Azure SQL Database and use Elastic Jobs to run the PowerShell script.

Question 14

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 15

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.. A failover group with a readable secondary provides both high availability and read-scale capabilities. The readable secondary allows read-only workloads to be offloaded, while automatic failover ensures business continuity during an outage.

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.