Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 301–375

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

Take a mock examExam hub
Page 4

Page 5 of 13

Page 6
301
MCQmedium

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

A.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = OFF
B.Set 'Force Encryption' = ON and 'Trust Server Certificate' = OFF
C.Set 'Force Encryption' = ON and 'Trust Server Certificate' = ON
D.Set 'Force Encryption' = OFF and 'Trust Server Certificate' = ON
AnswerB

This ensures encryption and certificate validation.

Why this answer

Setting 'Force Encryption' = ON ensures that all connections to Azure SQL Managed Instance use TLS encryption, while setting 'Trust Server Certificate' = OFF forces the client to validate the server's certificate against a trusted certificate authority (CA). This combination guarantees both data-in-transit encryption and server identity verification, meeting the requirement for a mission-critical application.

Exam trap

The trap here is that candidates often confuse 'Trust Server Certificate' = ON as a convenience setting that simplifies connections, not realizing it disables certificate validation and undermines security for mission-critical workloads.

How to eliminate wrong answers

Option A is wrong because setting 'Force Encryption' = OFF allows unencrypted connections, violating the encryption requirement. Option C is wrong because setting 'Trust Server Certificate' = ON instructs the client to trust the server certificate without validation, bypassing identity verification and potentially allowing man-in-the-middle attacks. Option D is wrong because setting 'Force Encryption' = OFF permits unencrypted connections, and 'Trust Server Certificate' = ON disables certificate validation, failing both encryption and identity verification requirements.

Full explanation →
302
MCQhard

You have an Azure SQL Managed Instance configured with failover groups to a secondary region. During a disaster, you perform a planned failover to the secondary region. After the primary region recovers, you want to fail back. What should you do first?

A.Add a new secondary replica in the original region.
B.Perform a planned failover from the current primary to the original primary.
C.Start geo-replication from the current primary to the original region.
D.Configure the original primary as a secondary replica.
AnswerB

This re-establishes replication and fails back.

Why this answer

After a planned failover to a secondary region, the secondary region becomes the primary. To fail back to the original primary region, you perform another planned failover from the current primary (the former secondary) to the original primary. This is the only way to switch roles back without data loss.

Option B correctly identifies this process. Option A is wrong because you cannot add a new secondary replica while failover groups are already in place; failover groups manage replicas. Option C is wrong because geo-replication is not used with managed instance failover groups; failover groups handle replication.

Option D is wrong because you cannot directly configure the original primary as a secondary; you must perform a failover.

Full explanation →
303
Multi-Selecthard

Which THREE components are required to set up automated monitoring and alerting for an Azure SQL Database using Azure Monitor?

Select 3 answers
A.Diagnostic setting for the database
B.Log Analytics workspace
C.Azure Automation account
D.SQL Agent Job
E.Alert rule with action group
AnswersA, B, E

Sends logs and metrics to Log Analytics.

Why this answer

The three required components are: a diagnostic setting on the database to send logs and metrics to a Log Analytics workspace, a Log Analytics workspace to store the data, and an alert rule with an action group to notify when conditions are met. A SQL Agent Job (option D) is not required for automated monitoring and alerting; it is used for scheduled tasks. An Azure Automation account (option C) is also not required; it is used for runbooks and process automation, not for monitoring alerts.

Full explanation →
304
MCQeasy

You are a junior DBA at a startup that uses Azure SQL Database for its SaaS application. The application uses a single database and the development team frequently makes schema changes. You need to implement security measures to: 1. Ensure that all connections from the application use TLS encryption. 2. Prevent unauthorized access from the internet. 3. Allow your office IP range (203.0.113.0/24) to connect for management. 4. Enable auditing of all schema changes. 5. Encrypt the database at rest using Azure-managed keys. You have already enabled Transparent Data Encryption (TDE) with service-managed keys. What should you do next to meet the remaining requirements?

A.Set the minimal TLS version to 1.2. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.
B.Add a firewall rule for the office IP range. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.
C.Set the minimal TLS version to 1.2. Add a firewall rule for the office IP range and remove the default 'Allow Azure services' rule. Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP.
D.Add a firewall rule for the office IP range. Remove the default 'Allow Azure services' rule.
AnswerC

All requirements met.

Why this answer

It addresses all remaining requirements: setting the minimal TLS version to 1.2 ensures encrypted connections (requirement 1), adding a firewall rule for the office IP range and removing the default 'Allow Azure services' rule prevents unauthorized internet access while allowing management traffic (requirements 2 and 3), and enabling auditing with SCHEMA_OBJECT_CHANGE_GROUP captures schema changes (requirement 4). TDE with service-managed keys already satisfies requirement 5, so no further action is needed for encryption at rest.

Exam trap

The trap here is that candidates often forget to remove the default 'Allow Azure services' firewall rule, assuming it is harmless, but it actually opens the database to all Azure services and must be explicitly disabled to meet strict internet access controls.

How to eliminate wrong answers

Option A is wrong because it only sets TLS 1.2 and enables auditing, but it does not add a firewall rule for the office IP range or remove the default 'Allow Azure services' rule, leaving the database exposed to unauthorized internet access. Option B is wrong because it adds a firewall rule and enables auditing but does not set the minimal TLS version to 1.2, failing to enforce TLS encryption for all connections. Option D is wrong because it adds a firewall rule and removes the default rule but does not set TLS 1.2 or enable auditing, missing requirements for encryption and schema change tracking.

Full explanation →
305
MCQmedium

Refer to the exhibit. You are reviewing the audit settings for an Azure SQL Database. What will this configuration do?

A.Auditing is disabled because no storage endpoint is provided.
B.Audit logs are written to Azure Monitor (Log Analytics).
C.Only failed authentication attempts are audited.
D.Audit logs are written to an Azure Storage account.
AnswerB

isAzureMonitorTargetEnabled: true sends logs to Log Analytics.

Why this answer

The exhibit shows that the audit destination is set to 'Log Analytics' (Azure Monitor), which means audit logs will be sent to a Log Analytics workspace. This configuration does not require a storage endpoint because Log Analytics is the designated target. Option B correctly identifies that audit logs are written to Azure Monitor (Log Analytics).

Exam trap

The trap here is that candidates assume auditing requires a storage endpoint and thus choose Option A, overlooking that Log Analytics and Event Hubs are valid destinations that do not need a storage endpoint.

How to eliminate wrong answers

Option A is wrong because auditing is not disabled when no storage endpoint is provided; the destination can be Log Analytics or Event Hubs instead of Azure Storage. Option C is wrong because the configuration audits all database events (such as successful logins, failed logins, and DML operations) unless specific audit action groups are filtered; it does not limit auditing to only failed authentication attempts. Option D is wrong because the exhibit explicitly shows 'Log Analytics' as the destination, not an Azure Storage account.

Full explanation →
306
MCQeasy

You need to automate the process of refreshing a development copy of an Azure SQL Database from a production backup every night. The production database is in the West US region, and the dev database is in East US. What is the most cost-effective method?

A.Export the production database to a BACPAC file, store it in Azure Blob Storage, and import it to dev using a Data Factory pipeline.
B.Configure active geo-replication between production and dev, then initiate a planned failover each night.
C.Use Azure Data Studio to export the database as a script and run it on the dev server.
D.Use the Restore-AzSqlDatabase PowerShell cmdlet to restore the production database to the dev server using a point-in-time backup from the same day.
AnswerD

Point-in-time restore is cost-effective and can be automated.

Why this answer

Azure SQL Database supports point-in-time restore (PITR) to a different server and region. Restore-AzSqlDatabase can automate this process using a production backup from the same day, which is cost-effective as it only uses existing backups without additional replication costs. Options A and C involve manual steps or extra services, while option B is for active geo-replication, which incurs ongoing costs and is not designed for nightly refresh.

Full explanation →
307
MCQmedium

You are optimizing an Azure SQL Database that has a large table with a clustered columnstore index. You notice that the index has been fragmented over time. You need to improve query performance. What should you do?

A.Drop and recreate the columnstore index
B.Reorganize the columnstore index
C.Update statistics on the table
D.Rebuild the columnstore index
AnswerB

REORGANIZE removes rows from deleted bitmaps and is the recommended maintenance.

Why this answer

REORGANIZE is the recommended maintenance operation for clustered columnstore indexes to defragment them by removing rows from deleted bitmaps without requiring a full rebuild. Option A (drop and recreate) is incorrect because it is disruptive and unnecessary for resolving fragmentation. Option C (update statistics) does not address index fragmentation directly.

Option D (rebuild) is more resource-intensive and typically reserved for indexes with high fragmentation, whereas reorganize is optimal for regular maintenance.

Full explanation →
308
MCQeasy

A company plans to deploy Azure SQL Database in a new region. They require that the database be accessible only from a specific set of Azure virtual machines in the same region. What should the DBA configure?

A.Use a private endpoint only
B.Configure a VNet service endpoint and a firewall rule for the VNet subnet
C.Configure a point-to-site VPN
D.Set the firewall to allow Azure services and resources to access the server
AnswerB

This restricts access to the specified VNet.

Why this answer

A VNet service endpoint extends your virtual network private address space and the identity of your VNet to the Azure SQL Database service over a direct connection. By combining a service endpoint on the subnet with a firewall rule that allows traffic only from that specific VNet/subnet, you restrict access exclusively to Azure VMs in that subnet, meeting the requirement without exposing the database to the public internet.

Exam trap

The trap here is that candidates often confuse private endpoints with service endpoints, assuming a private endpoint alone provides network-level access control, when in fact it only provides a private IP connection and still requires explicit firewall rules to restrict which VMs can use it.

How to eliminate wrong answers

Option A is wrong because a private endpoint uses a private IP from the VNet to connect to Azure SQL Database, but it does not by itself restrict access to only a specific set of VMs; it still requires a firewall rule to block other traffic, and the question asks for a solution that ensures accessibility only from specific VMs, which a private endpoint alone does not enforce. Option C is wrong because a point-to-site VPN is designed for individual client machines connecting from outside Azure (e.g., on-premises or remote users) and does not restrict access to a specific set of Azure VMs within the same region; it also adds unnecessary complexity and latency. Option D is wrong because allowing Azure services and resources to access the server opens the database to all Azure IP addresses (including other subscriptions and regions), which violates the requirement of restricting access to only a specific set of Azure VMs.

Full explanation →
309
Multi-Selecteasy

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

