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

Question 1

You are deploying an Azure SQL Database for a new application. The database must be encrypted at rest using Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault. You need to ensure that the key is automatically rotated every 90 days and that the database remains accessible if the key is rotated. What should you configure?

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

Accepted Answer

Create an Azure Key Vault, generate a key, and configure the SQL server's TDE protector to use that key. Then set up a key rotation policy in Key Vault.. Configuring TDE with a customer-managed key in Azure Key Vault and setting a rotation policy in Key Vault enables automatic key rotation. Azure SQL Database automatically uses the latest key version when the key is rotated, provided the server is configured to use the latest version. This meets the 90-day rotation and continuous access requirements.

Answer

Use Always Encrypted with a column master key stored in Azure Key Vault and configure automatic rotation.

Answer

Enable TDE with a service-managed key and configure automatic key rotation in the Azure portal.

Answer

Store the key in Azure Key Vault and manually update the TDE protector every 90 days using PowerShell scripts.

Question 2

You are a database administrator for a financial services company that uses Azure SQL Database. The company must ensure that all database backups are encrypted with a customer-managed key stored in Azure Key Vault. You need to configure the database to meet this requirement. What should you do first?

Accepted Answer

Create an Azure Key Vault and generate or import a key.. To use a customer-managed key for TDE, the key must exist in Azure Key Vault and the Azure SQL logical server must be granted permissions to access it. Creating the key vault and key is the necessary first step before enabling TDE with that key. Other options are security features but do not directly enable customer-managed key encryption.

Answer

Enable transparent data encryption (TDE) with a service-managed key.

Answer

Configure Azure SQL Database auditing to log all access to the database.

Answer

Enable Advanced Threat Protection on the Azure SQL server.

Question 3

You are deploying an Azure SQL Database for a new application. The database must be encrypted at rest using Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault. You need to ensure that the key is automatically rotated every 90 days and that the database remains accessible if the key is rotated. What should you configure?

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

Accepted Answer

Serverless. Serverless compute automatically scales vCores up and down based on workload demand and pauses the database during inactivity, billing only for storage while paused. This directly addresses the need for automatic scaling and cost minimization during idle periods. Other service tiers require manual scaling and do not auto-pause, so they cannot meet the stated requirements.

Answer

General Purpose

Answer

Business Critical

Answer

Hyperscale

Question 4

You are planning to deploy a new Azure SQL Database. The database will store sensitive financial data and must be encrypted at rest using a key that your organization manages and rotates independently. You need to implement this encryption with minimal administrative overhead. What should you do?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys stored in Azure Key Vault.. Transparent Data Encryption with customer-managed keys in Azure Key Vault provides encryption at rest while allowing your organization to manage and rotate the encryption key. This meets the requirement for independent key management with minimal overhead, as Azure SQL Database handles the encryption and decryption transparently.

Answer

Configure Always Encrypted with column encryption keys stored in Azure Key Vault.

Answer

Enable Azure Disk Encryption on the underlying virtual machines hosting the database.

Answer

Enable Transparent Data Encryption (TDE) with service-managed keys.

Question 5

You are deploying an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server database. The application requires the ability to restore a database from a backup taken on an on-premises SQL Server 2019 instance. The backup file is stored in an Azure Storage account. You need to restore the database to the managed instance. What should you do first?

Accepted Answer

Use the RESTORE DATABASE statement with the FROM URL clause pointing to the backup file in Azure Storage.. Azure SQL Managed Instance supports the RESTORE DATABASE statement with the FROM URL clause, allowing you to restore a native SQL Server backup stored in Azure Blob Storage. This is the correct first step to restore the on-premises backup. Other tools like SqlPackage or DMA do not support native backup restoration.

Answer

Import the backup file into an Azure SQL Database using the SqlPackage utility.

Answer

Configure Azure Backup to back up the on-premises SQL Server and restore directly to the managed instance.

Answer

Use the Data Migration Assistant to migrate the database schema and data to the managed instance.

Question 6

You are deploying a new Azure SQL Database for a line-of-business application. The application's usage pattern is unpredictable, with long idle periods overnight and short bursts of heavy activity during business hours. Cost optimization is a priority, and the database can tolerate a brief reconnection delay when scaling. You need to select a purchasing model and service tier that minimizes cost while automatically adjusting compute resources. What should you do?

