DP-300 Practice Exam 60 — 20 Questions

Question 1

You have an Azure SQL Database that needs to be automatically scaled up during peak hours and scaled down during off-peak. The solution must use native Azure capabilities without custom code. What should you use?

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

Accepted Answer

Create Elastic Database Jobs that run ALTER DATABASE to change the service objective at scheduled times.. Elastic Database Jobs can be used to run ALTER DATABASE statements on a schedule, enabling automated scaling without custom code. Option A is wrong because it requires custom code (PowerShell) and is not a native Azure SQL capability. Option B is wrong because autoscale for elastic pools adjusts per-database DTUs, not the overall database service objective, and does not support scheduled scaling. Option C is wrong because autoscale settings are not available for Azure SQL Database at the server level.

Answer

Use Azure Functions with timer trigger and PowerShell to change the pricing tier.

Answer

Configure autoscale for the elastic pool that contains the database.

Answer

Configure autoscale settings on the Azure SQL Database server.

Question 2

You are setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements:
- The sensitive data should be automatically masked in query results for all developers except the database administrator.
- The masking should be applied without modifying the application code.
- The solution should be easy to manage and not require changes to the data model.

What should you implement?

Accepted Answer

Configure dynamic data masking on the sensitive columns, and add the database administrator to the unmask permission.. Dynamic Data Masking (DDM) can be configured on sensitive columns to automatically mask data in query results without modifying application code or the data model. The database administrator can be added to the unmask permission to see the actual data. Option A is incorrect because creating views would require changes to the data model and application queries. Option C is incorrect because Always Encrypted requires application code changes to handle encryption/decryption. Option D is incorrect because Row-Level Security filters rows based on predicates, not columns, and does not mask data.

Answer

Create views that exclude sensitive columns and grant developers access to the views instead of the base tables.

Answer

Implement Always Encrypted with column encryption, and grant the developers access to the encryption keys.

Answer

Create a row-level security policy that denies access to sensitive rows for developers.

Question 3

You need to design a business continuity plan for an Azure SQL Database that must meet an RPO of 5 seconds and an RTO of 1 hour. The database is used by a global application with users in North America and Europe. Which configuration should you implement?

Accepted Answer

Configure Active Geo-Replication with a readable secondary in a different Azure region and add to a failover group.. Active Geo-Replication with a readable secondary in a different Azure region, added to a failover group, meets the RPO of 5 seconds and RTO of 1 hour. Active Geo-Replication provides asynchronous replication with an RPO of up to 5 seconds, and failover groups enable automatic or manual failover with an RTO typically under 1 hour. The readable secondary supports read-only workloads in Europe, optimizing global application performance.

Answer

Configure auto-failover groups between two Azure regions.

Answer

Deploy the database as zone-redundant within a single region.

Answer

Use geo-restore of automated backups to recover in another region.

Question 4

You run the above Kusto query and see that a database named 'OrdersDB' has high wait times for 'FAILOVER_GROUP_WAIT' every hour. What does this indicate?

Accepted Answer

Replication lag between primary and secondary is high. High wait times for 'FAILOVER_GROUP_WAIT' indicate significant replication lag between the primary and secondary databases, as this wait type measures the time the primary spends waiting for the secondary to apply changes. Option A is incorrect because the query does not measure connection requests. Option B is incorrect because write transactions may still occur; the wait is about replication, not transaction absence. Option C is incorrect because frequent automatic failovers are not indicated by this wait stat; it measures replication lag, not failover frequency.

Answer

The secondary database is experiencing high connection requests

Answer

The primary database is not receiving any write transactions

Answer

The failover group is experiencing frequent automatic failovers

Question 5

Your company runs a mission-critical Azure SQL Database in the East US region. To meet an RPO of 5 seconds and an RTO of 30 minutes in the event of a regional outage, which deployment option should you choose?

Accepted Answer

Failover groups with auto-failover policy. Failover groups with auto-failover policy provide automatic failover with an RPO of 5 seconds (default) and RTO of 30 minutes. Active geo-replication requires manual failover, which does not meet the RTO. Zone-redundant configuration only protects within a region. Backup restore has much higher RPO/RTO.

Answer

Zone-redundant configuration

Answer

Point-in-time restore

Answer

Active geo-replication with manual failover

Question 6

You need to automatically scale an Azure SQL Database based on workload patterns. The solution must use built-in Azure features and minimize manual intervention. Which feature should you configure?

Accepted Answer

Configure autoscale settings for the Azure SQL Database.. Azure SQL Database supports built-in autoscale through the 'Autoscale' feature (serverless compute tier or DTU-based scaling policies), which automatically adjusts resources based on workload patterns without manual intervention. This is the only option that leverages a native Azure feature for dynamic, reactive scaling rather than scheduled or manual actions.

