Knowledge + Practice

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 151–225

953 questions total · 13pages · All types, answers revealed

Take a mock exam Exam hub

Page 3 of 13

151

MCQeasy

You are designing a high availability solution for an Azure SQL Database used by a critical application. The application requires no more than 5 seconds of data loss and automatic failover within 1 minute. Which service tier and configuration should you choose?

A.Hyperscale tier with geo-replication and auto-failover group.

B.Hyperscale tier with zone redundancy.

C.General Purpose tier with active geo-replication.

D.Business Critical tier with zone redundancy.

AnswerD

Zone redundancy provides automatic failover within region with zero data loss.

Why this answer

Option C is correct because Business Critical tier with zone redundancy provides automatic failover within seconds and zero data loss. Option A is wrong because General Purpose does not meet RPO requirements. Option B is wrong because Hyperscale zone redundancy does not guarantee automatic failover on its own.

Option D is wrong because Hyperscale geo-replication has a higher RTO.

Full explanation →

152

MCQeasy

You are designing a high availability solution for an Azure SQL Database that is used by a mission-critical application. The database is currently in the Standard tier. You need to minimize downtime during planned maintenance and unplanned failures while keeping costs low. What should you recommend?

A.Use the Basic tier with a failover group.

B.Configure active geo-replication to a secondary region.

C.Upgrade to the Premium tier with zone-redundant configuration.

D.Upgrade to the Standard tier and enable zone redundancy.

AnswerC

Premium tier with zone redundancy provides high availability within a region.

Why this answer

Option B is correct because Zone-redundant configuration (Premium tier) provides high availability within a region by replicating across availability zones, minimizing downtime. Option A is wrong because Standard tier does not support zone redundancy. Option C is wrong because geo-replication adds cost and complexity for cross-region DR, not primarily for HA within region.

Option D is wrong because Basic tier does not support HA features.

Full explanation →

153

MCQmedium

You have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

A.Always Encrypted with a column encryption key.

B.Row-Level Security to filter rows for non-admin users.

C.Dynamic Data Masking with a masking function configured on the column.

D.Azure Policy to enforce masking rules on the database.

AnswerC

Dynamic Data Masking masks data at query time based on user permissions.

Why this answer

Option A is correct because Dynamic Data Masking can be configured via T-SQL or Azure portal and masks data automatically for non-admin users. Option B is wrong because Always Encrypted encrypts data at rest and in transit, but does not mask for non-admins. Option C is wrong because Row-Level Security filters rows, not columns.

Option D is wrong because Azure Policy cannot mask columns.

Full explanation →

154

Multi-Selectmedium

Which TWO actions can you take to optimize query performance in Azure SQL Database without changing the application code? (Choose two.)

Select 2 answers

A.Rewrite queries to use sargable predicates.

B.Upgrade to a higher service tier.

C.Enable row-level security on sensitive tables.

D.Create missing indexes identified by the Database Engine Tuning Advisor.

E.Update statistics on tables with outdated statistics.

AnswersD, E

Index creation can improve query performance without code changes.

Why this answer

Options A and B are correct. Creating missing indexes and updating statistics are common performance optimization tasks that do not require code changes. Option C requires code changes.

Option D is not a direct optimization. Option E is about hardware, but still no code change; however, it's not a typical DBA action for query performance.

Full explanation →

155

MCQmedium

A company has multiple Azure SQL Databases and wants to automate the deployment of schema changes using a CI/CD pipeline. They need to ensure that changes are automatically rolled back if any step fails. Which Azure service should they use?

A.Azure Automation with PowerShell runbooks

B.Azure SQL Database elastic jobs

C.Azure Machine Learning

D.Azure DevOps with Azure SQL Database deployment task

AnswerD

Azure DevOps provides CI/CD pipelines with built-in support for Azure SQL Database deployment, including transactional deployment and rollback.

Why this answer

Azure SQL Database elastic jobs are designed for automating administrative tasks, not CI/CD. Azure Automation with PowerShell runbooks can orchestrate rollbacks but requires custom scripting. Azure DevOps with Azure SQL Deployment task supports transactional deployments with built-in rollback on failure.

Azure Machine Learning is irrelevant.

Full explanation →

156

MCQmedium

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

A.Configure active geo-replication to East US

B.Configure an auto-failover group with a readable secondary in East US

C.Enable zone redundancy for the database

D.Configure geo-restore from the West US database

AnswerA

Active geo-replication with synchronous commit provides zero data loss.

Why this answer

Active geo-replication for Azure SQL Database Business Critical tier provides a continuous, asynchronous replication stream to a secondary region, ensuring minimal data loss (typically a few seconds) during a regional outage. Unlike auto-failover groups, active geo-replication allows you to manually initiate a failover to the secondary region with a Recovery Point Objective (RPO) of up to 5 seconds, which meets the requirement for minimal data loss. The Business Critical tier also supports readable secondaries, but the key here is the replication mechanism that prioritizes low RPO over automatic failover.

Exam trap

The trap here is that candidates often confuse auto-failover groups (which offer automatic failover but with the same RPO as active geo-replication) with active geo-replication, but the question's emphasis on 'failed over' (manual action) and 'minimal data loss' points to active geo-replication as the more precise choice for controlling failover timing and achieving the lowest possible RPO.

How to eliminate wrong answers

Option B is wrong because auto-failover groups use asynchronous replication with a default RPO of up to 5 seconds, but they are designed for automatic failover, not manual failover with minimal data loss; the question emphasizes 'failed over' (manual action) and 'minimal data loss,' which active geo-replication achieves more precisely. Option C is wrong because zone redundancy protects against failures within a single Azure region (e.g., a datacenter failure), not against a full regional outage, and it does not provide a secondary region for failover. Option D is wrong because geo-restore is a point-in-time restore from geo-replicated backups, which can have an RPO of up to 1 hour and an RTO of up to 12 hours, resulting in significant data loss and longer recovery time, not minimal data loss.

Full explanation →

157

MCQhard

Your company plans to migrate a large on-premises SQL Server database to Azure SQL Managed Instance. The database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. How should you manage the TDE protector after migration to ensure minimal administrative overhead and high availability?

A.Use Azure Key Vault to store the TDE protector

B.Use the service-managed TDE key provided by Azure

C.Bring your own certificate and store it in the managed instance

D.Disable TDE after migration and use Always Encrypted instead

AnswerA

Azure Key Vault provides centralized, secure, and highly available key management for TDE.

Why this answer

Option B is correct because using Azure Key Vault as the TDE protector is recommended for centralized management and high availability. Option A is wrong because SQL Managed Instance does not support TDE with a certificate stored in the instance; it must use Azure Key Vault or service-managed. Option C is wrong because service-managed keys have less control and are not recommended for production.

Option D is wrong because TDE cannot be disabled after migration; it must be managed.

Full explanation →

158

MCQeasy

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

A.Scale up to a higher service tier or increase DTUs

B.Scale down to a lower service tier

C.Enable geo-replication

D.Enable read scale-out

AnswerA

Scaling up provides more resources, reducing DTU consumption percentage.

Why this answer

Option C is correct because scaling up to a higher service tier or increasing DTUs provides more resources, reducing pressure. Option A is incorrect because scaling down would worsen performance. Option B is incorrect because geo-replication does not reduce DTU consumption.

Option D is incorrect because read scale-out is for read replicas, not reducing DTUs.

Full explanation →

159

MCQhard

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

A.Set up an Azure Logic App with a failover trigger from Azure Monitor.

B.Configure automatic failover policy in the failover group.

C.Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.

D.Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.

AnswerD

Runbook can automate planned failover without data loss.

Why this answer

Option C is correct because Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet which supports planned failover without data loss. Option A is wrong because failover group automatic failover is for unplanned events. Option B is wrong because T-SQL ALTER DATABASE FAILOVER is for manual failover but not automated.

Option D is wrong because Logic Apps can call REST API but Runbooks are more appropriate.

Full explanation →

160

MCQeasy

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

A.Automatic index management (DROP INDEX)

B.Automatic index management (CREATE INDEX)

C.Automatic plan correction (FORCE_LAST_GOOD_PLAN)

D.Intelligent Insights

AnswerC

Detects regressions and forces previous good plan.

Why this answer

Option A is correct because the FORCE_LAST_GOOD_PLAN option in automatic tuning automatically forces a plan when a regression is detected. Option B is wrong because CREATE INDEX automates index creation, not plan forcing. Option C is wrong because DROP INDEX is for unused indexes.

Option D is wrong because Intelligent Insights provides analysis but does not automatically force plans.

Full explanation →

161

MCQeasy

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

A.Set the database to use contained database authentication.

B.Configure a server-level firewall rule to allow only Entra ID IPs.

C.Enable 'Azure AD-only authentication' on the Azure SQL logical server.

D.Set the Entra ID admin for the server and disable SQL authentication.

AnswerC

This prevents SQL authentication connections.

Why this answer

Option C is correct because enabling 'Azure AD-only authentication' on the Azure SQL logical server enforces that all connections must use Microsoft Entra ID authentication and blocks any SQL authentication attempts, even if SQL logins exist. This setting directly meets the security policy requirement that no SQL authentication users are created and all connections use Entra ID.

Exam trap

