DP-300 Practice Test 22 — 15 Questions

Question 1

Refer to the exhibit. You are configuring an Azure Automation schedule for a runbook that backs up Azure SQL Databases. The runbook should run daily at midnight. However, the runbook runs twice a day. What is the most likely cause?

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

Accepted Answer

Both interval and schedule.interval are defined, causing two triggers per day. The configuration has both 'interval' (1440 minutes = 1 day) and 'schedule.interval' (P1D = 1 day), causing the runbook to trigger twice daily. Option A is wrong because time zone doesn't cause double firing. Option B is wrong because the start time is set. Option D is wrong because the interval unit is minutes, not days.

Answer

The startTime is set to a past date

Answer

The schedule does not specify a time zone

Answer

The intervalUnit is set to Minutes instead of Days

Question 2

You are the database administrator for a large e-commerce company. The company has an Azure SQL Database named SalesDB that stores sensitive customer data including credit card numbers and personal details. The security team has mandated the following requirements:

1. All customer credit card numbers must be encrypted at rest and in transit between the application and the database. The encryption keys must be stored in a hardware security module (HSM) managed by the company.
2. All access to the database must be authenticated using Microsoft Entra ID, and multi-factor authentication (MFA) must be enforced for all administrative users.
3. Any attempts to access the database from unusual geographic locations must be detected and automatically blocked for 24 hours.
4. All schema changes must be audited, and the audit logs must be sent to a central Log Analytics workspace for analysis.

Currently, the database uses SQL authentication, no encryption, and no auditing. You need to implement the required security controls with minimal downtime. What should you do?

Accepted Answer

Enable Always Encrypted for the credit card columns using Azure Key Vault Managed HSM for column master keys. Switch to Microsoft Entra ID authentication and configure Conditional Access policy to require MFA for administrators. Enable Microsoft Defender for SQL with anomaly detection and automatic blocking. Enable Azure SQL Auditing with Log Analytics destination.. It directly addresses all mandated requirements: Always Encrypted with Azure Key Vault Managed HSM ensures credit card numbers are encrypted at rest and in transit with customer-managed HSM keys; switching to Microsoft Entra ID authentication with a Conditional Access policy enforces MFA for administrators; Microsoft Defender for SQL provides anomaly detection that can automatically block access from unusual geographic locations for 24 hours; and Azure SQL Auditing configured to send logs to Log Analytics meets the central audit requirement. This combination provides the required security controls with minimal downtime as Always Encrypted can be enabled online.

Answer

Enable Always Encrypted for the credit card columns with keys stored in Azure Key Vault. Keep SQL authentication but require users to use MFA via a VPN. Enable TDE for at-rest encryption.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault. Configure Azure SQL Auditing to send logs to Log Analytics. Enable Microsoft Defender for SQL.

Answer

Use Dynamic Data Masking to mask credit card numbers. Enable TDE and use Azure SQL Auditing with a storage account. Enable Microsoft Defender for SQL.

Question 3

You are reviewing the encryption protector for an Azure SQL Server as shown in the exhibit. The server hosts multiple databases. You need to ensure that the databases are encrypted using the customer-managed key from Azure Key Vault. However, you find that the databases are not using this key. What is the most likely reason?

Accepted Answer

The databases still have the service-managed key as protector and need to be updated.. The exhibit shows that the server's encryption protector is set to a customer-managed key from Azure Key Vault, but this setting only applies to newly created databases. Existing databases retain their original encryption protector, which is typically the service-managed key. To enforce the customer-managed key on existing databases, you must manually update each database's encryption protector using the ALTER DATABASE ENCRYPTION KEY statement or through the Azure portal. Option C correctly identifies that the databases still have the service-managed key as protector and need to be updated.

Answer

The key version '123456' is incorrect.

Answer

The key vault is in a different subscription and not accessible.

Answer

The 'serverKeyName' property is not set correctly.

Question 4

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

Accepted Answer

The retention period is too short to support point-in-time restore.. The current configuration shows a backup retention period of only 5 days, which is less than the required 7 days for point-in-time restore. With a retention period shorter than 7 days, you cannot restore to a point within the last 7 days. The backup interval of 12 hours is for full backups and does not affect point-in-time restore granularity, as transaction log backups are taken every 5-10 minutes, enabling 1-minute granularity.

Answer

The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.

Answer

The policy must specify a minimum retention of 30 days for point-in-time restore.

Answer

Geo-redundant backup must be disabled for point-in-time restore to work.

Question 5

You are the Azure DBA for a global e-commerce company. The company uses Azure SQL Database for its product catalog. The database is in the Business Critical tier with 16 vCores and 1 TB of storage. During a major sales event, the application experiences severe performance degradation. Analysis reveals that the database is experiencing high write latency on the secondary replicas due to heavy read workload routed to them. The primary replica is not overloaded. The application uses read-only routing to direct queries to readable secondaries. The DBA needs to reduce write latency on the secondaries without affecting the read workload or incurring additional cost. What should the DBA do?

Accepted Answer

