DP-300 Practice Test 50 — 15 Questions

Question 1

You are a database administrator for a healthcare company that uses Azure SQL Database with Hyperscale tier. The database contains patient records and is critical for operations. You need to automate the process of refreshing the staging database from the production database every night. The refresh process must occur during a maintenance window from 2:00 AM to 4:00 AM. The solution must use point-in-time restore to ensure consistency and must minimize the storage costs. Additionally, the automation must notify the operations team if the refresh fails. What should you do?

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

Accepted Answer

Create an Azure Automation runbook that performs a point-in-time restore of the production database to a new database, then renames the databases to swap staging. Schedule the runbook during the maintenance window and configure alerts for failure.. Azure Automation runbooks can schedule a point-in-time restore of the production database to a new database, then rename the databases to swap them, making the restored database the new staging database. This leverages the Hyperscale tier's fast restore and minimizes storage costs by avoiding multiple copies. Alerts can be configured to notify the operations team on failure. Option A is incorrect because Elastic Database Jobs cannot perform restore operations; they are for running T-SQL scripts across databases, not restoring from backups. Option C is incorrect because Azure Data Factory is an ETL tool and cannot perform point-in-time restore of a database. Option D is incorrect because export/import via BACPAC is slower, more expensive, and does not guarantee point-in-time consistency.

Answer

Use Elastic Database Jobs to run a T-SQL script that uses RESTORE DATABASE from a backup file.

Answer

Use Azure Data Factory to copy data from production to staging using a copy activity.

Answer

Use Azure SQL Database export to BACPAC from production and import to staging using Azure Automation.

Question 2

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

Accepted Answer

Auditing will fail because the storage account access key is null.. The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Answer

Auditing will fall back to Log Analytics workspace.

Answer

Auditing will work because managed identity is used.

Answer

Auditing will write to the storage account using the system-assigned managed identity.

Question 3

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

Accepted Answer

Up to 100 TB of database storage.. The Hyperscale service tier of Azure SQL Database supports up to 100 TB of database storage, which is significantly larger than the 4 TB limit of the General Purpose tier and the 4 TB limit of the Business Critical tier. This massive storage capacity is achieved through a distributed architecture where data is stored in page servers with a shared disk model, allowing near-instantaneous scaling of storage without downtime.

Answer

Built-in in-memory OLTP support.

Answer

Zone-redundant configuration by default.

Answer

Zero data loss in all scenarios.

Question 4

Your company uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

Accepted Answer

Microsoft Defender for Cloud (formerly Azure Security Center) with Advanced Threat Protection for Azure SQL Database. Microsoft Defender for Cloud with Advanced Threat Protection for Azure SQL Database is the correct choice because it specifically detects anomalous activities indicating SQL injection attempts, such as unusual SQL queries or patterns, and triggers security alerts. Azure SQL Auditing only logs database events for compliance and forensic analysis, not real-time threat detection. SQL Vulnerability Assessment identifies configuration weaknesses but does not monitor for active attacks. Azure Policy enforces compliance rules but lacks intrusion detection capabilities.

Answer

Azure SQL Auditing

Answer

SQL Vulnerability Assessment

Answer

Azure Policy

Question 5

Your organization uses Azure SQL Database with elastic pools. One of the databases in the pool, 'OrdersDB', consistently uses more eDTUs than other databases, causing performance degradation for other databases. You need to isolate the resource usage of 'OrdersDB' to prevent it from affecting others. The solution should minimize cost and administrative overhead. What should you do?

Accepted Answer

Move 'OrdersDB' to a higher service tier, such as a standalone database with a higher DTU limit.. Moving 'OrdersDB' to a standalone database with a higher DTU limit isolates its resource usage, preventing it from affecting other databases in the elastic pool. This approach minimizes administrative overhead as it's a straightforward configuration change. Option A is incorrect because purchasing reserved capacity is a billing benefit, not a resource isolation mechanism. Option B is incorrect because Query Store can identify high-usage queries but cannot limit resource consumption. Option D is incorrect because increasing the pool's eDTU limit would increase costs, and the high-usage database could still consume resources and affect others within the pool.

