Courseiva

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

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

Take a mock examExam hub
Page 2

Page 3 of 13

Page 4
151
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

Automatic plan correction (FORCE_LAST_GOOD_PLAN) is the correct feature to automatically force a plan that performed better than the existing plan. Option A (DROP INDEX) and Option B (CREATE INDEX) are for index management, not plan forcing. Option D (Intelligent Insights) provides analysis but does not automatically force plans.

Full explanation →
152
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

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 →
153
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

A SQL Server Failover Cluster Instance (FCI) with shared storage provides automatic failover within seconds and zero data loss, as the shared storage ensures no data is lost during failover. Option A (Backup and restore to another region) does not provide automatic failover and can have data loss up to the last backup. Option C (Always On Availability Group with synchronous commit) can achieve zero data loss and automatic failover if properly configured, but it requires a listener and may have more complex setup; however, FCI is typically the preferred solution for VM-level HA with zero data loss.

Option D (Log shipping) involves manual failover and potential data loss, so it does not meet the requirements.

Full explanation →
154
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

Using smaller log writes reduces log write latency, which improves write performance in Hyperscale. Option B is correct: Increasing the log rate limit allows higher log throughput, directly enhancing write performance. Option E is correct: Enabling Accelerated Database Recovery reduces log amplification and improves log write efficiency, benefiting write performance.

Option C is incorrect: The Hyperscale service tier is distinct from Business Critical; moving to Business Critical does not apply to Hyperscale. Option D is incorrect: Read scale-out improves read performance, not write performance.

Full explanation →
155
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 E are correct. Failover groups with automatic failover allow recovery by failing over to a secondary region (B). Geo-restore from geo-redundant backups restores the database to any Azure region using the most recent geo-redundant backup (E).

Option A is incorrect because log shipping is not supported for Azure SQL Database. Option C is incorrect because point-in-time restore can only restore to a point in time within the same region. Option D is incorrect because copying a database from a geo-redundant backup is not a direct method; you must perform a geo-restore.

Full explanation →
156
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

Using ApplicationIntent=ReadOnly in connection strings directs read-only queries to the secondary replica. To ensure consistent data during replication lag, you must verify the secondary is up-to-date before querying, for example by checking the replication lag via DMVs or using the 'latest' hint. This ensures reporting queries see consistent data.

Option B is incorrect because read scale-out is specific to Hyperscale databases, not active geo-replication. Option C is incorrect because an auto-failover group with read-write endpoint does not guarantee read consistency for reporting queries. Option D is incorrect because a failover group with read-only endpoint does not enforce consistency checks.

Full explanation →
157
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

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 →
158
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

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 →
159
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

Correct matches: A - Azure Database Migration Service (online/offline migration), B - Data Migration Assistant (assessment), C - SSMA (cross-platform migration), D - Azure Migrate (central hub). Common confusion: mixing up the migration service with offline-only tools or ignoring the assessment step.

Full explanation →
160
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 →
161
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
AnswerB

3 is correct. The Business Critical tier always has three readable secondary replicas that can serve read-only queries.

Why this answer

In the Business Critical tier of Azure SQL Database, there is a fixed set of four replicas: one primary and three secondary replicas. All secondary replicas are readable and can serve read-only queries. Therefore, the maximum number of readable replicas available for read-only queries is 3.

Option C (4) is incorrect because it would include the primary replica, which is not typically used for read-only queries unless read-scale is enabled, but even then the maximum readable replicas (including primary) is 4, but the question asks specifically for 'readable replicas available for read-only queries', which are the secondaries. The highAvailabilityReplicaCount property shown in the exhibit is not configurable for the Business Critical tier; it is only applicable to the Hyperscale tier. In Business Critical, the replica count is fixed at 4 total, with 3 readable secondaries.

Full explanation →
162
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

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 →
163
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

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 →
164
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 change 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