The trap here is that candidates often assume setting the Entra ID admin and disabling SQL authentication manually is sufficient, but the 'Azure AD-only authentication' property is a separate, explicit enforcement mechanism that must be enabled to fully block SQL authentication at the server level.

How to eliminate wrong answers

Option A is wrong because setting the database to use contained database authentication allows contained database users to authenticate with SQL authentication, which would violate the policy that no SQL authentication users are created. Option B is wrong because configuring a server-level firewall rule to allow only Entra ID IPs does not enforce authentication method; it only restricts network access by IP address, and Entra ID authentication is not tied to specific IPs. Option D is wrong because setting the Entra ID admin and disabling SQL authentication via the portal or T-SQL is not a server-level setting that fully enforces the policy; the 'Azure AD-only authentication' property must be explicitly enabled to block all SQL authentication attempts, including those from the server admin.

Full explanation →

162

MCQeasy

You are designing a disaster recovery plan for a SQL Server 2022 on Azure VM. The solution must provide automatic failover within seconds and zero data loss. Which SQL Server feature should you use?

A.Backup and restore to another region

B.Failover cluster instance (FCI) with shared storage

C.Always On Availability Group with synchronous commit

D.Log shipping

AnswerB

FCI provides automatic failover and zero data loss.

Why this answer

Option A is correct because SQL Server failover cluster instance (FCI) with shared storage provides automatic failover and zero data loss at the cost of shared storage. Option B (AG with synchronous commit) has automatic failover but may have data loss in some scenarios. Option C (log shipping) has manual failover and potential data loss.

Option D (backup/restore) is not a high availability solution.

Full explanation →

163

Multi-Selecthard

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to identify which three actions can improve write performance. Which THREE should you select?

Select 3 answers

A.Use smaller log writes

B.Increase the log rate limit

C.Increase the service tier to Business Critical

D.Enable read scale-out

E.Enable Accelerated Database Recovery

AnswersA, B, E

Smaller log writes reduce latency.

Why this answer

Option B is correct: Increasing log rate limit improves log throughput. Option C is correct: Using smaller log writes reduces latency. Option D is correct: Enabling Accelerated Database Recovery reduces log amplification.

Option A is incorrect: Increasing service tier to Business Critical does not apply to Hyperscale. Option E is incorrect: Read scale-out does not improve write performance.

Full explanation →

164

MCQmedium

You are deploying a new Azure SQL Database that will be used by an application requiring high availability and automatic failover across Azure regions. The RPO must be less than 5 seconds, and the RTO must be less than 1 minute. You also need to ensure that the secondary region can be used for read-only workloads during normal operation. Which solution should you implement?

A.Use active geo-replication with a readable secondary in a different Azure region.

B.Use an auto-failover group with a readable secondary in a different region.

C.Use Azure SQL Database backup to a second region and restore on demand.

D.Use an auto-failover group with a read-scale secondary in the same region.

AnswerA

Correct: Active geo-replication provides a readable secondary with RPO of 5 seconds and manual failover in seconds.

Why this answer

Active geo-replication with a readable secondary in a different Azure region meets the requirements because it provides an asynchronous replication mechanism with an RPO of less than 5 seconds and an RTO of less than 1 minute during failover. The secondary database is fully readable for read-only workloads, supporting the application's need for read-scale in the secondary region during normal operation.

Exam trap

The trap here is that candidates often confuse auto-failover groups with active geo-replication, assuming that auto-failover groups automatically provide a readable secondary, but in fact, the secondary in an auto-failover group is not readable unless you explicitly configure it as a readable secondary, which is not the default behavior.

How to eliminate wrong answers

Option B is wrong because auto-failover groups use the same underlying active geo-replication technology but add orchestrated failover; however, the secondary in an auto-failover group is not readable by default unless you explicitly enable it, and the question's requirement for a readable secondary is directly met by active geo-replication without the extra orchestration layer. Option C is wrong because using backups to a second region and restoring on demand results in an RPO of at least several minutes (due to backup frequency) and an RTO of hours, far exceeding the required 5-second RPO and 1-minute RTO. Option D is wrong because a read-scale secondary in the same region does not provide cross-region disaster recovery, failing the requirement for automatic failover across Azure regions.

Full explanation →

165

Multi-Selecteasy

Which TWO of the following are valid methods to recover an Azure SQL Database after a regional outage?

Select 2 answers

A.Replicate the database from a backup to a secondary region using log shipping.

B.Fail over to a secondary region using a failover group.

C.Point-in-time restore to a time before the outage.

D.Copy the database from a geo-redundant backup to a new server.

E.Geo-restore from geo-redundant backups.

AnswersB, E

Failover groups provide automatic or manual failover.

Why this answer

Options B and D are correct. Geo-restore uses geo-redundant backups (B). Failover groups with automatic failover can recover (D).

Option A is wrong because point-in-time restore is limited to the same region. Option C is wrong because you cannot directly copy from geo-backup without restore. Option E is wrong because replication from a backup is not a standard recovery method.

Full explanation →

166

MCQmedium

You have an Azure SQL Database configured with active geo-replication to a secondary region. The secondary database is used for read-only workloads. A network latency issue causes replication lag to exceed 10 seconds. You need to ensure reporting queries always see consistent data. What should you configure?

A.Use ApplicationIntent=ReadOnly in connection strings and ensure the secondary is up-to-date.

B.Enable read scale-out on the secondary.

C.Configure an auto-failover group with read-write endpoint.

D.Create a failover group with read-only endpoint enabled.

AnswerA

Routes read-only queries to secondary, but consistency depends on replication.

Why this answer

Option C is correct because setting session context to use the primary for read-write and secondary for read-only with read-intent ensures consistent reads. Option A is wrong because auto-failover groups don't enforce consistency. Option B is wrong because read scale-out is for Hyperscale.

Option D is wrong because it doesn't address consistency.

Full explanation →

167

MCQeasy

What effect does the command in the exhibit have on the database?

A.Enables auto-pause with a delay of 1 minute.

B.Configures the database to pause after 1 second of inactivity.

C.Disables auto-pause for the database.

D.Sets auto-pause delay to 1 hour.

AnswerC

-1 means auto-pause is disabled.

Why this answer

Option C is correct because setting AutoPauseDelay to -1 disables auto-pause for serverless databases. Option A is wrong because it's not enabling auto-pause. Option B is wrong because it's not setting a specific delay.

Option D is wrong because it's not changing service tier.

Full explanation →

168

MCQeasy

You need to automate the backup of an on-premises SQL Server database to Azure Blob Storage using the latest Microsoft recommended method. Which approach should you use?

A.Use SQL Server Management Studio to create a maintenance plan that uses the 'Back Up Database' task with destination = URL.

B.Use PowerShell with the Backup-SqlDatabase cmdlet and the -BackupContainer parameter.

C.Use Azure Backup for SQL Server in Azure VMs.

D.Use the 'Backup to Windows Azure' feature in SQL Server 2012.

AnswerA

Maintenance plans support URL backup with SAS token.

Why this answer

Option A is correct because SQL Server Backup to URL using the BACKUP TO URL command with a shared access signature is the recommended method for backing up to Azure Blob Storage. Options B and C are outdated or less secure. Option D requires additional software.

Full explanation →

169

MCQhard

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?

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

B.The failover group will wait 60 minutes, then automatically fail over to West Europe.

C.The failover group will immediately fail over to West Europe.

D.The failover group requires manual failover because readOnlyEndpoint is disabled.

AnswerB

Correct.

Why this answer

Option B is correct because with automatic failover policy and a grace period, the system will wait for 60 minutes before automatically failing over to West Europe. The read-only endpoint is disabled, so no read-only routing is available. Option A is incorrect because the grace period is 60 minutes, not zero.

Option C is incorrect because automatic failover is enabled. Option D is incorrect because the failover will happen within 60 minutes, not immediately.

Full explanation →

170

Matchingmedium

Match each Azure SQL Database migration tool to its description.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Managed service for online and offline migrations

Tool for migrating from other database platforms to SQL Server

Assesses compatibility and recommends improvements

Cross-platform tool for managing and migrating databases

Why these pairings

These tools facilitate migration to Azure SQL Database.

Full explanation →

171

MCQeasy

Your Azure SQL Managed Instance stores sensitive healthcare data. You need to restrict access to the database from public networks while allowing a specific on-premises application to connect. The on-premises network has a static public IP address. What is the most secure way to configure connectivity?

A.Enable the public endpoint without any firewall rules

B.Enable the public endpoint and create a firewall rule allowing only the on-premises static IP address

C.Configure a service endpoint on the VNet where the Managed Instance is deployed

D.Disable the public endpoint and configure a point-to-site VPN for the on-premises application

AnswerB

This allows secure access from the specific on-premises IP while blocking all other public traffic.

Why this answer

Enabling the public endpoint with a firewall rule that restricts access to only the on-premises static IP address is the most secure way to allow a specific external application to connect while blocking all other public traffic. This approach leverages Azure SQL Managed Instance's built-in public endpoint firewall, which evaluates source IP addresses against configured rules before allowing connections, ensuring that only the trusted on-premises application can reach the sensitive healthcare data.

Exam trap