Select 3 answers
A.Azure Monitor Metrics
B.Performance Insights
C.Query Store
D.Dynamic Management Views (DMVs)
E.SQL Server Profiler
AnswersA, C, D

Azure Monitor provides free metrics for Azure SQL Database.

Why this answer

Azure Monitor Metrics is a built-in, no-cost feature that collects and stores platform metrics from Azure SQL Database at near-real-time intervals (typically every minute). It provides performance counters such as DTU/CPU usage, data IO, and log write percentages without requiring additional configuration or licensing, making it a correct choice for real-time performance data.

Exam trap

The trap here is that candidates confuse Performance Insights (an AWS service) with Azure's Query Performance Insight, or assume SQL Server Profiler is a built-in, cost-free tool for Azure SQL Database when it is neither native nor free.

Full explanation →
310
Multi-Selecthard

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

Select 3 answers
A.Automatically rebuild fragmented indexes.
B.Automatically scale the database to a higher service tier.
C.Stream diagnostic logs to a Log Analytics workspace.
D.Set up an alert when DTU consumption exceeds 80% for 5 minutes.
E.Create a custom metric alert based on a KQL query.
AnswersC, D, E

Diagnostic settings allow log streaming.

Why this answer

Azure Monitor and SQL Insights allow you to configure diagnostic settings to stream SQL Database metrics and resource logs (e.g., query store runtime statistics, wait statistics) to a Log Analytics workspace. This enables centralized log analysis, custom KQL queries, and long-term retention for auditing and troubleshooting.

Exam trap

The trap here is that candidates confuse monitoring and alerting capabilities (Azure Monitor/SQL Insights) with automated remediation actions (like index rebuilds or auto-scaling), which are separate Azure services or manual tasks.

Full explanation →
311
MCQhard

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

A.The minCapacity is set too low
B.The autoPauseDelay is set to 60 minutes
C.The licenseType is set to BasePrice
D.The database uses VBS enclaves which are incompatible with serverless
AnswerD

Serverless does not support VBS enclaves.

Why this answer

Auto-pause is only supported for General Purpose serverless databases, and the use of VBS enclave (preferredEnclaveType: "VBS") indicates Always Encrypted with secure enclaves, which is not supported with serverless. Option A is wrong because minCapacity 0.5 is valid for serverless. Option B is wrong because licenseType BasePrice does not affect auto-pause.

Option C is wrong because autoPauseDelay 60 minutes is valid; the default is 60.

Full explanation →
312
MCQhard

Your Azure SQL Database is configured with active geo-replication to a secondary region. The primary region experiences a complete outage. You initiate a manual failover to the secondary. Users report that some client applications are failing to connect. What is the most likely cause?

A.The secondary server is behind a VPN that clients cannot access.
B.The secondary database is not fully provisioned yet.
C.The server name changed after failover and DNS is not updated.
D.Client applications are using the old connection string pointing to the primary server.
AnswerD

After failover, clients must update the connection string to the new primary server.

Why this answer

After a manual failover in active geo-replication, the secondary becomes the new primary. Client applications that were connecting to the original primary server will have connection strings pointing to that original server's endpoint. Unless the clients update their connection strings to point to the new primary server (the former secondary), they will fail to connect.

Option A is incorrect because geo-replication does not require VPN; the secondary database is accessible over the internet. Option B is incorrect because the secondary database is already provisioned and kept in sync, ready for failover. Option C is incorrect because the server name does not automatically change; the secondary server has its own name, and after failover, the connection string must be updated to that name.

Full explanation →
313
MCQmedium

You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?

A.Use the SQL Server Import/Export Wizard to copy data.
B.Export the database as a BACPAC file and import it into the managed instance.
C.Use transaction log shipping from the source to the target.
D.Use Azure Database Migration Service (DMS) with online mode.
AnswerD

DMS online mode supports TDE-enabled databases and minimizes downtime.

Why this answer

Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.

Exam trap

The trap here is that candidates often assume transaction log shipping (Option C) is viable for Azure SQL Managed Instance, but it is not supported; DMS online mode is the correct minimal-downtime method that also handles TDE preservation.

How to eliminate wrong answers

Option A is wrong because the SQL Server Import/Export Wizard only copies data at the table level, not the entire database, and it does not preserve TDE or support minimal downtime. Option B is wrong because exporting a BACPAC file does not preserve TDE; the exported data is decrypted, and the import process would require re-encrypting the database on the target, causing downtime and potential data exposure. Option C is wrong because transaction log shipping is not supported for Azure SQL Managed Instance as a target; it is a SQL Server on-premises feature and cannot be used to replicate logs to a managed instance.

Full explanation →
314
MCQeasy

Your company has a policy that requires all Azure SQL Databases to be recoverable to a point in time within the last 5 minutes in the event of a user error. What should you configure?

A.Configure point-in-time restore (PITR) with a retention period of at least 5 minutes
B.Enable zone-redundant configuration
C.Set up active geo-replication to a secondary region
D.Enable long-term backup retention (LTR)
AnswerA

PITR allows restoring to any point within the retention period; default is 7 days, sufficient for 5 minutes.

Why this answer

Point-in-time restore (PITR) allows restoring a database to any point within the retention period. By configuring a retention period that covers at least the last 5 minutes, you can recover from user errors within 5 minutes. The default retention is 7 days, but you can configure it up to 35 days, so 5 minutes is easily achievable.

Option B (zone-redundant configuration) provides high availability within a region but not point-in-time recovery. Option C (active geo-replication) is for disaster recovery to another region. Option D (long-term backup retention) is for backups beyond the PITR retention period, not for short-term recovery.

Full explanation →
315
MCQmedium

Refer to the exhibit. You are reviewing an ARM template for deploying an Azure SQL Database. The template specifies a point-in-time restore from a source database. The source database is configured with geo-redundant backup storage. You need to ensure that the restored database can be used for disaster recovery in a different region. What is missing from the template to achieve this?

A.Add a 'location' property to the database resource.
B.Set 'zoneRedundant' to true.
C.Change 'requestedBackupStorageRedundancy' to 'GeoZone'.
D.Set 'createMode' to 'GeoRestore' and specify a target server in the desired region.
AnswerD

GeoRestore allows restoring from geo-redundant backups to a different region.

Why this answer

To perform a point-in-time restore in a different region for disaster recovery, you must set 'createMode' to 'GeoRestore' and specify the target server in the desired region. The template currently uses 'PointInTimeRestore', which only restores to the same region. Option A is incorrect because adding a 'location' property alone does not enable cross-region restore—the restore mode must be changed.

Option B is incorrect because 'zoneRedundant' refers to high availability within a single region, not disaster recovery across regions. Option C is incorrect because 'GeoZone' backup redundancy still does not support cross-region point-in-time restore; geo-restore uses geo-redundant backups automatically.

Full explanation →
316
MCQmedium

You are a database administrator for a large e-commerce company. The company uses Azure SQL Database in a Business Critical tier for its transactional systems. The database is part of an elastic pool with multiple databases. You need to automate the process of purging old data from a specific table (OrderHistory) that grows by 5 GB per day. The data must be retained for 90 days. After 90 days, the data should be moved to an Azure Blob Storage archive for long-term storage. The automation must run daily at midnight and must minimize impact on the transactional workload. Additionally, the solution should be cost-effective and require minimal manual intervention. What should you do?

A.Create a SQL Agent job on the database to run a cleanup script at midnight.
B.Use Azure Automation with a PowerShell runbook that connects to the database, deletes old data, and uploads it to Blob Storage using AzCopy.
C.Create a stored procedure that deletes data older than 90 days and exports it to Blob Storage using PolyBase. Schedule the procedure using Elastic Database Jobs.
D.Use Azure Data Factory with a scheduled trigger to copy data older than 90 days to Blob Storage and then delete it from the table.
AnswerD

Azure Data Factory is the correct choice. It can be scheduled to run daily, perform the copy to Blob Storage using a copy activity, and then execute a delete activity against the database. This minimizes impact and is cost-effective.

Why this answer

Azure Data Factory (ADF) with a scheduled trigger can copy data older than 90 days to Azure Blob Storage and then delete it from the table. This approach minimizes impact on the transactional workload because ADF runs outside the database, is cost-effective for scheduled workloads, and requires minimal manual intervention. Option A is not available in Azure SQL Database (SQL Agent is only for on-premises or Managed Instance).

Option B requires a Hybrid Runbook Worker if the database is not publicly accessible, adding complexity. Option C is incorrect because PolyBase in Azure SQL Database does not support writing to Blob Storage; it only reads external data.

Full explanation →
317
Multi-Selectmedium

Your company uses Azure SQL Database and wants to implement row-level security (RLS) to restrict access to customer data based on the user's Microsoft Entra ID group membership. Which TWO actions are required?

Select 2 answers
A.Create a column master key for the secured columns.
B.Grant the user the db_securityadmin server role.
C.Create a security policy that uses a predicate function.
D.Apply a dynamic data masking rule to hide sensitive columns.
E.Create a predicate function that filters rows based on the user's group membership.
AnswersC, E

A security policy enforces the predicate.

Why this answer

Row-level security (RLS) in Azure SQL Database restricts data access at the row level by using a security policy that invokes a predicate function. The predicate function defines the logic for filtering rows, such as checking the caller's Microsoft Entra ID group membership via IS_MEMBER(). Therefore, creating both the predicate function (Option E) and the security policy that uses it (Option C) are required steps.

Exam trap

The trap here is that candidates confuse row-level security with dynamic data masking or Always Encrypted, assuming that hiding or encrypting columns is equivalent to restricting row access, but RLS specifically filters rows based on user attributes, not column-level protection.

Full explanation →
318
MCQhard

You have an Azure SQL Managed Instance with a large number of databases. You need to monitor the storage space used by each database to proactively manage capacity. Which tool should you use?

A.Query Performance Insight
B.Automatic tuning
C.Intelligent Insights
D.Azure SQL Analytics (preview)
AnswerD

Azure SQL Analytics provides monitoring for multiple databases including storage.

Why this answer

Azure SQL Analytics (preview) in Azure Monitor provides a consolidated monitoring solution for Azure SQL databases and managed instances, including detailed storage usage per database. Option A is wrong because Query Performance Insight focuses on query-level metrics like query duration and CPU usage, not per-database storage. Option B is wrong because Automatic tuning is a feature that automatically indexes and optimizes query plans, not a monitoring tool.