Enabling serverless with auto-pause allows SalesDB to automatically pause during off-hours (6 PM to 8 AM) when not in use, significantly reducing compute costs for a database used only during business hours. Option D is incorrect because reducing vCores alone does not stop charges during idle time, while auto-pause eliminates compute costs entirely when paused. Option A is incorrect because storage reduction does not address the core issue of paying for idle compute.

Option B is incorrect because Hyperscale is designed for high-performance, large-scale workloads and is not cost-optimized for a schedule-based usage pattern.

Full explanation →
165
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

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 →
166
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

Forced parameterization (option D) is the correct mitigation because it instructs the SQL Server query optimizer to treat all literal values in queries as parameters, which reduces the likelihood of parameter sniffing causing suboptimal plans for repeated queries. This is done at the database level without requiring any changes to application code, making it ideal for scenarios where code modification is not feasible.

Exam trap

The trap here is that candidates often confuse 'clearing the plan cache' (option B) as a permanent fix, when it is only a temporary workaround that does not address the root cause of parameter sniffing.

How to eliminate wrong answers

Option A is wrong because adding OPTION (RECOMPILE) to all queries would require modifying application code, which violates the constraint of not changing application code. Option B is wrong because clearing the plan cache with DBCC FREEPROCCACHE is a temporary fix that removes all cached plans but does not prevent parameter sniffing from reoccurring when queries are recompiled. Option C is wrong because enabling 'Optimize for Ad Hoc Workloads' only defers caching of single-use ad hoc query plans to reduce memory bloat; it does not address parameter sniffing or forced parameterization.

Full explanation →
167
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

It leverages Azure SQL Database's built-in automatic tuning feature while still allowing you to enforce a schedule. By setting the 'INDEX_CREATE' option to 'OFF' and using a script (e.g., via Azure Automation or SQL Server Agent) to toggle it on during off-peak hours and off during peak hours, you can automatically create missing indexes outside the prohibited window. This approach combines the intelligence of automatic tuning with your scheduling requirement.

Option A does not allow scheduling; Option C relies on manual scripts without the built-in tuning intelligence; Option D is similar to B but less integrated with automatic tuning.

Full explanation →
168
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

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 →
169
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

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 →
170
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 →
171
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

Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.

Exam trap

The trap here is that candidates often confuse wait statistics (sys.dm_os_wait_stats) with query-level performance data, but wait stats show system-wide bottlenecks, not the specific queries causing high DTU.

Full explanation →
172
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 a built-in view of query performance metrics, including top queries by CPU, duration, and execution count, making it suitable for generating automated weekly reports.

Why this answer

Query Performance Insight in Azure SQL Database provides query-level performance metrics including CPU, duration, and execution count. It can be used to identify the top 10 queries by CPU consumption and automate report generation. Option D is correct.

Option A (SQL Server Profiler) is a traditional tool for capturing SQL Server events, not for automated reporting in Azure. Option B (Database Engine Tuning Advisor) focuses on index recommendations, not report generation. Option C (Azure Monitor Metrics) provides platform-level metrics but lacks query-level granularity.

Exam trap

Candidates may confuse Query Performance Insight with Azure Monitor Metrics, but Query Performance Insight is specifically designed for query-level analysis in Azure SQL Database.

Full explanation →
173
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

Implementing table partitioning allows you to efficiently manage large tables by splitting them into smaller, more manageable partitions. You can then move older partitions to a separate database with a lower service tier (e.g., from General Purpose to Basic), which reduces storage costs while keeping the archived data queryable when needed. Stretch Database (option A) is deprecated and no longer recommended.

Elastic Query (option C) is used to query remote databases, not to archive data. Hyperscale (option D) is designed for high scalability and performance, not for cost reduction on infrequently accessed data.

Full explanation →
174
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 B and E are correct. Automatic tuning can identify and correct query plan regressions (B) and automatically create missing indexes (E). Option A is wrong because automatic tuning does not automatically scale the service tier; that's auto-scale.

Option C is wrong because automatic tuning does not automatically create read replicas. Option D is wrong because automatic tuning does not automatically update statistics.

Exam trap

Automatic tuning in Azure SQL Database includes only index creation and plan regression correction. It does not include auto-scaling, read replicas, or statistics updates.

