DP-300 Practice Test 6 — 10 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