Medium DP-300 practice questions

A query on an Azure SQL Database joins a large orders table to a filtered set of customers. The number of qualifying customer rows varies dramatically between executions depending on the WHERE predicate values. You observe that some executions use a nested loops join (fast for small inputs) while others suffer badly because a nested loops plan was cached and reused when the input was actually large. The database is on a recent compatibility level. Which Intelligent Query Processing feature will let the engine defer the choice of physical join type until the actual number of rows is known at runtime?

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure hosting a 900 GB database. You must add a third secondary replica in a different region. The corporate WAN link to that region is reliable but you want to avoid manually backing up the database to a file share, copying it across the WAN, and restoring it on the new replica. You need the new replica to be initialized with minimal administrative steps directly over the AG's data movement channel. Which method should you configure when adding the secondary replica?

You manage a two-node Always On availability group on SQL Server 2022 VMs in Azure, with both replicas in the same region and Availability Zone-aligned. The business requires zero data loss (RPO = 0) on automatic failover, but a recent audit shows transaction latency increased after the AG was deployed. The primary replica currently uses asynchronous-commit mode with manual failover. Which configuration change best meets the RPO = 0 automatic failover requirement?

You manage a two-node Always On availability group spread across two Azure regions using SQL Server on Azure VMs. The primary is in East US and the secondary is in West US. During a recent East US zone disruption, the Windows Server Failover Cluster lost quorum and both nodes went offline even though the West US node was healthy. You need to choose a quorum configuration that keeps the cluster online when a single region or node is lost, while minimizing cost and administrative overhead. What should you configure?

You administer an Always On availability group hosted on two SQL Server on Azure VMs deployed across two Azure regions, placing the replicas in different subnets. The availability group listener has two IP addresses, one in each subnet. After a failover to the secondary region, a .NET application experiences long connection delays (roughly 20 seconds) before it eventually connects to the listener. You must minimize these connection times without changing the network topology. What should you do?

You are implementing Always Encrypted (without secure enclaves) on an Azure SQL Database. The Customers table has two sensitive columns: SSN, which must support equality lookups in WHERE clauses and be used as a join key, and MedicalNotes, which is only ever displayed and never filtered. You want the strongest encryption that still satisfies the application's query requirements for each column. Which encryption type should you assign to each column?

You manage a SQL Server 2022 Standard Edition instance running on an Azure VM. The business wants automatic failover for a single 200 GB production database to a second VM in the same region, with a synchronous secondary. Management refuses to pay for Enterprise Edition. Which high availability solution can you configure to meet these requirements while staying on Standard Edition?

Your company runs SQL Server workloads across on-premises data centers and a third-party cloud provider. Management wants a single control plane in Azure to apply consistent governance, automated backups, and point-in-time restore to these SQL instances without migrating the databases into Azure SQL Database or Managed Instance. They also require the ability to run the service where the data currently resides for data residency reasons. Which solution should you recommend?

Your team maintains an ARM template that deploys an Azure SQL logical server and database across dev, test, and prod environments. The administrator login name, database SKU, and server name must change per environment when the pipeline runs, but the resource API versions and a computed database resource ID used in an output must stay consistent regardless of environment. Which approach correctly separates the values that vary at deployment time from the values that should be defined internally?

You manage an Azure SQL Database that stores regulated healthcare data. A compliance auditor requires evidence that captures every successful and failed login attempt to the database, including the client IP address and time of each attempt. You have already enabled Azure SQL Auditing to a Log Analytics workspace. Which audit action group must be included in the audit specification to satisfy this requirement?

You manage an Azure SQL Database with a business application deployed across two regions. You have configured an auto-failover group between the primary server (East US) and secondary server (West US). The application's connection string currently points directly to the primary server's fully qualified server name. After a recent regional outage triggered an automatic failover, the application could not reconnect to the database even though the secondary was successfully promoted. What change should you make to ensure the application automatically follows the primary during future failovers?

You manage an Azure SQL Database supporting a high-concurrency OLTP application. Users intermittently report query timeouts that coincide with statistics being updated. Investigation shows queries are waiting during query compilation because the optimizer triggers a synchronous statistics update on a large table before the plan is generated. You want to reduce this compilation-time blocking without disabling automatic statistics updates. Which database setting change should you make?

