Hard DP-300 practice questions

You manage a three-node Always On availability group on Azure SQL VMs across a single region. The configuration uses Node and File Share Majority quorum with the file share witness hosted on the primary node's local disk. During a planned reboot of the primary node for OS patching, the availability group unexpectedly goes offline and no automatic failover occurs to either secondary replica. What is the root cause?

A healthcare application stores patient Social Security numbers and salary values in an Azure SQL Database. Compliance requires that these columns remain encrypted at all times, including in memory during query processing, and that the database engine never has access to the plaintext encryption keys. However, analysts must run range queries (for example, WHERE Salary BETWEEN 50000 AND 90000) directly against the encrypted salary column without decrypting the data on the client first. Which configuration meets all of these requirements?

You manage a three-node Always On availability group on Azure SQL Server VMs. The primary replica hosts a high-throughput OLTP workload, and business users complain that nightly full backups cause CPU and I/O contention on the primary. You want to offload backup operations away from the primary while still meeting your recovery requirements. The availability group has two synchronous secondary replicas configured for automatic failover. What should you configure?

An Azure SQL Database (compatibility level 150) hosts a large rowstore-only reporting table. Analysts run heavy aggregation queries (SUM, GROUP BY) over millions of rows, and execution plans show these queries process rows one at a time, consuming significant CPU. There are no columnstore indexes, and business rules prohibit adding them. Which Intelligent Query Processing feature is designed to improve these CPU-bound analytic queries without a columnstore index?

You are configuring an Elastic Job agent in Azure SQL Database to run a nightly index maintenance script across 40 databases in a target group. The job must connect to each target database and also write execution output to a central logging database. The job repeatedly fails with authentication errors when connecting to the target databases. You confirm the job's database-scoped credential exists in the job database. What must you also do to allow the job to authenticate successfully against every target database?

You manage a set of Azure SQL databases protected by an auto-failover group with automatic failover enabled. The application requires that regional failover happen automatically during a prolonged outage, but the business will not tolerate silent data loss during transient network blips. You configure the failover group's read/write failover policy to Automatic and set GracePeriodWithDataLossHours to 4. During a real regional outage where the primary region is unreachable and replication cannot catch up, what behavior will occur?

You are designing high availability for a mission-critical SQL Server workload on Azure VMs. The application requires instance-level protection (SQL Agent jobs, linked servers, logins, and the entire instance must fail over together), and the business mandates automatic failover with no data loss within a single Azure region. The application vendor does not support databases in an availability group. Which solution should you recommend?

A stored procedure named dbo.uspArchiveOrders performs cross-schema DML that the calling application account (an Entra ID contained user named app_worker) is not permitted to execute directly. You want the procedure to run successfully when called by app_worker without granting that user broad permissions on the underlying tables, and without relying on ownership chaining (the procedure touches objects in a different schema with a different owner). Which approach follows least privilege?

A financial services company runs a mission-critical application on Azure SQL Managed Instance in the East US region. Compliance requires that the database remain readable and available for reporting even if the primary region experiences an outage, and that failover to a secondary region can occur with minimal manual intervention while preserving the same connection endpoint for the application. Which capability should you implement to meet these requirements?

You maintain a log shipping configuration between a primary SQL Server instance and a secondary instance for disaster recovery. The primary server is still online and healthy, but you must perform a planned, controlled failover to the secondary as part of a datacenter migration with zero data loss. Which sequence of actions correctly performs the role change?

A company currently runs a 250 GB database on an Azure SQL Managed Instance that was originally chosen for SQL Server Agent and cross-database queries. Those instance-level features are no longer used. To reduce cost, the team wants to move this single database to Azure SQL Database (Hyperscale) with the least possible downtime, keeping the application online during most of the migration. Which migration approach should you use?

You manage a mission-critical SQL Server 2022 workload running on Azure VMs in the East US region. Leadership defines a disaster recovery requirement of RPO = 0 (no data loss on failover) and an RTO of a few minutes, and the solution must protect against a full regional outage. You already have a two-node Always On availability group with synchronous-commit replicas within East US. Which change best meets the cross-region RPO = 0 requirement?

A retail company is deploying a new order-processing database on Azure SQL Database. The database is expected to grow rapidly to several terabytes within a year. Traffic is highly unpredictable, with long idle periods overnight and sudden spikes during flash-sale events. The architecture team wants automatic scaling of compute during spikes, the ability to pause compute during idle periods to reduce cost, and support for databases that can grow beyond 4 TB without a full data reload. Which deployment configuration should you recommend?

You administer an Azure SQL Managed Instance that hosts both a critical OLTP application and an ad-hoc reporting group that runs unpredictable, memory-heavy queries. During peak periods the ad-hoc queries consume large memory grants, causing the OLTP workload to suffer RESOURCE_SEMAPHORE waits. You want to cap the maximum memory grant percentage available to the ad-hoc queries without affecting the OLTP workload. Which approach should you use?

You manage an Azure SQL Managed Instance that hosts a database for a regulated healthcare application. The compliance team requires a server-level audit that captures every CREATE, ALTER, and DROP statement executed against database objects (such as tables, views, and stored procedures), while minimizing the volume of unrelated events logged. Which server audit specification action group should you add to satisfy this requirement?

You maintain a SQL Server Agent job that runs a CmdExec step. The step uses the token $(ESCAPE_SQUOTE(SRVR)) to pass the server name to a batch file. After upgrading to a newer SQL Server version, the job step now fails immediately with an error stating that the job step contains an unsafe, unescaped token. Other steps that reference $(DATE) and $(TIME) run without error. What is the MOST likely cause and correct fix?

You are deploying a mission-critical SQL Server 2022 workload on an Azure virtual machine. The application is write-intensive with frequent, large transactions, and you observe high write latency during peak load. You must configure the VM storage to maximize performance for the transaction log and tempdb while following Microsoft's recommended practices for SQL Server on Azure VMs. Which storage configuration should you implement?

You are using Azure Database Migration Service (DMS) to perform an online migration from an on-premises SQL Server 2019 instance to Azure SQL Managed Instance. The initial full backup restores successfully, but the migration stalls during the continuous log-shipping phase and never reaches the point where you can complete the cutover. You inspect the source database and confirm it is in the SIMPLE recovery model. What is the most likely cause of the failure?

A healthcare application already stores patient Social Security numbers in a plaintext NVARCHAR column of an Azure SQL Database. Compliance now requires this column to be protected with Always Encrypted using randomized encryption, and administrators must be able to perform equality lookups on the encrypted data in the future. The DBA wants to encrypt the existing data in place — without exporting it to a client tool — and avoid re-inserting rows through a middle-tier driver. Which configuration allows the encryption operation and future equality searches to be performed while meeting these goals?