DP-300 Practice Test 2 — 30 Questions

Question 1

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Accepted Answer

Connect to the instance, create a job, define steps, schedule, then enable and start.. Connect to the instance, create a job, define steps, schedule, then enable and start.

Answer

Create a job, define steps, schedule, connect to the instance, then enable and start.

Answer

Connect to the instance, define steps, create a job, schedule, then enable and start.

Answer

Connect to the instance, create a job, schedule, define steps, then enable and start.

Question 2

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 3

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 4

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.. Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Answer

Create an Elastic Database Job that runs a T-SQL script to export the database.

Answer

Deploy an Azure Data Factory pipeline with a Copy activity to export the database.

Answer

Use an Azure Logic App with the SQL Server connector to export the database.

Question 5

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing. Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 6

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 7

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 8

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 9

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

Accepted Answer

Create an auto-failover group with a secondary replica in West US.. An auto-failover group in Azure SQL Managed Instance provides a secondary replica in a paired region with automatic failover, meeting the requirement for recovery within 1 hour. It uses asynchronous replication and allows a single connection string to fail over automatically, ensuring business continuity during a regional disaster.

Answer

Configure log shipping to a secondary instance in West US.

Answer

Enable geo-redundant backup storage and use geo-restore.

Answer

Deploy an Always On availability group with a synchronous secondary in West US.

Question 10

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 11

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

Accepted Answer

Use Azure DevOps with a database project and release pipelines.. Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Answer

Use Azure Data Factory to run stored procedures for schema changes.

Answer

Use SQL Server Agent jobs to run deployment scripts on schedule.

Answer

Use Azure Automation with PowerShell scripts to execute T-SQL scripts.

Question 12

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Accepted Answer

Log write latency. The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance. Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Answer

Log IO percent

Answer

Transaction log usage

Answer

Average IO latency

Question 13

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

Accepted Answer

Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.. Creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Answer

Use TLS 1.2 for all connections to the database.

Answer

Configure server-level firewall rules to allow only the application VM IP addresses.

Answer

Enable forced tunneling on the application VMs to route all traffic through the on-premises network.

Question 14

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

Accepted Answer

Increase the log rate limit by scaling up the service level objective. In Azure SQL Database Hyperscale, log rate is governed by the service level objective, and when the log generation rate exceeds the limit the workload is throttled. Scaling up to a higher service level objective raises the log rate limit, directly relieving the throttling. This is the documented remedy for log rate throttling in Hyperscale.

Answer

Increase max degree of parallelism

Answer

Reduce backup retention period

Answer

Add more compute replicas

Question 15

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

Accepted Answer

Zone-redundant deployment for Business Critical tier. Option A is correct because Azure SQL Managed Instance in the Business Critical service tier supports zone-redundant deployment, which places the primary and secondary replicas across different availability zones to protect against datacenter-level failures. Option C is correct because SQL Managed Instance includes a built-in Always On Availability Groups mechanism that automatically provisions and manages replicas for high availability, with the Business Critical tier using local SSD and multiple secondary replicas. Database mirroring (B) is a deprecated SQL Server feature and is not offered as a supported HA option in Azure SQL Managed Instance. Log shipping to a secondary instance (D) is not a built-in HA feature of SQL Managed Instance, though it can be configured manually for disaster recovery scenarios. Windows Server Failover Clustering (E) is an underlying infrastructure concept used by SQL Server on-premises and Azure VMs, but it is not exposed or supported as a user-configurable HA feature in Azure SQL Managed Instance.

Answer

Database mirroring

Answer

Log shipping to a secondary instance

Answer

Windows Server Failover Clustering

Question 16

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

Accepted Answer

The AuditActionGroup specified does not capture SELECT operations.. The script likely specifies an AuditActionGroup that does not include the group responsible for capturing SELECT operations. In Azure SQL Database auditing, SELECT operations are captured by the SUCCESSFUL_SCHEMA_OBJECT_ACCESS_GROUP or similar action groups. If the configured AuditActionGroup omits this group, SELECT statements will not be logged, even though other operations may be audited correctly.

Answer

The retention days are set too low, causing logs to be overwritten.

Answer

The storage endpoint is incorrectly formatted.

Answer

The storage account access key is invalid.

Question 17

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

Accepted Answer

Failover of multiple databases in a single group.. Failover groups extend active geo-replication by allowing you to manage failover for a group of databases as a single unit. This simplifies the failover process when you have multiple databases that must be failed over together to maintain application consistency. Option B is correct because failover groups support the coordinated failover of multiple databases, which active geo-replication alone does not.

Answer

Support for manual failover.

Answer

Readable secondary replicas for read-scale workloads.

Answer

Synchronous data replication to the secondary.

Question 18

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

Accepted Answer

Enable advanced threat protection using Microsoft Defender for Cloud.. Option A is correct because enabling advanced threat protection via Microsoft Defender for Cloud provides detection and alerting for anomalous activities and potential vulnerabilities on Azure SQL Managed Instance, which is a core detective control in a defense-in-depth strategy. Option B is correct because server-level auditing captures database events and writes them to an audit log, providing the monitoring and traceability required for a layered security approach. Option D is correct because configuring network security groups (NSGs) on the subnet restricts inbound traffic to the managed instance, enforcing network-level segmentation and reducing the attack surface, which is a fundamental preventive control. Option C is not a security control; columnstore indexes are a performance optimization for analytical workloads and do not contribute to defense-in-depth. Option E is not the best fit because application roles manage permissions within a database but do not address the broader layered security controls expected in a defense-in-depth strategy for Azure SQL Managed Instance.

Answer

Create columnstore indexes on large tables to improve query performance.

Answer

Create application roles in each database to manage permissions.

Question 19

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

Accepted Answer

Create an ARM template that defines the database and deploy it from your CI/CD pipeline.. B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Answer

Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.

Answer

Set up an Elastic Database Job that runs a CREATE DATABASE statement.

Answer

Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.

Question 20

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

Accepted Answer

Long-term retention (LTR) policy. Long-term retention (LTR) policy is the correct feature for keeping database backups for 10 years, as it supports retention of full backups for up to 10 years. Point-in-time restore (PITR) is limited to a maximum of 35 days and cannot meet the 10-year requirement. Geo-redundant storage (GRS) provides geographic redundancy for backups but does not extend the retention period. Automated backups are retained for a maximum of 35 days by default and cannot be configured for 10-year retention.

Answer

Geo-redundant storage (GRS)

Answer

Automated backups

Answer

Point-in-time restore (PITR)