DP-300 Timed Hard Questions — 25 Difficult Questions

Question 1

You are a database architect for a multinational corporation that uses Azure SQL Database for a customer relationship management (CRM) system. The primary database is in the East US region using the Business Critical service tier. The compliance team requires that in the event of a regional disaster, the database can be failed over to a secondary region with zero data loss and an RTO of 30 seconds. Additionally, the secondary region must be able to handle read-only queries during normal operations to reduce load on the primary. You need to design a solution that meets these requirements with the lowest possible latency for write operations. What should you do?

15 minute time limit — choose an answer to begin.

Accepted Answer

Deploy a failover group with a readable secondary in a paired region using Business Critical, and ensure the failover is planned to achieve zero data loss.. A failover group with a readable secondary in a paired region using Business Critical meets all requirements: it provides zero data loss via planned failover, an RTO under 30 seconds, and read-only query offloading. Business Critical tier supports local read replicas and geo-replication with the lowest write latency because it uses local SSD and in-memory OLTP. The failover group also manages the listener endpoint for seamless redirection.

Answer

Create a secondary database in a paired region using active geo-replication and configure it as readable.

Answer

Deploy a failover group with a secondary database in a paired region using Business Critical, but do not make the secondary readable.

Answer

Configure zone redundancy on the existing Business Critical database in East US.

Question 2

Your company uses GitHub Actions to deploy changes to Azure SQL Database. You need to ensure that rollbacks can be performed automatically if a deployment fails. Which strategy should you implement?

Accepted Answer

Create a database copy or snapshot before each deployment. Creating a database copy or snapshot before each deployment provides a restore point that can be used to roll back automatically if the deployment fails. This is the most direct strategy for enabling rollback of Azure SQL Database changes in a CI/CD pipeline. Other options do not provide a database restore point.

Answer

Use Azure App Service deployment slots for the database

Answer

Use GitHub Actions deployment environments with approval gates

Answer

Use Azure SQL Managed Instance point-in-time restore

Question 3

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

Accepted Answer

Batch transactions and reduce log writes.. Batching transactions reduces the number of log write operations, lowering the log write throughput below the service tier limit and avoiding throttling. Option A is incorrect because increasing the max log size does not affect the log write rate; it only provides more storage. Option C is incorrect because accelerated database recovery (ADR) improves recovery time and may reduce log I/O for crash recovery, but it does not directly reduce the sustained log write throughput from transaction processing. Option D is incorrect because the requirement explicitly states not to move to Business Critical.

Answer

Increase the max log size using ALTER DATABASE.

Answer

Enable accelerated database recovery to reduce log I/O.

Answer

Move to Business Critical tier.

Question 4

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

Accepted Answer

I/O subsystem bottleneck for read operations. PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU. Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Answer

CPU bottleneck

Answer

Blocking between concurrent transactions

Answer

Memory pressure

Question 5

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

Accepted Answer

Select the Intel Software Guard Extensions (Intel SGX) enclave type.. Always Encrypted with secure enclaves on Azure SQL Managed Instance requires the Intel Software Guard Extensions (Intel SGX) enclave type. Intel SGX is the only supported enclave technology for this feature on managed instances, providing a trusted execution environment that protects sensitive data in memory during cryptographic operations.

Answer

Configure the column master key to be stored in Azure Key Vault.

Answer

Enable the enclave attestation policy on the managed instance.

Answer

Enable Virtualization-Based Security (VBS) enclave type.

Question 6

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 7

You are reviewing the ARM template snippet for an Azure SQL Database failover group. The primary server is in East US. The secondary is in West Europe. The readWriteEndpoint has automatic failover with a grace period of 60 minutes. The readOnlyEndpoint is disabled. After a complete outage in East US, what will happen?

Accepted Answer

The failover group will wait 60 minutes, then automatically fail over to West Europe.. With automatic failover enabled on the read/write endpoint and a 60-minute grace period, Azure SQL failover groups wait the full grace period after detecting the primary outage before promoting the secondary. Only after that window elapses does the geo-secondary in West Europe become the new primary. The readOnlyEndpoint setting is irrelevant to read/write failover behavior.

Answer

The failover group will not fail over automatically because the grace period is too long.

Answer

The failover group will immediately fail over to West Europe.

Answer

The failover group requires manual failover because readOnlyEndpoint is disabled.

Question 8

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?

Accepted Answer

Enable soft-delete and purge protection on the Key Vault.. Enabling soft-delete and purge protection on the Key Vault is a best practice because soft-delete retains deleted keys for a configurable retention period (default 90 days), allowing recovery if a key is accidentally deleted. Purge protection prevents permanent deletion of keys even after the soft-delete retention period expires, which is critical for TDE because if the key is permanently lost, the encrypted database becomes inaccessible. Together, these features ensure that the TDE protector key is never irrevocably lost, maintaining database recoverability and compliance.

Answer

Store the Key Vault in the same resource group as the SQL server.

Answer

Disable Key Vault auditing to reduce costs.

Question 9

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

Accepted Answer

Hyperscale tier with 4 vCores. The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Answer

Business Critical tier with 8 vCores

Answer

Serverless tier with auto-pause

Answer

General Purpose tier with 2 vCores

Question 10

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database. Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 11

You are designing a secure architecture for Azure SQL Managed Instance. You need to ensure that all connections to the instance are encrypted and that the instance can only be accessed from a specific virtual network. Which TWO configurations should you implement?

Accepted Answer