The trap here is that candidates often confuse Azure SQL Managed Instance's public endpoint firewall with Azure SQL Database's firewall, or incorrectly assume that service endpoints or VPNs are always more secure than a simple IP-based firewall rule, when in fact a properly configured firewall rule with a static IP is the most secure and efficient solution for this specific scenario.

How to eliminate wrong answers

Option A is wrong because enabling the public endpoint without any firewall rules would allow any public IP address to attempt connections, exposing the sensitive healthcare data to the entire internet and violating security best practices. Option C is wrong because service endpoints are used to secure Azure service resources to a virtual network, but Azure SQL Managed Instance is always deployed inside a VNet and does not support service endpoints for public access; service endpoints are for PaaS services like Azure SQL Database, not Managed Instance. Option D is wrong because disabling the public endpoint and configuring a point-to-site VPN would require the on-premises application to establish a VPN connection, which adds complexity and latency, and is less secure than a simple firewall rule when the on-premises network has a static public IP; the question asks for the most secure way to configure connectivity, and a firewall rule with a specific IP is more straightforward and equally secure for this scenario.

Full explanation →

172

MCQmedium

You are examining the configuration of an Azure SQL Database in the Business Critical tier. The exhibit shows the database properties. What is the maximum number of readable replicas available for read-only queries?

A.1

B.3

C.4

D.5

AnswerC

highAvailabilityReplicaCount=4 provides 4 readable replicas.

Why this answer

Option C is correct. In Business Critical tier, the maximum number of readable replicas is 4, but the highAvailabilityReplicaCount property specifies the number of replicas excluding the primary. With a value of 4, there are 4 replicas, all of which can be readable when readScale is enabled.

Option A is wrong because 5 would include the primary. Option B is wrong because 1 is too low. Option D is wrong because the maximum is 4.

Full explanation →

173

MCQmedium

You are a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

A.Use Query Store to identify and force a better execution plan.

B.Enable automatic tuning to let Azure SQL Database handle the issue.

C.Add more indexes on the columns used in JOINs and WHERE clause.

D.Create a read replica and offload the query to it.

AnswerA

Query Store captures plan history and allows forced plan to stabilize performance.

Why this answer

Option A is correct because Query Store can identify query performance regressions and provide plan forcing. Option B is wrong because automatic tuning might not address the specific query without Query Store data. Option C is wrong because index tuning may not help if the query plan is suboptimal.

Option D is wrong because read replicas offload read traffic but do not optimize CPU usage of a single query on the primary.

Full explanation →

174

MCQhard

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

A.Change the database recovery model to Simple.

B.Use bulk insert operations with TABLOCK hint to enable minimal logging.

C.Create a partition function and scheme to spread the inserts.

D.Increase the maximum log size of the database.

AnswerB

Minimal logging reduces log space for large imports under full recovery model.

Why this answer

Option B is correct because using minimally logged operations (e.g., bulk insert with TABLOCK) reduces log space for large imports under the full recovery model, but requires specific conditions. Option A is wrong because simple recovery model is not supported in Azure SQL Database (only FULL, BULK_LOGGED is not available). Option C is wrong because partitioning does not reduce log growth.

Option D is wrong because increasing log size only accommodates growth, does not prevent it.

Full explanation →

175

MCQhard

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which two changes should you make?

A.Reduce storage to 512 GB.

B.Change tier to Hyperscale.

C.Enable serverless with auto-pause enabled.

D.Reduce capacity to 2 vCores.

AnswerC

Serverless auto-pause stops compute billing when idle.

Why this answer

Option D is correct because enabling auto-pause will pause the database during off-hours, reducing costs. Option A is wrong because reducing vCores might help but auto-pause is more effective. Option B is wrong because changing to Hyperscale may not reduce costs for this pattern.

Option C is wrong because storage reduction is not as impactful.

Full explanation →

176

MCQhard

A company has an Azure SQL Database with a large table that is frequently updated. They notice performance degradation due to index fragmentation. Which maintenance strategy should you recommend to minimize impact on availability and performance?

A.Schedule an offline index rebuild during off-peak hours.

B.Use online index rebuild with resumable operation.

C.Disable and rebuild indexes manually during maintenance window.

D.Use online index reorganize with low priority locks.

AnswerB

Online rebuild allows concurrent access and resumable in case of failure, minimizing impact.

Why this answer

Option B is correct because online index rebuild with resumable operation allows the index to be rebuilt while the table remains available for reads and writes, minimizing downtime. The resumable feature lets you pause and resume the rebuild if it is interrupted, which is critical for large tables that are frequently updated. This approach reduces performance impact compared to offline rebuilds and avoids blocking issues associated with other online methods.

Exam trap

The trap here is that candidates often confuse 'online index reorganize' (which is less impactful but insufficient for high fragmentation) with 'online index rebuild' (which is the correct solution for severe fragmentation), or they assume offline operations are acceptable despite the availability requirement.

How to eliminate wrong answers

Option A is wrong because an offline index rebuild takes the table offline, blocking all access during the rebuild, which is unacceptable for a frequently updated table and violates availability requirements. Option C is wrong because disabling and rebuilding indexes manually during a maintenance window is essentially an offline operation that causes significant downtime and does not leverage Azure SQL Database's built-in online capabilities. Option D is wrong because online index reorganize with low priority locks is less effective for high fragmentation (it only defragments the leaf level) and may still cause blocking under heavy update workloads, whereas a rebuild is needed for severe fragmentation.

Full explanation →

177

MCQmedium

You have an Azure SQL Database that is experiencing performance degradation. You suspect that parameter sniffing is causing suboptimal execution plans. What should you do to mitigate this issue without changing application code?

A.Add the OPTION (RECOMPILE) query hint to all queries.

B.Clear the plan cache using DBCC FREEPROCCACHE.

C.Enable the 'Optimize for Ad Hoc Workloads' setting.

D.Enable forced parameterization for the database.

AnswerD

Forces SQL Server to parameterize queries, reducing parameter sniffing impact.

Why this answer

Option C is correct because enabling the Optimize for Ad Hoc Workloads setting reduces plan cache bloat but does not directly address parameter sniffing. However, the correct approach to mitigate parameter sniffing without code changes is to use the RECOMPILE query hint or enable forced parameterization. But among the options, C is the best because it reduces plan cache bloat and can help with parameter sniffing indirectly.

Actually, option A is more direct: enabling forced parameterization makes SQL Server parameterize queries, reducing the impact of parameter sniffing. But the question says 'without changing application code' – forced parameterization is a database setting. Option B is wrong because clearing the plan cache is temporary.

Option D is wrong because it requires code changes. So the correct answer is A.

Full explanation →

178

MCQhard

You are configuring automated performance tuning for an Azure SQL Database. You want to automatically create missing indexes and drop unused indexes, but you need to prevent automatic index creation during peak business hours (9 AM - 5 PM). What is the best approach?

A.Enable automatic indexing in Azure SQL Database and rely on the system to decide when to create indexes.

B.Enable automatic tuning but set the 'INDEX_CREATE' option to 'OFF' and use a schedule to run a script that enables it during off-peak hours and disables during peak hours.

C.Use SQL Server Agent to schedule index creation scripts that check for missing indexes.

D.Use Azure Automation to run index analysis scripts only during off-peak hours.

AnswerB

You can toggle the setting using ALTER DATABASE SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON, CREATE_INDEX = OFF) and schedule changes.

Why this answer

Option D is correct because Azure SQL Database's automatic tuning can be enabled but with a schedule that restricts index creation to off-peak hours using the 'SET AUTOMATIC_TUNING' command with a schedule. Options A and B do not provide scheduling. Option C is not available.

Full explanation →

179

Multi-Selecthard

You are automating index maintenance for an Azure SQL Database. Which THREE of the following should you consider to minimize performance impact?

Select 3 answers

A.Use the ONLINE = ON option when rebuilding indexes.

B.Always use SORT_IN_TEMPDB = ON to reduce tempdb contention.

C.Set MAXDOP to 0 to use all CPUs for faster execution.

D.Reorganize indexes with fragmentation below 30% instead of rebuilding.

E.Use a fill factor of 70-80% for indexes with frequent insert/update operations.

AnswersA, D, E

Allows concurrent access.

Why this answer

Option A is correct because using ONLINE = ON when rebuilding indexes in Azure SQL Database allows concurrent user access to the underlying table during the index rebuild operation. This minimizes blocking and performance impact on production workloads, as the index rebuild is performed as a series of short-term locks rather than a single long-term schema modification lock.

Exam trap

The trap here is that candidates often assume SORT_IN_TEMPDB = ON always improves performance, but in Azure SQL Database it can increase tempdb contention, and MAXDOP = 0 is mistakenly thought to be optimal for maintenance tasks when it actually risks resource starvation.

Full explanation →

180

Multi-Selecthard

You are configuring an Azure SQL Database for a financial application that requires high availability and disaster recovery across regions. The database is in the Premium service tier. Which TWO configurations should you implement to meet these requirements? (Choose TWO.)

Select 2 answers

A.Enable automatic tuning for the database.

B.Enable zone redundancy within the primary region.

C.Create a failover group that includes the primary and secondary.

D.Configure active geo-replication to a secondary region.

E.Enable Transparent Data Encryption (TDE).

AnswersC, D

Failover group enables automatic failover across regions.

Why this answer