Answer

Purchase reserved capacity for the pool to reduce costs.

Answer

Use Query Store to identify and kill the high-usage queries.

Answer

Increase the eDTU limit of the elastic pool to accommodate the high usage.

Question 6

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

Accepted Answer

Create an Azure Monitor metric alert on the 'dtu_consumption_percent' metric for the managed instance.. Azure Monitor metric alerts on the 'dtu_consumption_percent' metric provide a cost-effective, proactive monitoring solution for Azure SQL Managed Instance. Metric alerts are charged per rule, and there is no additional ingestion cost, making them minimal cost. Option B is incorrect because SQL Server Agent jobs require a SQL Server Agent service and are not designed for real-time metric monitoring; they also incur overhead. Option C is incorrect because Elastic Database Jobs are for executing T-SQL across databases, not for metric-based alerting. Option D is incorrect because streaming diagnostic logs to a Log Analytics workspace and creating a log alert rule incurs data ingestion costs, which is not minimal cost.

Answer

Create a SQL Server Agent job that checks sys.dm_db_resource_stats every 5 minutes and sends an email.

Answer

Use Elastic Database Jobs to run a query periodically and send alerts.

Answer

Stream diagnostic logs to a Log Analytics workspace and create a log alert rule.

Question 7

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

Accepted Answer

Step 1: Create a secondary Azure SQL Database server; Step 2: Create a failover group; Step 3: Add databases to the failover group; Step 4: Configure the failover policy; Step 5: Test failover.. Failover groups require a secondary server, then creating the group, adding databases, configuring policy, and testing.

Answer

Step 2: Create a failover group; Step 1: Create a secondary Azure SQL Database server; Step 3: Add databases to the failover group; Step 4: Configure the failover policy; Step 5: Test failover.

Answer

Step 1: Create a secondary Azure SQL Database server; Step 2: Create a failover group; Step 4: Configure the failover policy; Step 3: Add databases to the failover group; Step 5: Test failover.

Answer

Step 1: Create a secondary Azure SQL Database server; Step 3: Add databases to the failover group; Step 2: Create a failover group; Step 4: Configure the failover policy; Step 5: Test failover.

Question 8

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

Accepted Answer

Use delayed durability for transactions. Options D and E are correct. Option D: Using delayed durability (DELAYED_DURABILITY = ON for transactions) reduces the number of log flush operations, decreasing log writes. Option E: Increasing batch size in INSERT operations reduces the number of log records per row, thereby reducing total log writes. Option A is incorrect because changing the recovery model to SIMPLE is not supported in Azure SQL Database, and even if it were, it would not reduce log writes for heavy write workloads; it only affects transaction log management. Option B is incorrect because minimal logging for bulk operations is not fully supported in Azure SQL Database (only available for specific operations like BULK INSERT against a heap with certain conditions), and it is not a general action to reduce log writes for a heavy write workload. Option C is incorrect because rebuilding indexes during peak hours actually increases log writes due to index rebuild operations.

Answer

Change the database recovery model to SIMPLE

Answer

Use minimal logging for bulk operations

Answer

Rebuild indexes during peak hours

Question 9

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Accepted Answer

Configure active geo-replication. Active geo-replication (Option C) creates a readable secondary database in a different Azure region, supporting cross-region disaster recovery. When combined with an auto-failover group, it provides automatic failover with an RPO of 5 seconds and an RTO of 1 hour, meeting the requirements for a General Purpose tier database.

Answer

Change the service tier to Business Critical

Answer

Enable zone redundancy on the primary database

Answer

Create an auto-failover group

Question 10

You need to audit all 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 failed login attempts, and writes them to an audit log in an Azure Storage account, Log Analytics workspace, or Event Hubs. This allows you to review and analyze authentication failures for security and compliance purposes. The audit logs capture the exact timestamp, source IP address, and the specific error message for each failed login, such as 'Login failed for user'.

Answer

Azure SQL Advanced Threat Protection

Answer

Azure SQL Database Vulnerability Assessment

Answer

Dynamic Data Masking

Question 11

