DP-300 Practice Test 35 — 15 Questions

Question 1

You have a SQL Server on Azure Virtual Machine running a critical OLTP workload. You need to achieve high availability with automatic failover and no shared storage. The solution must use Azure features and avoid complex clustering configurations. What should you implement?

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

Accepted Answer

Configure an Always On Availability Group with automatic failover.. Always On Availability Groups provide high availability and automatic failover without requiring shared storage, and they leverage Azure features like internal load balancer. Option B (log shipping) offers disaster recovery but not automatic failover. Option C (database mirroring) is deprecated and not recommended for new implementations. Option D (Failover Cluster Instance) requires shared storage, which is not desired.

Answer

Set up log shipping to a secondary VM.

Answer

Use database mirroring with automatic failover.

Answer

Deploy a Windows Server Failover Cluster Instance (FCI) using Azure Shared Disks.

Question 2

You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?

Accepted Answer

Create a failover group with the secondary instance in North Europe.. SQL Managed Instance supports failover groups for automated failover between regions, and the secondary instance in a failover group is readable and supports backups. Option A is incorrect because log shipping is not supported for SQL Managed Instance. Option B is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate solution. Option C is incorrect because the secondary instance in a failover group is readable and can be used for read-only workloads.

Answer

Set up log shipping from West Europe to North Europe.

Answer

Configure active geo-replication between the instances.

Answer

Create a failover group, but note the secondary is not readable.

Question 3

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

Accepted Answer

Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.. Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous. Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Answer

Set up an Azure Logic App with a failover trigger from Azure Monitor.

Answer

Configure automatic failover policy in the failover group.

Answer

Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

Question 4

You are managing an Azure SQL Database in the General Purpose service tier with 100 DTUs. The database supports an e-commerce application. Over the past week, you notice that CPU usage frequently reaches 100% during peak hours, causing query timeouts. You have identified that the most expensive query is a SELECT statement that joins five tables and returns aggregated sales data. You need to reduce CPU pressure without changing the service tier or adding indexes. What should you do?

Accepted Answer

Use Query Store to identify and force a more efficient execution plan.. Using Query Store to identify and force a more efficient execution plan can reduce CPU usage without changing the service tier or adding indexes. Option A is incorrect because scaling to Business Critical changes the service tier, which is not allowed. Option B is incorrect because increasing DTUs changes the performance level and may increase cost, and it doesn't address the root cause of the expensive query. Option D is incorrect because adding indexes is explicitly prohibited by the requirement.

Answer

Scale up to the Business Critical tier.

Answer

Increase the DTUs to 200.

Answer

Add a nonclustered index on the join columns.

Question 5

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

Accepted Answer

Log Analytics Workspace. Options B and C are correct because Azure Monitor Alerts (C) can be configured to monitor performance metrics of Azure SQL Database and trigger actions when thresholds are exceeded. Log Analytics Workspace (B) collects and analyzes the diagnostic logs and metrics, enabling deeper analysis and alert rules. Option A (Azure Sentinel) is a SIEM tool for security monitoring, not primarily for performance alerts. Option D (Application Insights) is for application-level telemetry, not database-specific performance monitoring. Option E (Azure Advisor) provides recommendations but does not generate real-time alerts.

Answer

Azure Sentinel

Answer

Application Insights

Answer

Azure Advisor

Question 6

Your company has a policy that all Azure SQL Databases must have their performance data (DTU/CPU, memory, IO) monitored and analyzed weekly. You need to automate the generation of a weekly report summarizing the top 10 queries by average CPU time. What should you use?

Accepted Answer

Configure a Power BI dashboard that connects directly to Azure SQL Database Query Store.. SQL Agent is not available in Azure SQL Database, making Option C incorrect. Option A is correct because Power BI can connect directly to Azure SQL Database's Query Store, and with a Power BI Pro license, you can schedule email subscriptions of the report, automating the weekly generation and distribution of the top 10 queries report.

Answer

Create an Azure Monitor Workbook that queries the Query Store.

Answer

Set up a SQL Agent job to run queries against sys.dm_exec_query_stats and email the results.

Answer

Use the Query Performance Insight blade in the Azure portal and schedule an export to a storage account.

Question 7

You are a database administrator for a company that uses Azure SQL Managed Instance. You need to automate the process of patching the operating system and SQL Server engine for all managed instances in a specific region. The automation must minimize downtime and ensure high availability. Which two actions should you include in your automation strategy?

Accepted Answer

Configure a maintenance window using Azure Portal or PowerShell.. Correct options are C and D. Configure a maintenance window (C) to control when patching occurs, minimizing disruption. Deploy a failover group to another region with read-scale replicas (D) to redirect traffic during patching, ensuring high availability. Option A is incorrect because scaling up does not relate to patching; Azure Policy is for governance. Option B is incorrect because Microsoft manages patching for Azure SQL Managed Instance; manual patching is not supported. Option E is incorrect because Azure Update Manager is for IaaS VMs, not for Azure SQL Managed Instance.

