DP-300 Practice Questions 2 — 50 Questions

Question 1

Which THREE components are required to configure a failover group for Azure SQL Database? (Choose three.)

Accepted Answer

At least one database on the primary server.. Options B, C, and D are correct. A failover group requires a primary server, a secondary server, and at least one database on the primary server. Option A is incorrect because the failover group listener is automatically created and does not need to be separately configured. Option E is incorrect because an elastic pool is optional; failover groups can contain individual databases or elastic pools.

Answer

Failover group listener.

Answer

An elastic pool.

Question 2

You have an Azure SQL Database that uses a serverless compute tier. You want to automate the process of pausing the database during non-business hours (8 PM to 6 AM) to save costs. The database should automatically resume when the first connection is attempted. What should you configure?

Accepted Answer

Set the auto-pause delay to 10 hours in the serverless configuration.. The serverless compute tier in Azure SQL Database automatically pauses after a period of inactivity and resumes on the first connection. By setting the auto-pause delay to 10 hours (covering the 8 PM to 6 AM window), the database will pause during non-business hours and automatically resume when a connection is attempted. Options A and D are unnecessary because the serverless tier handles this natively, and Option B is incorrect because elastic jobs are not used for scaling to 0 vCores.

Answer

Create a SQL Agent job to run ALTER DATABASE PAUSE and ALTER DATABASE RESUME.

Answer

Configure an elastic job to scale down to 0 vCores during off-hours.

Answer

Use Azure Automation to run a script that calls the REST API to pause/resume the database.

Question 3

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

Accepted Answer

Azure SQL Analytics (Azure Monitor). The correct answers are D and E. Query Store (E) captures and retains query execution metrics and plans over time, enabling historical performance analysis. Azure SQL Analytics (D), now part of Azure Monitor, provides a comprehensive view of query performance across multiple databases with historical data. Option A (DMVs) displays current state and recent activity but lacks long-term history. Option B (Intelligent Insights) offers automated diagnostics and summaries but not detailed query-level history. Option C (Extended Events) is designed for real-time event capture, not historical analysis.

Answer

Dynamic Management Views (DMVs)

Answer

Intelligent Insights

Answer

Extended Events

Question 4

You are managing an Azure SQL Managed Instance that hosts a critical database. You need to automate the export of daily backups to a storage account for long-term retention. The solution must minimize administrative overhead and support point-in-time restore within the retention period. What should you use?

Accepted Answer

Configure a long-term retention (LTR) backup policy for the Managed Instance.. Azure SQL Managed Instance supports configuring a long-term retention (LTR) backup policy that automatically stores backups in a geo-redundant storage account for up to 10 years, minimizing administrative overhead while supporting point-in-time restore within the retention period. Option B is incorrect because Azure Backup is designed for IaaS VMs and other workloads, not for SQL Managed Instance native backups. Option C is incorrect because scheduling BACPAC exports is not a backup solution — it does not support point-in-time restore and requires manual setup. Option D is incorrect because using SQL Agent jobs with BACKUP DATABASE TO URL adds complexity and does not integrate with Azure's automated backup management.

Answer

Configure an Azure Backup policy for the Managed Instance.

Answer

Schedule a BACPAC export of the database to the storage account.

Answer

Create a SQL Agent job to perform backups using BACKUP DATABASE TO URL.

Question 5

Your company has an Azure SQL Database that uses the Business Critical service tier with three replicas. You need to ensure that during a regional outage, the database can be failed over to a secondary region with minimal data loss. What should you configure?

Accepted Answer

Create an auto-failover group with a secondary server in a different region.. Auto-failover groups with a secondary server in a different region provide cross-region disaster recovery with minimal data loss. Azure SQL Database supports configuring an auto-failover group that includes a primary and secondary server in different regions. When using the Business Critical service tier, synchronous replication is used within the primary region, but for cross-region failover, asynchronous replication is used, resulting in minimal data loss (typically a few seconds). Option A is wrong because zone-redundant replicas provide high availability within a single region, not cross-region DR. Option C is wrong because read scale-out allows offloading read workloads to a replica, but does not provide failover capability. Option D is wrong because geo-redundant backup storage and geo-restore can restore the database in a different region, but it involves longer recovery time (hours) and potential data loss, and is not a failover mechanism.

Answer

Configure zone-redundant replicas in the same region.

Answer

Enable read scale-out on the database.

Answer

Use geo-redundant backup storage and perform geo-restore.

Question 6

You have an Azure SQL Managed Instance configured with a failover group for disaster recovery. The primary instance is in the East US region and the secondary is in West US. You need to perform a planned failover for maintenance with zero data loss. What is the correct sequence of steps?

Accepted Answer

Use the Azure portal to perform a planned failover for the failover group.. To perform a planned failover with zero data loss for an Azure SQL Managed Instance failover group, you should use the Azure portal (or PowerShell/CLI) to initiate a planned failover. This ensures that all transactions are synchronized before switching roles, preventing data loss. Option A is incorrect because taking the primary offline before failover would cause downtime and potential data loss. Option C is incorrect because scaling down does not facilitate a planned failover. Option D is incorrect because removing the failover group would break replication and is unnecessary.