Full explanation →
175
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 →
176
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 is required for Basic availability groups on Azure VM to enable automatic failover.

Why this answer

A Basic availability group on SQL Server 2022 Azure VM requires a Windows Server Failover Cluster (WSFC) for automatic failover. Without WSFC, automatic failover is not possible. Azure Load Balancer is used for client connectivity via the listener, but does not provide automatic failover.

Virtual network gateway is for site-to-site connectivity. Availability group listener requires WSFC to function and does not itself enable automatic failover.

Full explanation →
177
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

To perform a planned failover with zero data loss for an Azure SQL Managed Instance failover group, you should use the Azure portal (or PowerShell/CLI) to initiate a planned failover. This ensures that all transactions are synchronized before switching roles, preventing data loss. Option A is incorrect because taking the primary offline before failover would cause downtime and potential data loss.

Option C is incorrect because scaling down does not facilitate a planned failover. Option D is incorrect because removing the failover group would break replication and is unnecessary.

Full explanation →
178
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

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 →
179
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

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 →
180
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

Stale statistics can cause the optimizer to significantly underestimate cardinality. In this scenario, the optimizer mistakenly estimated a low number of rows, leading it to choose a nested loops join (optimal for small datasets) instead of a hash join (better for larger datasets). This is a classic plan regression due to statistics staleness.

Option A is incorrect: parameter sniffing typically results in a plan optimized for the initial parameter values, but it does not directly cause a cardinality underestimation that forces a switch from hash to nested loops.

Option B is incorrect: dropping an index would likely cause a table scan or a different plan (e.g., clustered index scan), but the specific change from hash join to nested loops is not characteristic of a missing index; it is more indicative of a cardinality misestimate.

Option C is incorrect: forced parameterization encourages plan reuse and can prevent plan changes, not cause them.

Full explanation →
181
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

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 →
182
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 E are correct. Both managed instances must have the same service tier (A). The secondary instance must have no user databases (B).

Both instances must be in the same hardware generation (e.g., Gen5) (E). Option C is incorrect because the instances can be in different subnets within the same virtual network. Option D is incorrect because having a user database on the primary is not a requirement for configuring a failover group.

Full explanation →
183
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

(2) is correct. The highAvailabilityReplicaCount property in the ARM template is set to 2, which creates two readable secondary replicas. With read scale-out enabled, both are available for read-only queries. This is not related to failover groups; it is about Hyperscale read scale-out.

Why this answer

(2). The highAvailabilityReplicaCount property in the ARM template is set to 2, which creates two readable replicas in addition to the primary. With read scale-out enabled, both replicas are available for read-only queries.

Option A (3) is incorrect because it counts the primary as a readable replica, but the primary is not used for read-only queries in read scale-out. Option C (1) is incorrect because it underestimates the number of replicas. Option D (4) is incorrect because the value in the template is 2, not 4.

Full explanation →
184
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, C, and D are correct. Automatic tuning for Azure SQL Database can automatically apply FORCE LAST GOOD PLAN, CREATE INDEX, and DROP INDEX recommendations. Option B is incorrect because modifying statistics is not an automatic tuning option; it requires manual execution.

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

Full explanation →
185
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, when moving a database to an elastic pool, you must not specify the RequestedServiceObjectiveName parameter because the service objective is determined by the pool. The script incorrectly sets both Edition and RequestedServiceObjectiveName, causing a conflict.

Option C correctly identifies this issue. Option A is incorrect because the pool not existing would cause an error, not a silent failure. Option B is incorrect because the ServerName parameter does not require a fully qualified domain name; the resource group and server name suffice.

Option D is incorrect because the -Force parameter is not required for pool moves.

Full explanation →
186
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

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 →
187
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

Azure SQL Managed Instance includes SQL Server Agent, which can natively schedule T-SQL jobs with minimal latency. Option A is incorrect because Elastic Database Jobs require a separate job database and are designed for multi-database scalability, adding overhead. Option B is incorrect because Azure Logic Apps introduce external orchestration, potentially increasing latency and cost.