Configure the managed instance with the 'Force encryption' property set to Enabled.. Option C is correct because setting the 'Force encryption' property to Enabled on Azure SQL Managed Instance enforces TLS encryption for all client connections, ensuring data in transit is encrypted. Option E is correct because Azure SQL Managed Instance must be deployed inside a delegated subnet within an Azure virtual network, which inherently restricts access to that specific VNet and its connected networks. Option A is incorrect because service endpoints are used for PaaS services like Azure SQL Database or Storage, not for SQL Managed Instance, which requires native VNet integration via a delegated subnet. Option B is incorrect because a managed instance cannot be deployed outside a virtual network; VNet deployment is mandatory. Option D is incorrect because private endpoints are used to access PaaS services privately, but SQL Managed Instance already resides in a VNet and does not use private endpoints for its own connectivity.

Answer

Configure a service endpoint on the subnet of the managed instance.

Answer

Deploy the managed instance outside a virtual network.

Answer

Attach a private endpoint to the managed instance.

Question 12

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

Accepted Answer

Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.. REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Answer

Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.

Answer

Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.

Answer

Rebuild the entire database by creating a new database and copying data.

Question 13

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 14

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

Accepted Answer

Application-consistent snapshots are not enabled in the ASR replication policy.. Azure Site Recovery replicates VM disks, and by default it takes crash-consistent snapshots, which capture the disk state as if the power were cut — the database may have in-flight transactions and be inconsistent on recovery. Enabling application-consistent snapshots in the replication policy triggers VSS (Volume Shadow Copy Service) to quiesce the database and flush transactions before snapshotting, ensuring a consistent recovery point.

Answer

The secondary region is not in the same geo as the primary.

Answer

The backup retention period is too short.

Answer

The database is not part of an Always On availability group.

Question 15

Refer to the exhibit. You are configuring a backup policy for an Azure SQL Database. The database is in the East US region. You need to ensure that the database can be restored to a different region in the event of a regional disaster. Which setting in the exhibit must be correctly configured?

Accepted Answer

The backupStorageRedundancy is set to Geo.. BackupStorageRedundancy must be set to 'Geo' to enable geo-redundant backups that can be restored to a different region in case of a regional disaster. Option A is incorrect because the geoBackupPolicy state being Enabled simply enables the geo-backup policy, but it does not ensure geo-redundancy if the storage redundancy is not set to Geo. Option B is incorrect because the geoBackupPolicy location indicates where the geo-backup is stored, but the key setting for cross-region restore is the storage redundancy, not the location. Option D is incorrect because weeklyRetention defines how long weekly backups are retained, which does not affect the ability to restore to a different region.

Answer

The geoBackupPolicy state is Enabled.

Answer

The geoBackupPolicy location is set to eastus2.

Answer

The weeklyRetention is set to P4W.

Question 16

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

Accepted Answer

Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.. Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Answer

Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

Answer

Create a script that runs periodically to check and enable Defender for SQL on all databases.

Answer

Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

Question 17

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 18

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

Accepted Answer

Configure per-database min eDTU for the critical database. In an Azure SQL elastic pool, per-database min eDTU (or min vCore) guarantees a floor of resources that a specific database can always consume, even when other databases in the pool are competing for resources. Setting a min eDTU on the critical database ensures it is never starved during contention. This directly addresses the requirement that the critical database 'always gets a minimum amount of resources.'

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier

Question 19

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Accepted Answer

File tables and FILESTREAM.. Option A is correct because Azure SQL Managed Instance supports FILESTREAM and FileTable, but they are disabled by default and require specific configuration (enabling the FILESTREAM option and provisioning a file share/storage), so they must be explicitly planned for during migration. Option B is correct because SQL Managed Instance supports cross-database queries with three-part names within the same instance, unlike Azure SQL Database; however, this capability depends on all referenced databases residing on the same managed instance, so it is a key planning consideration. Option E is correct because SQL Server Agent is available on SQL Managed Instance and supports PowerShell job steps via the PowerShell subsystem, but the agent must be enabled and the PowerShell subsystem configured, making it an important migration consideration. Option C is not marked correct because SSIS is not hosted inside SQL Managed Instance; SSISDB and package execution require Azure-SSIS Integration Runtime in Azure Data Factory, so it is not a native Managed Instance consideration in the same way. Option D is not marked correct because contained databases are supported on SQL Managed Instance and do not represent a special migration blocker or planning constraint compared to the marked options.

Answer

SQL Server Integration Services (SSIS) packages.

Answer

Use of contained databases.

Question 20

You have an Azure SQL Database in the General Purpose tier. You need to implement high availability with automatic failover to a secondary database in a different Azure region. The solution must provide an RPO of 5 seconds and RTO of 1 hour. Which TWO components must you configure? (Select TWO.)

Accepted Answer

Configure active geo-replication. Option C (Configure active geo-replication) is correct because active geo-replication is the Azure SQL Database feature that asynchronously replicates a primary database to up to four readable secondaries in different regions, and it supports the required RPO of 5 seconds (typical replication lag is under 5 seconds) with manual or scripted failover. Option D (Create a secondary database in a different Azure region) is correct because active geo-replication requires you to provision the secondary database in a different Azure region, which is the target for failover and satisfies the cross-region requirement. Option A is not required because Business Critical is not necessary to meet a 5-second RPO and 1-hour RTO; General Purpose with active geo-replication can meet these targets. Option B is incorrect because zone redundancy provides intra-region high availability across availability zones, not cross-region failover. Option E is incorrect because auto-failover groups, while they also provide cross-region replication and automatic failover, are a separate feature and are not the components being asked for here; the question specifically requires configuring active geo-replication and its secondary database.

Answer

Change the service tier to Business Critical

Answer

Enable zone redundancy on the primary database

Answer

Create an auto-failover group