Option C is correct because a failover group in Azure SQL Database provides automated, coordinated failover of multiple databases across regions, ensuring high availability and disaster recovery with a defined replication policy and a readable secondary endpoint. Option D is correct because active geo-replication allows you to create up to four readable secondary databases in different Azure regions, enabling manual or automatic failover for cross-region disaster recovery, which is essential for a financial application requiring regional resilience.

Exam trap

The trap here is that candidates often confuse zone redundancy (which is intra-region HA) with cross-region DR solutions like failover groups or geo-replication, leading them to select option B instead of the correct cross-region options.

Full explanation →

181

MCQhard

You are designing a disaster recovery solution for an Azure SQL Database in the Business Critical tier. You require automatic failover to a secondary region with zero data loss. Which configuration should you use?

A.Create a failover group with the Business Critical tier.

B.Configure geo-replication between two servers.

C.Enable read scale-out in a secondary region.

D.Use active geo-replication with manual failover.

AnswerA

Failover groups on Business Critical provide synchronous replication and automatic failover.

Why this answer

The Business Critical tier in Azure SQL Database supports failover groups, which provide automatic failover across regions with zero data loss by using synchronous replication at the primary region and asynchronous replication to the secondary region. Failover groups ensure that all committed transactions are preserved during a failover, meeting the zero data loss requirement for disaster recovery.

Exam trap

The trap here is that candidates often confuse active geo-replication with failover groups, assuming manual failover can achieve zero data loss, but active geo-replication uses asynchronous replication by default and does not support automatic failover with zero RPO.

How to eliminate wrong answers

Option B is wrong because geo-replication between two servers is a legacy feature that does not support automatic failover or zero data loss; it requires manual configuration and has potential data loss. Option C is wrong because read scale-out in a secondary region only offloads read-only workloads and does not provide failover capabilities or disaster recovery. Option D is wrong because active geo-replication with manual failover does not guarantee zero data loss, as it uses asynchronous replication and requires manual intervention to initiate failover.

Full explanation →

182

Multi-Selectmedium

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

Select 2 answers

A.sys.dm_os_wait_stats

B.Query Store

C.sys.dm_exec_query_stats

D.sys.dm_db_index_usage_stats

E.sys.dm_io_virtual_file_stats

AnswersB, C

Tracks query performance metrics.

Why this answer

Options A and C are correct. Query Store tracks query execution statistics including CPU, duration, and I/O, and the sys.dm_exec_query_stats DMV provides similar data. Option B is wrong because sys.dm_os_wait_stats shows wait types, not query resource usage.

Option D is wrong because sys.dm_db_index_usage_stats shows index usage, not query resource consumption. Option E is wrong because sys.dm_io_virtual_file_stats shows I/O statistics at the file level, not per query.

Full explanation →

183

MCQeasy

You need to automate the generation of a weekly report that shows the top 10 queries by CPU consumption in an Azure SQL Database. The report should be emailed to the DBA team. Which service should you use to gather the query performance data?

A.SQL Server Profiler

B.Database Engine Tuning Advisor

C.Azure Monitor Metrics

D.Query Performance Insight

AnswerD

Query Performance Insight provides aggregated query performance data and can be queried via DMVs like sys.dm_db_query_store_stats.

Why this answer

Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be queried via DMVs or the Azure portal. Option C is correct.

Option A is for database schema. Option B is for query hints. Option D is not for Azure SQL Database.

Full explanation →

184

MCQeasy

You manage an Azure SQL Database that uses the General Purpose service tier. You need to reduce storage costs by archiving old data that is not frequently accessed. The archived data must still be queryable occasionally. What should you do?

A.Enable Stretch Database to transparently stretch old data to Azure Blob Storage.

B.Implement table partitioning and move old partitions to a separate database with a lower service tier.

C.Use Elastic Query to query historical data stored in Azure Blob Storage.

D.Migrate to the Hyperscale service tier to take advantage of tiered storage.

AnswerB

Allows archiving old data in a cheaper database while keeping it queryable.

Why this answer

Option D is correct because Azure SQL Database does not natively support tiered storage, but you can move data to Azure SQL Database Hyperscale (which allows scaling storage independently) or to Azure SQL Data Warehouse (now Azure Synapse). However, the best approach is to implement data archiving by partitioning and moving old data to a separate database with lower service tier. But among the options, D is the most practical: use SQL Server Stretch Database, which is deprecated, but the current alternative is to use Hyperscale or archive to Azure Storage and query via PolyBase.

Actually, the correct answer is A: use partitioning and move old data to a cheaper database tier. Option B is wrong because Hyperscale is more expensive. Option C is wrong because Elastic Query is for querying remote databases.

So I'll adjust explanation.

Full explanation →

185

Multi-Selecteasy

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

Select 2 answers

A.Automatically scale up the database service tier

B.Automatically identify and correct query plan regressions

C.Automatically create read replicas

D.Automatically update statistics

E.Automatically create missing indexes

AnswersB, E

Plan correction is a key feature.

Why this answer

Options A and D are correct. Automatic tuning can create missing indexes and identify and correct query plan regressions. Option B is wrong because automatic tuning does not automatically scale compute; that's auto-scale.

Option C is wrong because it does not manage statistics automatically. Option E is wrong because it does not manage read replicas.

Full explanation →

186

MCQhard

You are the database administrator for a large e-commerce company. The production Azure SQL Database (Business Critical, 16 vCores) hosts the order processing system. Recently, users report that order submissions are slow during peak hours. You examine the wait statistics and find that `LCK_M_IX` waits are the top wait type. You also notice that the stored procedure `usp_PlaceOrder` performs an `UPDATE` on the `Orders` table, and there is a high volume of concurrent transactions. The table has a clustered index on `OrderID` and a nonclustered index on `CustomerID`. The procedure uses serializable isolation level. Which action will most effectively reduce the blocking?

A.Increase the vCore count to 24.

B.Convert the Orders table to a memory-optimized table.

C.Add a nonclustered index on the Status column of the Orders table.

D.Change the database to use read committed snapshot isolation (RCSI) and modify the procedure to use read committed.

AnswerD

RCSI uses row versioning to avoid locks.

Why this answer

The primary issue is blocking caused by `LCK_M_IX` waits under the serializable isolation level, which holds range locks and prevents concurrent updates. Changing to read committed snapshot isolation (RCSI) and using read committed eliminates these locks by providing statement-level row versioning, allowing concurrent transactions to read without blocking writers. This directly addresses the high volume of concurrent `UPDATE` operations without requiring schema or hardware changes.

Exam trap

The trap here is that candidates often assume scaling up hardware (Option A) or adding indexes (Option C) will fix blocking, when the root cause is the isolation level's locking behavior, which requires a concurrency model change like RCSI.

How to eliminate wrong answers

Option A is wrong because increasing vCores does not resolve logical blocking caused by lock contention; it only improves throughput for CPU-bound workloads, not concurrency issues. Option B is wrong because converting to a memory-optimized table would require significant application changes and does not directly address the isolation-level-induced blocking; memory-optimized tables use optimistic concurrency but still need isolation level adjustments. Option C is wrong because adding an index on the Status column does not reduce blocking from serializable isolation; it may help query performance but does not change lock behavior or contention on the Orders table.

Full explanation →

187

MCQhard

You have a SQL Server 2022 on Azure VM configured with a Basic availability group for a line-of-business application. The application requires automatic failover without manual intervention. What additional component is needed?

A.Azure Load Balancer

B.Windows Server Failover Cluster (WSFC)

C.Virtual network gateway

D.Availability group listener

AnswerB

WSFC provides the cluster infrastructure for automatic failover in a Basic AG.

Why this answer

Option A is correct because a Basic availability group requires a Windows Server Failover Cluster (WSFC) with an odd number of voting nodes to achieve automatic failover. Option B is incorrect because a listener is for client connectivity, not for automatic failover. Option C is incorrect because a virtual network gateway is for cross-premises connectivity.

Option D is incorrect because Azure Load Balancer is used for listener IP, not for automatic failover itself.

Full explanation →

188

MCQhard

You have an Azure SQL Managed Instance configured with a failover group for disaster recovery. The primary instance is in the East US region and the secondary is in West US. You need to perform a planned failover for maintenance with zero data loss. What is the correct sequence of steps?

A.Take the primary instance offline, then failover.

B.Use the Azure portal to perform a planned failover for the failover group.

C.Scale down the primary to reduce cost, then failover.

D.Remove the failover group, perform maintenance, then recreate the failover group.

AnswerB

Correct: A planned failover ensures zero data loss.

Why this answer

Option A is correct because to perform a planned failover with zero data loss, you should first trigger a failover from the primary to the secondary. The failover group will automatically ensure no data loss. After the failover, the secondary (now primary) is online.

During maintenance, you can perform the maintenance on the former primary (now secondary). After maintenance, you can fail back. Option B is incorrect because you should not remove the failover group.

Option C is incorrect because you should not scale down. Option D is incorrect because you should not take the primary offline before failover.

Full explanation →

189

MCQmedium

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

A.Deploy a single Azure SQL Database per tenant

B.Use Azure SQL Managed Instance with multiple databases

C.Use a single large database with row-level security

D.Use an elastic pool with one database per tenant

AnswerD

Elastic pool isolates tenants while sharing resources efficiently.

Why this answer