Option D is incorrect because Azure Functions require external connectivity and are not as tightly integrated as SQL Agent.

Full explanation →
188
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

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 →
189
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 →
190
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 →
191
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
1Step 1
2Step 2
3Step 3
4Step 4

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 →
192
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 →
193
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 →
194
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 C and D are correct. sys.dm_db_file_space_usage provides data file space usage, and sys.dm_db_log_space_usage provides transaction log space usage. Option A is incorrect because sys.dm_db_partition_stats shows row counts and partition-level information, not storage space. Option B is incorrect because sys.dm_exec_query_stats is for query performance metrics.

Option E is incorrect because sys.dm_os_performance_counters includes various performance counters but does not directly show per-database space usage.

Full explanation →
195
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

Azure DevOps CI/CD pipeline with SQL Database deployment tasks provides automated schema changes with built-in rollback via deployment history and script versioning.

Why this answer

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 Elastic Database Jobs are for ad-hoc scripts, not for complex deployments with rollback. Option C is wrong because SQL Server Management Studio (SSMS) is a manual tool.

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

Full explanation →
196
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 →
197
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
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

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

Full explanation →
198
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

Always Encrypted with deterministic encryption provides column-level encryption, stores the column master key in Azure Key Vault, and ensures the database never sees plaintext. The client-side driver in Entity Framework Core handles encryption transparently, and deterministic encryption allows equality searches. Option A is incorrect because randomized encryption does not support equality searches; secure enclaves could enable that but add complexity.

Option B is incorrect because dynamic data masking does not encrypt data; it only hides it from unauthorized users at query time, and the database still has access to plaintext. Option C is incorrect because Transparent Data Encryption (TDE) encrypts the entire database at rest, not individual columns, and does not prevent the database from seeing plaintext during operations.

Full explanation →
199
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 E are correct because ARM/Bicep templates can define the entire infrastructure, Azure PowerShell can be used in scripts, and Azure CLI can also be used. Option B (Azure Migrate) is for migration, not automation. Option D (Azure Data Studio) is a client tool for querying, not automation.

Full explanation →
200
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 →
201
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

To automate monthly partition maintenance, you need to add a new partition for the next month (split the range) and remove the oldest partition (merge the oldest boundary). The T-SQL commands ALTER PARTITION FUNCTION ... SPLIT RANGE and ALTER PARTITION FUNCTION ...

MERGE RANGE accomplish this. Scheduling these via Elastic Database Jobs provides automation. Option A's SELECT INTO and TRUNCATE are not partition management operations.

Option B's ALTER INDEX REORGANIZE is for index maintenance, not partition boundary changes. Option D's SWITCH PARTITION moves data without altering the partition function/scheme.

Full explanation →
202
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

Correct. A new LTR policy with monthly backups for 7 years meets compliance and reduces costs by not retaining weekly/daily backups for the full 10 years.

Why this answer

Configuring a new long-term retention (LTR) policy that retains monthly backups for 7 years meets the compliance requirement while reducing storage costs compared to keeping weekly/daily backups for 10 years. LTR allows you to specify retention periods for weekly, monthly, and yearly backups; setting monthly backups to 7 years satisfies the requirement without extra cost. Option B is incorrect because disabling LTR entirely would lose the compliance capability.

Option C is incorrect because point-in-time restore (PITR) retention is limited to 35 days, not 7 years. Option D is incorrect because migrating to Azure SQL Database does not inherently reduce costs and may introduce other issues.

Full explanation →
203
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

The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance.

Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.

Full explanation →
204
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 C and E are correct. avg_data_io_percent measures data IO usage relative to the limit, and avg_log_write_percent measures log IO usage—both indicate disk IO bottlenecks. Option A (avg_cpu_percent) is CPU usage. Option B (max_size_percent) is storage space.

Option D (avg_memory_usage_percent) is memory.

Full explanation →
205
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 →
206
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 →
207
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

