DP-300 Practice Test 38 — 15 Questions

Question 1

Your company runs a mission-critical application on Azure SQL Database in the East US region. You need to ensure automatic failover with zero data loss in case of a regional outage. Which deployment option should you use?

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

Accepted Answer

Deploy a Business Critical tier database and configure an auto-failover group with a readable secondary in a paired region.. To achieve automatic failover with zero data loss during a regional outage, you need a solution that supports synchronous replication for a secondary replica in another region. Azure SQL Database Business Critical tier (or Premium tier) supports auto-failover groups with a readable secondary in a paired region, where the secondary is kept in sync synchronously, ensuring no data loss (RPO=0). Option A correctly describes this configuration. General Purpose tier (Option B) uses asynchronous geo-replication, which has potential data loss. Hyperscale tier (Option C) supports zone redundancy but not synchronous replication across regions for auto-failover groups. Serverless tier (Option D) is not suitable for mission-critical workloads and does not provide the required availability guarantees.

Answer

Deploy a General Purpose tier database with geo-replication.

Answer

Deploy a Hyperscale tier database with zone redundancy.

Answer

Deploy a Serverless tier database with active geo-replication.

Question 2

Which THREE of the following are required considerations when configuring a failover group for Azure SQL Managed Instance?

Accepted Answer

Both instances must be in the same partner region pair.. Options A, C, and E are correct. For Azure SQL Managed Instance failover groups, both instances must be in the same partner region pair (e.g., East US and West US). The secondary instance must be in a different Azure region to provide geo-redundancy. The databases must be in the same resource group. Option B is incorrect because the secondary instance can have a different service tier than the primary. Option D is incorrect because automatic failover is not required; you can use manual failover.

Answer

Both instances must have the same service tier.

Answer

Automatic failover must be enabled.

Question 3

You have an Azure SQL Database in the Premium tier configured with a failover group to a secondary region. The secondary region is not readable. You need to ensure that the secondary database is readable for reporting purposes without compromising the failover group's DR capabilities. Which TWO actions should you take? (Select two.)

Accepted Answer

Configure active geo-replication in addition to the failover group.. Setting the failover group's secondary type to 'readable' enables read-only access to the secondary database for reporting, while preserving the failover group's DR capabilities. Option A is correct because configuring active geo-replication in addition to the failover group creates a separate readable secondary database that does not interfere with the existing failover group's DR, providing an alternative method for read-only workloads. Option C is incorrect because adding a zone-redundant replica (a high-availability feature across availability zones) does not affect the readability of the secondary in a failover group. Option B is incorrect because read scale-out on the primary only allows read-only connections to the primary, not the secondary. Option D is incorrect because changing the secondary's service tier to Business Critical does not automatically make it readable; readability is controlled by the failover group's secondary type.

Answer

Enable read scale-out on the primary database.

Answer

Add a zone-redundant replica to the secondary region.

Answer

Change the secondary database's service tier to Business Critical.

Question 4

You manage an Azure SQL Managed Instance in the East US region. To meet a 5-second RPO, you configure a failover group with a secondary in West US. During a planned maintenance, the primary becomes unavailable. The failover group is set to automatic with a grace period of 1 hour. You need to minimize downtime. What should you do?

Accepted Answer

Initiate a manual forced failover to the secondary.. Initiating a manual forced failover immediately fails over to the secondary, minimizing downtime. Option A is incorrect because dropping and recreating the failover group introduces additional steps and downtime, and is unnecessary since a manual failover can be performed directly. Option B is incorrect because waiting for automatic failover would take up to 1 hour due to the grace period, causing longer downtime. Option C is incorrect because altering the grace period to 0 and then waiting for automatic failover is slower than performing a manual forced failover, which provides the fastest recovery.

Answer

Drop the failover group and create a new one with immediate failover.

Answer

Wait for the automatic failover to occur after the grace period.

Answer

Alter the failover group grace period to 0 and wait for automatic failover.

Question 5

Refer to the exhibit. You are reviewing the configuration of a failover group for an Azure SQL Managed Instance. Which statement is correct about this configuration?

Accepted Answer

The read-only endpoint will not automatically redirect connections after failover.. The read-only endpoint's failover policy is set to Disabled, meaning it will not automatically redirect connections after a failover. Option A is incorrect because the read-write endpoint has its failover policy set to Automatic, so it will redirect connections automatically. Option C is incorrect because the grace period for data loss is set to 1 hour (3600 seconds), but the statement says 'more than 60 minutes' which is not exactly accurate; also the primary focus is on the endpoint behavior. Option D is incorrect because the read-only endpoint is used for read-only connections, not read-write.

Answer

The read-write endpoint will not automatically redirect connections after failover.

Answer

The failover group will automatically fail over with data loss if the primary is unavailable for more than 60 minutes.

Answer

The read-only endpoint is used for read-write connections after failover.

Question 6

Which TWO are benefits of using a failover group for Azure SQL Database? (Select two.)

Accepted Answer

Allows the secondary database to be readable for reporting. Failover groups in Azure SQL Database provide a single read/write listener endpoint for the primary database (option C) and allow the secondary database to be readable for reporting purposes (option A). Option B is incorrect because Transparent Data Encryption (TDE) is not a feature of failover groups; TDE can be configured independently. Option D is incorrect because failover groups do not automatically balance read queries; for read scaling, you would use a read-scale replica. Option E is incorrect because failover groups use asynchronous replication, not synchronous.

Answer

Enables transparent data encryption (TDE) across regions

Answer

Automatically balances read queries between primary and secondary

Answer

Supports synchronous replication between primary and secondary