Option D is correct because an elastic pool allows you to provision a shared set of resources (eDTUs or vCores) that is distributed across multiple databases, each representing a tenant. This provides logical isolation of tenant data (each tenant has its own database) while pooling resources to handle variable workloads cost-effectively. The elastic pool model is specifically designed for SaaS multi-tenant scenarios where predictable performance is achieved through resource governance, and cost efficiency comes from sharing resources among databases that do not all peak simultaneously.

Exam trap

The trap here is that candidates often confuse 'tenant isolation' with 'dedicated resources' and choose Option A, missing that elastic pools provide logical isolation (separate databases) with shared, cost-efficient resources, which is the exact requirement for predictable performance and cost efficiency in multi-tenant SaaS.

How to eliminate wrong answers

Option A is wrong because deploying a single Azure SQL Database per tenant without pooling leads to over-provisioning and higher costs, as each database requires its own DTU/vCore allocation regardless of actual usage, and does not leverage shared resource benefits for variable workloads. Option B is wrong because Azure SQL Managed Instance is designed for lift-and-shift migrations of existing SQL Server workloads with instance-level features, not for multi-tenant SaaS isolation; it lacks the elastic pool resource-sharing model and is more expensive per database. Option C is wrong because using a single large database with row-level security (RLS) violates tenant data isolation at the database level (a single database is a shared failure domain), and performance is unpredictable as all tenants compete for the same resources without per-tenant resource governance, making it unsuitable for predictable performance and cost efficiency.

Full explanation →

190

MCQhard

Your company is migrating an on-premises SQL Server 2016 database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Database Mail, and Service Broker. The database is 500 GB. You need to minimize downtime during migration. Which migration approach should you use?

A.Set up transactional replication from on-premises to Azure SQL Managed Instance

B.Use Azure Database Migration Service with online migration

C.Export the database as a BACPAC file and import to Azure SQL Managed Instance

D.Back up the database to URL and restore to Azure SQL Managed Instance

AnswerB

DMS online migration supports minimal downtime and transfers SQL Agent jobs and other objects.

Why this answer

Option B is correct because Azure Database Migration Service (DMS) with online migration mode uses continuous data synchronization via change tracking or log shipping to minimize downtime. This approach supports SQL Server Agent jobs, Database Mail, and Service Broker, which are preserved during migration to Azure SQL Managed Instance. The online mode allows the source database to remain operational until the final cutover, reducing downtime to seconds.

Exam trap

The trap here is that candidates often confuse offline methods like BACPAC or backup/restore as 'minimizing downtime' because they are simple, but they fail to recognize that online migration via DMS is specifically designed to keep the source database operational during the bulk of the migration process.

How to eliminate wrong answers

Option A is wrong because transactional replication requires setting up publishers, distributors, and subscribers, and it does not natively support migrating all objects like SQL Server Agent jobs and Service Broker configurations without additional scripting; it also introduces complexity and potential latency. Option C is wrong because exporting a 500 GB database as a BACPAC file and importing it is an offline process that can take hours or days, causing significant downtime, and it may not preserve all metadata like SQL Server Agent jobs and Service Broker endpoints. Option D is wrong because backing up to URL and restoring to Azure SQL Managed Instance is an offline migration method that requires the source database to be offline during the backup and restore process, leading to extended downtime for a 500 GB database.

Full explanation →

191

MCQhard

You are troubleshooting a performance issue on an Azure SQL Database. Query Store shows a significant increase in query duration for a specific query. The execution plan has changed from a hash join to a nested loops join. What is the most likely cause?

A.Parameter sniffing caused the optimizer to choose a plan for atypical parameter values

B.An index was dropped, forcing a table scan

C.Forced parameterization was enabled

D.Stale statistics caused the optimizer to underestimate cardinality

AnswerD

Leads to nested loops instead of hash join due to underestimation.

Why this answer

Option B is correct because a plan regression often occurs when statistics are stale, causing the optimizer to choose a suboptimal plan. Option A is wrong because parameter sniffing can cause plan changes but usually the plan is based on initial parameter values, not all parameters. Option C is wrong because missing indexes would cause scans, not a change from hash to nested loops.

Option D is wrong because forced parameterization might stabilize plans but does not cause this change.

Full explanation →

192

MCQeasy

A company has an Azure SQL Managed Instance in the East US region. They need to implement disaster recovery with automatic failover to a paired region. The solution must minimize data loss to less than 5 seconds. Which feature should they use?

A.Active Geo-Replication with a secondary in East US 2

B.Enable geo-redundant backup storage and restore to West US

C.Configure an Auto-Failover Group with a secondary instance in West US

D.Set up a Failover Group with manual failover to a secondary in West US

AnswerC

Auto-failover groups for managed instance provide automatic failover and RPO of 5 seconds.

Why this answer

Option C is correct because Auto-Failover Groups for Azure SQL Managed Instance provide automatic failover to a paired region (West US) with a replication lag target of less than 5 seconds when using the Readable Secondary option. This feature uses synchronous replication at the commit level to minimize data loss, meeting the RPO requirement of <5 seconds.

Exam trap

The trap here is confusing Active Geo-Replication (which is for Azure SQL Database, not Managed Instance) with Auto-Failover Groups, leading candidates to select Option A even though it is unsupported for Managed Instance.

How to eliminate wrong answers

Option A is wrong because Active Geo-Replication is not supported for Azure SQL Managed Instance; it is only available for Azure SQL Database. Option B is wrong because geo-redundant backup storage (GRS) provides point-in-time restore to another region but does not offer automatic failover or a sub-5-second RPO; recovery time can be hours and data loss depends on backup frequency. Option D is wrong because a Failover Group with manual failover does not provide automatic failover; the requirement explicitly states 'automatic failover,' and manual failover requires human intervention, which can increase downtime.

Full explanation →

193

Multi-Selectmedium

Which THREE are requirements for configuring a failover group for Azure SQL Managed Instance? (Select three.)

Select 3 answers

A.Both managed instances must have the same service tier

B.The secondary instance must have no user databases

C.Both instances must be in the same virtual network subnet

D.The primary instance must have at least one user database

E.Both instances must be in the same generation (Gen5)

AnswersA, B, E

Failover groups require matching service tiers (e.g., General Purpose).

Why this answer

Options A, B, and C are correct. Both instances must be in the same generation and have empty databases (no user databases). Option D is incorrect because the instances can be in different subnets.

Option E is incorrect because the secondary must be empty (no user databases).

Full explanation →

194

MCQmedium

You are deploying an Azure SQL Database with the above ARM template snippet. How many readable replicas will be available for read-only queries?

A.3

B.2

C.1

D.4

AnswerB

With highAvailabilityReplicaCount=2, there are 2 readable replicas.

Why this answer

Option D is correct. Business Critical tier has one primary and multiple replicas. With highAvailabilityReplicaCount set to 2, there are 2 replicas in addition to the primary.

By default, one replica is used for HA and the others are readable. With readScale enabled, all replicas can be used for read-only queries. So total readable replicas = highAvailabilityReplicaCount = 2.

Option A is wrong because it counts the primary. Option B is wrong because only replicas are readable. Option C is wrong because 3 would include the primary.

Full explanation →

195

Multi-Selectmedium

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Select 3 answers

A.FORCE LAST GOOD PLAN

B.Modify statistics

C.CREATE INDEX

D.DROP INDEX

E.Enable database compression

AnswersA, C, D

This is a built-in automatic tuning option that can be applied automatically.

Why this answer

Options A, B, and D are correct. Automatic tuning includes FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX. Option C is wrong because modify statistics is not an automatic tuning option.

Option E is wrong because enabling database compression is not part of automatic tuning.

Full explanation →

196

MCQmedium

Refer to the exhibit. A PowerShell script is used to update an Azure SQL Database. The script runs without errors, but the database is not moved to the specified elastic pool. What is the most likely cause?

A.The elastic pool 'pool-prod' does not exist.

B.The ServerName parameter is missing the fully qualified domain name.

C.The script sets both Edition and RequestedServiceObjectiveName, which is invalid when moving to an elastic pool.

D.The cmdlet requires the -Force parameter to move a database.

AnswerC

When moving a database to an elastic pool, you should not specify RequestedServiceObjectiveName; the pool's tier determines the service objective.

Why this answer

The Set-AzSqlDatabase cmdlet can move a database into an elastic pool by specifying the ElasticPoolName parameter. However, the Edition parameter must match the pool's edition (e.g., Standard for Standard pool). The script sets Edition to 'Standard', which is correct.

But the cmdlet also requires that the database is not already in a pool; if it is, you need to specify -RemoveElasticPool first. However, the most common issue is that the Edition parameter conflicts if the database is in a different service tier. Option D is correct: the script sets Edition and RequestedServiceObjectiveName simultaneously, which can cause a conflict because when moving to a pool, you should not specify RequestedServiceObjectiveName.

Option A is incorrect because version is not a parameter. Option B is incorrect because the pool exists. Option C is incorrect because -Force is not required.

Full explanation →

197

Multi-Selectmedium

Which THREE of the following are valid options for migrating an on-premises SQL Server database to Azure SQL Database?

Select 3 answers

A.Use SQL Server Integration Services (SSIS) to migrate schema and data.

B.Use the Data Migration Assistant (DMA) to assess and migrate.