The 'az sql db update' command updates an existing database's service objective to S2. This immediate effect changes the performance level of the OrdersDB database. Option A is incorrect because the command targets the database, not the server.

Option C is incorrect because the command does not specify scaling direction; it simply sets the service objective, which could be an upgrade or downgrade depending on the current setting. Option D is incorrect because the update command does not create a new database; it modifies the existing one.

Full explanation →
208
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 →
209
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

The serverless compute tier in Azure SQL Database automatically pauses after a period of inactivity and resumes on the first connection. By setting the auto-pause delay to 10 hours (covering the 8 PM to 6 AM window), the database will pause during non-business hours and automatically resume when a connection is attempted. Options A and D are unnecessary because the serverless tier handles this natively, and Option B is incorrect because elastic jobs are not used for scaling to 0 vCores.

Full explanation →
210
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

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 →
211
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

TDE protects data at rest by encrypting the database files. Always Encrypted protects data in use by encrypting on the client side. Dynamic Data Masking hides sensitive data in query results from unauthorized users.

Common confusions involve mixing up TDE and Always Encrypted.

Full explanation →
212
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

Correct because Azure SQL Analytics (now part of Azure Monitor) provides long-term retention and alerting capabilities.

Why this answer

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 DMVs only show current state and are not designed for long-term retention and alerting.

Option D is wrong because Intelligent Insights provides analysis but not configurable retention.

Full explanation →
213
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 →
214
MCQhard

Refer to the exhibit. A backup administrator checks the protection status of a VM and receives an error. What is the most likely cause?

A.The VM has been deleted from Azure.
B.The backup vault is in a different region than the VM.
C.The backup policy has been deleted, causing the VM to be unprotected.
D.The VM has been moved to a different resource group.
AnswerA

The error clearly states the VM was not found, likely deleted.

Why this answer

The error code 'UserErrorVMNotFound' and message indicate that the VM 'myVM' has been deleted. The backup item exists but the underlying VM is gone, so backups can no longer be performed.

Full explanation →
215
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 E are correct. Option B: Using the KEEPFIXED PLAN query hint prevents recompilations caused by statistics changes, ensuring the query plan remains stable. Option E: Ensuring parameter data types match the column data types avoids type mismatches that trigger recompilations.

Option A (sp_refreshsqlmodule) updates metadata but doesn't reduce recompilations. Option C (OPTIMIZE FOR UNKNOWN) can lead to plan instability without reducing recompilations. Option D (RECOMPILE) forces recompilation on each execution, increasing recompilations.

Full explanation →
216
MCQhard

Your company has a critical Azure SQL Database named SalesDB in the West US region. To meet disaster recovery requirements, you configured active geo-replication with a readable secondary replica in East US. You need to automate failover in the event of a regional outage. The solution must ensure that the failover is initiated automatically based on health metrics and that the application connection string is updated to point to the new primary. Currently, the application uses a connection string with the primary server name. You have the following requirements: minimize manual intervention, use built-in Azure features, and ensure that failover is tested regularly. You consider the following options: A) Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover. B) Configure auto-failover group with read-write failover policy and grace period. C) Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS. D) Use Azure Automation with a runbook that checks the database status and initiates failover via REST API. Which option best meets the requirements?

A.Use Azure Traffic Manager with endpoint monitoring to route traffic to the primary and automatically failover.
B.Create an Azure Logic App that queries the database health and runs a PowerShell script to perform failover and update DNS.
C.Configure auto-failover group with read-write failover policy and grace period.
D.Use Azure Automation with a runbook that checks the database status and initiates failover via REST API.
AnswerC

Auto-failover groups automatically fail over based on health metrics and provide a listener endpoint that updates automatically.

Why this answer

Auto-failover groups are the built-in Azure feature designed specifically for automating failover of Azure SQL Database geo-replication. They support a read-write failover policy with a configurable grace period that automatically triggers failover based on health metrics, and they automatically update the connection string endpoint for applications using the failover group listener name, not the individual server name. This minimizes manual intervention and allows regular testing via manual failover without affecting the production environment.

Exam trap