Question 7

Your Azure SQL Database is configured with active geo-replication to a secondary region. The primary region experiences a complete outage. You initiate a manual failover to the secondary. Users report that some client applications are failing to connect. What is the most likely cause?

Accepted Answer

Client applications are using the old connection string pointing to the primary server.. After a manual failover in active geo-replication, the secondary becomes the new primary. Client applications that were connecting to the original primary server will have connection strings pointing to that original server's endpoint. Unless the clients update their connection strings to point to the new primary server (the former secondary), they will fail to connect. Option A is incorrect because geo-replication does not require VPN; the secondary database is accessible over the internet. Option B is incorrect because the secondary database is already provisioned and kept in sync, ready for failover. Option C is incorrect because the server name does not automatically change; the secondary server has its own name, and after failover, the connection string must be updated to that name.

Answer

The secondary server is behind a VPN that clients cannot access.

Answer

The secondary database is not fully provisioned yet.

Answer

The server name changed after failover and DNS is not updated.

Question 8

You have an Azure SQL Database configured with active geo-replication. The primary region experiences an outage that lasts longer than the recovery time objective (RTO) of 5 minutes. You need to initiate a failover to the secondary region with minimal data loss. What should you do?

Accepted Answer

Force a failover to the secondary region using a PowerShell script.. When the primary region is offline, a planned failover is not possible because it requires synchronization with the primary. A forced failover (option C) is the only way to promote the secondary to primary without the primary being online. While it may result in some data loss, it provides the fastest failover, meeting the RTO of 5 minutes. Option B is incorrect because auto-failover groups have a grace period and may not fail over automatically within the RTO. Option D describes geo-restore from backups, which is not a failover operation.

Answer

Initiate a planned failover to the secondary region using the Azure portal.

Answer

Configure an auto-failover group and let it fail over automatically.

Answer

Use geo-restore to recover the database from the most recent geo-replicated backup.

Question 9

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Accepted Answer

Create Elastic Database Jobs that run index maintenance T-SQL scripts.. Options A and C are correct. Elastic Database Jobs (Option A) allow running custom T-SQL scripts for index maintenance across databases. Automatic tuning (Option C) can automatically create and drop indexes based on workload patterns, which is a form of index maintenance. Option B is incorrect because Azure Automation PowerShell runbooks are not a native option for Azure SQL Database index maintenance; they would require additional setup and do not directly execute T-SQL. Option D is incorrect because SQL Agent jobs are not available in Azure SQL Database (they are available in SQL Server on-premises or on Azure VMs). Option E is incorrect because Azure Data Factory is primarily for data movement and orchestration, not for direct index maintenance.

Answer

Use Azure Automation PowerShell runbooks to invoke index rebuilds.

Answer

Schedule SQL Agent jobs with ALTER INDEX statements.

Answer

Use Azure Data Factory to copy data and rebuild indexes.

Question 10

Refer to the exhibit. You have an Azure SQL Database failover group configured with the ARM template snippet shown. You need to ensure that read-only queries are routed to the secondary region when the primary is healthy. What should you modify?

Accepted Answer

Set the readOnlyEndpoint failoverPolicy to Enabled.. The readOnlyEndpoint failoverPolicy must be set to Enabled to route read-only queries to the secondary region when the primary is healthy. Option A is incorrect because the readWriteEndpoint failoverPolicy being Manual does not affect read-only routing; it controls failover initiation. Option C is incorrect because adding databases to the partnerServers array is unrelated to read-only routing. Option D is incorrect because changing the failoverGracePeriodMinutes to 0 does not enable read-only endpoint routing.

Answer

Set the readWriteEndpoint failoverPolicy to Manual.

Answer

Add the database to the partnerServers array.

Answer

Change the failoverGracePeriodMinutes to 0.

Question 11

Which TWO of the following are benefits of using Azure SQL Database failover groups for high availability?

Accepted Answer

Automatic failover to a secondary region based on a grace period.. Failover groups provide automatic failover to a secondary region based on a grace period (option B), allowing for continuous availability during regional outages. They also support routing read-only queries to the secondary region (option C) to offload read workloads and improve performance. Option A is incorrect because synchronous replication across availability zones is achieved through zone-redundant configuration, not failover groups. Option D is incorrect because load balancing read-write traffic across replicas is not a feature of failover groups; that is handled by read-scale replicas or connection policies. Option E is incorrect because geo-redundant backup storage is a separate feature (geo-redundant storage), not a direct benefit of failover groups.

Answer

Synchronous replication of data across availability zones.

Answer

Load balancing read-write traffic across multiple replicas.

Answer

Geo-redundant backup storage for disaster recovery.

Question 12

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Accepted Answer

Azure Monitor Metrics. Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Answer

Performance Insights

Answer

SQL Server Profiler

Question 13

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Accepted Answer

Stream diagnostic logs to a Log Analytics workspace.. Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Answer

Automatically rebuild fragmented indexes.

Answer

Automatically scale the database to a higher service tier.

Question 14

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

Accepted Answer

ARM templates with Bicep. Options A, C, and E are correct because ARM/Bicep templates can define the entire infrastructure, Azure PowerShell can be used in scripts, and Azure CLI can also be used. Option B (Azure Migrate) is for migration, not automation. Option D (Azure Data Studio) is a client tool for querying, not automation.

Answer

Azure Migrate

Answer

Azure Data Studio

Question 15

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

Accepted Answer

Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.. Creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Answer

Use a server-level firewall rule to restrict access to the tenant's IP.

Answer

Create a server-level login and assign it as db_owner on all databases.

Answer

Create a database-level firewall rule for each tenant database.