Option C is wrong because Intelligent Insights provides proactive performance diagnostics and anomaly detection, but does not directly per-database storage monitoring. Therefore, only Azure SQL Analytics meets the requirement.

Full explanation →
319
MCQmedium

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

A.Scale up the managed instance to a higher service tier
B.Enable delayed durability
C.Configure a readable secondary replica
D.Add more data files to the filegroup
AnswerD

Distributes page allocation and reduces contention.

Why this answer

Adding more data files to the filegroup spreads out page allocations, reducing contention for allocation structures and thus decreasing PAGELATCH_SH waits. Option A is incorrect because scaling up may provide more resources but does not directly address the page latch contention caused by allocation bottlenecks. Option B is incorrect because delayed durability only affects transaction log write behavior, not page latches.

Option C is incorrect because a readable secondary replica does not alleviate latch contention on the primary; it is designed for read workload offloading, not contention reduction.

Full explanation →
320
MCQhard

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

A.The automatic failover policy requires a quorum that is not met.
B.The listener is not configured correctly for SQL2.
C.The secondary replica SQL2 has an incompatible database version.
D.The network connection between SQL1 and SQL2 is still down after the outage.
AnswerD

The connection timeout error and the DISCONNECTED state suggest network issues.

Why this answer

The error 35201 indicates a connection timeout. SQL2 is configured for automatic failover and synchronous commit. After the restart, SQL2 remains disconnected and not healthy.

This is likely because the network connectivity between SQL1 and SQL2 is still interrupted or there is a persistent issue. Since SQL3 is connected and healthy, the issue is specific to SQL2.

Full explanation →
321
MCQhard

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

A.Disable and re-enable Query Store to reset the plans.
B.Execute sys.sp_query_store_set_hints to add a query hint.
C.Use sys.dm_db_tuning_recommendations to apply a plan.
D.Execute sys.sp_query_store_force_plan with the query_id and plan_id.
AnswerD

Forces Query Store to use a specific plan.

Why this answer

`sys.sp_query_store_force_plan` is the dedicated system stored procedure in Azure SQL Database that forces the Query Store to use a specific execution plan for a given query. When a stored procedure's performance regresses, you identify the query_id and plan_id of the better-performing plan from Query Store views and execute this procedure to pin that plan, overriding the optimizer's choice.

Exam trap

The trap here is that candidates confuse the DMV for recommendations (`sys.dm_db_tuning_recommendations`) with the actual stored procedure to enforce a plan, or they think resetting Query Store is a valid troubleshooting step when it actually destroys historical data.

How to eliminate wrong answers

Option A is wrong because disabling and re-enabling Query Store would clear all historical plan data and force statistics, losing the ability to identify and force a previous good plan, which is counterproductive. Option B is wrong because `sys.sp_query_store_set_hints` is used to add query-level hints (e.g., `RECOMPILE`, `MAXDOP`) to influence future plan generation, not to force an existing historical plan. Option C is wrong because `sys.dm_db_tuning_recommendations` is a dynamic management view that surfaces automated tuning recommendations (e.g., create/drop indexes, force plan), but applying a plan force requires executing `sys.sp_query_store_force_plan` directly; the DMV itself does not apply changes.

Full explanation →
322
MCQhard

You are deploying an Azure SQL Database that will contain highly sensitive personal data. The security policy requires that the data be encrypted at rest, in transit, and in use. Additionally, the encryption keys must be stored in a hardware security module (HSM) and be customer-managed. Which combination of features should you implement?

A.TDE with a service-managed key, enforce TLS 1.2, and Always Encrypted with a column master key in Key Vault.
B.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and use Dynamic Data Masking.
C.TDE with a customer-managed key in Key Vault, enforce TLS 1.2, and Always Encrypted with a column master key stored in Windows Certificate Store.
D.TDE with a customer-managed key in Azure Key Vault (HSM-backed), enforce TLS 1.2, and Always Encrypted with a column master key in Azure Key Vault (HSM-backed).
AnswerD

This combination meets all requirements: at rest, in transit, in use, and HSM-backed customer-managed keys.

Why this answer

It satisfies all requirements: encryption at rest via TDE with a customer-managed key stored in an HSM-backed Key Vault, encryption in transit by enforcing TLS 1.2, and encryption in use via Always Encrypted with the column master key also stored in an HSM-backed Key Vault. This ensures that all three states of data are encrypted and that keys are both customer-managed and hardware-protected.

Exam trap

The trap here is that candidates may confuse Dynamic Data Masking with encryption in use, or overlook that storing keys in Key Vault does not automatically imply HSM protection unless the vault is specifically HSM-backed.

How to eliminate wrong answers

Option A is wrong because TDE with a service-managed key does not meet the customer-managed key requirement, and storing the column master key in Key Vault alone does not guarantee HSM protection unless the vault is HSM-backed. Option B is wrong because Dynamic Data Masking does not encrypt data in use; it only obfuscates data at query time, failing the encryption-in-use requirement. Option C is wrong because storing the column master key in the Windows Certificate Store does not use an HSM, violating the requirement that keys be stored in a hardware security module.

Full explanation →
323
MCQhard

A retail company is migrating its on-premises SQL Server database to Azure SQL Database. The database has a table with 500 million rows and receives 10,000 INSERT operations per second during peak hours. The application requires read-committed snapshot isolation. Which Azure SQL Database tier and configuration should the DBA recommend to minimize cost while meeting performance requirements?

A.Business Critical tier with 8 vCores
B.Serverless tier with auto-pause
C.General Purpose tier with 2 vCores
D.Hyperscale tier with 4 vCores
AnswerD

Hyperscale supports high INSERT throughput and RCSI is enabled by default.

Why this answer

The Hyperscale tier with 4 vCores is correct because it supports up to 100 TB of storage, provides fast scaling for high-volume INSERT workloads (10,000 rows/second), and natively supports read-committed snapshot isolation (RCSI) without additional configuration. It minimizes cost compared to Business Critical while offering the necessary performance and isolation level for a 500-million-row table.

Exam trap

The trap here is that candidates may choose General Purpose (C) due to its lower cost, overlooking that it cannot sustain 10,000 INSERTs/second and does not natively support read-committed snapshot isolation without explicit configuration, while Hyperscale (D) is designed for such large-scale, high-throughput workloads with built-in RCSI.

How to eliminate wrong answers

Option A is wrong because Business Critical tier with 8 vCores is over-provisioned and more expensive than necessary; it provides high availability and low latency but is not cost-minimizing for this workload. Option B is wrong because Serverless tier with auto-pause is unsuitable for sustained high-throughput INSERT operations (10,000 rows/second) as it can cause performance throttling and auto-pause delays during peak hours. Option C is wrong because General Purpose tier with 2 vCores cannot handle 10,000 INSERTs per second due to its limited IOPS and log write throughput, and it does not natively support read-committed snapshot isolation without enabling READ_COMMITTED_SNAPSHOT; it would also struggle with a 500-million-row table size.

Full explanation →
324
MCQeasy

You are tasked with automating the creation of user databases in an Azure SQL Managed Instance after deployment. Which tool should you use?

A.Elastic Database Jobs
B.Azure Automation with PowerShell runbooks
C.Azure Resource Manager templates
D.SQL Agent jobs
AnswerB

PowerShell runbooks can connect to the managed instance and run CREATE DATABASE.

Why this answer

Azure Automation with PowerShell runbooks is ideal for automating post-deployment tasks such as creating user databases. Azure Resource Manager templates deploy infrastructure but not subsequent database operations. SQL Agent jobs are available in Azure SQL Managed Instance, but Azure Automation provides more orchestration flexibility.

Elastic Database Jobs are supported in Azure SQL Managed Instance (in preview), but they are primarily designed for cross-database query scheduling and management, making Azure Automation a more appropriate choice for database creation automation.

Full explanation →
325
MCQhard

You are reviewing an Azure SQL Database audit policy configuration. The policy is set to audit successful and failed database authentication events. You notice that audit logs are being written to both Azure Blob Storage and Azure Monitor. However, you are concerned about security of the storage account access key in the policy. What is the recommended approach to securely reference the storage account?

A.Remove the storageAccountAccessKey property and rely on the storage endpoint.
B.Use a different storage account with a key that expires daily.
C.Disable the Azure Monitor destination and only use Blob Storage.
D.Configure the server's system-assigned managed identity and grant it access to the storage account.
AnswerD

Managed identity eliminates the need for access keys.

Why this answer

Using a system-assigned managed identity for the Azure SQL Database logical server eliminates the need to store a storage account access key in the audit policy configuration. Managed identities provide a secure, Azure AD-backed identity that can be granted specific permissions (e.g., Storage Blob Data Contributor) on the storage account, ensuring that access is controlled via RBAC rather than a static key. This approach aligns with security best practices by removing the risk of key exposure or rotation failures.

Exam trap

The trap here is that candidates may think removing the access key property (Option A) is sufficient, not realizing that Azure SQL Database audit requires explicit authentication to the storage account and will fail without a valid access key or managed identity configuration.

How to eliminate wrong answers

Option A is wrong because removing the storageAccountAccessKey property without configuring an alternative authentication method (like managed identity) will cause audit log writes to fail; the storage endpoint alone does not provide authentication. Option B is wrong because using a key that expires daily introduces operational complexity and still requires storing a secret in the policy, which does not eliminate the fundamental security concern of key exposure. Option C is wrong because disabling the Azure Monitor destination does not address the security of the storage account access key; it only removes one destination while leaving the key-based storage access unchanged.

Full explanation →
326
Drag & Dropmedium

Drag and drop the steps to configure an Azure SQL Database elastic pool 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 create the pool, then add databases, configure per-database limits, monitor, and scale.

Full explanation →
327
MCQmedium

You are automating the creation of Azure SQL Databases using ARM templates. You need to ensure that the databases are created with the 'Hyperscale' service tier and with 'ZoneRedundant' enabled. Which ARM template property should you set?

A.Set 'sku' to 'HS_Gen5_2' and 'properties.zoneRedundant' to true.
B.Set 'requestedServiceObjectiveName' to 'HS_Gen5_2' and 'zoneRedundant' to true.
C.Set 'edition' to 'Hyperscale' and 'zoneRedundant' to true.
D.Set 'maxSizeBytes' to a value and 'zoneRedundant' to true.
AnswerA