Answer

Use Azure Data Factory to scale the database based on pipeline triggers.

Answer

Create an Azure Automation runbook that scales the database on a schedule.

Answer

Use an elastic pool and manually adjust eDTUs.

Question 7

Your Azure SQL Managed Instance is configured to allow connections only from a specific virtual network. You need to ensure that clients from on-premises can connect using a point-to-site VPN. What additional configuration is required?

Accepted Answer

Configure a point-to-site VPN connection on the virtual network gateway and ensure the gateway subnet is in the same VNet as the managed instance.. A point-to-site VPN connection on the virtual network gateway allows individual on-premises clients to securely connect to the Azure VNet over SSTP or IKEv2. Since the managed instance is deployed inside a subnet of that VNet and its endpoint is restricted to the VNet, the VPN tunnel provides the necessary network path for on-premises clients to reach the instance without exposing it to the public internet.

Answer

Configure a private endpoint for the managed instance.

Answer

Deploy Azure Bastion in the same VNet.

Answer

Add the on-premises public IP address to the managed instance firewall rules.

Question 8

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that during peak hours, the log rate is throttled frequently. You need to reduce log write throttling. What should you do?

Accepted Answer

Increase the service level objective (SLO) to a higher vCore count.. In Azure SQL Database Hyperscale, log write throttling occurs when the log generation rate exceeds the throughput capacity of the current service level objective (SLO). Increasing the vCore count raises the log write throughput limit, directly alleviating throttling. This is the correct approach because Hyperscale log rate limits are tied to the SLO, and higher tiers provide higher log I/O bandwidth.

Answer

Enable accelerated database recovery to reduce log generation.

Answer

Reduce the size of individual transactions to commit more frequently.

Answer

Partition large tables to spread log writes across files.

Question 9

You are the database administrator for an Azure SQL Database used by a financial trading application. The database is in the Business Critical service tier with 16 vCores. The application executes thousands of small, high-frequency transactions per second. Recently, the application's response time has increased, and you observe high PAGELATCH_EX waits in sys.dm_os_waiting_tasks. The database is 500 GB with a single data file (tempdb.mdf) and a single log file (tempdb_log.ldf). TempDB is configured with the default settings. You need to reduce PAGELATCH_EX contention in TempDB. What should you do?

Accepted Answer

Add additional TempDB data files equal to the number of vCores (16).. PAGELATCH_EX contention in TempDB is typically caused by allocation page contention when many concurrent transactions compete for the same system page (e.g., PFS, GAM, SGAM). In Azure SQL Database Business Critical tier, adding multiple TempDB data files equal to the number of vCores (16) reduces contention by distributing allocations across files, each with its own allocation structures. This is a proven best practice for high-concurrency workloads like financial trading applications.

Answer

Move TempDB to Azure Premium Storage for better IO latency.

Answer

Increase the database service tier to 24 vCores.

Answer

Increase the initial size of the TempDB data file to 100 GB.

Question 10

You are troubleshooting a failed automated backup for an Azure SQL Database. The backup policy is configured for geo-redundant storage (RA-GRS). You notice that the last successful backup was 48 hours ago. The database is still online and accessible. What is the most likely cause of the backup failure?

Accepted Answer

The database is experiencing high transaction log generation, exceeding the backup throughput limit.. Azure SQL Database automated backups can fail if the database generates a high volume of transaction logs, causing the backup process to exceed its throughput limit or timeout. This can happen even if the database is online and accessible. Option A (storage account issues) would likely cause persistent failures for all backups, not just a single one. Option C (geo-replication link) is unrelated to backup failure. Option D (paused state) would prevent backups entirely. Therefore, B is the correct answer.

Answer

The backup storage account has been deleted or has incorrect firewall rules.

Answer

The geo-replication link is broken, causing backup failures.

Answer

The database is in a paused state due to serverless compute.

Question 11

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure sensitivity labels using Microsoft Purview Information Protection.. Microsoft Purview Information Protection provides sensitivity labels that can be applied to columns in Azure SQL Database to classify and protect personal data, meeting GDPR requirements. These labels enforce encryption, access restrictions, and visual markings, integrating with Azure SQL's data classification capabilities.

Answer

Implement Always Encrypted for all columns containing personal data.

Answer

Install the Azure Information Protection client on all client machines.

Answer

Enable Microsoft Defender XDR for the database server.

Question 12

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

Accepted Answer

Query Store. Query Store captures a history of query execution plans, runtime statistics, and wait statistics, enabling detailed performance analysis and troubleshooting. It is the correct choice because it is specifically designed to log query-level performance data for Azure SQL Database without requiring external storage or additional configuration.

