DP-300 Practice Test 46 — 15 Questions

Question 1

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

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

Accepted Answer

Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance. Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Answer

Perform a native backup and restore to Azure SQL Managed Instance

Answer

Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database

Answer

Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database

Question 2

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts a critical OLTP workload. The primary instance is deployed in West Europe. You need to ensure that failover to a secondary region occurs automatically when the primary becomes unavailable due to a regional outage, and that the secondary database is readable during normal operations. You also want to minimize storage costs. What should you configure?

Accepted Answer

Create a failover group between the primary and a secondary managed instance in a paired region.. Failover groups for Azure SQL Managed Instance provide automatic failover to a secondary instance in a paired region, and the secondary is readable for read-only workloads, meeting the requirements. Option B is incorrect because 'auto-failover groups' is a term used for Azure SQL Database, not SQL Managed Instance; SQL MI uses failover groups, which also provide automatic failover. Option A is incorrect because geo-zone-redundant storage is a storage redundancy option, not a disaster recovery configuration for SQL MI. Option C is incorrect because active geo-replication is available for Azure SQL Database, not for SQL Managed Instance.

Answer

Deploy a secondary instance using a Bicep template with geo-zone-redundant storage.

Answer

Implement auto-failover groups with a readable secondary in another region.

Answer

Configure active geo-replication between two managed instances.

Question 3

You have a SQL Server on Azure VM that hosts a mission-critical database. The VM is in a single availability zone. You need to achieve a 99.99% SLA for the SQL Server instance. Which two actions should you perform? (Choose two.)

Accepted Answer

Configure SQL Server Always On availability group with synchronous replication.. To achieve a 99.99% SLA for SQL Server on Azure VM, you need to ensure high availability. Deploying two VMs in an availability set (Option E) and configuring an Always On availability group with synchronous replication (Option A) provides the required redundancy and automatic failover. Option C, configuring a failover cluster instance using Azure shared disks, is supported for SQL Server 2019 and later, but it is not the correct answer because the question specifically asks for two actions, and A and E are the designated correct ones. Option B (geo-redundant backup) does not affect availability. Option D (single VM with premium SSD) does not achieve 99.99% because it lacks redundancy.

Answer

Enable geo-redundant backup storage.

Answer

Configure a failover cluster instance using Azure shared disks.

Answer

Use a single VM with premium SSD disks.

Question 4

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Accepted Answer

Create appropriate indexes on the columns used in joins and filters.. An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Answer

Disable parallelism for the query using the MAXDOP 1 hint.

Answer

Increase the DTU or vCore count of the database.

Answer

Rewrite the query using table hints to force a specific join order.

Question 5

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property. Configuring the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property ensures that all connections to Azure SQL Database use at least TLS 1.2, which encrypts data in transit. This directly addresses the requirement to encrypt sensitive data between the application and the database. Option E is correct because Always Encrypted with a column master key stored in Azure Key Vault ensures that sensitive data is encrypted at the client side and the encryption keys are never exposed to the database engine, preventing DBAs from viewing the sensitive data.

Answer

Implement row-level security (RLS) to filter rows

Answer

Enable transparent data encryption (TDE) on the database

Answer

Implement dynamic data masking on sensitive columns

Question 6

You are a database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 50 databases across 10 logical servers, each with a mix of General Purpose and Business Critical service tiers. The company has a strict requirement to automatically scale databases based on workload patterns to optimize cost without manual intervention. Specifically, during Black Friday sales, one of the Business Critical databases (DB-Sales) experiences a surge in transactions, and you need to temporarily upgrade it to a higher service objective (S9 instead of S6) for 48 hours. After the sale, it should automatically revert to S6. Additionally, you need to ensure that all databases have automated backups with a 35-day point-in-time restore retention and that backup storage costs are minimized by using geo-redundant storage only for critical databases. You have been asked to design an automation solution using Azure native services. Which approach should you recommend?

Accepted Answer

Use Azure Automation Runbooks scheduled to run before and after Black Friday to change the service objective, and set backup retention policies using Azure Policy.. Azure Automation Runbooks can be scheduled to execute PowerShell scripts that change the service objective of an Azure SQL Database (e.g., Set-AzSqlDatabase with the -RequestedServiceObjectiveName parameter). This allows scheduling a scale-up before Black Friday and a scale-down after 48 hours, meeting the temporary upgrade requirement. Additionally, Azure Policy can enforce backup retention policies (e.g., 35-day PITR) and specify geo-redundant storage only for critical databases, ensuring compliance and cost minimization. Option A is incorrect because Elastic Job agents are for executing T-SQL scripts across many databases, not for scheduling one-off scaling actions, and backup retention via portal is not automated. Option B is incorrect because Azure SQL Database's autoscale feature is only available in the serverless compute tier, not for provisioned service objectives like S6/S9, and it adjusts based on workload automatically, not on a scheduled temporary upgrade basis. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it's used in SQL Server on-premises or Azure SQL Managed Instance), and ALTER DATABASE MODIFY SERVICE_OBJECTIVE is not supported in Azure SQL Database; scaling requires Azure PowerShell or REST API.