The 'sku' property specifies the service tier, and 'zoneRedundant' is a property of the database.

Why this answer

In ARM templates for Azure SQL Database, the 'sku' property defines the service tier and performance level. For Hyperscale, the 'sku' name is 'HS_Gen5_2' (Gen5, 2 vCores) and the 'sku.tier' is 'Hyperscale'. The 'zoneRedundant' property is set under 'properties' to true for zone redundancy.

Option B is incorrect because 'requestedServiceObjectiveName' is used for DTU-based tiers, not vCore-based Hyperscale. Option C is incorrect because 'edition' is not a valid ARM template property; the tier is specified through 'sku.tier'. Option D is incorrect because 'maxSizeBytes' only sets the maximum database size, not the service tier.

Full explanation →
328
MCQhard

You are the database administrator for a global e-commerce company. The company uses Azure SQL Database in the Business Critical service tier for its transactional database, db1, hosted in the East US region. The database is 500 GB and experiences high write throughput. The current disaster recovery solution uses active geo-replication to a secondary in West US, but during a recent failover drill, the failover took 45 seconds, exceeding the corporate RTO of 30 seconds. The RPO requirement is zero data loss. You need to improve the failover time while maintaining zero data loss. The secondary must be in a different Azure region for compliance. What should you do?

A.Increase the number of replicas in the Business Critical tier to 5.
B.Change the secondary to a General Purpose tier and use async replication.
C.Configure an auto-failover group between East US and West US using zone-redundant deployment within each region and ensure the secondary is zone-redundant.
D.Move both primary and secondary to Hyperscale tier and use named replicas.
AnswerC

Auto-failover groups use asynchronous replication across regions, so they cannot guarantee zero data loss (RPO=0). While they can achieve failover times under 30 seconds, the RPO requirement is not met.

Why this answer

None of the provided options fully satisfy both requirements: zero data loss (RPO=0) and failover time under 30 seconds across regions. Auto-failover groups (Option C) use asynchronous replication across regions, so RPO is not zero. General Purpose tier (Option B) also uses async replication.

Increasing replicas (Option A) does not affect failover time. Hyperscale with named replicas (Option D) does not support cross-region failover. To achieve zero data loss across regions, an alternative solution such as SQL Server on Azure VMs with an availability group or Azure SQL Managed Instance with a distributed availability group would be required.

Exam trap

A common trap is assuming auto-failover groups provide synchronous geo-replication. In reality, they use asynchronous replication, so zero data loss is not guaranteed across regions.

Full explanation →
329
Multi-Selecteasy

Which TWO of the following are valid disaster recovery options for Azure SQL Database that provide a secondary in a different Azure region?

Select 2 answers
A.Active geo-replication
B.Failover groups
C.Geo-restore
D.Zone-redundant configuration
E.Auto-failover groups
AnswersA, B

Correct: Creates a secondary in a different region.

Why this answer

Options A and B are correct. Active geo-replication and Failover groups are both valid disaster recovery options for Azure SQL Database that provide a secondary database in a different Azure region. Options C, D, and E are incorrect: Geo-restore (C) restores from backups and does not maintain a continuous secondary; Zone-redundant configuration (D) provides high availability within a single region, not disaster recovery; Auto-failover groups (E) is a feature that enables automatic failover for failover groups, but it is not a separate disaster recovery option—it is part of failover groups.

Exam trap

Candidates often confuse zone-redundant configuration (which is for high availability within a region) with disaster recovery options that span regions.

Full explanation →
330
MCQhard

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that the database is experiencing high I/O latency during peak hours. After analyzing the query performance, you determine that the primary bottleneck is due to log write throughput. You need to reduce log write latency without changing the service tier. What should you do?

A.Configure Query Store hints to force parameterization.
B.Enable read scale-out to offload read queries.
C.Scale up to a higher Hyperscale service objective.
D.Increase the MAXDOP setting for the database.
AnswerC

Higher SLO increases the log rate limit and reduces log write latency.

Why this answer

Scaling up to a higher Hyperscale service objective increases the log write throughput by provisioning more log I/O capacity and faster storage. Since the bottleneck is specifically log write latency, and you cannot change the service tier, increasing the service objective within Hyperscale directly addresses the issue by allocating more resources to the log write path.

Exam trap

The trap here is that candidates may confuse scaling up with changing the service tier, or think that read scale-out or query hints can solve a write-specific bottleneck, when in fact only increasing the service objective within the same tier addresses log write throughput.

How to eliminate wrong answers

Option A is wrong because Query Store hints to force parameterization help with plan reuse and query performance, but they do not affect log write throughput or I/O latency. Option B is wrong because enabling read scale-out offloads read queries to a secondary replica, which does not reduce log write latency on the primary replica. Option D is wrong because increasing MAXDOP can reduce parallelism and potentially lower CPU pressure, but it does not improve log write throughput or reduce log I/O latency.

Full explanation →
331
Drag & Dropmedium

Drag and drop the steps to configure automatic tuning for an Azure SQL Database 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

Automatic tuning is configured via the portal by selecting the database, navigating to automatic tuning, enabling options, and saving.

Full explanation →
332
MCQeasy

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

A.Azure Monitor metric alerts
B.Azure Advisor
C.Azure SQL Insights (preview)
D.Log Analytics workspace
AnswerA

Can alert on DTU percentage metric.

Why this answer

Azure Monitor metric alerts can be configured on DTU percentage. Option B is wrong because Azure Advisor provides recommendations but not real-time alerts. Option C is wrong because Azure SQL Insights is for visualization, not alerting.

Option D is wrong because Log Analytics workspaces store logs but do not natively provide metric alerts.

Full explanation →
333
MCQmedium

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

A.High CPU utilization on the database server
B.Long-running blocking transactions
C.The log rate limit has been reached due to high transaction throughput
D.Insufficient storage space for data files
AnswerC

Log rate limits are a common cause of log write waits, especially in Business Critical or Hyperscale tiers.

Why this answer

High log write wait times typically indicate that the transaction log throughput is a bottleneck, often due to the log rate limit. Option A is wrong because high CPU utilization would cause other wait types like SOS_SCHEDULER_YIELD, not WRITELOG. Option B is wrong because long-running blocking transactions cause wait types like LCK_M_*, not increased log write wait time.

Option D is wrong because insufficient storage space for data files causes different symptoms, such as write errors or data file growth issues, but not specifically log write wait.

Full explanation →
334
MCQeasy

You need to migrate an on-premises SQL Server database to Azure SQL Database. The database uses SQL Server Integration Services (SSIS) packages for ETL. You want to continue using SSIS in the cloud with minimal changes. Which Azure service should you use?

A.Use Azure Data Factory with Azure-SSIS Integration Runtime
B.Deploy SSIS on Azure SQL Database
C.Migrate SSIS packages to Azure Databricks
D.Use Azure SQL Managed Instance to run SSIS
AnswerA

Azure-SSIS IR in ADF allows running existing SSIS packages in the cloud.

Why this answer

Azure Data Factory with Azure-SSIS Integration Runtime is the correct choice because it provides a fully managed service that lifts and shifts existing SSIS packages to the cloud with minimal changes. The Azure-SSIS IR runs a dedicated cluster of Azure VMs that host the SSIS engine, allowing you to deploy, execute, and manage your existing SSIS packages directly in Azure without rewriting code.

Exam trap

The trap here is confusing Azure SQL Managed Instance with a full SSIS host; candidates often assume Managed Instance runs SSIS natively, but it only supports SQL Server Agent jobs, not the SSIS runtime itself.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not support running SSIS packages; it is a PaaS database service without an SSIS runtime. Option C is wrong because Azure Databricks is a Spark-based analytics platform, not designed to run SSIS packages, and migrating would require significant re-engineering of ETL logic. Option D is wrong because Azure SQL Managed Instance does not natively run SSIS; it supports SQL Server Agent but not the SSIS runtime, and you would still need Azure-SSIS IR for SSIS execution.

Full explanation →
335
MCQmedium

Your organization needs to comply with a regulation that requires data to be encrypted at rest using a customer-managed key stored in Azure Key Vault. You have an Azure SQL Database. What should you configure?

A.Use Azure Policy to enforce encryption at rest.
B.Enable Always Encrypted and store the column master key in Azure Key Vault.
C.Configure Azure Storage Service Encryption for the database files.
D.Enable Transparent Data Encryption (TDE) and use a customer-managed key in Azure Key Vault as the TDE protector.
AnswerD

TDE with customer-managed key in Key Vault provides encryption at rest with customer control.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault meets the requirement for data at rest encryption using a customer-controlled key. TDE encrypts the database files (data and log files) at rest, and by using a customer-managed key as the TDE protector, you retain control over key rotation and revocation, satisfying regulatory compliance.

Exam trap

The trap here is confusing Always Encrypted (which encrypts specific columns at the application level) with TDE (which encrypts the entire database at rest), leading candidates to choose Option B when the requirement is for full database encryption at rest with a customer-managed key.

How to eliminate wrong answers

Option A is wrong because Azure Policy can enforce compliance rules but does not itself encrypt data; it can only audit or enforce that TDE is enabled, not provide the encryption mechanism. Option B is wrong because Always Encrypted protects sensitive data in transit and at rest within the application layer, but it encrypts specific columns rather than the entire database at rest, and it is not the standard solution for full database encryption required by the regulation. Option C is wrong because Azure Storage Service Encryption applies to Azure Blob Storage and Azure Files, not to Azure SQL Database files, which are managed internally by the SQL Database service and cannot be directly encrypted via Storage Service Encryption.

Full explanation →
336
MCQhard

You are designing an automation strategy for deploying schema changes to 50 Azure SQL Databases in a elastic pool. You need to ensure that changes are applied in a consistent order and rolled back if any database fails. Which approach should you use?

A.Use Azure Data Studio with the Schema Compare extension and apply changes manually.
B.Create an Elastic Job that executes the schema change script within a transaction and uses target group error handling to roll back on failure.
C.Deploy the schema changes using Azure Resource Manager templates with SQL extensions.
D.Use PowerShell to loop through each database and execute the script, catching exceptions.
AnswerB

Elastic Jobs support transactions and error handling.

Why this answer

Azure SQL Database Elastic Jobs can run T-SQL scripts across multiple databases with transactional control and error handling. Options A and C do not provide rollback capability. Option D is for infrastructure deployment, not schema changes.

