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

Question 1

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

Accepted Answer

Create an Azure Key Vault and a key.. Option A is correct because implementing TDE with customer-managed keys (BYOK) requires an Azure Key Vault (or Managed HSM) to hold the asymmetric RSA key that protects the database encryption key; the vault must be created and the key generated before it can be referenced. Option B is correct because the key must be assigned to the Azure SQL logical server (via the server's TDE protector, e.g., Set-AzSqlServerTransparentDataEncryptionProtector or the portal's Transparent data encryption blade) and TDE then enabled so the server uses that customer-managed key as the protector for its databases. Option C is not required because backup encryption in Azure SQL is automatic and tied to TDE; there is no separate 'Encrypted' backup encryption level setting to configure. Option D is not required because server certificates are a SQL Server on-premises/IaaS mechanism, not how Azure SQL Database BYOK is implemented. Option E is not required because column encryption keys belong to Always Encrypted, a different feature from TDE.

Answer

Set the backup encryption level to 'Encrypted'.

Answer

Create a server certificate in the database.

Answer

Configure column encryption keys in the database.

Question 2

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

Accepted Answer

Always Encrypted with deterministic encryption. Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Answer

Always Encrypted with randomized encryption

Answer

Transparent Data Encryption

Answer

Dynamic Data Masking

Question 3

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Accepted Answer

Create contained database users mapped to Microsoft Entra ID identities. To enable Microsoft Entra ID authentication for an Azure SQL Database, you must first assign a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B). After the admin is assigned, you create contained database users mapped to Microsoft Entra ID identities in the database (Option A) so those identities can authenticate. Setting the database's authentication mode to 'Microsoft Entra ID Only Authentication' (Option C) is optional and enforces that only Microsoft Entra ID identities can connect; it is not required to enable Microsoft Entra ID authentication. Azure SQL Managed Instance (Option D) and registering an application in Microsoft Entra ID (Option E) are not required.

Answer

Set the database's authentication mode to 'Azure AD Only Authentication'

Answer

Deploy the database in Azure SQL Managed Instance

Answer

Register the application in Microsoft Entra ID

Question 4

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

Accepted Answer

Configure a Virtual Network service endpoint. Option B is correct because a Virtual Network service endpoint extends the Azure VNet identity to Azure SQL Database, allowing you to restrict the logical SQL server's firewall so that only subnets in that VNet can reach the database over the Azure backbone, removing public internet exposure. Option C is correct because a Private Endpoint creates a private IP address for the Azure SQL Database inside your VNet via Azure Private Link, so traffic stays on the Microsoft private network and the public endpoint can be disabled entirely. Option A is not valid because IPsec VPN secures connectivity to a VNet or on-premises network but does not by itself control or secure access to Azure SQL Database's firewall/endpoint. Option D is not valid because Azure SQL Database cannot be domain-joined to an on-premises Active Directory; it supports Microsoft Entra ID authentication (and AD DS via Microsoft Entra Domain Services), not a direct domain join. Option E is not valid because disabling all firewall rules would expose the database publicly and relying only on authentication does not secure network access, which is the point of the question.

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 5

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 6

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 7

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 8

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 9

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 10

You are planning to deploy a new Azure SQL Database for an internal HR application. The application is used only during business hours on weekdays and can tolerate a brief outage if the database needs to be scaled. To minimize cost, you need the database to automatically scale compute resources based on workload demand and allow the database to be paused when not in use. Which purchasing model and service tier should you choose?

Accepted Answer

vCore-based purchasing model with the General Purpose service tier and serverless compute. The vCore-based General Purpose tier with serverless compute is designed for intermittent, unpredictable workloads. It automatically scales compute within a configured range and pauses the database when inactive, reducing cost. The other options use provisioned compute or DTU-based models that do not support auto-scaling or auto-pausing, so they cannot meet the cost-saving requirements.

Answer

DTU-based purchasing model with the Standard service tier

Answer

vCore-based purchasing model with the Business Critical service tier and provisioned compute

Answer

DTU-based purchasing model with the Basic service tier

Question 11

You are deploying an Azure SQL Database for a new line-of-business application. The database must remain fully available during planned maintenance windows and provide a secondary copy in a different Azure region for disaster recovery. You need to configure the deployment to meet these requirements with minimal administrative effort. What should you implement?

Accepted Answer