Answer

Use Elastic Job agents to run a script that alters the service objective and configure backup retention using the Azure portal.

Answer

Enable autoscale on the database to automatically adjust service objective based on CPU usage.

Answer

Create a SQL Server Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = 'S9') and configure backup retention using T-SQL.

Question 7

You are deploying an Azure SQL Database that will be used by a critical financial application. The database must be protected against accidental deletion or modification of data by administrators. You need to implement a solution that allows point-in-time restore to any point within the last 35 days, even if an administrator accidentally drops a table. Which feature should you enable?

Accepted Answer

Enable automated backups and configure point-in-time restore with a retention period of 35 days.. Azure SQL Database's automated backups with point-in-time restore (PITR) allow you to restore a database to any point within the configured retention period, up to 35 days. This includes recovery from accidental table drops or data modifications, as PITR operates at the database level and can recover to a specific timestamp before the incident occurred.

Answer

Configure long-term backup retention (LTR) policies.

Answer

Enable the Azure SQL Database recycle bin feature.

Answer

Set up Active Geo-Replication with a failover group.

Question 8

You manage an Azure SQL Database that uses a Business Critical tier with a zone-redundant configuration. During a planned maintenance event, you notice a significant increase in query latency. You suspect that the read-only intent routing is not working as expected. What should you check first to ensure read-only connections are using the secondary replica?

Accepted Answer

Verify that the application connection string includes 'ApplicationIntent=ReadOnly' for read-only queries.. The read-only intent routing in Azure SQL Database relies on the client connection string including `ApplicationIntent=ReadOnly`. Without this parameter, all connections, including those intended for read-only workloads, will be directed to the primary replica, even if a readable secondary replica is available. This is the first and most fundamental check to ensure read-only queries are routed to the secondary.

Answer

Review the maintenance window schedule to see if the primary replica was being updated.

Answer

Check the 'ReadScale' property of the database to ensure it is set to 'Enabled'.

Answer

Confirm that the database is in a zone-redundant configuration by checking the 'ZoneRedundant' property.

Question 9

You need to automate the process of scaling an Azure SQL Database up during peak hours and down during off-peak hours to optimize cost. The solution must be serverless and not require any custom infrastructure. What should you use?

Accepted Answer

Create a scheduled Azure Logic App that uses the Azure SQL Database REST API to update the service objective.. Azure Logic Apps can be scheduled to run a workflow that uses the Azure SQL Database REST API (or Azure Resource Manager REST API) to update the service objective (DTU or vCore) of an Azure SQL Database. This enables automated scaling without custom infrastructure. Option A is incorrect because SQL Agent jobs run within the SQL Server context and cannot directly change the service objective; the ALTER DATABASE MODIFY (SERVICE_OBJECTIVE) command is not valid. Option C is incorrect because Elastic Job agents are designed for executing T-SQL scripts across multiple databases, not for changing service tiers. Option D is incorrect because Azure SQL Database does not support autoscale; scaling must be performed manually or programmatically.

Answer

Create a SQL Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = ...).

Answer

Use an Elastic Job agent to run a script that changes the service objective.

Answer

Enable autoscale on the Azure SQL Database.

Question 10

You are designing a high availability solution for an Azure SQL Database used by a critical application. The application requires no more than 5 seconds of data loss and automatic failover within 1 minute. Which service tier and configuration should you choose?

Accepted Answer

Business Critical tier with zone redundancy.. Business Critical tier with zone redundancy provides automatic failover within seconds and zero data loss, meeting the RPO of 5 seconds and RTO of 1 minute. Option A is incorrect because Hyperscale tier with geo-replication and auto-failover group typically has a higher RTO (often up to 1 hour) and RPO may exceed 5 seconds. Option B is incorrect because Hyperscale tier with zone redundancy does not provide automatic failover on its own; it only offers local resilience. Option C is incorrect because General Purpose tier with active geo-replication has a higher RTO (up to 1 hour) and does not guarantee an RPO of 5 seconds.

Answer