Full explanation →
337
MCQhard

You are tuning a query in Azure SQL Database that uses a nonclustered columnstore index. The query is supposed to use batch mode execution but shows row mode. What is the most likely cause?

A.The query does not have enough memory grant
B.The database compatibility level is below 130
C.The index is defined with a filter predicate
D.The query hint MAXDOP 1 is used
AnswerA

Insufficient memory grant forces row mode execution.

Why this answer

Batch mode execution requires a sufficient memory grant. If the query does not get enough memory, it falls back to row mode. Option B is incorrect: while compatibility level 130 or higher is required for batch mode, the question states the index is a nonclustered columnstore index, and the most common cause of row mode despite having such an index is insufficient memory.

Option C is incorrect: a filter predicate on a columnstore index does not prevent batch mode. Option D is incorrect: MAXDOP 1 does not disable batch mode; batch mode can work with a single degree of parallelism.

Full explanation →
338
Multi-Selecthard

You are monitoring an Azure SQL Database using Query Performance Insight and notice that a specific query has a high average duration and high CPU usage. The query plan shows a clustered index scan on a large table. Which two actions should you take to optimize performance? (Choose two.)

Select 2 answers
A.Rebuild the clustered index to reduce fragmentation.
B.Update statistics on the table to ensure the optimizer has current information.
C.Force a different query plan using Query Store hints.
D.Increase the DTU service tier of the database.
E.Create a covering nonclustered index on the columns used in the query.
AnswersB, E

Up-to-date statistics help the optimizer choose efficient plans.

Why this answer

Updating statistics provides the query optimizer with current data distribution information, which can lead to a more efficient query plan, potentially avoiding the clustered index scan. In Azure SQL Database, stale statistics are a common cause of suboptimal plans, and updating them is a low-cost, non-disruptive first step before considering index changes.

Exam trap

The trap here is that candidates often jump to index rebuilds or scaling up resources, overlooking that stale statistics are a frequent and easily fixable cause of poor query plans in Azure SQL Database.

Full explanation →
339
Multi-Selectmedium

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Select 3 answers
A.Azure Automation account
B.SQL Server Agent
C.Target groups
D.An elastic job agent
E.Job credentials
AnswersC, D, E

Target groups specify which databases to run the job on.

Why this answer

Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Exam trap

The trap here is that candidates confuse SQL Server Agent (an on-premises/IaaS tool) with the cloud-native elastic job agent, or assume an Azure Automation account is needed for scheduling, when in fact elastic jobs have their own built-in scheduling and agent service.

Full explanation →
340
MCQmedium

You are a database administrator for a logistics company that uses Azure SQL Managed Instance. The instance hosts a database that tracks shipments. You need to automate the process of updating statistics for all tables in the database every night. The update should only include tables that have had more than 10% of rows modified since the last statistics update. The automation must be efficient and not rebuild statistics unnecessarily. Additionally, the solution should be self-contained within the Managed Instance and not rely on external services. What should you do?

A.Create a SQL Agent job with a T-SQL script that uses sys.dm_db_stats_properties to identify tables needing statistics updates based on modification counters, then executes UPDATE STATISTICS only on those tables.
B.Enable automatic tuning for the database to automatically update statistics.
C.Use Azure Automation with a PowerShell runbook that connects to the instance and runs sp_updatestats.
D.Use Elastic Database Jobs to schedule a T-SQL script that updates all statistics unconditionally.
AnswerA

SQL Agent is available and this approach is efficient.

Why this answer

SQL Agent is available on Azure SQL Managed Instance, and you can create a T-SQL script that uses sys.dm_db_stats_properties to check modification counters and then executes UPDATE STATISTICS only on tables that have had more than 10% of rows modified. This meets the efficiency requirement without rebuilding statistics unnecessarily. Option B is incorrect because automatic tuning does not update statistics based on modification counters; it manages indexes and query plan issues.

Option C is incorrect because Azure Automation requires external dependencies like a hybrid worker and is not self-contained within the Managed Instance. Option D is incorrect because Elastic Database Jobs are not available for Azure SQL Managed Instance; they are for Azure SQL Database.

Full explanation →
341
MCQmedium

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

A.Create a SQL Agent job using T-SQL script with sys.dm_db_index_physical_stats.
B.Use Elastic Database Jobs to run a script that rebuilds/reorganizes indexes.
C.Schedule a maintenance task using SQL Server Agent in a VM running SQL Server.
D.Enable automatic index tuning in Azure SQL Database with the desired fragmentation thresholds.
AnswerB

Correct. Elastic Database Jobs allows scheduling T-SQL scripts that can query sys.dm_db_index_physical_stats to check fragmentation and then rebuild or reorganize indexes based on thresholds and schedule.

Why this answer

Elastic Database Jobs can be used to run a T-SQL script on a schedule, checking fragmentation thresholds and performing rebuilds/reorganizations. This meets the requirement of weekly execution while checking thresholds (30% and 10%). Option D is incorrect because automatic index tuning in Azure SQL Database does not support a fixed schedule; it operates in real-time based on workload and cannot be set to run weekly.

Options A and C are not suitable: A uses a SQL Agent job which is not available in Azure SQL Database (only in SQL Server on VMs), and C pertains to SQL Server on VMs, not Azure SQL Database.

Full explanation →
342
MCQmedium

Your company has an Azure SQL Database that stores financial data. You need to implement auditing to track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL). Which audit action group should you configure?

A.SCHEMA_OBJECT_CHANGE_GROUP and DATABASE_OBJECT_CHANGE_GROUP
B.DATABASE_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP
C.DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP and SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP
D.SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP
AnswerA

These groups capture DDL and DML changes respectively.

Why this answer

To track all data modifications (INSERT, UPDATE, DELETE) and schema changes (DDL) in Azure SQL Database, you need to audit both data changes and schema changes. The SCHEMA_OBJECT_CHANGE_GROUP captures DDL changes like CREATE, ALTER, DROP on schema objects, while DATABASE_OBJECT_CHANGE_GROUP captures DML changes on database objects (tables, views). Together, they cover the required tracking.

Option A is correct because these two action groups specifically target the stated requirements.

Exam trap

The trap here is that candidates may confuse action groups that track changes (CHANGE_GROUP) with those that track permissions or authentication, leading them to select options that audit security events rather than data and schema modifications.

How to eliminate wrong answers

Option B is wrong because DATABASE_OBJECT_PERMISSION_CHANGE_GROUP and SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP audit permission changes (GRANT, DENY, REVOKE), not data modifications or schema changes. Option C is wrong because DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP and SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP audit ownership changes (ALTER AUTHORIZATION), not DML or DDL operations. Option D is wrong because SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP audit login success/failure events, not data or schema modifications.

Full explanation →
343
MCQmedium

Your company has an Azure SQL Database that stores sensitive customer data. You need to ensure that data is encrypted at rest and in transit. The database is currently using Transparent Data Encryption (TDE) with service-managed keys. Compliance requirements now mandate that you use customer-managed keys stored in Azure Key Vault. Additionally, all connections must use encrypted connections. What should you do?

A.Create a new Azure SQL Database with TDE enabled using a customer-managed key from Key Vault. Migrate data using SQL Server Management Studio (SSMS) with 'Encrypt connection' enabled.
B.Configure the Azure SQL Server to use a customer-managed key from Azure Key Vault for TDE and set 'Encrypted connection' to 'Required' on the server.
C.Enable Transparent Data Encryption (TDE) with service-managed keys and set 'Minimum TLS version' to 1.2.
D.Implement Always Encrypted with keys stored in Azure Key Vault and set 'Encrypted connection' to 'Required' on the server.
AnswerB

This directly meets both requirements.

Why this answer

It directly addresses both requirements: using a customer-managed key from Azure Key Vault for TDE (which replaces the service-managed key) and enforcing encrypted connections by setting 'Encrypted connection' to 'Required' on the Azure SQL Server. This configuration ensures data at rest is encrypted with a key you control, and all client connections must use TLS encryption, meeting compliance mandates without requiring a new database or data migration.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, thinking column-level encryption satisfies the 'at rest' requirement for the entire database, or they assume that setting 'Minimum TLS version' alone ensures all connections are encrypted, when in fact 'Encrypted connection' must be explicitly set to 'Required' to reject unencrypted connections.

How to eliminate wrong answers

Option A is wrong because creating a new database and migrating data is unnecessary; you can change the TDE key type on the existing database by configuring the server to use a customer-managed key from Key Vault, and 'Encrypt connection' in SSMS only affects that specific migration session, not all future connections. Option C is wrong because it keeps service-managed keys for TDE, which does not satisfy the compliance requirement for customer-managed keys, and setting 'Minimum TLS version' to 1.2 only enforces a minimum protocol version but does not require encrypted connections for all clients. Option D is wrong because Always Encrypted protects data in use and in transit at the column level, but it does not encrypt the entire database at rest (TDE is needed for that), and it does not address the requirement to use customer-managed keys for TDE.

Full explanation →
344
Multi-Selectmedium

You are configuring performance monitoring for an Azure SQL Database. You need to identify which two tools can be used to analyze query performance over time. Which TWO should you select?

Select 2 answers
A.Dynamic Management Views (DMVs)
B.Intelligent Insights
C.Extended Events
D.Azure SQL Analytics (Azure Monitor)
E.Query Store
AnswersD, E

Azure SQL Analytics provides historical performance metrics.

Why this answer

The correct answers are D and E. Query Store (E) captures and retains query execution metrics and plans over time, enabling historical performance analysis. Azure SQL Analytics (D), now part of Azure Monitor, provides a comprehensive view of query performance across multiple databases with historical data.

Option A (DMVs) displays current state and recent activity but lacks long-term history. Option B (Intelligent Insights) offers automated diagnostics and summaries but not detailed query-level history. Option C (Extended Events) is designed for real-time event capture, not historical analysis.

Full explanation →
345
MCQmedium

Refer to the exhibit. You have an Azure SQL Database failover group configured with the ARM template snippet shown. You need to ensure that read-only queries are routed to the secondary region when the primary is healthy. What should you modify?

A.Set the readWriteEndpoint failoverPolicy to Manual.
B.Set the readOnlyEndpoint failoverPolicy to Enabled.
C.Add the database to the partnerServers array.
D.Change the failoverGracePeriodMinutes to 0.
AnswerB