Answer

Use Azure Policy to automatically scale up the instance before patching.

Answer

Create an Azure Automation runbook to manually apply OS and SQL patches.

Answer

Enable Azure Update Manager for the managed instances.

Question 8

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

Accepted Answer

Azure SQL Database long-term retention (LTR) backup policy. Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

Answer

Azure Site Recovery

Answer

Azure Blob Storage snapshots

Answer

Azure Backup for SQL Server in Azure VMs

Answer

Azure Storage lifecycle management

Question 9

You need to automate the backup strategy for an Azure SQL Database to meet a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour. Which THREE features should you combine? (Choose three.)

Accepted Answer

Long-term retention (LTR) backup policy.. Options A, B, and D are correct. Long-term retention (LTR) backup policy provides archival backups for compliance and point-in-time restore. Active geo-replication maintains a synchronized readable secondary database with a low RPO (typically less than 5 seconds). Auto-failover groups automate failover and enable a low RTO (typically less than 1 hour). Together, these three features meet the RPO of 5 minutes and RTO of 1 hour. Option C (Azure Backup service) is not used for Azure SQL Database; it is for Azure VMs and on-premises workloads. Option E (Point-in-time restore) can meet the RPO but does not provide automated failover and has a higher RTO, so it is insufficient alone.

Answer

Azure Backup service.

Answer

Point-in-time restore (PITR).

Question 10

You need to automate the deployment of a new Azure SQL Database with a specific performance tier, collation, and firewall rules using infrastructure as code. Which technology should you use?

Accepted Answer

ARM template with Microsoft.Sql/servers/databases resource definition.. Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure, allowing declarative deployment of Azure SQL Database with specific settings like performance tier, collation, and firewall rules. Option A (Azure CLI) and Option C (PowerShell) are procedural scripts that can be used but do not provide true declarative IaC. Option B (SSMS) is a GUI tool, not suitable for automation.

Answer

Azure CLI script with az sql db create command.

Answer

SQL Server Management Studio (SSMS) with a deployment script.

Answer

PowerShell script with New-AzSqlDatabase cmdlet.

Question 11

You are deploying an Azure SQL Database with the above ARM template snippet. How many readable replicas will be available for read-only queries?

Accepted Answer

2. (2). The highAvailabilityReplicaCount property in the ARM template is set to 2, which creates two readable replicas in addition to the primary. With read scale-out enabled, both replicas are available for read-only queries. Option A (3) is incorrect because it counts the primary as a readable replica, but the primary is not used for read-only queries in read scale-out. Option C (1) is incorrect because it underestimates the number of replicas. Option D (4) is incorrect because the value in the template is 2, not 4.

Answer

3

Answer

1

Answer

4

Question 12

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

Accepted Answer

Run a forced failover accepting potential data loss.. A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure. Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.

Answer

Run a planned failover to ensure zero data loss.

Answer

Wait for the primary to come back online and then failover.

Answer

Remove the primary database from the failover group and then failover.

Question 13

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

Accepted Answer

The database is in General Purpose tier and supports zone redundancy.. The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid. Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose. Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Answer

The database has one high-availability replica.

Answer

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Answer

The database uses locally-redundant backup storage.

Question 14

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

Accepted Answer

Failover groups. Options A, C, and E are correct. Failover groups and active geo-replication are built-in disaster recovery features for Azure SQL Database that allow failover to a secondary region. Geo-restore (point-in-time restore to a different region) is also a valid DR method by restoring from geo-replicated backups. Option B (Always On availability groups) is a feature for SQL Server on VMs or on-premises, not for Azure SQL Database. Option D (long-term backup retention) is a backup retention capability, not a disaster recovery method.

Answer

Always On availability groups

Answer

Long-term backup retention

Question 15

You have an Azure SQL Database in the General Purpose tier. You need to implement a disaster recovery solution that provides an RPO of 5 seconds and an RTO of 1 hour. The solution must use Azure PaaS features only. What should you do?

Accepted Answer

Configure active geo-replication to a secondary server in a different region.. Active geo-replication provides asynchronous replication with a typical RPO of 5 seconds, and manual failover can be completed within the 1‑hour RTO window. Failover groups (Option A) are fully supported for Azure SQL Database (not limited to managed instances) and also meet the RPO/RTO targets, but they are designed to manage multiple databases as a group and introduce additional overhead for a single‑database scenario. Geo‑restore (Option C) has an RPO of up to one hour and a much longer RTO. Copy‑only backups (Option D) are not a disaster recovery solution.

Answer

Create a failover group between two servers in different regions.

Answer

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

Answer

Configure copy-only backups to a secondary region.