Hyperscale tier with geo-replication and auto-failover group.

Answer

Hyperscale tier with zone redundancy.

Answer

General Purpose tier with active geo-replication.

Question 11

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

Accepted Answer

Dynamic Data Masking with a masking function configured on the column.. Dynamic Data Masking (DDM) is the correct feature for masking a specific column for non-admin users. DDM can be configured on a column using a masking function (e.g., default, email, custom) and automatically obfuscates the data in query results for users without the UNMASK permission. Option A (Always Encrypted) encrypts the column but does not mask it selectively; non-admin users cannot see the data at all unless they have the key. Option B (Row-Level Security) filters entire rows based on a predicate, not columns. Option D (Azure Policy) applies governance rules at the subscription/resource level and cannot mask database columns.

Answer

Always Encrypted with a column encryption key.

Answer

Row-Level Security to filter rows for non-admin users.

Answer

Azure Policy to enforce masking rules on the database.

Question 12

You are designing a disaster recovery plan for an Azure SQL Database that supports an e-commerce application. The application requires an RPO of 15 seconds and an RTO of 1 hour during a regional outage. Which solution should you recommend?

Accepted Answer

Deploy active geo-replication to a secondary region and automate failover. Deploy active geo-replication to a secondary region and automate failover. Active geo-replication provides an asynchronous secondary replica in a different region with an RPO of up to 5 seconds, meeting the 15-second requirement. By automating failover (e.g., via Azure PowerShell, CLI, or Azure Automation), the RTO can be minimized to under 1 hour. Option A, creating a failover group with automatic failover, also uses geo-replication but the automatic failover policy may introduce additional detection time, making it harder to guarantee an RTO of exactly 1 hour. Additionally, failover groups are designed for managing multiple databases, adding unnecessary complexity for a single database. Option C, long-term backup retention and restore, has an RPO of hours, not seconds. Option D, zone-redundant availability, protects against zone failures within a single region, not a regional outage.

Answer

Create a failover group with automatic failover policy

Answer

Use long-term backup retention and restore in another region

Answer

Configure zone-redundant availability for the database

Question 13

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

Accepted Answer

The outage duration is less than the grace period for data loss.. The grace period for data loss is set to 3600 seconds (1 hour), and the outage lasted only 30 minutes. Automatic failover for Azure SQL Database failover groups will not occur until the grace period expires, as the system waits for the outage to resolve within that time to avoid unnecessary failover and potential data loss. Option A is incorrect because the failover policy is not specified as manual; if it were manual, the automatic failover would not occur regardless of the grace period. Option C is incorrect because the grace period is actually longer than the outage duration, so it is not too short. Option D is incorrect because there is no indication that the secondary region is also experiencing an outage.

Answer

The failover policy is set to manual.

Answer

The grace period for data loss is too short.

Answer

The secondary region is also experiencing an outage.

Question 14

You manage an Azure SQL Database that uses Azure SQL Database elastic jobs to execute maintenance scripts across multiple databases. You need to ensure that job execution is logged in a central database for auditing and troubleshooting. What should you configure?

Accepted Answer

Ensure the Elastic Job agent uses a dedicated job database to store job execution logs.. Azure SQL Database elastic jobs automatically store job execution history in a dedicated job database, which is specified when the job agent is created. This database serves as the central repository for job logs, making it the appropriate location for auditing and troubleshooting. Option A is incorrect because elastic jobs log to the job database, not Azure Table Storage. Option B is incorrect because SQL Server Audit is used for auditing database events, not for capturing job execution logs. Option D is incorrect because Azure Monitor diagnostic settings can be used for additional monitoring but are not the central logging mechanism for elastic jobs.

Answer

Configure the job to write logs to Azure Table Storage using a custom output.

Answer

Enable SQL Server Audit on the target databases to capture job execution.

Answer

Set up Azure Monitor diagnostic settings for the elastic job agent.

Question 15

You are deploying an Azure SQL Database for a new application that requires high availability with automatic failover within the same region. The database must be able to recover from a zonal failure without data loss. Which deployment option should you use?

Accepted Answer

Business Critical tier with zone redundancy enabled. The Business Critical tier with zone redundancy enabled provides the highest level of availability within a single Azure region by synchronously replicating data across three different availability zones. This ensures that if one zone fails, automatic failover occurs with no data loss because all transactions are committed to at least two replicas before being acknowledged. This meets the requirement for zonal failure recovery without data loss.

Answer

Hyperscale tier with zone redundancy

Answer

Standard tier with failover group

Answer

Basic tier with active geo-replication