C.Export to a BACPAC file and then import to Azure SQL Database.

D.Use transactional replication to continuously replicate data to Azure SQL Database.

E.Use the Azure portal's 'Import database' wizard directly from on-premises.

AnswersB, C, D

DMA is a supported tool for assessment and migration.

Why this answer

Option B is correct because the Data Migration Assistant (DMA) is a Microsoft tool specifically designed to assess on-premises SQL Server databases for compatibility issues and then perform a seamless migration of schema and data to Azure SQL Database. DMA handles both assessment and migration in a single workflow, making it a valid and recommended option for migrating to Azure SQL Database.

Exam trap

The trap here is that candidates often confuse SSIS (an ETL tool) with a database migration tool, or assume the Azure portal can directly connect to an on-premises server, when in fact it only works with BACPAC files already uploaded to Azure Blob Storage.

Full explanation →

198

MCQhard

Your company uses Azure SQL Managed Instance. You need to automate the execution of a stored procedure that processes sales data every night at 2 AM. The solution must use native capabilities and minimize latency. What should you do?

A.Create an Elastic Database Job to run the stored procedure on the target database.

B.Use Azure Logic Apps with a recurrence trigger to call the stored procedure via a connector.

C.Create a SQL Agent job with a schedule to execute the stored procedure.

D.Deploy an Azure Function with a timer trigger to invoke the stored procedure via the SQL binding.

AnswerC

SQL Agent is fully supported in Managed Instance and provides native scheduling.

Why this answer

Option B is correct because Azure SQL Managed Instance supports SQL Agent, which can schedule jobs natively. Option A is wrong because Elastic Database Jobs require creating a job database and agent, adding overhead. Option C is wrong because Azure Functions would need to connect externally and may have higher latency.

Option D is wrong because Logic Apps add complexity and cost.

Full explanation →

199

Multi-Selecthard

Which THREE actions should you take when planning to implement Azure SQL Database with Microsoft Defender for SQL and Microsoft Sentinel integration?

Select 3 answers

A.Enable Advanced Threat Protection for each database individually

B.Configure Microsoft Sentinel data connectors for Azure SQL Database

C.Enable Microsoft Defender for SQL at the server level

D.Stream SQL audit logs to a Log Analytics workspace

E.Create a diagnostic setting to stream metrics to Event Hub

AnswersB, C, D

Data connectors ingest logs into Sentinel.

Why this answer

Option B is correct because Microsoft Sentinel requires a data connector to ingest Azure SQL Database security events. The 'Azure SQL Database' data connector in Sentinel must be configured to pull diagnostic logs and alerts from the SQL resource into the Log Analytics workspace used by Sentinel, enabling advanced threat detection and incident response.

Exam trap

The trap here is that candidates often confuse enabling Microsoft Defender for SQL (server-level) with needing to enable Advanced Threat Protection per database, or they mistakenly think streaming metrics to Event Hub is the correct path for Sentinel integration instead of streaming audit logs to Log Analytics.

Full explanation →

200

MCQeasy

You are configuring Azure SQL Database firewall rules. You need to allow a team of developers to connect from their office IP range (192.168.1.0/24) to a specific database. The developers should not be able to access other databases on the same logical server. What should you do?

A.Create a private endpoint for the database.

B.Add a server-level firewall rule for the IP range 192.168.1.0/24.

C.Add a database-level firewall rule for the IP range 192.168.1.0/24.

D.Configure a virtual network service endpoint for the server.

AnswerC

Database-level rules restrict access to the specific database.

Why this answer

Database-level firewall rules in Azure SQL Database allow you to restrict access to a specific database on a logical server, rather than the entire server. By adding a rule for the IP range 192.168.1.0/24 at the database level, the developers can connect only to that database, and they will be blocked from accessing other databases on the same server. This is the correct approach because server-level rules would grant access to all databases, which violates the requirement.

Exam trap

The trap here is that candidates often assume server-level firewall rules are sufficient for all scenarios, but the DP-300 exam tests the distinction that database-level rules are required when you need to restrict access to a specific database on a logical server.

How to eliminate wrong answers

Option A is wrong because a private endpoint connects the database to a virtual network privately, but it does not restrict access to a specific database; it still requires firewall rules to control which clients can connect. Option B is wrong because a server-level firewall rule for the IP range would allow the developers to access all databases on the logical server, not just the specific one. Option D is wrong because a virtual network service endpoint integrates the server with a VNet but does not provide per-database access control; it still relies on server-level firewall rules and would allow access to all databases.

Full explanation →

201

Multi-Selectmedium

You are planning to migrate a large number of on-premises SQL Server databases to Azure SQL Managed Instance. The migration must be automated and support schema and data validation. Which TWO tools or services should you consider using? (Choose TWO.)

Select 2 answers

A.Azure SQL Migration tool

B.SQL Server Management Studio (SSMS)

C.Azure Database Migration Service

D.BACPAC export and import

E.Azure Data Studio with the SQL Migration extension

AnswersC, E

DMS supports automated online migrations with validation.

Why this answer

Azure Database Migration Service (DMS) is the correct choice because it is a fully managed service designed to automate the migration of on-premises SQL Server databases to Azure SQL Managed Instance with minimal downtime. It supports both schema and data validation through built-in assessment, data integrity checks, and cutover capabilities, making it ideal for large-scale automated migrations.

Exam trap

The trap here is that candidates often confuse the 'Azure SQL Migration tool' (a feature name) with the actual service 'Azure Database Migration Service,' or they mistakenly think SSMS or BACPAC can handle automated, large-scale migrations with validation.

Full explanation →

202

Drag & Dropmedium

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

The link requires a secondary instance first, then establishing the link, configuring as readable, monitoring, and failing over when needed.

Full explanation →

203

MCQhard

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

A.Implement Always Encrypted with column master key stored in Azure Key Vault.

B.Use dynamic data masking to obfuscate the sensitive columns for unauthorized users.

C.Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

D.Create a row-level security policy to restrict access to the sensitive rows.

AnswerA

Always Encrypts ensures column-level encryption and that the database never sees plaintext.

Why this answer

Always Encrypted is the correct choice because it ensures that sensitive data is encrypted at the column level and that the encryption keys are never revealed to the database engine. By storing the column master key in Azure Key Vault and using client-side encryption, the application manages the encryption and decryption process, so only authorized users with access to the key can read the plaintext data. This meets the requirement that encryption be managed by the application, not the database.

Exam trap

The trap here is that candidates often confuse dynamic data masking with encryption, or assume TDE provides column-level control, but the key differentiator is that Always Encrypted keeps encryption keys client-side, fulfilling the 'managed by the application' requirement.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data at query time for unauthorized users but does not encrypt the data at rest or in transit, and the database still holds the plaintext values, so it does not meet the requirement for application-managed encryption. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level granularity, and the encryption is managed by the database engine, not the application. Option D is wrong because row-level security restricts access to rows based on predicates but does not encrypt the data, and it is managed by the database, not the application.

Full explanation →

204

MCQmedium

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

A.SQL Agent jobs

B.Azure Data Factory

C.Azure Automation with PowerShell runbooks

D.Elastic Database Jobs

AnswerD

Elastic jobs can target a group of databases and execute T-SQL scripts.

Why this answer

Elastic Database Jobs are designed for executing T-SQL across many databases in a pool. SQL Agent is not available in Azure SQL Database. Azure Automation runbooks can run T-SQL but require more setup.

Azure Data Factory is for data movement, not T-SQL execution.

Full explanation →

205

Multi-Selectmedium

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

Select 2 answers

A.sys.dm_db_partition_stats

B.sys.dm_exec_query_stats

C.sys.dm_db_file_space_usage

D.sys.dm_db_log_space_usage

E.sys.dm_os_performance_counters

AnswersC, D

Shows data file space usage.

Why this answer

Options A and D are correct. sys.dm_db_log_space_usage shows transaction log space. sys.dm_db_file_space_usage shows data file space. Option B is wrong because sys.dm_db_partition_stats shows row counts, not space. Option C is wrong because sys.dm_exec_query_stats is for query performance.

Option E is wrong because sys.dm_os_performance_counters includes many counters but not direct space usage per database.

Full explanation →

206

MCQhard

You manage a fleet of 100 Azure SQL Databases used by different departments. You need to automate the deployment of schema changes across all databases with rollback capability. What is the best approach?

A.Create an Elastic Database Job that runs the schema script

B.Implement a CI/CD pipeline using Azure DevOps with Azure SQL Database deployment tasks

C.Use SQL Server Management Studio (SSMS) to run scripts against each database

D.Use Azure Automation with PowerShell to run Invoke-SqlCmd

AnswerB

Provides automated, repeatable deployments with rollback support.

Why this answer

Option B is correct because Azure SQL Database supports deployment pipelines with CI/CD using Azure DevOps or GitHub Actions, allowing automated schema changes with rollback. Option A is wrong because SQL Server Management Studio (SSMS) is a manual tool. Option C is wrong because Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback.

Option D is wrong because it lacks full automation and rollback.

Full explanation →

207

MCQmedium

You are a database administrator for Azure SQL Managed Instance. You need to configure a custom time zone for the instance because the application uses a specific time zone. What should you do?

A.Deploy a virtual machine running SQL Server instead.