Enabling the read-only endpoint allows routing read-only queries to the secondary.

Why this answer

The readOnlyEndpoint failoverPolicy must be set to Enabled to route read-only queries to the secondary region when the primary is healthy. Option A is incorrect because the readWriteEndpoint failoverPolicy being Manual does not affect read-only routing; it controls failover initiation. Option C is incorrect because adding databases to the partnerServers array is unrelated to read-only routing.

Option D is incorrect because changing the failoverGracePeriodMinutes to 0 does not enable read-only endpoint routing.

Full explanation →
346
MCQmedium

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

A.Deploy each tenant's database as a single database with reserved capacity
B.Use a single Hyperscale database with schema per tenant
C.Use a single Azure SQL Managed Instance with multiple databases
D.Use an elastic pool and add databases as needed
AnswerD

Elastic pool allows sharing resources and scaling based on aggregate load.

Why this answer

Elastic pools are designed for multi-tenant SaaS scenarios where each tenant has its own database but usage patterns are unpredictable. They provide performance isolation through per-database resource limits (e.g., min/max DTUs or vCores) while sharing a fixed pool of resources, which minimizes cost by allowing idle databases to borrow from others. This matches the requirement of fluctuating tenant counts and cost efficiency.

Exam trap

The trap here is that candidates confuse 'performance isolation' with 'dedicated resources' and choose single databases (Option A), not realizing that elastic pools provide isolation via per-database resource caps while sharing a common pool for cost efficiency.

How to eliminate wrong answers

Option A is wrong because deploying each tenant's database as a single database with reserved capacity locks in fixed resources per database, leading to over-provisioning and higher costs when tenant activity fluctuates. Option B is wrong because a single Hyperscale database with schema per tenant breaks performance isolation — a noisy tenant can consume shared resources (e.g., log throughput or page server I/O) and impact others, plus Hyperscale is optimized for large, single databases, not multi-tenant isolation. Option C is wrong because Azure SQL Managed Instance is a single-instance deployment with shared resources across all databases; it lacks the per-database resource governance and elastic scaling of an elastic pool, and it is more expensive for many small databases.

Full explanation →
347
MCQmedium

Your company runs a financial application on Azure SQL Managed Instance. The primary instance is deployed in North Europe. You need to implement a disaster recovery solution that meets the following requirements: automatic failover to a secondary region in case of a regional outage; the secondary must be readable during normal operations to serve read-only queries; the RPO must be less than 10 seconds; the RTO must be less than 30 seconds; and you must minimize compute costs by using the smallest possible secondary instance size. The primary instance uses the Business Critical service tier. What should you do?

A.Create a failover group with a General Purpose secondary instance in a different region to reduce costs.
B.Create a failover group between the primary and a secondary Business Critical instance in a paired region, and use a smaller compute size for the secondary.
C.Deploy a secondary Business Critical instance in the same region and configure a failover group.
D.Configure active geo-replication to a secondary managed instance in a different region.
AnswerB

Failover groups support different compute sizes for secondary, reducing costs.

Why this answer

A failover group between two Azure SQL Managed Instances in different regions provides automatic failover and a readable secondary. The secondary must be in the Business Critical service tier, matching the primary's tier, but you can use a smaller compute size (fewer vCores) to reduce costs while still supporting read-only workloads. Option A is incorrect because you cannot change the secondary's service tier to General Purpose in a failover group; it must match the primary.

Option C is incorrect because the secondary must be in a different region for disaster recovery. Option D is incorrect because active geo-replication is not supported for SQL Managed Instance; failover groups are the appropriate DR solution.

Full explanation →
348
MCQeasy

You need to implement a disaster recovery solution for a SQL Server on Azure Virtual Machine using SQL Server Always On Availability Groups. The secondary replica must be in a different Azure region. What is the minimum number of VMs required to achieve automatic failover?

A.1
B.3
C.4
D.2
AnswerD

Two replicas are the minimum for automatic failover.

Why this answer

(2 VMs). To achieve automatic failover, an Always On Availability Group requires at least two SQL Server replicas (primary and secondary) configured for synchronous-commit mode with automatic failover. A two-node Windows Server Failover Cluster also requires a witness (cloud witness or file share witness) to maintain quorum during automatic failover, but that witness does not require an additional VM.

Therefore, the minimum number of VMs remains 2. Option A (1 VM) offers no high availability. Option B (3 VMs) exceeds the minimum.

Option C (4 VMs) is unnecessary.

Full explanation →
349
MCQeasy

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

A.Row-Level Security
B.Transparent Data Encryption (TDE)
C.Dynamic Data Masking
D.Always Encrypted
AnswerD

Always Encrypted encrypts column data at rest and in transit, with client-side encryption keys.

Why this answer

Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) as covering both at-rest and in-transit encryption, but TDE only encrypts data at rest on disk, not during network transmission or while in memory.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls access to rows based on user identity or context, but it does not encrypt data at rest or in transit. Option B is wrong because Transparent Data Encryption (TDE) encrypts the database files at rest but does not protect data in transit between the application and the database; it also does not prevent the database engine from seeing plaintext data during query processing. Option C is wrong because Dynamic Data Masking obfuscates data in query results for unauthorized users but does not encrypt the underlying data at rest or in transit, and the database engine still processes plaintext data.

Full explanation →
350
MCQhard

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

A.I/O subsystem bottleneck for read operations
B.CPU bottleneck
C.Blocking between concurrent transactions
D.Memory pressure
AnswerA

PAGEIOLATCH_SH waits occur when waiting for I/O to complete for reading pages.

Why this answer

PAGEIOLATCH_SH waits indicate that a query is waiting for a data page to be read from disk into the buffer pool, which is an I/O operation. High wait_time_ms for this wait type typically points to an I/O subsystem bottleneck for read operations, making option A correct. Option B (CPU bottleneck) is incorrect because PAGEIOLATCH_SH is related to I/O, not CPU.

Option C (blocking) is incorrect because blocking is associated with LOCK waits, not PAGEIOLATCH_SH. Option D (memory pressure) is incorrect; while memory pressure can increase physical I/O, the wait type itself specifically indicates I/O latency for reading pages from disk.

Full explanation →
351
MCQmedium

You are deploying an Azure SQL Database that will store sensitive customer data. Compliance requirements dictate that the data must be encrypted at rest using a customer-managed key that is rotated every 90 days. You configure TDE with Azure Key Vault. What additional step is critical to ensure data remains accessible after key rotation?

A.Store the key in a different region for disaster recovery.
B.Ensure the previous key version remains enabled in Azure Key Vault.
C.Use a premium tier of Azure Key Vault.
D.Re-encrypt the database with the new key immediately after rotation.
AnswerB

TDE uses the key version that was active when the data was written; disabling it causes decryption failures.

Why this answer

When using TDE with Azure Key Vault and customer-managed keys, the database is encrypted using a Data Encryption Key (DEK) that is protected by the key in Key Vault. Key rotation creates a new key version, but the DEK remains encrypted with the previous key version. To decrypt the DEK and access the data after rotation, the previous key version must remain enabled in Key Vault.

If it is disabled or deleted, the database becomes inaccessible.

Exam trap

The trap here is that candidates often assume key rotation automatically re-encrypts the database or that disabling the old key is safe, but Azure SQL Database requires the previous key version to remain enabled until the DEK is explicitly re-encrypted with the new key.

How to eliminate wrong answers

Option A is wrong because storing the key in a different region is a disaster recovery strategy, not a requirement for maintaining accessibility after key rotation; TDE keys must be available in the same region as the database for decryption. Option C is wrong because the Azure Key Vault tier (Standard vs. Premium) does not affect the ability to access data after key rotation; the critical factor is key version availability, not the vault's performance or feature set.

Option D is wrong because re-encrypting the database with the new key is not required after rotation; TDE automatically uses the new key version for new data, but the old key version must remain enabled to decrypt existing data until the DEK is re-encrypted, which is a separate manual operation.

Full explanation →
352
MCQeasy

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

A.Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.
B.Set the 'minimal TLS version' to 1.2 in the database's properties.
C.Add a firewall rule to deny connections using TLS 1.0 or 1.1.
D.Enable the 'Force encryption' option in the connection string and require TLS 1.2.
AnswerA

This enforces TLS 1.2 for all databases on the server.

Why this answer

To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Exam trap

The trap here is that candidates often confuse the 'minimal TLS version' setting with a database-level property or think that firewall rules or connection string options can enforce TLS version restrictions, but Azure SQL Database only exposes this control at the server level.

How to eliminate wrong answers

Option B is wrong because the 'minimal TLS version' setting is a server-level property, not a database-level property; Azure SQL Database does not expose a per-database TLS version configuration. Option C is wrong because firewall rules in Azure SQL Database control IP-based access, not TLS protocol versions; they cannot inspect or deny connections based on the TLS version used. Option D is wrong because 'Force encryption' in the connection string ensures encryption is used but does not enforce a specific TLS version; the client and server may negotiate a lower TLS version (e.g., 1.0 or 1.1) even with encryption enabled.

Full explanation →
353
MCQhard

You are reviewing a deployment template for an Azure SQL Database. The above snippet configures a security alert policy. What is a potential issue with this configuration?

A.The state is not set to "Disabled"
B.The emailAccountAdmins property is set to true
C.The retentionDays is set to 0, which may cause logs to be deleted immediately
D.The emailAddresses array is missing an entry
AnswerC

RetentionDays set to 0 disables retention, meaning audit logs will not be retained in storage.

Why this answer

RetentionDays set to 0 disables retention, meaning audit logs will not be retained in storage. Option A is wrong because state Enabled is fine. Option B is wrong because emailAddresses is provided.

Option D is wrong because emailAccountAdmins true is fine.

Full explanation →
354
MCQeasy

You need to configure Azure SQL Database to automatically scale up based on CPU usage. Which feature should you use?

A.Autoscale settings for the database
B.Elastic job
C.Elastic pool
D.Automatic tuning
AnswerA

Enables automatic scaling based on workload.

Why this answer

Azure SQL Database can use autoscale settings (in the DTU or vCore model) to automatically adjust resources based on metrics like CPU usage. Option B (Elastic job) is for running scheduled tasks across databases, not scaling. Option C (Elastic pool) is for managing multiple databases with shared resources, but it does not automatically scale a single database.