The trap here is that candidates may think custom automation (Logic App or Automation runbook) is needed for automatic failover, but they overlook that auto-failover groups already provide built-in, policy-driven automatic failover with connection string transparency, which is the simplest and most reliable solution for this scenario.

How to eliminate wrong answers

Option A is wrong because Azure Traffic Manager operates at the DNS level and does not natively understand Azure SQL Database geo-replication health metrics; it cannot automatically trigger a geo-failover or update the database replication state, and it would require custom endpoint monitoring and manual failover scripts. Option B is wrong because creating a Logic App that runs a PowerShell script introduces custom code, manual maintenance, and potential latency, which does not minimize manual intervention or use built-in Azure features as effectively as auto-failover groups. Option D is wrong because Azure Automation with a runbook that checks database status and initiates failover via REST API is a custom solution that requires development, testing, and ongoing management, and it does not provide automatic connection string updates or the built-in grace period and health monitoring of auto-failover groups.

Full explanation →
217
MCQeasy

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

A.Tempdb has allocation contention.
B.The transaction log is on a slow I/O subsystem.
C.Queries are blocked by locks.
D.CPU is under pressure.
AnswerB

WRITELOG waits directly relate to log write performance.

Why this answer

A high percentage of WRITELOG waits indicates that transaction log writes are slow, often due to I/O latency on the log drive. Therefore, the most likely cause is a slow I/O subsystem for the transaction log (Option B). Option A is incorrect because tempdb allocation contention manifests as PAGELATCH waits.

Option C is incorrect because blocking is represented by LCK_M_* waits. Option D is incorrect because CPU pressure shows SOS_SCHEDULER_YIELD waits.

Full explanation →
218
MCQeasy

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON. The source database 'db1' was created on 2025-03-01 and has a current backup retention of 7 days. The restore point is 2025-03-15T10:00:00Z. What will be the result of deploying this template?

A.The deployment will fail because the restore point is older than 7 days.
B.The deployment will succeed but the restore point will be ignored and the latest backup will be used.
C.The deployment will fail because the source database is not geo-replicated.
D.The deployment will succeed and create a new database restored to the specified point in time.
AnswerD

The restore point is valid and within retention.

Why this answer

The ARM template specifies a restore point of 2025-03-15T10:00:00Z. Azure SQL Database point-in-time restore (PITR) uses a sliding window of the configured backup retention period (7 days) backward from the current date. Assuming the current date is 2025-03-22, the retention window covers from 2025-03-15 to 2025-03-22.

The restore point falls exactly at the start of this window, so it is valid. The deployment will succeed and create a new database restored to that exact timestamp.

Exam trap

The trap is that candidates may incorrectly think the 7-day retention is counted forward from the database creation date, making them believe a restore point 14 days after creation is too old. In reality, Azure SQL Database retention is a sliding window backward from the current date, so any restore point within the last 7 days is valid regardless of when the database was created.

How to eliminate wrong answers

Option A is wrong because the restore point 2025-03-15T10:00:00Z is only 14 days after the database creation date (2025-03-01), which is well within the 7-day backup retention period (backups are retained from creation date forward, not backward). Option B is wrong because the restore point is valid and will be honored; Azure SQL Database does not ignore a specified restore point within the retention window. Option C is wrong because geo-replication is not a prerequisite for point-in-time restore; PITR works on any single database regardless of geo-replication status.

Full explanation →
219
MCQhard

You are responsible for a SQL Server 2019 instance on an Azure VM. The VM is part of a failover cluster instance (FCI) using Azure shared disks. During a recent failover test, the cluster took 15 minutes to bring the database online. You need to reduce the failover time to under 5 minutes. What should you do?

A.Remove unnecessary databases from the FCI and distribute them to other instances.
B.Upgrade the VM to a larger size with more CPU and memory.
C.Increase the size of the Azure shared disks.
D.Enable instant file initialization on all SQL Server instances.
AnswerA

Fewer databases to recover reduces failover time.

Why this answer

