DP-300 Practice Test 4 — 25 Questions

Question 1

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Accepted Answer

Use a service endpoint. A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Answer

Use Always Encrypted

Answer

Use a public endpoint with a firewall rule

Answer

Use a site-to-site VPN gateway and connect to public endpoint

Question 2

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 (also called the 'grace period' or 'data loss grace period') is the maximum time the failover group waits before forcing a failover when the primary region is unavailable. If the outage duration is shorter than this grace period, Azure SQL Database will not trigger automatic failover because it still hopes to recover the primary without data loss. Here, the grace period is 3600 seconds (60 minutes) and the outage lasted only 30 minutes, so the condition for automatic failover was not met. Thus, the most likely reason is that the outage duration is less than the grace period.

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 3

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.. Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault allows you to encrypt the database backup files using a key that you control. When TDE is enabled and configured with a customer-managed key (CMK) stored in Azure Key Vault, Azure SQL Managed Instance automatically encrypts backups with the same TDE protector key, meeting the requirement for customer-managed backup encryption.

Answer

Configure Always Encrypted with keys stored in Key Vault.

Answer

Use Azure Storage Service Encryption to encrypt the backup files.

Answer

Enable backup encryption using a certificate stored in the managed instance.

Question 4

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

Accepted Answer

Built-in high availability with a standby replica. Azure SQL Database in the General Purpose service tier includes built-in high availability with a standby replica, which is provisioned automatically and used to fail over during planned maintenance or unplanned outages. This built-in HA ensures the database remains available during patching events without requiring the customer to configure geo-replication or zone redundancy. The standby replica is maintained synchronously and failover is automatic.

Answer

Active geo-replication

Answer

Zone-redundant availability

Answer

Business Critical service tier

Question 5

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

Accepted Answer

4 weeks.. In the ARM template snippet, the weeklyRetention property is set to 'P4W' (ISO 8601 duration format), which means 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Answer

4 days.

Answer

4 months.

Answer

4 years.

Question 6

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

Accepted Answer

In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.. Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Answer

Configure a vulnerability assessment recurring scan and email the report.

Answer

Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.

Answer

Enable SQL auditing and stream logs to a Log Analytics workspace.

Question 7

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Accepted Answer

Create a nonclustered index on the columns used in WHERE clause. Option B is correct because a nonclustered index on the WHERE-clause columns gives the optimizer a narrow access path, so it can seek directly to matching rows instead of scanning the base table or clustered index, which lowers the number of 8 KB pages read and therefore logical reads. Option E is correct because updating statistics gives the optimizer accurate cardinality estimates, enabling better plan choices such as index seeks and appropriate join strategies, which reduces the pages touched per execution. Option A is not correct because forcing an index with a hint does not by itself reduce logical reads and can even increase them if the optimizer's original plan was better. Option C is not correct because wrapping the query in a stored procedure mainly aids plan reuse and parameterization, not the number of pages read per execution. Option D is not correct because increasing max memory affects buffer pool caching and physical I/O, not the logical read count, which is measured independently of whether pages come from memory or disk.

Answer

Add query hints to force index usage

Answer

Rewrite the query as a stored procedure

Answer

Increase the database max memory setting

Question 8

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Accepted Answer

Service level objective (SLO). The maximum log rate for an Azure SQL Database in the Business Critical tier is determined by the service level objective (SLO) and the number of vCores. Option A is correct because the SLO defines the performance tier and hardware configuration, which directly caps the log generation rate (e.g., Business Critical with 4 vCores has a specific log rate limit). Option B is correct because within a given SLO, the log rate scales with the number of vCores—more vCores allow a higher maximum log rate. Option C is incorrect because backup retention period affects storage and recovery, not log throughput. Option D is incorrect because Azure SQL Database manages log files automatically; the number of log files is not a user-configurable factor affecting log rate. Option E is incorrect because page compression reduces data size and I/O but does not directly determine the maximum log generation rate.

Answer

Backup retention period

Answer

Number of log files

Answer

Page compression level

Question 9

You have an Azure SQL Managed Instance in the East US region. To meet a 1-hour RPO and 2-hour RTO, you configure a failover group with a secondary in West US using automatic failover. During a test, you notice that the RTO is consistently 10 minutes longer than required. What is the most likely cause?

Accepted Answer

The failover group has a grace period of 20 minutes configured.. The failover group's automatic failover includes a grace period (default 20 minutes) that delays the actual failover after detecting unavailability. This grace period directly contributes to the RTO, explaining the 10-minute exceedance of the required 2-hour RTO. Option A is incorrect because Microsoft Entra ID authentication does not add noticeable latency to the failover process; authentication occurs independently of replication and failover. Option B is incorrect because asynchronous commit is the standard replication mode for failover groups and does not inherently cause additional delay beyond the grace period; the issue is not the commit mode but the waiting period before failover. Option D is incorrect because the secondary database is automatically seeded when the failover group is created; no manual restore is needed.

Answer

The failover group uses Microsoft Entra ID authentication, which adds latency.

Answer

The secondary database is configured with asynchronous commit, causing delay.

Answer

The secondary database is not seeded and needs to be restored.

Question 10