Option D (Automatic tuning) optimizes query performance, not resource scaling.

Full explanation →
355
MCQhard

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

A.Intelligent Insights report
B.Automatic Tuning recommendations
C.Azure Monitor metrics for the database
D.Query Store to review query execution plans and wait statistics
AnswerD

Query Store provides detailed query performance data for root cause analysis.

Why this answer

Query Store is the correct tool because it captures historical execution plans, runtime statistics, and wait statistics for individual queries, allowing you to pinpoint the root cause of a performance regression. Intelligent Insights provides high-level diagnostics but not the granular per-query plan and wait data needed for deep analysis of a specific query issue.

Exam trap

The trap here is that candidates confuse Intelligent Insights' automated diagnostics with the granular, query-level historical data that Query Store provides, assuming the alert's source (Intelligent Insights) is also the tool for deep manual investigation.

How to eliminate wrong answers

Option A is wrong because Intelligent Insights provides automated root cause analysis and recommendations at the database level, but it does not expose detailed per-query execution plans or wait statistics for manual investigation. Option B is wrong because Automatic Tuning focuses on automatically applying index and plan regression fixes, not on providing a historical record of query execution plans and waits for root cause analysis. Option C is wrong because Azure Monitor metrics (e.g., DTU/CPU usage, IOPS) show aggregate resource consumption, not per-query execution plans or wait statistics, so they cannot isolate the specific query's performance issue.

Full explanation →
356
Multi-Selecthard

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Select 3 answers
A.Log IO percentage (sys.dm_db_resource_stats)
B.Log backup frequency
C.Database size and growth rate
D.Wait statistics (sys.dm_os_wait_stats)
E.Query Store for query performance regressions
AnswersA, D, E

High log IO can indicate transaction throughput issues.

Why this answer

Options A, D, and E are correct. Log IO percentage (A) from sys.dm_db_resource_stats indicates transaction log throughput bottlenecks. Wait statistics (D) from sys.dm_os_wait_stats show where queries are waiting, revealing contention or resource pressure.

Query Store (E) tracks query plan regressions and performance degradation over time. Option B is incorrect because log backup frequency affects recovery point objectives (RPO), not proactive performance monitoring. Option C is incorrect because database size and growth rate relate to storage capacity planning, not real-time performance.

Full explanation →
357
MCQmedium

You have an Azure SQL Database configured with active geo-replication. The primary region experiences an outage that lasts longer than the recovery time objective (RTO) of 5 minutes. You need to initiate a failover to the secondary region with minimal data loss. What should you do?

A.Initiate a planned failover to the secondary region using the Azure portal.
B.Configure an auto-failover group and let it fail over automatically.
C.Force a failover to the secondary region using a PowerShell script.
D.Use geo-restore to recover the database from the most recent geo-replicated backup.
AnswerC

Correct. Forced failover initiates the failover immediately without waiting for synchronization, making it the only viable option when the primary is unavailable. It uses the latest available data on the secondary, minimizing data loss under the circumstances.

Why this answer

When the primary region is offline, a planned failover is not possible because it requires synchronization with the primary. A forced failover (option C) is the only way to promote the secondary to primary without the primary being online. While it may result in some data loss, it provides the fastest failover, meeting the RTO of 5 minutes.

Option B is incorrect because auto-failover groups have a grace period and may not fail over automatically within the RTO. Option D describes geo-restore from backups, which is not a failover operation.

Full explanation →
358
MCQhard

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

A.Migrate the database to Business Critical tier.
B.Modify the batch jobs to use bulk insert with TABLOCK and batch inserts into smaller transactions.
C.Increase the managed instance storage to 2048 GB to improve log throughput.
D.Enable accelerated database recovery to reduce log I/O.
AnswerB

Minimally logged operations reduce log writes, staying within throughput limit.

Why this answer

To resolve the timeout errors, you need to reduce the log write throughput to stay within the 50 MB/s limit of the General Purpose tier. Option B achieves this by modifying batch jobs to use bulk insert with TABLOCK, which enables minimal logging, and breaking transactions into smaller batches to reduce the log write rate per transaction. Option A is incorrect because moving to Business Critical is not possible due to budget constraints.

Option C is incorrect because increasing storage does not affect the log write throughput limit; it only increases data and log file space. Option D is incorrect because accelerated database recovery reduces the amount of version store I/O during transaction rollback, not the log write throughput during normal operations.

Full explanation →
359
MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

A.Full backups are taken every 12 hours and retained for 7 days.
B.Long-term retention (LTR) is set to 7 days.
C.Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.
D.Transaction log backups are taken every 12 hours.
AnswerC

The properties directly set PITR retention and differential backup interval.

Why this answer

The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days.

Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Exam trap

The trap here is that candidates confuse the PITR retention period with the frequency of backups, or assume that the retention setting controls the backup schedule (e.g., thinking full backups occur every 12 hours), when in fact it only controls how long backups are kept, not how often they are taken.

How to eliminate wrong answers

Option A is wrong because full backups in Azure SQL Database are taken once per week, not every 12 hours, and the retention setting shown does not change the full backup frequency. Option B is wrong because long-term retention (LTR) is a separate feature that retains full backups for up to 10 years, configured via a different policy, not the 7-day PITR retention setting shown. Option D is wrong because transaction log backups are taken every 5–10 minutes, not every 12 hours, and their frequency is not configurable via this retention setting.

Full explanation →
360
MCQmedium

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

A.Create an Azure Monitor alert based on fragmentation
B.Use Elastic Database Jobs to check fragmentation and send email
C.Configure SQL Agent job to send email
D.Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email
AnswerD

PowerShell runbooks can query the DMV and use SendGrid to send email notifications.

Why this answer

Azure Automation runbooks can query fragmentation and send emails via SendGrid. Elastic Database Jobs can check fragmentation but cannot send emails directly. Azure Monitor alerts can trigger on metrics like DTU but not on index fragmentation.

Azure Logic Apps can receive data but require custom setup.

Full explanation →
361
Drag & Dropmedium

Drag and drop the steps to restore an Azure SQL Database to a point in time 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 restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Full explanation →
362
MCQmedium

You are a database administrator for an e-learning platform that uses Azure SQL Database with the General Purpose tier. The database stores course content and user progress. You need to automate the process of exporting the database schema (without data) to a BACPAC file every week for version control. The BACPAC file must be stored in an Azure Blob Storage container with a retention policy of 30 days. The automation must use Azure native services and should not require manual intervention. What should you do?

A.Use Azure Data Factory with a Copy activity to export the database schema to a BACPAC file in Blob Storage.
B.Use Elastic Database Jobs to run a T-SQL script that uses the BACKUP DATABASE command to a URL.
C.Create an Azure Automation runbook with PowerShell that uses SqlPackage.exe with /p:ExtractAllTableData=False to export the schema-only BACPAC to a Blob Storage container. Schedule the runbook to run weekly.
D.Create a SQL Agent job on the database to run SqlPackage.exe.
AnswerC

Correct. An Azure Automation runbook can invoke SqlPackage.exe with the /p:ExtractAllTableData=False option to create a schema-only BACPAC, and the runbook can be scheduled weekly.

Why this answer

Azure Automation runbooks can execute PowerShell scripts that use the SqlPackage.exe utility to export a schema-only BACPAC file. The command 'SqlPackage.exe /Action:Export /SourceServerName:<server> /SourceDatabaseName:<database> /TargetFile:<bacpac> /p:ExtractAllTableData=False' creates a BACPAC containing only the schema. The runbook can be scheduled weekly using an Azure Automation schedule.

Option A is incorrect because Azure Data Factory's Copy activity is designed for data movement, not schema exports. Option B is incorrect because Elastic Database Jobs runs T-SQL scripts and cannot produce BACPAC files. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Exam trap

Candidates may incorrectly assume that the New-AzSqlDatabaseExport cmdlet has a -SchemaOnly parameter, but it does not exist. The correct approach is to use SqlPackage.exe with /p:ExtractAllTableData=False inside a runbook.

Full explanation →
363
MCQeasy

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

A.az sql db restart --resource-group rg1 --server server1 --name db3
B.az sql db resume --resource-group rg1 --server server1 --name db3
C.az sql db start --resource-group rg1 --server server1 --name db3
D.az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online
AnswerB

Correct command to resume a paused database.

Why this answer

`az sql db resume` is the command to resume a paused database. Option A is wrong because `az sql db restart` restarts an online database but does not resume a paused one. Option C is wrong because `az sql db start` is not a valid command for Azure SQL Database.

Option D is wrong because `az sql db update` can modify properties but cannot resume a paused database; resuming requires a dedicated command.

Full explanation →
364
MCQhard

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

A.The JSON syntax is invalid because property names use camelCase instead of PascalCase.
B.Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.
C.The diffBackupIntervalInHours should be 12 for a business-critical database.
D.The retentionDays value of 28 exceeds the maximum allowed for PITR.
AnswerB

LTR properties belong to a separate resource type: backupLongTermRetentionPolicies.

Why this answer

The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, the resource type is 'backupShortTermRetentionPolicies', which only supports short-term retention properties like retentionDays. Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) belong to a different resource type ('backupLongTermRetentionPolicies').

Including them in the same resource will cause a deployment error. Option B correctly identifies this. Option A is incorrect because the JSON syntax is valid (camelCase is used in ARM templates).

Option C is incorrect because diffBackupIntervalInHours is not relevant to this issue. Option D is incorrect because 28 days is within the allowed range for PITR (up to 35 days).

Full explanation →
365
MCQmedium

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

A.The SQL connection is not authenticated.
B.The recurrence trigger uses a schedule that is not valid.
C.The API version specified is outdated.
D.The pause action is not supported for Azure SQL Database.
AnswerD

Pause/resume is only available for Azure Synapse dedicated SQL pools, not Azure SQL Database.

Why this answer

Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the pause action is not supported for Azure SQL Database, making option D correct. Option A is incorrect because the connection authentication is not the issue here.

Option B is incorrect because the recurrence trigger schedule is valid. Option C is incorrect because the API version is not the primary reason; the operation itself is unsupported.

Full explanation →
366
Multi-Selecthard

You are deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). You need to ensure that the data is encrypted at rest and that access to encryption keys is logged. Which THREE actions should you take? (Choose three.)