Your organization requires that all Azure SQL Database deployments be fully automated, version-controlled in a Git repository, and repeatable so that redeploying the same template does not create duplicate resources or fail if the resources already exist. The DevOps team wants a native Azure infrastructure-as-code approach that integrates with Azure Pipelines and produces a declarative definition of the database and its logical server. Which deployment method should you recommend?

You manage an Azure SQL Database supporting an OLTP application. Over the past weeks, several queries have degraded because commonly filtered columns lack supporting indexes, while other unused indexes consume write overhead. Your team wants Azure SQL to automatically create beneficial indexes and drop redundant ones, but only after validating that each change actually improves performance—and to revert changes that cause regressions. Which configuration should you enable?

You manage an Azure SQL Database supporting an OLTP application. Users intermittently report slow response times after application deployments and statistics updates. Investigation shows that certain queries suddenly choose worse execution plans, causing CPU spikes that resolve on their own hours later. You want Azure to automatically detect these plan regressions and revert to the previously well-performing plan without manual intervention. Which configuration should you enable?

You created an Azure Automation account with a system-assigned managed identity to run a PowerShell runbook that executes maintenance T-SQL against an Azure SQL Database each night. The runbook authenticates using the managed identity, but every execution fails with a login error stating the token principal cannot connect. What must you do so the runbook can authenticate and run T-SQL successfully?

Your company runs a provisioned vCore Azure SQL Database that experiences heavy batch processing every weekend and light activity on weekdays. Management wants to automatically scale the database up to a higher service objective on Friday evening and scale it back down on Monday morning to reduce cost, with no dedicated server to host SQL Server Agent. The solution must run unattended and require minimal ongoing administration. Which approach should you implement?

Your team maintains an Azure DevOps pipeline that provisions a new Azure SQL Database in an existing logical server for each feature branch. The pipeline uses a Linux agent and authenticates with a service principal that has Contributor rights on the resource group. You need to add a single command-line step that creates the database at the S1 service objective and is safe to re-run without error if the database already exists. Which approach should you use?

You are automating the setup of Elastic Jobs to run scheduled index maintenance across 40 Azure SQL databases. Your deployment pipeline uses the Azure CLI. Before you can create an Elastic Job agent named 'jobagent1', which prerequisite must the Azure CLI command reference, and how is it satisfied?

You manage a mission-critical SQL Server 2022 database on an Azure VM. Compliance requires that every full backup written to an Azure Blob Storage container be validated so that any media corruption is detected before the backup is ever needed for a disaster recovery restore. You need to configure the backup process to detect page-level corruption during the backup operation and then confirm that the backup set on the URL is complete and readable, without actually restoring the database. Which combination of actions should you implement?

Your team maintains Azure SQL Database infrastructure using Bicep files stored in a Git repository. Before applying changes to the production environment through an Azure DevOps pipeline, the change-management policy requires that reviewers see exactly which resources would be created, modified, or deleted—without actually deploying anything. Which action should you add to the pipeline to satisfy this requirement?

A financial services company runs a mission-critical OLTP application on Azure SQL Database. The application requires the lowest possible I/O latency and must offload heavy reporting queries to a read-only replica without impacting write performance. The reporting queries must connect to a replica in the same region with in-memory OLTP support. Which service tier should you recommend?

A retail company runs a legacy line-of-business application on SQL Server 2008 R2 hosted on-premises. The database is 300 GB and is used only during store hours (7 AM to 9 PM). Management has approved a scheduled overnight maintenance window and wants to migrate the database to Azure SQL Managed Instance with the least operational complexity. You must recommend the migration approach that meets these requirements while minimizing cost and effort. Which approach should you recommend?

Your company hosts an Azure SQL Database that must be accessed only by application servers running inside a specific Azure virtual network. A security audit requires that no traffic to the database traverse the public internet and that the database's public endpoint be unreachable. Which combination of actions meets these requirements with the least administrative effort?

You manage an Azure SQL Database that stores regulated healthcare data. A compliance audit requires that all client connections be encrypted in transit and that clients validate the server certificate to prevent man-in-the-middle attacks. A legacy application uses a connection string that currently sets Encrypt=false. What must you configure in the application's connection string to meet the audit requirement?