DP-300 Practice Test 16 — 15 Questions

Question 1

You are planning high availability for an Azure SQL Database that runs an e-commerce application. The database uses the Business Critical service tier. Which TWO features are automatically enabled to provide high availability within a single region? (Choose two.)

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

Accepted Answer

Automatic failover to a secondary replica if the primary becomes unavailable.. Options A and B are correct. Business Critical includes multiple replicas (one primary and two secondary) that are automatically managed, and zone-redundant configuration is optional but not automatic. Option C is wrong because active geo-replication is a separate DR feature. Option D is wrong because auto-failover groups are for DR. Option E is wrong because read scale-out is an optional feature, not automatic for HA.

Answer

Auto-failover groups with a secondary in another region.

Answer

Active geo-replication to a paired region.

Answer

Read scale-out with a readable secondary replica.

Question 2

You have an Azure SQL Managed Instance. You need to implement a disaster recovery solution that provides automatic failover with an RTO of less than 1 hour. Which THREE actions should you take?

Accepted Answer

Create a secondary Managed Instance in a different Azure region.. To achieve automatic failover with an RTO under 1 hour for Azure SQL Managed Instance, you need a secondary instance in a different region (B), a failover group for automatic failover (C), and the Business Critical service tier which supports failover groups (E). Zone redundancy (A) provides high availability within a region, not disaster recovery across regions. Geo-replication (D) is not supported on Managed Instance; failover groups are the correct mechanism.

Answer

Enable zone redundancy on the primary instance.

Answer

Enable geo-replication on the instance.

Question 3

Your company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance. You enable SQL Database auditing and configure a storage account for logs. However, you notice that some DELETE operations are not being audited. What could be the cause?

Accepted Answer

The audit is configured to capture only successful operations.. Azure SQL Database auditing can be configured to capture only successful operations or only failed operations, or both. If the audit is set to capture only successful operations, DELETE operations that fail (e.g., due to permissions or constraint violations) will not be logged. The question states that some DELETE operations are missing, which aligns with a filter that excludes failed operations.

Answer

The diagnostic setting is configured incorrectly.

Answer

The storage account firewall is blocking the audit logs.

Answer

The database has Transparent Data Encryption (TDE) enabled.

Question 4

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

Accepted Answer

Invite the consultants as guest users in your Microsoft Entra ID tenant using B2B collaboration, then create a contained database user for each guest user. External consultants from a different Microsoft Entra ID tenant must first be invited as guest users via B2B collaboration to your tenant. Once they are guest users, you can create contained database users in Azure SQL Database mapped to their guest user identities (e.g., their UPN in your tenant) and grant them read-only permissions (e.g., db_datareader role). This approach respects the isolation of the partner's tenant while enabling access through your tenant's identity.

Answer

Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)

Answer

Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant

Answer

Create a SQL Server authentication login and user for the consultants

Question 5

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

Accepted Answer

sys.dm_os_wait_stats. C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Answer

sys.dm_exec_query_stats

Answer

Query Store Wait Stats in SSMS

Answer

sys.dm_db_index_usage_stats

Question 6

Your company uses Azure SQL Database with Elastic Jobs to run maintenance scripts. You need to ensure that the Elastic Job agent can connect to the target databases securely. The agent runs in an Azure virtual network and uses a managed identity. The target databases are in a different Azure region and have public endpoint disabled. What is the most secure way to connect the Elastic Job agent to the target databases?

Accepted Answer

Configure a private endpoint for each target database and grant the Elastic Job agent's managed identity access to the databases via a contained database user.. It combines Azure Private Link (private endpoint) for network-level isolation with managed identity for authentication, eliminating exposure to the public internet and removing the need for passwords. The Elastic Job agent's managed identity is mapped to a contained database user in each target database, enabling secure, passwordless connections even across regions when public endpoints are disabled.

Answer

Use virtual network service endpoints to allow the agent's subnet to connect to the target databases.

Answer

Add the agent's public IP address to the target database firewall rules.

Answer

Create a SQL authentication login for the Elastic Job agent and use that in the job credentials.

Question 7

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Accepted Answer

Review the Query Store Top Resource Consuming Queries report.. To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries. Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Answer

Rebuild all indexes to improve query performance.

Answer

Change the database recovery model to Simple.

Answer

Scale the database to a higher service tier to mitigate the issue.

Question 8

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Accepted Answer

Connect to the instance, create a job, define steps, schedule, then enable and start.. Connect to the instance, create a job, define steps, schedule, then enable and start.

Answer