Removing unnecessary databases from the FCI reduces the number of databases that need to be recovered during failover, thereby decreasing the overall failover time. Option A is correct. Option B is incorrect because upgrading the VM does not directly reduce the number of databases to recover.

Option C is incorrect because increasing disk size does not improve failover time. Option D is incorrect because instant file initialization helps with file growth, not failover recovery.

Full explanation →
220
MCQeasy

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

A.sys.dm_exec_query_stats
B.sys.dm_exec_requests
C.sys.dm_db_index_usage_stats
D.sys.dm_db_resource_stats
AnswerA

Returns aggregate performance statistics for cached query plans.

Why this answer

Sys.dm_exec_query_stats. This DMV returns aggregated performance statistics for cached query plans, including total_worker_time (which represents total CPU time). By filtering on last_execution_time, you can identify queries executed within the last hour. sys.dm_exec_requests (B) shows only currently executing requests, not historical data. sys.dm_db_index_usage_stats (C) tracks index seek/scan operations, not CPU usage. sys.dm_db_resource_stats (D) provides resource consumption at the database level (e.g., DTU, CPU percentage), not per query.

Full explanation →
221
MCQeasy

You need to prevent users from accidentally deleting an Azure SQL Database. What should you configure?

A.Apply a 'CanNotDelete' Azure Resource Lock on the resource group.
B.Revoke the db_ddladmin role from users.
C.Create an Azure Policy to deny SQL Database creation.
D.Set a deny rule in the SQL Database firewall.
AnswerA

Resource lock prevents deletion of Azure resources.

Why this answer

A 'CanNotDelete' Azure Resource Lock on the resource group prevents any user, including those with high-level permissions like Owner, from deleting the Azure SQL Database. This lock overrides all role-based access control (RBAC) permissions at the resource or resource group level, ensuring accidental deletion is blocked even if a user has delete permissions.

Exam trap

The trap here is that candidates confuse database-level permissions (like db_ddladmin) with Azure Resource Manager-level operations, mistakenly thinking that revoking schema modification rights will prevent database deletion, when in fact deletion is an ARM operation controlled by locks or RBAC at the subscription/resource group scope.

How to eliminate wrong answers

Option B is wrong because revoking the db_ddladmin role prevents users from modifying the database schema (e.g., creating or altering tables), but it does not prevent deletion of the database itself, which is an Azure Resource Manager (ARM) operation, not a SQL Server-level operation. Option C is wrong because creating an Azure Policy to deny SQL Database creation prevents new databases from being provisioned, but it does not protect an existing database from being deleted. Option D is wrong because setting a deny rule in the SQL Database firewall controls network access to the database (blocking IP addresses), but it has no effect on the ability to delete the database resource via ARM.

Full explanation →
222
MCQhard

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

A.The server name in the template does not match the actual server
B.The storage container URI is incorrectly formatted
C.The SAS token has expired or is invalid
D.The database is not in the same region as the storage account
AnswerC

Expired or invalid SAS token is a common cause of access errors.

Why this answer

The error indicates that the storage account is not accessible. In the template, a SAS token is used to authenticate access to the storage container for long-term backup retention. The most common reason for this error is that the SAS token has expired or is invalid, as SAS tokens have an expiration date.

Therefore, Option C is correct. Option A is incorrect because the server name mismatch would cause a different error. Option B is incorrect because the URI format may be valid, but the token is the issue.

Option D is incorrect because cross-region storage is supported for long-term retention.

Full explanation →
223
MCQhard

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

A.Auditing will fall back to Log Analytics workspace.
B.Auditing will work because managed identity is used.
C.Auditing will fail because the storage account access key is null.
D.Auditing will write to the storage account using the system-assigned managed identity.
AnswerC

Blob auditing requires a storage account key or a valid subscription ID with appropriate permissions; null key indicates misconfiguration.

Why this answer

The exhibit shows that the storage account access key is null, which means Azure SQL Database cannot authenticate to the storage account using the access key. Without a valid access key or a configured managed identity, blob auditing will fail because the database cannot write audit logs to the specified storage container. Option C is correct because a null access key directly prevents auditing from functioning when no alternative authentication method is configured.

