DP-300 Practice Test 29 — 15 Questions

Question 1

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?

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

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 2

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

Accepted Answer

Implement Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data is encrypted at the column level and that the encryption keys are never revealed to the database engine. By storing the column master key in Azure Key Vault and using client-side encryption, the application manages the encryption and decryption process, so only authorized users with access to the key can read the plaintext data. This meets the requirement that encryption be managed by the application, not the database.

Answer

Use dynamic data masking to obfuscate the sensitive columns for unauthorized users.

Answer

Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

Answer

Create a row-level security policy to restrict access to the sensitive rows.

Question 3

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?

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 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 a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the failover group to use asynchronous commit mode.. Changing the failover group to asynchronous commit mode decouples the primary's transaction commit from the secondary's log write. In synchronous mode, the primary waits for the secondary to confirm log hardening, which causes performance impact when the secondary has high log write latency. Asynchronous commit allows the primary to commit without waiting, thus minimizing performance impact while still maintaining disaster recovery capabilities (the secondary will apply changes eventually, though with potential data loss if a failover occurs before sync). Option A is incorrect because backup storage redundancy (LRS vs GRS) only affects backup storage, not live log write latency for replication. Option B is incorrect because adding another secondary does not reduce latency on the existing secondary; it could even increase overhead. Option D is incorrect because decreasing the secondary's service tier to General Purpose would reduce its I/O capacity and likely worsen log write latency.

Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 6

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 7

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 8

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 9

You are managing an Azure SQL Database that experiences periods of high CPU usage. You need to identify the top resource-consuming queries and their execution plans to optimize performance. You want to use a built-in feature that provides historical query performance data with minimal configuration. What should you use?

Accepted Answer

Query Store. Query Store is a built-in feature that captures query execution history, plans, and runtime statistics. It requires minimal setup and provides the historical data needed to identify top CPU-consuming queries and analyze their execution plans, making it the best choice for performance tuning.

Answer

Dynamic management views (DMVs) such as sys.dm_exec_query_stats

Answer

Azure SQL Analytics

Answer

Automatic tuning

Question 10

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Accepted Answer

Internal Load Balancer. Option A (Internal Load Balancer) is required because in Azure, the availability group listener needs an Azure Internal Load Balancer to route client connections to the current primary replica, since Azure networking does not support the traditional floating IP/ARP method used on-premises. Option C (Windows Server Failover Cluster) is required because Always On Availability Groups are built on top of a WSFC, which provides the cluster quorum, health detection, and failover orchestration for the SQL Server replicas on the Azure VMs. Option B is not required because a cloud witness (an Azure Storage account blob) is the typical witness for a WSFC in Azure, not an Azure Files share. Option D is incorrect because Azure SQL Database is a separate PaaS offering and cannot host the SQL Server instances that form an Always On Availability Group on VMs. Option E is not required because a VPN gateway is only needed for cross-region or hybrid connectivity, not for the core configuration of an availability group within Azure.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 11

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

Accepted Answer

The script uses Out-GridView, which is not supported in Azure Automation.. In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting. Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

Answer

The script must use the -OutputAs parameter to return results.

Answer

The Invoke-SqlCmd cmdlet is not compatible with Azure SQL Database.

Answer

The query syntax is incorrect for Azure SQL Database.

Question 12

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 13

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Answer

Always Encrypted

Answer

Row-Level Security (RLS)

Answer

Transparent Data Encryption (TDE)

Question 14

You manage an Azure SQL Database that runs a reporting workload. Users report that queries are slow only when they filter on a specific customer region, and the slowness began after a large data load. You run Query Store and identify a plan that regressed. You want the database engine to automatically detect and revert to the last known good plan for that query. What should you configure?

Accepted Answer

Enable automatic tuning with the FORCE_LAST_GOOD_PLAN option.. Automatic tuning with FORCE_LAST_GOOD_PLAN is the correct choice because it directly addresses plan regression by automatically reverting to a previously good plan when performance degrades. The other options either collect data without acting, create indexes, or change compatibility levels, none of which provide the automatic regression correction required.

Answer

Enable automatic tuning with the CREATE_INDEX option.

Answer

Set the database compatibility level to the latest version.

Answer

Configure Query Store to use AUTO plan capture mode.

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.