Answer

Data classification

Answer

Server-level audit

Answer

Diagnostic settings for SQLInsights

Question 13

A company plans to deploy a new application on Azure VMs that requires a highly available SQL Server database with automatic failover and readable secondaries. The database size is 1 TB. Which deployment option meets these requirements with the least administrative overhead?

Accepted Answer

Azure SQL Managed Instance in the Business Critical tier.. Azure SQL Managed Instance in the Business Critical tier provides built-in high availability with automatic failover and readable secondaries via Always On Availability Groups, while requiring minimal administrative overhead. The 1 TB database size is supported in the Business Critical tier (up to 4 TB), and the managed platform handles patching, backups, and failover orchestration automatically, unlike IaaS-based solutions.

Answer

Azure SQL Database in the General Purpose tier.

Answer

SQL Server on Azure VMs with Always On Availability Groups.

Answer

Azure SQL Database in the Hyperscale tier.

Question 14

You have an Azure SQL Database that is configured with geo-replication. You need to optimize read performance for reporting queries that run on the secondary replica. Which three actions should you take? (Choose three.)

Accepted Answer

Enable automatic tuning on the secondary.. Enabling automatic tuning on the secondary replica allows the database to automatically create, drop, and force index recommendations based on the workload running on that replica. This is particularly beneficial for reporting queries, as the secondary can independently optimize its own index strategy without affecting the primary. Automatic tuning on a readable secondary helps maintain query performance by adapting to the read-heavy workload patterns typical of reporting scenarios.

Answer

Configure the secondary as a readable secondary.

Answer

Force query plans on the primary replica to benefit the secondary.

Question 15

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to monitor and review authentication activity for compliance and security analysis. Other features like Advanced Threat Protection, TDE, and Vulnerability Assessment do not capture login event logs.

Answer

Advanced Threat Protection

Answer

Transparent Data Encryption (TDE)

Answer

SQL Vulnerability Assessment

Question 16

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for sensitive patient data (e.g., Social Security numbers) using Always Encrypted. The application connecting to the database is a .NET application running on Azure Virtual Machines. The application should be able to perform parameterized queries on encrypted columns without revealing the plaintext to the database. Which configuration is essential for this setup?

Accepted Answer

Use Always Encrypted with secure enclaves and configure the enclave attestation URL.. The scenario requires the application to perform parameterized queries on encrypted columns without revealing plaintext to the database. Always Encrypted with secure enclaves allows rich computations (e.g., equality, pattern matching) on encrypted data inside a hardware-based enclave, and the enclave attestation URL is essential to verify the enclave's integrity. Without the enclave, Always Encrypted only supports equality comparisons and cannot handle parameterized queries that require operations like LIKE or range checks on encrypted columns.

Answer

Store the column master key in Azure Key Vault and configure the application to retrieve it.

Answer

Implement dynamic data masking on the columns containing Social Security numbers.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Question 17

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

Accepted Answer

Data Migration Assistant (DMA). The Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio with SQL Server migration extension

Answer

SQL Server Management Studio (SSMS)

Question 18

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Answer

Configure Always Encrypted with keys stored in Key Vault.

Answer

Use Azure Storage Service Encryption to encrypt the backup files.

Answer

Enable backup encryption using a certificate stored in the managed instance.

Question 19

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

Accepted Answer

Azure Automation with PowerShell runbooks. Azure Data Factory supports copy activity but not native restore. Azure Automation with PowerShell runbooks can orchestrate the entire workflow. Azure SQL Database elastic jobs are for T-SQL tasks across databases but not for restore operations. Azure Logic Apps can also orchestrate but is less suited for complex scripting.

Answer

Elastic Database Jobs

Answer

Azure Logic Apps

Answer

Azure Data Factory

Question 20

Refer to the exhibit. You are configuring a backup retention policy for an Azure SQL Database via ARM template. You need to ensure that point-in-time restore (PITR) is available for the maximum supported period. What is the issue with the current configuration?

Accepted Answer

The retentionDays value exceeds the maximum supported retention period. The exhibit shows retentionDays set to 40 days, but the maximum supported retention period for point-in-time restore (PITR) in Azure SQL Database is 35 days. Therefore, the configuration is invalid because it exceeds the maximum. Option A is incorrect because the minimum retention is 7 days, so 40 days is above the minimum. Option C is incorrect: diffBackupIntervalInHours of 12 is a supported value (12 or 24). Option D is incorrect because geo-redundant storage is not required for PITR; it is a separate backup option.

Answer

The retentionDays value is set below the minimum requirement

Answer

The diffBackupIntervalInHours value is not a supported value

Answer

The backup policy does not specify a geo-redundant storage option