Answer

Take the primary instance offline, then failover.

Answer

Scale down the primary to reduce cost, then failover.

Answer

Remove the failover group, perform maintenance, then recreate the failover group.

Question 7

You are planning a disaster recovery strategy for an Azure SQL Database that is part of a failover group. The application requires that after a failover, the database is accessible with minimal downtime and without data loss. Which THREE components are essential for this configuration? (Select three.)

Accepted Answer

Secondary database in a paired region with data synchronization. A secondary database in a paired region with data synchronization ensures that data is replicated to another region, enabling failover with minimal data loss. Option C is correct: The failover group listener endpoint provides a constant connection string that automatically routes to the current primary database, ensuring application connectivity after failover. Option E is correct: The failover group configured between primary and secondary servers orchestrates the replication and automatic failover process. Option A is incorrect: Geo-redundant backup storage is for point-in-time restore, not for failover. Option D is incorrect: Azure Traffic Manager with priority routing is not needed when using failover groups; the failover group listener handles routing.

Answer

Geo-redundant backup storage

Answer

Azure Traffic Manager with priority routing

Question 8

You have an Azure SQL Managed Instance configured with a failover group between two regions. The primary region becomes unavailable. You need to fail over to the secondary region with minimal data loss. What should you do?

Accepted Answer

Run 'az sql mi failover-group force-failover-allow-data-loss' from the Azure CLI. During a disaster where the primary region is unavailable, the correct action is to force a failover with potential data loss. The Azure CLI command 'az sql mi failover-group force-failover-allow-data-loss' (Option A) performs this forced failover, accepting the risk of data loss to restore availability. Option B is incorrect because adding to an elastic pool is unrelated to failover groups. Option C is incorrect because 'az sql mi failover-group failover' is not a valid command; the correct command includes 'force-failover-allow-data-loss'. Option D is incorrect because the Azure portal does not directly support geo-failover for managed instances; the CLI or PowerShell must be used for forced failover.

Answer

Add the secondary instance to an elastic pool and initiate failover

Answer

Run 'az sql mi failover-group failover' from the Azure CLI

Answer

Use Azure portal to initiate geo-failover for the managed instance

Question 9

You have a SQL Server on Azure VM that is part of a failover cluster instance (FCI). The cluster nodes are in an availability set. You need to ensure that the database can tolerate a complete Azure region failure. What should you add to the current architecture?

Accepted Answer

Deploy a second FCI in a different Azure region and configure SQL Server log shipping or availability group between the FCIs.. Deploying a second FCI in a different Azure region and configuring SQL Server log shipping or an availability group between the FCIs provides disaster recovery across regions. Option A is incorrect because adding a node in a different availability zone within the same region does not protect against a complete region failure. Option C is incorrect because geo-redundant backup storage only protects backups, not provide continuous replication for the database. Option D is incorrect because active geo-replication is a feature of Azure SQL Database, not SQL Server on Azure VM.

Answer

Add another node to the existing FCI in a different availability zone.

Answer

Configure geo-redundant backup storage for the SQL Server VMs.

Answer

Create an Azure SQL Database with active geo-replication and migrate the database.

Question 10

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

Accepted Answer

Use an elastic pool and add databases as needed. Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Answer

Deploy each tenant's database as a single database with reserved capacity

Answer

Use a single Hyperscale database with schema per tenant

Answer

Use a single Azure SQL Managed Instance with multiple databases

Question 11

Your company has a policy that requires all Azure SQL Databases to be recoverable to a point in time within the last 5 minutes in the event of a user error. What should you configure?

Accepted Answer

Configure point-in-time restore (PITR) with a retention period of at least 5 minutes. Point-in-time restore (PITR) allows restoring a database to any point within the retention period. By configuring a retention period that covers at least the last 5 minutes, you can recover from user errors within 5 minutes. The default retention is 7 days, but you can configure it up to 35 days, so 5 minutes is easily achievable. Option B (zone-redundant configuration) provides high availability within a region but not point-in-time recovery. Option C (active geo-replication) is for disaster recovery to another region. Option D (long-term backup retention) is for backups beyond the PITR retention period, not for short-term recovery.

Answer

Enable zone-redundant configuration

Answer

Set up active geo-replication to a secondary region

Answer

Enable long-term backup retention (LTR)

Question 12

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

Accepted Answer

7 days. Point-in-time restore retention can be set from 1 to 35 days. To restore to any point within 7 days, you need at least 7 days retention. Option A (1 day) is insufficient. Option C (14 days) is more than needed. Option D (3 days) is also insufficient.

Answer

1 day

Answer

14 days

Answer

3 days

Question 13

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

Accepted Answer