Accepted Answer

Deploy the database by using the vCore purchasing model with the General Purpose service tier and configure the serverless compute tier.. The serverless compute tier in the vCore purchasing model automatically scales compute resources based on workload activity and can pause the database during idle times, charging only for storage. This matches the need to minimize cost for unpredictable usage and tolerates the brief reconnection delay upon resuming. Other tiers either lack auto-scaling or are not cost-effective for this pattern.

Answer

Deploy the database by using the DTU purchasing model with the Standard service tier and configure elastic pool auto-scaling.

Answer

Deploy the database by using the vCore purchasing model with the Hyperscale service tier and enable read-scale replicas.

Answer

Deploy the database by using the vCore purchasing model with the Business Critical service tier and configure auto-scaling.

Question 7

You need to deploy a new Azure SQL Database that will store sensitive financial data. The database must use customer-managed keys for encryption at rest, and the keys must be stored in Azure Key Vault. You want to ensure that the keys are automatically rotated and that the database remains available during rotation. What should you configure?

Accepted Answer

Enable transparent data encryption (TDE) with a customer-managed key stored in Azure Key Vault and configure automatic key rotation.. Transparent data encryption (TDE) with a customer-managed key in Azure Key Vault is the correct solution for encryption at rest with customer-controlled keys. Azure SQL Database supports automatic key rotation, which seamlessly updates the database to use the latest key version. Always Encrypted is for client-side encryption, Azure Disk Encryption is for IaaS VMs, and dynamic data masking is for masking, not encryption.

Answer

Enable Always Encrypted with column master keys stored in Azure Key Vault and configure automatic key rotation.

Answer

Use dynamic data masking with a customer-managed key to encrypt sensitive columns.

Answer

Configure Azure Disk Encryption on the underlying virtual machines hosting the Azure SQL Database.

Question 8

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that you manage in Azure Key Vault. You also need to ensure that the key is automatically rotated. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.. TDE with customer-managed keys in Azure Key Vault allows you to control the encryption key and manage its lifecycle. You can configure automatic key rotation by setting a rotation policy in Key Vault and enabling the TDE protector to use the latest key version. This satisfies both the encryption at rest and key management requirements.

Answer

Enable Transparent Data Encryption (TDE) with service-managed keys.

Answer

Enable Dynamic Data Masking with Azure Key Vault integration.

Answer

Enable Always Encrypted with column master keys in Azure Key Vault.

Question 9

You are planning the deployment of a new Azure SQL Database for a line-of-business application. The application's workload is not yet known, and you must keep the monthly cost as low as possible while still being able to scale compute resources up or down without redeploying the database. You also need to ensure that storage is billed based on the actual data and log used rather than a pre-provisioned maximum. Which purchasing model and service tier should you choose?

Accepted Answer

Serverless compute tier with General Purpose service tier. The serverless compute tier in the General Purpose service tier is designed for unpredictable workloads and cost optimisation. It automatically scales compute based on demand and can auto-pause during inactive periods, billing only for compute used. Storage is billed based on actual data and log usage. Provisioned tiers reserve compute capacity and bill continuously, making them less suitable when the workload is unknown and cost must be minimised.

Answer

Provisioned compute tier with General Purpose service tier

Answer

Hyperscale service tier with provisioned compute

Answer

Provisioned compute tier with Business Critical service tier

Question 10

You manage an Azure SQL Database that supports a critical web application. The database is currently configured with the General Purpose service tier and locally redundant backup storage. The compliance team requires that all backups be stored in a paired Azure region to ensure availability during a regional outage. You need to change the backup storage redundancy without affecting the database availability. What should you do?

Accepted Answer

Modify the database's backup storage redundancy setting to geo-redundant backup storage.. Changing the backup storage redundancy setting to geo-redundant backup storage replicates backups to a paired Azure region. This operation is performed at the database or server level and does not require downtime. It directly fulfills the compliance requirement without adding unnecessary replication or migration steps.

Answer

Configure active geo-replication to a secondary region and fail over the database.

Answer

Enable long-term retention (LTR) policies to copy backups to a separate storage account in the paired region.