Disable read-scale out for non-critical queries and use the primary for read-write. Disabling read-scale out for non-critical queries forces those queries to use the primary replica, reducing the read workload on the secondaries. This directly lowers the write latency on secondaries, as fewer reads compete for resources with the log apply process. The change does not affect the read workload for critical queries that still use read-only routing, and it incurs no additional cost.

Answer

Scale up the service tier to Hyperscale to offload writes

Answer

Add more replicas to distribute the read load

Answer

Remove the secondary replicas and use only the primary

Question 6

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

Accepted Answer

Apply Dynamic Data Masking (DDM) to the credit card column.. Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results for non-privileged users (like db_datareader) while permitting users with elevated permissions (like db_owner) to see the unmasked data. DDM is applied at the column level and does not modify the underlying data; it simply masks the output based on the user's permissions. The db_owner role is exempt from masking by default, meeting the requirement exactly.

Answer

Implement Row-Level Security (RLS) to filter rows based on user role.

Answer

Implement Always Encrypted with deterministic encryption.

Answer

Enable Transparent Data Encryption (TDE).

Question 7

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machine

Answer

Azure SQL Database with elastic query

Question 8

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

Accepted Answer

I/O subsystem bottleneck. The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Answer

Blocking due to lock contention

Answer

CPU pressure from high-complexity queries

Answer

Insufficient memory allocation for the database

Question 9

You have an Azure SQL Database that stores personally identifiable information (PII). You need to classify and label the sensitive columns using Microsoft Purview. Additionally, you want to automatically mask these columns for a specific application user. Which two actions should you take? (Choose two.)

Accepted Answer

Apply Dynamic Data Masking (DDM) rules to mask the classified columns for the application user.. Dynamic Data Masking (DDM) can be applied to classified columns to obfuscate sensitive data for a specific application user without altering the underlying data. DDM rules are defined at the column level and can be configured to mask data for designated users while allowing full access to others, meeting the requirement to automatically mask PII for the application user.

Answer

Enable Always Encrypted on the classified columns.

Answer

Configure Microsoft Sentinel to monitor access to classified columns.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Question 10

You have an Azure SQL Database that uses the SQL Server Agent to run a daily maintenance job. The job fails intermittently with the error 'Login failed for user'. The job uses a SQL Server authentication login. What is the most likely cause and solution?

Accepted Answer

The login password has expired; update the password in the job step.. The intermittent 'Login failed for user' error with a SQL Server authentication login strongly indicates a password expiration issue. Azure SQL Database enforces password expiration policies by default, and if the password for the SQL authentication login used by the job step has expired, the job will fail until the password is updated. This is the most likely cause because the failure is intermittent (occurring after the password expires) and the job uses SQL authentication, which is subject to password policies.

Answer

The database is in a failover group and the secondary is read-only; connect to the primary.

Answer

The job schedule is conflicting with another job; change the schedule.

Answer

The SQL Server Agent is not running; start the agent.

Question 11

You have a SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled and that recommendations are automatically executed. What should you do?

Accepted Answer

Set the server-level automatic_tuning option to INHERIT and set database-level FORCE_LAST_GOOD_PLAN to ON. To enable automatic tuning in Azure SQL Managed Instance, you must set the server-level automatic_tuning option to INHERIT (which allows the server to inherit the default Azure tuning behavior) and then set the database-level FORCE_LAST_GOOD_PLAN to ON. This configuration ensures that the automatic tuning system can both generate recommendations and automatically apply them (specifically the FORCE_LAST_GOOD_PLAN recommendation) without manual intervention.

Answer

Set the database-level automatic tuning option to INHERIT

Answer

Set the database-level query_store_desired_state to ON

Answer

Set the server-level automatic_tuning option to OFF

Question 12

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

Accepted Answer

Elastic Database Jobs. Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Answer

SQL Server Agent

Answer

Azure Automation Runbook

Answer

Azure Logic Apps

Question 13

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

Accepted Answer

Create secondary server in another region, then configure the secondary database, then initiate replication, then verify the replication link.. Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Answer

Create secondary server in another region, then initiate replication, then configure the secondary database, then verify the replication link.

Answer

Initiate replication, then create secondary server in another region, then configure the secondary database, then verify the replication link.

Answer

Create secondary server in another region, then configure the secondary database, then verify the replication link, then initiate replication.

Question 14

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

Accepted Answer

Set the backup retention period to 35 days in the backup settings. Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Answer

Create a backup policy in Azure Backup Vault

Answer

Enable geo-redundant storage (GRS)

Answer

Configure long-term retention (LTR) policy

Question 15

Match each Azure SQL Database monitoring metric to its meaning.

Accepted Answer

DTU percentage: Measures the percentage of DTU limit used by the database.. These metrics are used to monitor resource usage and performance in Azure SQL Database.

Answer

DTU percentage: Measures the percentage of CPU limit used by the database.

Answer

CPU percentage: Measures the percentage of data I/O limit used by the database.

Answer

Data IO percentage: Measures the percentage of DTU limit used by the database.