Configure an auto-failover group that includes the primary Azure SQL Database and a secondary database in a paired region.. Auto-failover groups are designed to provide disaster recovery and high availability across regions with minimal administrative overhead. They include a read-write listener and a read-only listener, enabling applications to reconnect automatically after failover. Planned failover can be initiated for maintenance, keeping the database available. Active geo-replication lacks automatic failover and listener endpoints, zone redundancy only covers a single region, and Business Critical tier does not provide cross-region replication.

Answer

Enable active geo-replication for the Azure SQL Database and configure the application to connect to the secondary database manually during failover.

Answer

Create a zone-redundant Azure SQL Database and rely on the built-in high availability within the primary region.

Answer

Deploy the Azure SQL Database with the Business Critical service tier and configure long-term retention backups to a different region.

Question 12

You are a database administrator for a company that runs a SaaS application on Azure SQL Database. The application's workload is unpredictable, with rapid bursts of activity that last only a few minutes. You need to ensure that the database can handle these bursts without manual intervention, while minimizing cost during idle periods. What should you do?

Accepted Answer

Enable the serverless compute tier for the database.. The serverless compute tier is specifically designed for single databases with unpredictable or intermittent usage. It automatically scales compute based on demand and pauses during inactivity, which directly addresses the need to handle bursts without manual intervention while minimizing cost. Other options either require manual scaling or are optimized for different scenarios such as large databases or read offloading.

Answer

Configure the database to use the Hyperscale service tier with a fixed number of vCores.

Answer

Create a read scale-out replica and route all write operations to the primary replica.

Answer

Implement elastic database pool with multiple databases sharing resources.

Question 13

You are deploying an Azure SQL Database for a new line-of-business application. The application's usage pattern is unpredictable, with long idle periods and occasional bursts of heavy read/write activity. You need to minimize compute cost while ensuring the database automatically scales compute resources based on workload demand. The database must remain online during scaling operations. What should you do?

Accepted Answer

Configure the database with the General Purpose service tier and the Serverless compute tier, setting an appropriate auto-pause delay.. The Serverless compute tier in Azure SQL Database is designed for intermittent, unpredictable workloads. It automatically scales compute based on demand and can pause during inactivity, reducing cost. Configuring an appropriate auto-pause delay ensures the database pauses when idle and resumes on the next connection, keeping the database available during scaling without manual intervention.

Answer

Configure the database with the Basic service tier and set a maximum database size of 2 GB.

Answer

Configure the database with the Hyperscale service tier and enable read-scale out.

Answer

Configure the database with the Business Critical service tier and enable zone redundancy.

Question 14

You are deploying a new Azure SQL Database for an internal HR application. The database will store employee records and must be encrypted at rest using a key that your organization rotates every 90 days. The key must be stored in Azure Key Vault and must not be accessible to Microsoft. You need to configure Transparent Data Encryption (TDE) to meet these requirements. What should you do first?

Accepted Answer

Create an Azure Key Vault, generate a key, and grant the Azure SQL logical server's managed identity access to the key.. The requirement is to use a customer-managed key stored in Azure Key Vault for TDE, ensuring Microsoft cannot access the key. The first step is to create the Key Vault and key, then grant the Azure SQL logical server's managed identity access to that key. This enables the server to use the key for TDE. Only after this trust is established can you configure the database to use the key. Other options either use service-managed keys or address unrelated security aspects.

Answer

Enable TDE with a service-managed key on the Azure SQL logical server.

Answer

Create a database master key (DMK) in the master database and encrypt it with a password.

Answer

Configure a firewall rule to allow the application to connect to the database.

Question 15

You need to deploy an Azure SQL Database that automatically scales compute resources based on workload demand, with minimal administrative overhead. The database should scale between a minimum and maximum number of vCores. Which purchasing model and service tier should you use?

Accepted Answer

vCore-based purchasing model with the Serverless compute tier.. The vCore-based Serverless compute tier is designed for automatic scaling and minimal administration. It scales compute based on workload and can pause during inactivity, making it ideal for applications with unpredictable or intermittent usage. It allows setting a minimum and maximum vCore range, providing control over cost and performance.

Answer

DTU-based purchasing model with the Basic service tier.

Answer

vCore-based purchasing model with the Provisioned compute tier.

Answer

DTU-based purchasing model with the Standard service tier.