Select 3 answers
A.Use Always Encrypted to encrypt all PII columns.
B.Grant the managed identity of the database access to the key vault with 'get', 'wrapKey', and 'unwrapKey' permissions.
C.Configure TDE with service-managed keys.
D.Enable Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.
E.Enable auditing on the Azure Key Vault to log key operations.
AnswersB, D, E

This is necessary for TDE with CMK to access the encryption key.

Why this answer

To use customer-managed TDE keys stored in Azure Key Vault, the Azure SQL Database's managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions. This allows the database to access the key for encryption and decryption operations while maintaining a secure, auditable key management chain.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, selecting Always Encrypted for at-rest encryption when it is actually designed for client-side encryption of sensitive columns, not for full database-level encryption at rest.

Full explanation →
367
MCQhard

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

A.The elastic pool must be in a different server
B.The database must be in the same server as the elastic pool
C.The database must be in the Basic tier
D.The database size must be less than 10 GB
AnswerB

The database must be in the same server as the elastic pool is correct because moving a database into an elastic pool requires the database to be on the same server as the pool.

Why this answer

Moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the elastic pool must be in the same server as the database, not a different server. Option C is wrong because the database can be any service tier (Basic, Standard, Premium, etc.) as long as it fits within the pool's resource limits.

Option D is wrong because the database size can be any size up to the pool's max size limit, which is not necessarily less than 10 GB.

Full explanation →
368
MCQeasy

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

A.The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.
B.The retention period is too short to support point-in-time restore.
C.The policy must specify a minimum retention of 30 days for point-in-time restore.
D.Geo-redundant backup must be disabled for point-in-time restore to work.
AnswerB

Retention of 35 days exceeds 7 days needed.

Why this answer

The current configuration shows a backup retention period of only 5 days, which is less than the required 7 days for point-in-time restore. With a retention period shorter than 7 days, you cannot restore to a point within the last 7 days. The backup interval of 12 hours is for full backups and does not affect point-in-time restore granularity, as transaction log backups are taken every 5-10 minutes, enabling 1-minute granularity.

Exam trap

Candidates often confuse the purpose of backup intervals. The 12-hour interval shown is for full/differential backups, not transaction logs. Transaction log backups are taken frequently (every 5-10 minutes) and are responsible for point-in-time restore granularity.

How to eliminate wrong answers

Option B is wrong because the retention period (e.g., 7 days) is sufficient for point-in-time restore; PITR requires a minimum retention of 1 day for Basic tier and 7 days for other tiers, so 7 days meets the requirement. Option C is wrong because Azure SQL Database does not mandate a minimum retention of 30 days for point-in-time restore; the default retention is 7 days, and it can be configured up to 35 days, but 7 days is valid. Option D is wrong because geo-redundant backup storage (RA-GRS) does not need to be disabled for point-in-time restore; PITR works with both locally redundant (LRS) and geo-redundant (RA-GRS) backup storage, and disabling it is not a requirement.

Full explanation →
369
MCQhard

Refer to the exhibit. You are configuring a backup policy for an Azure SQL Database. The database is in the East US region. You need to ensure that the database can be restored to a different region in the event of a regional disaster. Which setting in the exhibit must be correctly configured?

A.The geoBackupPolicy state is Enabled.
B.The geoBackupPolicy location is set to eastus2.
C.The backupStorageRedundancy is set to Geo.
D.The weeklyRetention is set to P4W.
AnswerC

Geo-redundant backups are required for geo-restore.

Why this answer

BackupStorageRedundancy must be set to 'Geo' to enable geo-redundant backups that can be restored to a different region in case of a regional disaster. Option A is incorrect because the geoBackupPolicy state being Enabled simply enables the geo-backup policy, but it does not ensure geo-redundancy if the storage redundancy is not set to Geo. Option B is incorrect because the geoBackupPolicy location indicates where the geo-backup is stored, but the key setting for cross-region restore is the storage redundancy, not the location.

Option D is incorrect because weeklyRetention defines how long weekly backups are retained, which does not affect the ability to restore to a different region.

Full explanation →
370
MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

A.Change the retentionDays property to 30.
B.Change the diffBackupIntervalInHours to 24.
C.No action is needed; the current policy meets the requirement.
D.Add a long-term retention policy with a weekly retention of 30 days.
AnswerA

retentionDays controls PITR retention; setting to 30 meets the requirement.

Why this answer

The retentionDays property in the ARM template controls the point-in-time restore (PITR) retention period. Currently set to 14 days, it must be changed to 30 days to meet the compliance requirement. Option B is incorrect because diffBackupIntervalInHours specifies the interval for differential backups, not retention.

Option C is incorrect because 14 days does not satisfy the 30-day requirement. Option D is incorrect because long-term retention (LTR) is for archival purposes beyond PITR and does not replace the PITR retention setting.

Full explanation →
371
MCQeasy

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

A.Configure server-level firewall rules to block all IP addresses except Azure services.
B.Disable SQL authentication for all contained database users.
C.Create a database-level trigger to reject connections using SQL authentication.
D.Enable 'Azure AD-only authentication' on the logical server.
AnswerD

Enabling 'Azure AD-only authentication' on the logical server disables all SQL authentication methods, including the server-level admin login. This directly enforces the policy that all connections must use Microsoft Entra ID, as any attempt to connect with a SQL username and password is rejected at the server level. This satisfies the constraint of preventing accidental SQL authentication by developers.

Why this answer

Enabling 'Azure AD-only authentication' on the logical server explicitly blocks all SQL authentication connections, including those from contained database users. This setting enforces that only Microsoft Entra ID (formerly Azure AD) principals can authenticate, directly aligning with the policy to prevent accidental use of SQL authentication.

Exam trap

The trap here is that candidates may think disabling SQL authentication for contained users (Option B) is sufficient, but they miss that the server-level authentication policy must be enforced to block all SQL authentication attempts, including those from server-level logins or newly created contained users.

How to eliminate wrong answers

Option A is wrong because server-level firewall rules control network access, not authentication methods; they cannot distinguish between SQL and Entra ID authentication. Option B is wrong because disabling SQL authentication for contained database users does not prevent SQL authentication at the server level; a contained user could still be created with SQL authentication if the server allows it. Option C is wrong because a database-level trigger cannot intercept or reject connections; triggers fire after a connection is established, so they cannot block the initial authentication attempt.

Full explanation →
372
MCQmedium

You have an Azure SQL Managed Instance configured with a failover group between two regions. The primary region becomes unavailable. You need to fail over to the secondary region with minimal data loss. What should you do?

A.Run 'az sql mi failover-group force-failover-allow-data-loss' from the Azure CLI
B.Add the secondary instance to an elastic pool and initiate failover
C.Run 'az sql mi failover-group failover' from the Azure CLI
D.Use Azure portal to initiate geo-failover for the managed instance
AnswerA

This command forces failover to the secondary, accepting potential data loss due to unreplicated transactions.

Why this answer

During a disaster where the primary region is unavailable, the correct action is to force a failover with potential data loss. The Azure CLI command 'az sql mi failover-group force-failover-allow-data-loss' (Option A) performs this forced failover, accepting the risk of data loss to restore availability. Option B is incorrect because adding to an elastic pool is unrelated to failover groups.

Option C is incorrect because 'az sql mi failover-group failover' is not a valid command; the correct command includes 'force-failover-allow-data-loss'. Option D is incorrect because the Azure portal does not directly support geo-failover for managed instances; the CLI or PowerShell must be used for forced failover.

Full explanation →
373
Multi-Selectmedium

Which TWO of the following are valid reasons to use zone-redundant configuration for Azure SQL Database? (Choose Two.)

Select 2 answers
A.Reduce read latency for globally distributed users.
B.Automatic failover to a paired region.
C.Protection against a single availability zone failure within a region.
D.Protection against a regional disaster.
E.Higher availability compared to a single zone deployment.
AnswersC, E

Correct.

Why this answer

Zone-redundant configuration for Azure SQL Database replicates databases across multiple availability zones within the same region, providing protection against a single zone failure (option C) and offering higher availability than a single-zone deployment (option E). Option A is incorrect because zone redundancy does not reduce read latency; that is achieved by geo-replication or read scale-out. Option B is incorrect because automatic failover to a paired region is a feature of geo-redundant disaster recovery, not zone redundancy.

Option D is incorrect because zone redundancy does not protect against a full regional disaster; it only protects within a region.

Full explanation →
374
MCQhard

Your Azure SQL Managed Instance is experiencing high latency for write transactions. You have identified that log write latency is the bottleneck. The instance uses Premium SSD with 5000 IOPS and 200 MB/s throughput. You observe that the log file is 500 GB and has grown significantly. What is the most likely cause and solution?

A.Increase the log file size to allow better write performance.
B.The instance has insufficient CPU; scale up the managed instance.
C.The disk is not fast enough for random writes; switch to Ultra Disk.
D.The log file is too large causing fragmentation; shrink it to reduce latency.
AnswerC

Correct. Ultra Disk provides very low latency and high throughput for sequential writes, directly addressing the log write latency bottleneck.

Why this answer

For Azure SQL Managed Instance, log write latency is critical for transaction write performance. Premium SSD P30 with 5000 IOPS and 200 MB/s throughput may become a bottleneck under heavy write workloads. Switching to Azure Ultra Disk provides significantly lower latency and higher IOPS/throughput, which can reduce log write latency.

Option D is incorrect because shrinking the transaction log does not improve performance and can cause severe fragmentation and performance degradation. Proper log backup scheduling is the correct method to manage log size.

Exam trap

Many candidates mistakenly believe shrinking a large log file improves write performance, but it actually degrades it due to increased fragmentation.

Full explanation →
375
Multi-Selectmedium

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

Select 3 answers
A.Build pipeline
B.Release pipeline
C.Elastic job agent
D.Azure Automation runbook
E.Variable group
AnswersA, B, E

The build pipeline compiles the schema changes into artifacts.

Why this answer

A build pipeline (A) produces artifacts like a .dacpac file. A release pipeline (B) deploys those artifacts to the target database. A variable group (E) stores configuration values such as connection strings.

Options C (Elastic job agent) and D (Azure Automation runbook) are not required for schema deployment via DevOps CI/CD. Elastic jobs are used for scheduled tasks across databases, not for deploying schema changes. Azure Automation runbooks automate processes outside of DevOps pipelines.

Full explanation →
Page 4

Page 5 of 13

Page 6

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 →