B.Set the time zone for each database using ALTER DATABASE.

C.Use ALTER INSTANCE SET TIMEZONE to set the desired time zone.

D.Configure the time zone in the Azure portal under the instance settings.

AnswerC

This is the correct command to change the time zone.

Why this answer

Azure SQL Managed Instance runs on top of a Windows operating system, and the time zone for the entire instance is controlled at the host level. The `ALTER INSTANCE SET TIMEZONE` command is the correct T-SQL method to change the time zone for the instance, which then applies to all databases within that instance. This command allows you to set a custom time zone that the application requires, without needing to deploy a separate VM or configure each database individually.

Exam trap

The trap here is that candidates might assume time zone is a database-level setting (like in some other database systems) or that it can be configured through the Azure portal, but Azure SQL Managed Instance requires the T-SQL command `ALTER INSTANCE SET TIMEZONE` because the time zone is tied to the underlying Windows OS hosting the instance.

How to eliminate wrong answers

Option A is wrong because deploying a virtual machine running SQL Server is unnecessary and does not leverage the managed service benefits of Azure SQL Managed Instance; the time zone can be changed directly on the managed instance. Option B is wrong because `ALTER DATABASE` does not have a clause to set the time zone; time zone is an instance-level property, not a database-level property. Option D is wrong because the time zone for Azure SQL Managed Instance is not configurable through the Azure portal; it must be set using the T-SQL command `ALTER INSTANCE SET TIMEZONE`.

Full explanation →

208

Drag & Dropmedium

Drag and drop the steps to migrate an on-premises SQL Server database to Azure SQL Database using the Data Migration Assistant (DMA) in the correct order.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps

Order

Why this order

First assess and fix issues, then deploy the target, then migrate schema, then data.

Full explanation →

209

MCQhard

Your organization is migrating a legacy on-premises SQL Server database to Azure SQL Managed Instance. The database contains sensitive financial data. You need to implement column-level encryption so that even database administrators cannot view the plaintext data. The encryption keys must be stored in Azure Key Vault, and the application must be able to encrypt and decrypt data transparently. The application currently uses Entity Framework Core and runs on Azure App Service. You have the following requirements: - Use a solution that provides the strongest security by ensuring the database never has access to the plaintext. - Minimize changes to the application code. - The application must be able to perform equality searches on encrypted columns. What should you implement?

A.Use Always Encrypted with secure enclaves and randomized encryption.

B.Use dynamic data masking to hide the data from unauthorized users.

C.Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

D.Use Always Encrypted with deterministic encryption and store column master key in Azure Key Vault.

AnswerD

Deterministic encryption enables equality joins and lookups, and the database cannot decrypt the data.

Why this answer

Option A is correct because Always Encrypted with deterministic encryption allows equality searches and ensures the database never sees the plaintext. Client-side drivers handle encryption transparently. Option B is wrong because TDE does not provide column-level encryption.

Option C is wrong because secure enclaves require additional configuration and are not necessary for equality searches. Option D is wrong because dynamic data masking does not encrypt data.

Full explanation →

210

Multi-Selecthard

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

Select 3 answers

A.ARM templates with Bicep

B.Azure Migrate

C.Azure PowerShell

D.Azure Data Studio

E.Azure CLI

AnswersA, C, E

Declarative infrastructure as code.

Why this answer

Options A, C, and D are correct because ARM/Bicep templates can define the entire infrastructure, Azure CLI can be used in scripts, and Azure PowerShell can also be used. Option B (Azure Data Studio) is a client tool for querying, not automation. Option E (Azure Migrate) is for migration.

Full explanation →

211

MCQeasy

You need to ensure that all users accessing Azure SQL Database from outside the corporate network are required to use multi-factor authentication (MFA). What should you configure?

A.Enable Azure RBAC for the SQL server.

B.Configure a Conditional Access policy in Microsoft Entra ID.

C.Create an Azure Policy to require MFA.

D.Turn on Transparent Data Encryption (TDE).

AnswerB

Conditional Access policies can require MFA based on conditions like network location.

Why this answer

Conditional Access policies in Microsoft Entra ID (formerly Azure AD) allow you to enforce MFA based on network location, device state, or risk level. By configuring a policy that targets the Azure SQL Database application and requires MFA for all access from outside the corporate network, you meet the requirement without altering the database or server configuration.

Exam trap

The trap here is confusing Azure Policy (which governs resource configuration compliance) with Conditional Access (which governs user authentication and access conditions), leading candidates to choose Azure Policy when only Conditional Access can enforce MFA at the sign-in level.

How to eliminate wrong answers

Option A is wrong because Azure RBAC controls management-plane permissions (who can create, delete, or modify the SQL server), not data-plane authentication or MFA enforcement for user connections. Option C is wrong because Azure Policy enforces compliance rules on Azure resource configurations (e.g., requiring TDE or auditing), but it cannot enforce MFA at the authentication layer for database users. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, not in transit or during authentication, and has no effect on MFA requirements.

Full explanation →

212

MCQmedium

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

A.Create a stored procedure that uses SELECT INTO to copy old data to a history table and then TRUNCATE the partition.

B.Use ALTER INDEX REORGANIZE to compress old partitions.

C.Schedule a T-SQL script via Elastic Database Jobs that uses ALTER PARTITION FUNCTION to SPLIT the range and MERGE the oldest boundary.

D.Use the SWITCH PARTITION operation to move the oldest partition to a staging table and then drop the staging table.

AnswerC

ALTER PARTITION FUNCTION with SPLIT and MERGE is the standard way to add and remove partitions.

Why this answer

Splitting and merging partition ranges is done with T-SQL ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME. The SWITCH operation moves data to a staging table, but splitting does not require it. Option A is correct.

Option B is for columnstore indexes. Option C is for data archiving. Option D is for data movement, not partition management.

Full explanation →

213

MCQmedium

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

A.Configure a new long-term retention policy that retains monthly backups for 7 years.

B.Disable long-term backup retention and rely solely on point-in-time restore backups.

C.Set the point-in-time restore retention period to 7 years.

D.Migrate the database to Azure SQL Database and use geo-redundant storage.

AnswerA

LTR allows granular retention schedules; monthly for 7 years is compliant and cost-effective.

Why this answer

Option D is correct because configuring a long-term retention (LTR) policy with monthly backups for 7 years meets compliance while reducing cost by not retaining weekly/daily backups for the full 10 years. Option A is wrong because disabling LTR entirely loses compliance. Option B is wrong because moving to Azure SQL Database does not automatically reduce cost and may not be feasible.

Option C is wrong because point-in-time restore (PITR) retention is limited to 35 days, not 7 years.

Full explanation →

214

Multi-Selecteasy

You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?

Select 3 answers

A.sys.dm_exec_requests

B.sys.dm_os_wait_stats

C.sys.dm_exec_query_plan

D.sys.dm_exec_sessions

E.sys.dm_exec_query_stats

AnswersA, C, E

This DMV shows currently executing requests and their resource consumption.

Why this answer

Options A, C, and D are correct. sys.dm_exec_query_stats returns aggregated performance statistics for cached query plans. sys.dm_exec_requests shows currently executing requests. sys.dm_exec_query_plan shows the execution plan for cached queries. Option B is wrong because sys.dm_exec_sessions provides session-level information, not query-specific resource usage. Option E is wrong because sys.dm_os_wait_stats shows wait statistics, not query-specific resource use.

Full explanation →

215

Multi-Selectmedium

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

Select 2 answers

A.avg_cpu_percent

B.max_size_percent

C.avg_data_io_percent

D.avg_memory_usage_percent

E.avg_log_write_percent

AnswersC, E

Data IO utilization

Why this answer

Options B and D are correct. avg_data_io_percent measures data IO usage relative to limit; avg_log_write_percent measures log IO usage. Option A is CPU. Option C is memory.

Option E is storage space.

Full explanation →

216

MCQeasy

You need to audit all successful and failed login attempts on an Azure SQL Database. Which feature should you enable?

A.Microsoft Defender for SQL threat detection.

B.SQL Vulnerability Assessment.

C.Enable Microsoft Defender for Cloud's regulatory compliance dashboard.

D.SQL Server Audit with a server-level audit specification that includes SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP.

AnswerD

These audit action groups capture all login attempts.

Why this answer

SQL Server Audit is the correct feature because it allows you to capture both successful and failed login attempts at the server level by defining a server audit specification that includes the SUCCESSFUL_LOGIN_GROUP and FAILED_LOGIN_GROUP audit action groups. These groups specifically log authentication events, which is exactly what is needed to audit all login attempts. Microsoft Defender for SQL, Vulnerability Assessment, and regulatory compliance dashboards do not provide granular login event auditing.

Exam trap

The trap here is that candidates often confuse Microsoft Defender for SQL's threat detection (which does log some security events) with the dedicated, configurable SQL Server Audit feature that is required for explicit login auditing.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for SQL threat detection focuses on identifying anomalous database activities and potential threats, not on auditing individual login success or failure events. Option B is wrong because SQL Vulnerability Assessment is a tool for discovering, tracking, and remediating potential database vulnerabilities, not for capturing login audit logs. Option C is wrong because the regulatory compliance dashboard in Microsoft Defender for Cloud provides a view of compliance posture against standards like CIS or SOC 2, but does not itself generate or store login audit records.