Create a job, define steps, schedule, connect to the instance, then enable and start.

Answer

Connect to the instance, define steps, create a job, schedule, then enable and start.

Answer

Connect to the instance, create a job, schedule, define steps, then enable and start.

Question 9

Drag and drop the steps to migrate an on-premises SQL Server database to Azure SQL Database using the Data Migration Assistant (DMA) in the correct order.

Accepted Answer

Assess database compatibility, then fix compatibility issues, then deploy Azure SQL Database, then migrate schema and data. First assess and fix issues, then deploy the target, then migrate schema, then data.

Answer

Deploy Azure SQL Database, then assess database compatibility, then fix compatibility issues, then migrate schema and data

Answer

Assess database compatibility, then deploy Azure SQL Database, then fix compatibility issues, then migrate schema and data

Answer

Migrate schema and data, then assess database compatibility, then fix compatibility issues, then deploy Azure SQL Database

Question 10

Refer to the exhibit. An administrator tries to restore the database OrdersDB to a point in time 2025-03-14 10:00 UTC and receives error 406. What is the most likely reason?

Accepted Answer

The database is currently in a geo-replication secondary role.. Error 406 in Azure SQL Database indicates that the database is not in a state that allows recovery to the specified point in time. This commonly occurs when the database is a secondary replica in a geo-replication relationship. Geo-secondary databases are read-only and cannot be restored to a point in time directly; instead, the primary database must be used for restore operations. Options B, C, and D are incorrect: long-term retention does not interfere with PITR, the requested time is within the 7-day retention window, and Azure SQL Database automatically takes transaction log backups every 5-10 minutes, enabling PITR regardless of full backup frequency.

Answer

The long-term retention policy interferes with point-in-time restore.

Answer

The requested restore point is outside the 7-day retention period.

Answer

The automated backup frequency is 12 hours, so point-in-time restore is not supported.

Question 11

You are designing a high availability solution for an Azure SQL Database that is used by a mission-critical application. The database is currently in the Standard tier. You need to minimize downtime during planned maintenance and unplanned failures while keeping costs low. What should you recommend?

Accepted Answer

Upgrade to the Premium tier with zone-redundant configuration.. The Premium tier supports zone-redundant configuration, which replicates data across availability zones within the same region, providing high availability and minimizing downtime during planned maintenance or unplanned failures. Option A is wrong: the Basic tier does not support failover groups or zone redundancy. Option B is wrong: active geo-replication is designed for disaster recovery across regions, not for high availability within a region, and incurs higher cost and complexity. Option D is wrong: the Standard tier does not support zone redundancy; zone redundancy is only available in the Premium or Business Critical tiers.

Answer

Use the Basic tier with a failover group.

Answer

Configure active geo-replication to a secondary region.

Answer

Upgrade to the Standard tier and enable zone redundancy.

Question 12

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Accepted Answer

Add the primary database to the failover group.. Adding the primary database to the failover group is the essential step that associates the database with the group, enabling automated replication and failover to the secondary server. Without this step, the failover group has no database to replicate, and the readable secondary cannot be created or maintained.

Answer

Create a server-level firewall rule on the secondary server to allow client IPs.

Answer

Configure a grace period of at least 1 hour.

Question 13

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

Accepted Answer

Use an Auto-Failover Group with the secondary in the paired region. Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Answer

Restore a geo-redundant backup to the secondary region

Answer

Configure Active Geo-Replication to the secondary region

Answer

Create a named replica in the secondary region and fail over manually

Question 14

Which TWO of the following are valid methods to recover an Azure SQL Database after a regional outage?

Accepted Answer

Fail over to a secondary region using a failover group.. Options B and E are correct. Failover groups with automatic failover allow recovery by failing over to a secondary region (B). Geo-restore from geo-redundant backups restores the database to any Azure region using the most recent geo-redundant backup (E). Option A is incorrect because log shipping is not supported for Azure SQL Database. Option C is incorrect because point-in-time restore can only restore to a point in time within the same region. Option D is incorrect because copying a database from a geo-redundant backup is not a direct method; you must perform a geo-restore.

Answer

Replicate the database from a backup to a secondary region using log shipping.

Answer

Point-in-time restore to a time before the outage.

Answer

Copy the database from a geo-redundant backup to a new server.

Question 15

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

Accepted Answer

The audit actions and groups do not include login events. The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Answer

The audit logs are being sent to Azure Monitor instead of blob storage

Answer

The retention days are set too high, causing logs to be truncated

Answer

Auditing is disabled at the server level