Answer

Create a new database in the paired region and migrate all data by using transactional replication.

Question 11

You manage an Azure SQL Database that is used by a reporting application. The application runs large analytical queries during business hours and you need to isolate these read-only workloads from the primary database to avoid performance impact. You want to use the built-in read-only replica without changing the application connection string to a different server. What should you configure?

Accepted Answer

Enable read scale-out on the database and set ApplicationIntent=ReadOnly in the connection string.. Read scale-out in Azure SQL Database provides a built-in read-only replica that can be accessed by setting ApplicationIntent=ReadOnly in the connection string. The connection continues to use the same logical server name, so the application does not need to be reconfigured to point to a different server. This allows analytical queries to run on a separate replica, reducing contention on the primary database.

Answer

Configure an elastic query to distribute the analytical queries across shards.

Answer

Create a geo-secondary database and point the reporting application to the secondary server.

Answer

Create a readable secondary by enabling Active Geo-Replication and use the same connection string.

Question 12

You are planning to migrate an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration supports these features with minimal changes. What should you do first?

Accepted Answer

Run Data Migration Assistant (DMA) to assess the database for compatibility issues with Azure SQL Managed Instance.. Data Migration Assistant (DMA) is the recommended tool to assess on-premises SQL Server databases for compatibility with Azure SQL Managed Instance. It identifies unsupported features such as certain cross-database queries and SQL Server Agent job steps, allowing you to plan remediation. Azure DMS is for migration, transactional replication is an alternative but not an assessment, and direct restore does not evaluate compatibility.

Answer

Deploy an Azure SQL Managed Instance and restore a backup of the on-premises database directly.

Answer

Use the Azure Database Migration Service (DMS) to perform an online migration without prior assessment.

Answer

Configure transactional replication from the on-premises SQL Server to Azure SQL Managed Instance.

Question 13

You are configuring an Azure SQL Database to support a mission-critical application. The database is in the Business Critical service tier and uses zone redundancy. You need to ensure that the database remains available even if an entire Azure region becomes unavailable. The solution must minimize data loss and provide automatic failover. What should you do?

Accepted Answer

Configure an auto-failover group with a secondary database in a paired Azure region.. Auto-failover groups provide automatic failover to a secondary region, minimizing downtime and data loss. They support multiple databases and provide listener endpoints for seamless connection redirection. This is the appropriate solution for regional disaster recovery with automatic failover.

Answer

Enable active geo-replication for the database to a secondary region.

Answer

Enable geo-backup and configure a long-term retention policy.

Answer

Configure a failover group with a secondary database in the same region but in a different availability zone.

Question 14

You are planning the deployment of an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server 2019 workload. The workload requires the ability to run cross-database queries, use SQL Server Agent, and support Service Broker. You need to choose a service tier that provides the highest availability and lowest latency for I/O-intensive operations. The budget allows for premium storage performance. Which service tier should you select?

Accepted Answer

Business Critical. The Business Critical service tier in Azure SQL Managed Instance is designed for high-performance, low-latency workloads. It uses locally attached SSD storage and maintains a secondary replica for failover, ensuring high availability. It supports all necessary SQL Server features like cross-database queries, SQL Server Agent, and Service Broker, making it the correct choice for this scenario.

Answer

Hyperscale

Answer

General Purpose

Answer

Premium

Question 15

You are configuring an Azure SQL Database to meet a compliance requirement that mandates encryption of data at rest with customer-managed keys and the ability to audit all access to the database. You need to implement the necessary features. Which two actions should you perform? (Choose two.)

Accepted Answer

Enable transparent data encryption (TDE) with a customer-managed key stored in Azure Key Vault.. To meet encryption at rest with customer-managed keys, enable TDE with a customer-managed key in Azure Key Vault. To audit all access, configure SQL auditing to a storage account or Log Analytics. Advanced Threat Protection is for threat detection, dynamic data masking is for masking, and Always Encrypted is for column-level encryption, not full database encryption at rest.

Answer

Implement dynamic data masking on all sensitive columns.

Answer

Configure Always Encrypted with column master keys in Azure Key Vault.

Answer

Enable Advanced Threat Protection on the Azure SQL Database.