You manage an Azure SQL Database in the East US region. The database uses the General Purpose service tier with geo-redundant backup storage. You need to ensure that in the event of a regional outage, you can restore the database to the West US region with the least possible downtime. What should you use?

Accepted Answer

Configure active geo-replication to a secondary server in West US.. Active geo-replication to a secondary server in West US is the correct choice because it maintains a continuously synchronized secondary database in the target region, enabling a fast failover with minimal downtime if the primary region fails. Geo-restore can restore to West US from geo-redundant backups, but it has a much longer recovery time than failing over to an already synchronized secondary. Creating a copy-only backup and manually transferring it is slow and not automated. Point-in-time restore is limited to the same region and cannot restore directly to West US.

Answer

Use the geo-restore feature to restore from the geo-redundant backups to a new database in West US.

Answer

Create a copy-only backup and manually transfer it to West US.

Answer

Perform a point-in-time restore to a new database in West US.

Question 11

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

Accepted Answer

Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email. Azure SQL Database does not expose SQL Server Agent, and index fragmentation is not a native Azure Monitor metric, so the correct approach is an Azure Automation runbook that connects to the database, queries sys.dm_db_index_physical_stats, evaluates fragmentation, and sends email via SendGrid, Office 365, or SMTP. This is the standard pattern for custom T-SQL-based monitoring and alerting in Azure SQL Database.

Answer

Create an Azure Monitor alert based on fragmentation

Answer

Use Elastic Database Jobs to check fragmentation and send email

Answer

Configure SQL Agent job to send email

Question 12

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

Accepted Answer

sys.dm_os_wait_stats. C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.

Answer

sys.dm_exec_query_stats

Answer

Query Store Wait Stats in SSMS

Answer

sys.dm_db_index_usage_stats

Question 13

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

Accepted Answer

Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.. Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Answer

Implement row-level security (RLS) to filter rows based on user identity.

Answer

Change the encryption type from randomized to deterministic encryption.

Answer

Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.

Question 14

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Accepted Answer

Create a plan guide for the previous plan and then disable the automatic tuning recommendation for that query.. When automatic tuning forces a plan that causes regression, the correct remediation is to create a plan guide that pins the previous (good) plan for that specific query, then disable the automatic tuning recommendation for that query so Azure SQL doesn't re-force the bad plan. This is a targeted fix that preserves automatic tuning for other queries.

Answer

Reindex the tables involved in the query.

Answer

Disable automatic tuning for the database.

Answer

Run DBCC FREEPROCCACHE to clear the plan cache.

Question 15

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.. Initiating an unplanned failover from the primary to the secondary is the correct action because the primary region is experiencing a full outage, and unplanned failover is designed for such disaster scenarios. It promotes the secondary database to become the new primary with minimal data loss, typically within the recovery point objective (RPO).

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 16

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

Accepted Answer

Query Store with custom retention settings. Query Store is the only feature that persists query execution plans and runtime statistics in the database, enabling historical performance analysis and before/after comparisons for index changes. It captures query text, plans, and runtime metrics over time, which is exactly what is needed to compare performance before and after index modifications. Custom retention settings allow you to control how long the data is kept, ensuring you have the necessary history.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 17

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

Accepted Answer

Bicep templates. Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner. Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Answer

SQL Server Data Tools (SSDT) database projects

Answer

T-SQL scripts

Answer

PowerShell scripts

Question 18

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.. Option A is correct because Elastic Database Jobs are a native Azure SQL Database feature that lets you define and schedule T-SQL scripts (such as ALTER INDEX ... REBUILD/REORGANIZE) across one or many databases without needing an external orchestrator. Option C is correct because Azure SQL Database's built-in automatic tuning can automatically create and drop indexes based on workload analysis, which is a native, server-side index maintenance capability. Option B is not native to Azure SQL Database: Azure Automation is a separate Azure service, and its PowerShell runbooks must connect externally to run T-SQL. Option D is wrong because SQL Server Agent is not available in Azure SQL Database (it exists in Azure SQL Managed Instance and SQL Server on Azure VMs). Option E is wrong because Azure Data Factory is a data integration/ETL service, not an index maintenance mechanism.

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 19

Your company has an Azure SQL Database that uses a failover group with a secondary in a different region. You need to ensure that read-only queries are directed to the secondary database to offload the primary. What should you configure?

Accepted Answer

Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener.. To direct read-only queries to the secondary database in a failover group, you must use the failover group listener and add 'ApplicationIntent=ReadOnly' to the connection string. The listener routes read-write connections to the primary and read-only connections to the secondary, based on the ApplicationIntent parameter. This is the built-in read-scale-out mechanism for failover groups.

Answer

Use a Traffic Manager profile to route read traffic.

Answer

Enable read-scale for the database.

Answer

Configure a separate connection string pointing to the secondary server.

Question 20

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

Accepted Answer

Configure the maintenance window for the Managed Instance.. Azure SQL Managed Instance provides a built-in maintenance window feature that allows you to schedule patching of the underlying OS and SQL Server engine with minimal downtime. Configuring the maintenance window ensures updates are applied during a specified time, reducing impact on production workloads.

Answer

Use an Elastic Job agent to run a script that applies updates.

Answer

Schedule a manual patching using the Azure portal.

Answer

Use Azure Update Manager to schedule patching.