Initiate an unplanned failover from the primary to the secondary.. During a full outage, the primary is offline, so a planned failover (which requires connectivity to the primary to synchronize) is not possible. The only option to fail over with minimal data loss is to initiate an unplanned (forced) failover, which will make the secondary the new primary. Although there may be some data loss from transactions not yet replicated, this is the best available option. Deleting the secondary or creating a new secondary does not fail over.

Answer

Delete the secondary database and create a new one in the primary region.

Answer

Initiate a planned failover from the primary to the secondary.

Answer

Create a new secondary database in the same region as the primary.

Question 14

Your company has a SQL Server on Azure VM hosting a critical database. You need to ensure high availability with automatic failover and no data loss during a planned patching event. The solution must minimize cost. Which configuration should you use?

Accepted Answer

Deploy two Azure VMs in an availability set, configure SQL Server Always On availability group with synchronous commit and automatic failover.. Deploying two Azure VMs in an availability set and configuring SQL Server Always On availability group with synchronous commit and automatic failover meets the requirements of high availability with automatic failover and no data loss during planned patching (by performing a manual failover). This solution minimizes cost compared to Azure SQL Database Managed Instance or geo-replication. Option A is incorrect because migrating to Azure SQL Database Managed Instance is a different platform, not SQL Server on Azure VM, and geo-replication is for disaster recovery, not planned patching. Option B is incorrect because log shipping is not automatic and can result in data loss. Option C is incorrect because a single VM with FCI using Storage Spaces Direct does not provide automatic failover for planned patching; it requires a secondary node.

Answer

Migrate to Azure SQL Database Managed Instance and configure geo-replication.

Answer

Configure log shipping with a secondary VM in another region.

Answer

Use a single VM with a failover cluster instance (FCI) using Storage Spaces Direct.

Question 15

Your company uses Azure SQL Database with active geo-replication for disaster recovery. The primary database is in the West Europe region. You need to perform a planned failover to the secondary in North Europe for a maintenance window. Which THREE actions should you take? (Choose three.)

Accepted Answer

After failover, verify that the new secondary (in West Europe) is configured as a readable secondary.. The correct actions are C, D, and E. For a planned failover during a maintenance window, you should initiate a planned failover (no data loss) from the Azure portal or PowerShell (E) to ensure all transactions are synchronized before switching. After failover, verify that the new secondary (in West Europe) is configured as a readable secondary (C) to maintain disaster recovery capabilities. Additionally, update the application connection strings to point to the new primary in North Europe (D). Option A is incorrect because a forced failover could cause data loss and is intended for unplanned outages. Option B is incorrect because seeding of the new secondary happens automatically via geo-replication.

Answer

Initiate a forced failover to ensure the fastest switchover.

Answer

Manually seed the new secondary database after failover.

Question 16

Your company runs a financial application on Azure SQL Managed Instance. The primary instance is deployed in North Europe. You need to implement a disaster recovery solution that meets the following requirements: automatic failover to a secondary region in case of a regional outage; the secondary must be readable during normal operations to serve read-only queries; the RPO must be less than 10 seconds; the RTO must be less than 30 seconds; and you must minimize compute costs by using the smallest possible secondary instance size. The primary instance uses the Business Critical service tier. What should you do?

Accepted Answer

Create a failover group between the primary and a secondary Business Critical instance in a paired region, and use a smaller compute size for the secondary.. A failover group between two Azure SQL Managed Instances in different regions provides automatic failover and a readable secondary. The secondary must be in the Business Critical service tier, matching the primary's tier, but you can use a smaller compute size (fewer vCores) to reduce costs while still supporting read-only workloads. Option A is incorrect because you cannot change the secondary's service tier to General Purpose in a failover group; it must match the primary. Option C is incorrect because the secondary must be in a different region for disaster recovery. Option D is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate DR solution.

Answer

Create a failover group with a General Purpose secondary instance in a different region to reduce costs.

Answer

Deploy a secondary Business Critical instance in the same region and configure a failover group.

Answer

Configure active geo-replication to a secondary managed instance in a different region.

Question 17

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 18

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

Accepted Answer

Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.. To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Answer

Set the 'minimal TLS version' to 1.2 in the database's properties.

Answer

Add a firewall rule to deny connections using TLS 1.0 or 1.1.

Answer

Enable the 'Force encryption' option in the connection string and require TLS 1.2.

Question 19

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Accepted Answer

Query Store to review query execution plans and wait statistics. Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Answer

Intelligent Insights report

Answer

Automatic Tuning recommendations

Answer

Azure Monitor metrics for the database

Question 20

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

Accepted Answer

Autoscale settings for the database. Azure SQL Database can use autoscale settings (in the DTU or vCore model) to automatically adjust resources based on metrics like CPU usage. Option B (Elastic job) is for running scheduled tasks across databases, not scaling. Option C (Elastic pool) is for managing multiple databases with shared resources, but it does not automatically scale a single database. Option D (Automatic tuning) optimizes query performance, not resource scaling.

Answer

Elastic job

Answer

Elastic pool

Answer

Automatic tuning