Exam trap

The trap here is that candidates assume managed identity is automatically used when the access key is null, but in reality, managed identity must be explicitly configured and granted permissions, and the exhibit shows no such configuration.

How to eliminate wrong answers

Option A is wrong because auditing does not automatically fall back to Log Analytics workspace; the audit destination is explicitly set to storage, and if storage fails, auditing fails entirely unless a different destination is configured in the policy. Option B is wrong because managed identity is not automatically used; it must be explicitly enabled and assigned to the SQL Database, and the exhibit shows no indication of a managed identity being configured. Option D is wrong because writing to the storage account using a system-assigned managed identity requires that the managed identity be enabled and that the storage account grants appropriate RBAC permissions (e.g., Storage Blob Data Contributor) to that identity, which is not shown in the exhibit.

Full explanation →
224
MCQhard

You are migrating an on-premises SQL Server database to Azure SQL Database. The database uses Service Broker for asynchronous messaging. After migration, you notice that performance is degraded. What should you do to optimize?

A.Enable Service Broker in Azure SQL Database by setting ENABLE_BROKER.
B.Scale up the database to a higher service tier to improve Service Broker performance.
C.Replace Service Broker with Azure Queue Storage or Event Grid.
D.Configure Service Broker to use external activation via Azure Functions.
AnswerC

Azure SQL Database does not support Service Broker; use PaaS messaging services.

Why this answer

Service Broker is not fully supported in Azure SQL Database; it has performance and reliability limitations. The recommended approach for asynchronous messaging is to use Azure-native services like Queue Storage or Event Grid. Therefore, the correct action is to replace Service Broker (Option C).

Option A is incorrect because while ENABLE_BROKER can be used, it is not recommended for production due to poor performance. Option B is incorrect because scaling up does not resolve the fundamental feature incompatibility. Option D is incorrect because external activation via Azure Functions does not address the root cause and adds complexity.

Full explanation →
225
MCQhard

Your company is planning to migrate on-premises SQL Server databases to Azure SQL Managed Instance. The security team requires that all database connections be encrypted and that the server's identity be verified using a certificate from a trusted public certificate authority (CA). What should you configure?

A.Set the 'Force Encryption' property to True for the managed instance.
B.Install a custom CA-signed certificate on the managed instance.
C.Enable Transparent Data Encryption (TDE) with a server certificate.
D.Configure Always Encrypted with column master key in Key Vault.
AnswerA

Correct: This ensures encryption for all connections, and the server certificate is from a trusted CA.

Why this answer

Setting 'Force Encryption' to True on the managed instance enforces TLS encryption for all client connections and ensures the server presents a certificate from a trusted public CA to verify its identity. This meets both requirements: encryption of data in transit and server identity verification via a trusted certificate chain.

Exam trap

The trap here is that candidates confuse encryption in transit (Force Encryption/TLS) with encryption at rest (TDE) or column-level encryption (Always Encrypted), and mistakenly think a custom CA certificate is needed when Azure SQL Managed Instance already uses a trusted public CA certificate by default.

How to eliminate wrong answers

Option B is wrong because Azure SQL Managed Instance automatically provisions a certificate from a trusted public CA (Microsoft's CA) for TLS connections; installing a custom CA-signed certificate is not supported and would not replace the built-in certificate. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest, not in transit, and does not address connection encryption or server identity verification. Option D is wrong because Always Encrypted protects sensitive data at the column level with client-side encryption and a column master key in Key Vault, but it does not enforce encryption for all database connections or verify the server's identity via a public CA certificate.

Full explanation →
Page 2

Page 3 of 13

Page 4

All pages

12345678910111213

Practice DP-300 by domain

Target a specific domain to shore up weak areas.

Plan and configure a high availability and disaster recovery environmentPlan and implement data platform resourcesMonitor, configure, and optimize database resourcesConfigure and manage automation of tasksPlan and configure high availability and disaster recoveryImplement a secure environment
See all domains with question counts →