Full explanation →

217

MCQeasy

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

A.Microsoft Entra ID authentication and firewall rules

B.VNet service endpoints and firewall rules

C.Advanced Threat Protection and VNet service endpoints

D.Private endpoint and VNet service endpoints

AnswerB

Service endpoints restrict traffic from a VNet; firewall rules allow specific public IP addresses.

Why this answer

To restrict access to an Azure SQL Database to traffic from a specific virtual network and a specific set of public IP addresses, you need to combine VNet service endpoints with firewall rules. VNet service endpoints allow you to restrict inbound traffic from a specific subnet in a virtual network, while firewall rules (IP-based) allow you to specify allowed public IP address ranges. Together, they provide a layered network security approach that meets the requirement.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules, service endpoints) with identity/security monitoring features (Entra ID, ATP), leading them to pick options that address authentication or threat detection instead of network access restrictions.

How to eliminate wrong answers

Option A is wrong because Microsoft Entra ID authentication controls identity and access (who can connect), not network-level traffic filtering (where traffic originates). Option C is wrong because Advanced Threat Protection is a security monitoring and threat detection service, not a network access control mechanism. Option D is wrong because Private endpoint and VNet service endpoints are both network-level features, but private endpoint uses a private IP address from your VNet and does not support allowing a specific set of public IP addresses; it only allows traffic from the VNet, not from public IPs.

Full explanation →

218

MCQeasy

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

A.The server's performance tier is changed to S2.

B.The OrdersDB database's service objective is updated to S2.

C.The database is scaled down from a higher tier to S2.

D.A new database named OrdersDB is created with S2 tier.

AnswerB

The command updates the database's service tier to S2.

Why this answer

Option C is correct because the command changes the service objective to S2, which updates the performance level of the database. Option A is wrong because the command only updates the current database, not the server. Option B is wrong because it's an update, not a creation.

Option D is wrong because S2 is a higher tier than S0? Actually S2 is higher than S0, but the command does not specify previous tier; it sets to S2.

Full explanation →

219

MCQeasy

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

A.Transparent Data Encryption (TDE)

B.SQL Vulnerability Assessment

C.Azure SQL Auditing

D.Diagnostic settings

AnswerC

Auditing can capture login events by auditing the SQL Server audit category.

Why this answer

Azure SQL Auditing is the correct feature because it tracks database events, including both successful and failed login attempts, and writes them to an audit log in your Azure Storage account, Log Analytics workspace, or Event Hubs. This meets the requirement to audit all authentication events for security and compliance purposes.

Exam trap

The trap here is that candidates often confuse Diagnostic settings (which capture metrics and resource logs) with Azure SQL Auditing (which captures detailed database event logs), leading them to select Option D instead of the correct feature for login auditing.

How to eliminate wrong answers

Option A is wrong because Transparent Data Encryption (TDE) performs real-time encryption of data at rest, not logging of login attempts. Option B is wrong because SQL Vulnerability Assessment scans for potential security misconfigurations and vulnerabilities, but does not capture login audit trails. Option D is wrong because Diagnostic settings capture platform-level metrics and resource logs (e.g., for the SQL server or database), but they do not provide the granular, event-level auditing of successful and failed logins that Azure SQL Auditing offers.

Full explanation →

220

MCQmedium

You have an Azure SQL Database that uses a serverless compute tier. You want to automate the process of pausing the database during non-business hours (8 PM to 6 AM) to save costs. The database should automatically resume when the first connection is attempted. What should you configure?

A.Create a SQL Agent job to run ALTER DATABASE PAUSE and ALTER DATABASE RESUME.

B.Configure an elastic job to scale down to 0 vCores during off-hours.

C.Set the auto-pause delay to 10 hours in the serverless configuration.

D.Use Azure Automation to run a script that calls the REST API to pause/resume the database.

AnswerC

The auto-pause delay can be configured to a maximum of 24 hours.

Why this answer

Option A is correct because serverless databases automatically pause after a period of inactivity and resume on first connection; you can set the auto-pause delay to 10 hours (from 8 PM to 6 AM). Options B and C are not applicable. Option D is for elastic pools.

Full explanation →

221

Multi-Selecthard

You need to audit all successful and failed login attempts to an Azure SQL Database server for compliance. The audit logs must be stored in a Log Analytics workspace for analysis and retention. Which two actions should you perform? (Choose two.)

Select 2 answers

A.Use SQL Server Audit to capture login events.

B.Enable auditing on the Azure SQL Database server and configure the audit destination as Log Analytics.

C.Configure diagnostic settings to send the SQL security audit events to Log Analytics.

D.Enable auditing on each database individually.

E.Enable login auditing by running ALTER SERVER CONFIGURATION SET AUDIT_LOGIN_EVENTS.

AnswersB, C

Auditing can send logs to Log Analytics.

Why this answer

Option B is correct because Azure SQL Database server-level auditing can be configured to send audit logs directly to a Log Analytics workspace, which is required for compliance analysis and retention. This captures both successful and failed login attempts at the server level, meeting the audit requirement without needing per-database configuration.

Exam trap

The trap here is that candidates confuse on-premises SQL Server audit commands (like ALTER SERVER CONFIGURATION) with Azure SQL Database's cloud-native auditing, which requires diagnostic settings or server-level audit configuration, not T-SQL commands.

Full explanation →

222

Matchingmedium

Match each Azure SQL Database security feature to its purpose.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Encrypts data at rest

Encrypts sensitive data in transit and at rest

Limits exposure of sensitive data by masking it to non-privileged users

Restricts access to rows based on user characteristics

Why these pairings

These features help protect data in Azure SQL Database.

Full explanation →

223

MCQmedium

You are designing a database monitoring solution for a critical Azure SQL Database. You need to retain performance data for 90 days and receive alerts when DTU usage exceeds 80%. Which combination of tools should you use?

A.Query Store with retention set to 90 days.

B.DMVs queried by a custom script.

C.Azure SQL Analytics (now integrated in Azure Monitor) and configure alerts.

D.Intelligent Insights and configure email notifications.

AnswerC

Azure Monitor allows long-term log retention and metric alerts.

Why this answer

Option C is correct because Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities. Option A is wrong because Query Store default retention is limited. Option B is wrong because Intelligent Insights provides analysis but not configurable retention.

Option D is wrong because DMVs only show current state.

Full explanation →

224

MCQmedium

Refer to the exhibit. You are reviewing the JSON configuration for an elastic job. The job is expected to run every week on Sunday at midnight UTC. However, the job is not executing as scheduled. What is the most likely cause?

A.The retention policy is set too low, causing the job to be pruned before execution.

B.The startTime is in the past; the job should have a future start time.

C.The job definition is missing the 'scheduleType' property.

D.The interval and frequency properties are conflicting; interval should be removed for weekly frequency.

AnswerD

For weekly frequency, interval is not used; it causes ambiguity.

Why this answer

The job configuration includes both an 'interval' property (e.g., 'PT1H' for every hour) and a 'frequency' property set to 'Weekly'. In Azure Elastic Jobs, when 'frequency' is specified, the 'interval' property is ignored or causes a conflict because the schedule is defined by the cron-like recurrence pattern. Removing the 'interval' property resolves the conflict and allows the weekly schedule to execute correctly.

Exam trap

Microsoft often tests the misconception that both 'interval' and 'frequency' must always be specified together, when in fact for weekly or monthly frequencies, 'interval' is not applicable and its presence causes a scheduling conflict.

How to eliminate wrong answers

Option A is wrong because the retention policy controls how long job history is kept, not whether the job executes; a low retention policy would prune history but not prevent execution. Option B is wrong because Azure Elastic Jobs allow startTime in the past; the job will run at the next scheduled recurrence after the startTime. Option C is wrong because the 'scheduleType' property is not required for elastic job schedules; the schedule is defined by 'frequency' and 'interval' properties.

Full explanation →

225

Multi-Selecthard

Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?

Select 2 answers

A.Execute sp_refreshsqlmodule on the stored procedure

B.Use the KEEPFIXED PLAN query hint

C.Use the OPTIMIZE FOR UNKNOWN query hint

D.Use the RECOMPILE query hint

E.Ensure parameter data types match the column data types

AnswersB, E

KEEPFIXED PLAN prevents recompilation due to statistics changes, reducing recompilations.

Why this answer

Options B and D are correct. B is correct because using RECOMPILE hint forces recompilation only for that execution, but the question asks to reduce recompilations, so that's wrong. Wait, the correct answers are A and C.

Option A is correct because using KEEPFIXED PLAN prevents recompilations due to statistics changes. Option C is correct because keeping parameter data types consistent avoids recompilations due to type mismatch. Option B is wrong because RECOMPILE hint increases recompilations.

Option D is wrong because OPTIMIZE FOR UNKNOWN can cause plan instability but not reduce recompilations. Option E is wrong because sp_refreshsqlmodule does not reduce recompilations.

Full explanation →

Page 3 of 13

All pages

1 2 3 4 5 6 7 8 9 10 11 12 13

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

Plan and configure a high availability and disaster recovery environment Plan and implement data platform resources Monitor, configure, and optimize database resources Configure and manage automation of tasks Plan and configure high availability and disaster recovery Implement a secure environment

See all domains with question counts →