Your company uses Azure SQL Managed Instance for a critical application. You need to meet a Recovery Point Objective (RPO) of 15 seconds and a Recovery Time Objective (RTO) of 30 seconds for planned maintenance. You also need to protect against a regional failure with an RPO of 5 minutes and an RTO of 1 hour. What combination of features should you use?

Accepted Answer

Deploy the instance in the Business Critical tier with zone redundancy, and configure a failover group to a secondary region.. Business Critical tier with zone redundancy provides high availability within a region with an RPO of 0 and RTO of approximately 30 seconds for planned maintenance. Configuring a failover group to a secondary region provides disaster recovery with an RPO of 5 minutes and RTO of 1 hour. Option A is incorrect because active geo-replication is not supported for Azure SQL Managed Instance; failover groups are used instead. Option B is incorrect because General Purpose tier does not provide an RTO of 30 seconds for planned maintenance, and geo-redundant backup does not meet the required RPO and RTO for planned maintenance. Option C is incorrect because a failover group alone does not provide the sub-minute RTO needed for planned maintenance within the region.

Answer

Configure active geo-replication to a secondary region and use automatic failover.

Answer

Deploy in General Purpose tier with zone redundancy and geo-redundant backup.

Answer

Create a failover group to a secondary region and rely on that for both HA and DR.

Question 12

Your company uses Azure SQL Managed Instance and requires that all connections from client applications use Microsoft Entra authentication with multi-factor authentication (MFA). You configure Azure SQL Managed Instance to support Microsoft Entra authentication and create a contained database user for the application. However, when the application attempts to connect, it receives error '18456, state 10' indicating that the login is not recognized. What is the most likely cause?

Accepted Answer

The application is using a SQL login instead of a Microsoft Entra token or integrated authentication.. Error 18456, state 10 specifically indicates that the login was not recognized by SQL Server. Since the application is configured to use a contained database user with Microsoft Entra authentication, the most likely cause is that the application is attempting to connect using a SQL login (username/password) rather than presenting a Microsoft Entra access token or using integrated authentication. Contained database users require authentication through the Microsoft Entra ID token flow, not SQL Server authentication.

Answer

The contained database user is not mapped to a server-level login.

Answer

The client IP address is not allowed by the server-level firewall rule.

Answer

The server principal (server admin) is not a Microsoft Entra account.

Question 13

You are deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). You need to ensure that the data is encrypted at rest and that access to encryption keys is logged. Which THREE actions should you take? (Choose three.)

Accepted Answer

Grant the managed identity of the database access to the key vault with 'get', 'wrapKey', and 'unwrapKey' permissions.. To use customer-managed TDE keys stored in Azure Key Vault, the Azure SQL Database's managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions. This allows the database to access the key for encryption and decryption operations while maintaining a secure, auditable key management chain.

Answer

Use Always Encrypted to encrypt all PII columns.

Answer

Configure TDE with service-managed keys.

Question 14

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Accepted Answer

Increased query duration.. Options A and D are correct. In Azure SQL Database's Intelligent Insights, increased query duration and high DTU consumption are key metrics that indicate performance degradation due to increased resource consumption. Increased query duration suggests that queries are taking longer, often due to resource contention. High DTU consumption reflects high usage of CPU, memory, and I/O, which can degrade performance. Option B (high number of deadlocks) indicates concurrency issues but does not directly measure resource consumption. Option C (increased transaction log usage) is related to write activity, not a primary performance metric in Intelligent Insights. Option E (failed authentication attempts) is a security metric, unrelated to resource consumption.

Answer

High number of deadlocks.

Answer

Increased transaction log usage.

Answer

Failed authentication attempts.

Question 15

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Accepted Answer

Create the elastic pool, then add databases to the pool, then configure per-database limits, then monitor pool performance, then scale the pool as needed.. First create the pool, then add databases, configure per-database limits, monitor, and scale.

Answer

Add databases to an existing pool first, then create the elastic pool, then configure per-database limits, then monitor, then scale.

Answer

Create the elastic pool, then configure per-database limits, then add databases, then monitor, then scale.

Answer

Create the elastic pool, then add databases, then monitor, then configure per-database limits, then scale.