Courseiva

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

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

Take a mock examExam hub
Page 4

Page 5 of 13

Page 6
301
MCQeasy

You are a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

A.Query Store
B.Automatic Tuning
C.Intelligent Insights
D.Database Advisor for SQL Database
AnswerB

Automatic Tuning continuously monitors and automatically identifies and fixes regressed query plans.

Why this answer

Option C is correct because Automatic Tuning can automatically identify and fix regressed query plans. Option A is wrong because Query Store captures performance data but does not automatically fix plans. Option B is wrong because Intelligent Insights provides monitoring, not automatic fixing.

Option D is wrong because Database Advisor gives recommendations but requires manual action.

Full explanation →
302
MCQeasy

Your company uses Azure SQL Database in a General Purpose service tier. You need to minimize downtime during a planned patching event. Which feature should you enable?

A.Auto-failover group
B.Read scale-out
C.Active geo-replication
D.Zone-redundant configuration
AnswerD

Provides high availability across zones, minimizing patching downtime.

Why this answer

Option B is correct because zone-redundant configuration for Azure SQL Database provides high availability by replicating across zones, reducing downtime during patching. Option A is incorrect because geo-replication is for disaster recovery. Option C is incorrect because auto-failover groups require geo-replication.

Option D is incorrect because read scale-out only offloads read workloads.

Full explanation →
303
MCQmedium

Your team uses GitHub Actions for CI/CD. You need to automatically deploy schema changes to an Azure SQL Database after a pull request merge. The solution must use a service principal with minimal permissions. Which authentication method should the workflow use?

A.Service principal with a client secret stored in GitHub Secrets.
B.Azure CLI action using a user-assigned managed identity for the GitHub runner.
C.Environment variables containing the connection string.
D.SQL authentication using a user name and password stored in GitHub Secrets.
AnswerB

Managed identities eliminate credential management and are securely tied to Azure resources.

Why this answer

Option B is correct because Azure CLI with az sql db deploy action and managed identity integrates securely with GitHub Actions. Option A is wrong because SQL authentication requires credentials in secrets, which is less secure. Option C is wrong because connection strings in variables are not recommended.

Option D is wrong because service principal with secret is acceptable but managed identity is more secure.

Full explanation →
304
MCQmedium

You have an Azure SQL Database in the Premium tier with a failover group to a secondary region. The primary database experiences a performance issue due to a sudden increase in workload. You need to temporarily offload read-only queries to the secondary replica without affecting the failover group configuration. What should you do?

A.Use the secondary database in the failover group for read-only queries by setting ApplicationIntent=ReadOnly.
B.Configure active geo-replication to a new secondary in the same region.
C.Enable read scale-out on the primary database.
D.Scale up the primary database to a higher performance level.
AnswerA

The failover group secondary supports read-only workloads.

Why this answer

Option C is correct because the secondary in a failover group can be used for read-only queries by setting ApplicationIntent=ReadOnly in the connection string. Option A is wrong because scaling up the primary does not offload reads. Option B is wrong because active geo-replication would create a separate secondary outside the failover group.

Option D is wrong because read scale-out is only available in Premium and Business Critical tiers, but it uses replicas within the same region, not the failover group secondary.

Full explanation →
305
Multi-Selectmedium

You manage an Azure SQL Database in the Business Critical service tier. The database must be highly available within a single region. Which TWO features contribute to this high availability?

Select 2 answers
A.Active geo-replication
B.Multiple synchronous replicas (4 replicas)
C.Always On availability groups
D.Auto-failover groups
E.Zone-redundant configuration (when enabled)
AnswersB, E

Business Critical includes 4 replicas for HA.

Why this answer

Options B and D are correct because Business Critical uses multiple replicas (at least 4) and zone redundancy with zone-deployment for higher availability. Options A and C are incorrect because geo-replication is for cross-region DR, and auto-failover groups are for cross-region failover. Option E is incorrect because always on availability groups are internal, not a separate feature.

Full explanation →
306
MCQeasy

You need to monitor the performance of a set of Azure SQL Databases from a single dashboard. Which Azure service should you use?

A.Azure Monitor Workbooks
B.Query Performance Insight
C.Azure SQL Analytics
D.Azure Data Studio
AnswerC

Provides a pre-built monitoring dashboard for multiple databases.

Why this answer

Azure SQL Analytics provides a unified monitoring solution for multiple Azure SQL databases. Option C is correct. Option A is wrong because Query Performance Insight is per-database.

Option B is wrong because Azure Monitor Workbooks can be used but require manual configuration. Option D is wrong because Azure Data Studio is a client tool.

Full explanation →
307
Multi-Selecteasy

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

Select 2 answers
A.Connect using Azure AD authentication with multi-factor authentication.
B.Connect using a shared access key from Azure Storage.
C.Connect using a private endpoint within a virtual network.
D.Connect directly using the server's public IP address without encryption.
E.Connect using SQL authentication with a simple password.
AnswersA, C

Azure AD with MFA provides strong authentication.

Why this answer

Option A is correct because Azure AD authentication with multi-factor authentication (MFA) provides strong identity-based security, eliminating password vulnerabilities and supporting conditional access policies. This method integrates with Azure SQL Database's built-in support for Azure AD tokens, ensuring that only authenticated users with MFA can connect, which meets the exam's focus on secure environment implementation.

Exam trap

The trap here is that candidates may confuse shared access keys (a Storage concept) with SQL Database connection methods, or assume that a simple password is acceptable for security, when the exam emphasizes Azure AD and network isolation as the secure standards.

Full explanation →
308
MCQmedium

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

A.Enable instant file initialization in SQL Server.
B.Increase the virtual machine size to a series with higher I/O limits.
C.Migrate the data files to Premium SSD managed disks.
D.Migrate the data files to Standard SSD managed disks.
AnswerC

Premium SSD provides lower latency and is cost-effective without changing VM size.

Why this answer

Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Exam trap

The trap here is that candidates may choose Standard SSD (Option D) thinking it is a sufficient upgrade, but they overlook that Premium SSD is required to achieve the low latency needed for critical databases, and that Standard SSD still uses HDD-like latency profiles under sustained load.

How to eliminate wrong answers

Option A is wrong because enabling instant file initialization only speeds up data file growth operations by skipping zeroing, but it does not reduce I/O latency for ongoing read/write operations during peak hours. Option B is wrong because increasing the virtual machine size to a series with higher I/O limits would increase compute costs and is explicitly excluded by the requirement to not change the VM size. Option D is wrong because Standard SSD managed disks offer better performance than Standard HDDs but still have higher latency and lower IOPS than Premium SSDs, making them insufficient to resolve high I/O latency in a critical database scenario.

Full explanation →
309
MCQeasy

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

A.Configure minimum and maximum eDTU per database in the pool
B.Migrate the pool to a vCore-based elastic pool
C.Add more databases to the pool to spread the load
D.Move each database to a standalone DTU tier
AnswerA

Guarantees resources per database while sharing.

Why this answer

Option B is correct because configuring minimum and maximum eDTU per database ensures that each database gets guaranteed resources when needed, while still sharing the pool. Option A is wrong because moving to separate standalone databases would increase cost. Option C is wrong because using a vCore pool instead of DTU does not solve the issue inherently.

Option D is wrong because adding more databases to the pool increases contention.

Full explanation →
310
MCQhard

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

A.The log IOPS is limited by the disk performance; increase the provisioned IOPS.
B.The compute replica is undersized; scale up the compute to increase log throughput.
C.High log generation rate is causing log rate governance throttling; reduce the log generation rate by batching transactions.
D.The log write latency is due to network congestion; move the database to a different region.
AnswerC

Log rate governance throttles to protect secondary replicas; reducing log generation mitigates.

Why this answer

Option C is correct because in Hyperscale, log rate is governed to protect secondary replicas; high latency indicates throttling. Option A is wrong because Hyperscale uses local SSD for log, not PIOPS. Option B is wrong because log rate governance affects all log writes.

Option D is wrong because scaling up compute doesn't increase log throughput limits.

Full explanation →
311
MCQhard

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

A.Use Azure Data Box to physically transfer the data.
B.Use transaction log shipping to continuously sync changes.
C.Export as BACPAC and upload to Azure Blob Storage, then import.
D.Use Azure Database Migration Service (DMS) online migration.
AnswerA

Data Box bypasses network limitations.

Why this answer

Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Exam trap

The trap here is that candidates often overestimate the speed of online migration services like DMS or BACPAC, failing to calculate that at 100 Mbps, a 5 TB transfer takes over 5 days, making physical shipping via Data Box the faster choice despite the initial impression that 'online' is always quicker.

How to eliminate wrong answers

Option B is wrong because transaction log shipping requires an initial full backup transfer over the network, which would still be bottlenecked by the 100 Mbps link, and continuous log shipping does not minimize the initial migration time. Option C is wrong because exporting a 5 TB database as a BACPAC file is extremely slow due to the serial export process and the subsequent upload over 100 Mbps would take days, making it impractical for minimizing migration time. Option D is wrong because Azure Database Migration Service online migration relies on continuous data replication over the network, which is constrained by the 100 Mbps bandwidth and would take an excessively long time for the initial sync of 5 TB.

Full explanation →
312
MCQeasy

Your company has an Azure SQL Database in the East US region. You need to configure a disaster recovery solution that provides a readable secondary in the West US region with an RPO of 5 seconds. The solution must support automatic failover from the application. What should you configure?

A.Enable zone-redundant replicas in the same region.
B.Enable geo-redundant backup storage and perform geo-restore.
C.Configure active geo-replication to a secondary server in West US.
D.Create an auto-failover group with a secondary server in West US using synchronous replication.
AnswerD

Auto-failover groups can achieve low RPO with synchronous replication.

Why this answer

Option A is correct because auto-failover groups with synchronous replication can achieve an RPO of 5 seconds and provide automatic failover. Option B is wrong because active geo-replication does not support automatic failover. Option C is wrong because geo-restore does not provide a readable secondary.

Option D is wrong because zone redundancy does not provide cross-region DR.

Full explanation →
313
MCQeasy

You have an Azure SQL Database that must be automatically stopped during weekends to save costs. Which combination of Azure services can achieve this automation?

A.Azure Automation Runbook with PowerShell script using Stop-AzSqlDatabase cmdlet, scheduled via Automation schedule.
B.Azure Logic Apps with a recurrence trigger and the SQL connector to execute ALTER DATABASE SET SUSPEND.
C.Azure Functions with a timer trigger and the Azure SDK to stop the database.
D.Elastic Database Jobs with a T-SQL script to ALTER DATABASE SET SUSPEND.
AnswerA

Automation Runbooks can directly manage Azure resources with PowerShell.

Why this answer

Option B is correct because Azure Automation with PowerShell can stop and start Azure SQL Database using the Stop-AzSqlDatabase cmdlet, scheduled for weekends. Option A is wrong because Azure Logic Apps can stop/start via REST API but require more configuration; Automation Runbooks are simpler. Option C is wrong because Elastic Database Jobs cannot stop databases.

Option D is wrong because Azure Functions can do it but require more custom setup.

Full explanation →
314
MCQeasy

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

A.Set the 'Deny public network access' property to 'Yes'
B.Configure a server-level firewall rule to block all IP addresses
C.In the Azure portal, navigate to the SQL server's 'Microsoft Entra ID' blade and enable 'Azure AD-only authentication'
D.Query sys.sql_logins to check for any SQL authenticated logins
AnswerC

Enabling this setting disables SQL authentication and enforces Microsoft Entra authentication for all connections.

Why this answer

Option B is correct because the 'Azure AD-only authentication' setting in the Azure portal disables SQL authentication and requires Microsoft Entra authentication for the logical server. Option A is wrong because it only lists existing SQL logins, not enforcement. Option C is wrong because server-level firewall rules control network access, not authentication.

Option D is wrong because 'Deny public network access' controls network connectivity, not authentication type.

Full explanation →
315
Multi-Selecteasy

You are planning high availability for an Azure SQL Database that runs an e-commerce application. The database uses the Business Critical service tier. Which TWO features are automatically enabled to provide high availability within a single region? (Choose two.)

Select 2 answers
A.Automatic failover to a secondary replica if the primary becomes unavailable.
B.Multiple synchronous replicas (one primary, two secondary) in the same region.
C.Auto-failover groups with a secondary in another region.
D.Active geo-replication to a paired region.
E.Read scale-out with a readable secondary replica.
AnswersA, B

The service automatically fails over to a secondary replica within seconds.

Why this answer

Options A and B are correct. Business Critical includes multiple replicas (one primary and two secondary) that are automatically managed, and zone-redundant configuration is optional but not automatic. Option C is wrong because active geo-replication is a separate DR feature.

Option D is wrong because auto-failover groups are for DR. Option E is wrong because read scale-out is an optional feature, not automatic for HA.

Full explanation →
316
MCQeasy

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

A.Geo-restore feature
B.Long-Term Retention (LTR) policy
C.Automated backups retention period
D.Point-In-Time Restore (PITR) retention
AnswerB

LTR allows you to retain backups for up to 10 years with configurable weekly, monthly, yearly schedules.

Why this answer

Option A is correct because Long-Term Retention (LTR) is designed for retaining backups beyond the default retention period, with weekly, monthly, yearly options. Option B is wrong because Point-in-Time Restore (PITR) only allows restore to a point within the retention period, not long-term. Option C is wrong because automated backups have a maximum retention of 35 days.

Option D is wrong because geo-restore is for disaster recovery, not long-term retention.

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

Option B is correct because 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 →
318
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

Option A is correct because after a planned failover, the secondary region becomes the primary. To fail back, you need to re-establish replication by performing another failover from the new primary back to the original region. Option B is incorrect because you cannot add a new secondary if the original is unavailable.

Option C is incorrect because you need to fail over again, not just start replication. Option D is incorrect because you cannot directly set the new primary as secondary.

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

Options B, C, and D are correct. A Log Analytics workspace is needed for storing and querying metrics and logs. A diagnostic setting sends logs and metrics to the workspace.

An alert rule defines conditions and actions. Option A is wrong because a SQL Agent Job is not required. Option E is wrong because Azure Automation is not required.

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

Option C is correct because it addresses all remaining requirements: (1) Set minimal TLS version to 1.2; (2) Add firewall rule for office IP only; (3) Enable auditing with SCHEMA_OBJECT_CHANGE_GROUP; (4) TDE already enabled. Option A is incorrect because it does not include firewall restrictions. Option B is incorrect because it does not enforce TLS.

Option D is incorrect because it does not include auditing.

Full explanation →
321
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 →
322
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

Option B is correct because Azure SQL Database supports point-in-time restore to a different server and region, which is cost-effective and automated via PowerShell or CLI. Options A and D require manual steps. Option C is for cross-region replication, which is not designed for nightly refresh.

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

Option C is correct because REORGANIZE is the recommended maintenance for columnstore indexes to remove rows from deleted bitmaps. Option A is incorrect because REBUILD is more resource-intensive and not always necessary. Option B is incorrect because dropping and recreating is disruptive.

Option D is incorrect because updating statistics alone does not address fragmentation.

Full explanation →
324
MCQmedium

You manage an Azure SQL Database that uses the General Purpose tier. The database has a failover group with a secondary in a paired region. During a regional outage, you initiate a forced failover. After the outage is resolved, you want to bring the original primary region back online without data loss. What should you do?

A.Initiate a forced failover from the new primary to the old primary.
B.Perform a forced failover again to switch back.
C.Wait for data synchronization, then initiate a planned failover.
D.Delete the failover group and recreate it with the original primary as primary.
AnswerC

Planned failover ensures no data loss by synchronizing before switching roles.

Why this answer

Option C is correct because after a forced failover, the old primary becomes a secondary. To fail back, you need to re-sync data (which happens automatically) and then perform a planned failover to switch roles without data loss. Option A is wrong because it causes data loss.

Option B is wrong because you cannot simply delete and re-add; the group still exists. Option D is wrong because a forced failover from the new primary would cause data loss.

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

Option B is correct because 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 →
326
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

Options A, B, and D are correct. Query Store, DMVs, and Azure Metrics are built-in and free. Option C is wrong because SQL Server Profiler is deprecated and not supported.

Option E is wrong because Performance Insights is not an Azure SQL feature.

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

Option C is correct because 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 →
328
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

Option D is correct because 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 →
329
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

Option B is correct because after failover, the connection string must point to the new primary server. Client applications often cache the old endpoint. Option A is incorrect because geo-replication does not require VPN.

Option C is incorrect because the secondary database is fully provisioned. Option D is incorrect because failover does not change the server name automatically in client configurations.

Full explanation →
330
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 →
331
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

Option B is correct because point-in-time restore (PITR) can restore a database to any point within the retention period, which for most tiers is up to 35 days. The default retention is 7 days, but you can configure it to allow recovery to 5 minutes ago. Option A is for long-term retention.

Option C is for regional DR. Option D is for availability within a region.

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

Option A is correct. To perform a point-in-time restore in a different region, you need to set the restore mode to 'GeoRestore' and specify the target server in the desired region. The template shows 'PointInTimeRestore', which is for restoring to the same region.

Option B is wrong because a target server in a different region must be specified. Option C is wrong because geo-restore uses geo-redundant backups automatically. Option D is wrong because zone redundancy is unrelated to geo-restore.

Full explanation →
333
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.
AnswerC

Elastic Database Jobs can efficiently run T-SQL on a schedule with minimal overhead.

Why this answer

Option B is the correct answer. Elastic Database Jobs can run a stored procedure that deletes data older than 90 days and exports it to Blob Storage using BULK INSERT or PolyBase. This approach is designed for minimal impact and uses Azure's PaaS capabilities.

Option A (Azure Data Factory) is more suitable for complex ETL, but for simple purge and archive, Elastic Jobs is simpler and more cost-effective. Option C (Azure Automation runbook) would require additional components like Hybrid Runbook Worker and may introduce latency. Option D (SQL Agent) is not available in Azure SQL Database.

Full explanation →
334
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 →
335
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 solution specifically for monitoring Azure SQL databases and managed instances, including storage usage per database. Option A is wrong because Query Performance Insight focuses on query performance. Option C is wrong because Automatic tuning is for performance optimization.

Option D is wrong because Intelligent Insights provides proactive diagnostics but not per-database storage monitoring.

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

Option D is correct because increasing the number of files in the filegroup can distribute page allocation and reduce contention. Option A is wrong because read-only replicas do not reduce primary's latch contention. Option B is wrong because increasing instance size may help but does not directly address PAGELATCH_SH.

Option C is wrong because delayed durability affects log write, not page latches.

Full explanation →
337
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 →
338
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

Option D is correct because `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 →
339
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

Option D is correct because 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 →
340
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 →
341
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 Resource Manager templates can deploy the managed instance itself but are not ideal for subsequent database creation. Azure Automation with PowerShell runbooks can execute T-SQL to create databases. SQL Agent jobs are not available in managed instance.

Azure SQL Database elastic jobs are for multiple databases, but simpler for single database creation is Automation.

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

Option D is correct because 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 →
343
MCQhard

You are a database administrator for a government agency that uses Azure SQL Database to store classified data. The database contains highly sensitive columns (e.g., Social Security Numbers) that must be masked for most users, but fully visible to a small group of compliance officers. The compliance officers authenticate using Microsoft Entra ID. You need to implement a solution that automatically masks the sensitive columns for all users except the compliance officers, without requiring application code changes. The solution must also ensure that the compliance officers are identified by their Microsoft Entra ID user principal name (UPN). What should you do?

A.Grant SELECT permission on the sensitive columns only to the compliance officers and deny SELECT to all other users
B.Use Always Encrypted to encrypt the sensitive columns and configure the column master key to allow access only to compliance officers
C.Implement row-level security (RLS) to filter rows based on user's UPN
D.Configure dynamic data masking (DDM) on the sensitive columns and define a masking policy that excludes the compliance officers based on their UPN
AnswerD

DDM masks data at the column level for low-privileged users. You can exclude specific users by adding them to the excluded users list for each masking rule.

Why this answer

Option C is correct. Dynamic data masking (DDM) masks sensitive columns based on the user's identity. You can define a masking policy that excludes users with a specific UPN pattern (e.g., '@compliance.gov').

Option A is wrong because row-level security filters rows, not columns. Option B is wrong because always encrypted encrypts data and requires application changes. Option D is wrong because column-level security (GRANT permissions) requires application changes to handle permission errors and does not mask data.

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

Why this order

First create the pool, then add databases, configure per-database limits, monitor, and scale.

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

The 'sku' property in ARM templates defines the service tier. For Hyperscale, the name is 'HS', and tier is 'Hyperscale'. The 'zoneRedundant' property is a separate property under 'properties'.

Option B is wrong because 'edition' is not used in ARM templates for Azure SQL Database. Option C is wrong because 'requestedServiceObjectiveName' is for DTU-based tiers. Option D is wrong because 'maxSizeBytes' does not define the tier.

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

Reduces latency and failover time while maintaining sync replication.

Why this answer

Option B is correct because using an auto-failover group with a secondary in the same region (West US) but in a different availability zone provides synchronous replication with zero data loss and faster failover due to lower latency. Option A is wrong because increasing replicas does not reduce failover time significantly. Option C is wrong because changing to General Purpose would increase RPO.

Option D is wrong because Hyperscale does not guarantee zero data loss in this context.

Full explanation →
347
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 B and D are correct. Active geo-replication and failover groups both create a readable secondary in a different region. Options A, C, and E are incorrect.

Zone-redundant configuration provides high availability within a single region. Auto-failover groups is another term for failover groups, but it is not a separate option. Geo-restore restores from backups and does not maintain a continuous secondary.

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

Option C is correct because 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 →
349
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

Why this order

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

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

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

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

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

Option C is correct because 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 CPU pressure does not directly cause log write waits. Option B is wrong because insufficient storage space causes different symptoms.

Option D is wrong because blocking causes other wait types like LCK_M_*.

Full explanation →
352
Multi-Selectmedium

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Select 2 answers
A.Create contained database users mapped to Microsoft Entra ID identities
B.Assign a Microsoft Entra ID user or group as the Azure SQL Database admin
C.Set the database's authentication mode to 'Azure AD Only Authentication'
D.Deploy the database in Azure SQL Managed Instance
E.Register the application in Microsoft Entra ID
AnswersB, C

Required to enable Entra ID authentication.

Why this answer

Assigning a Microsoft Entra ID user or group as the Azure SQL Database admin (Option B) is required because this establishes the administrative identity that can manage authentication for the database. Without this step, Microsoft Entra ID identities cannot be granted access or used to authenticate. Setting the database's authentication mode to 'Azure AD Only Authentication' (Option C) is also required because it disables SQL authentication, ensuring that only Microsoft Entra ID identities can connect, which is a key requirement for enabling Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates often think creating contained database users (Option A) is the first step, but the correct order is to first assign an Entra ID admin and set the authentication mode, then create users mapped to Entra ID identities.

Full explanation →
353
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 →
354
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 →
355
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

Option B is correct because 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 →
356
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

Option D is correct because batch mode requires sufficient memory grant; if the query is memory-constrained, it may fall back to row mode. Option A is wrong because compatibility level 130 or higher supports batch mode. Option B is wrong because columnstore indexes support batch mode by default.

Option C is wrong because MAXDOP setting does not disable batch mode.

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

Option B is correct because 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 →
358
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 →
359
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

Option B is correct. SQL Agent is available on Managed Instance and can execute a stored procedure that checks modification counters (sys.dm_db_stats_properties) and updates only those statistics that meet the threshold. Option A is incorrect because Elastic Database Jobs is not available for Managed Instance.

Option C is incorrect because Azure Automation would require a hybrid worker and is less efficient. Option D is incorrect because automatic tuning does not update statistics based on modification percentage; it only manages indexes.

Full explanation →
360
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.
AnswerD

Automatic index tuning can manage index rebuilds and reorganizations based on fragmentation.

Why this answer

Option C is correct because Azure SQL Database's built-in automatic index tuning can be configured to handle index maintenance based on fragmentation thresholds without custom scripting. Options A and B manage indexes at the database level but require manual job creation. Option D is for on-premises or SQL Server on VM, not Azure SQL Database managed instance or single database.

Full explanation →
361
Multi-Selecthard

Which TWO of the following are best practices for managing firewall rules for Azure SQL Database?

Select 2 answers
A.Use IP-based firewall rules for all client connections, including Azure services.
B.Create firewall rules with broad IP ranges (e.g., 0.0.0.0/0) to simplify management.
C.Use Azure Private Link to connect from Azure VNets instead of opening firewall rules to IP ranges.
D.Audit all firewall rule changes using Azure Activity Logs.
E.Enable the 'Allow Azure Services' firewall rule to allow connections from Azure services.
AnswersC, E

Private Link is more secure than opening firewall to IP ranges.

Why this answer

Option C is correct because Azure Private Link allows you to connect to Azure SQL Database from an Azure VNet using a private endpoint, which maps the database to a private IP address within the VNet. This eliminates the need to open firewall rules to public IP ranges, reducing the attack surface and ensuring traffic stays within the Microsoft backbone network. It is a security best practice recommended by Microsoft for isolating PaaS services.

Exam trap

The trap here is that candidates often confuse auditing (Option D) with a management best practice, or they mistakenly think that enabling 'Allow Azure Services' (Option E) is always insecure, when in fact it is a recommended setting for Azure-native services that cannot use Private Link.

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

Option C is correct because DATABASE_OBJECT_CHANGE_GROUP captures DML changes on all database objects, and SCHEMA_OBJECT_CHANGE_GROUP captures DDL changes. Option A is incorrect because it only contains DDL actions. Option B is incorrect because it only contains security-related actions.

Option D is incorrect because it only contains DML actions on specific objects.

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

Option B is correct because 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 →
364
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

Option A is correct: Query Store tracks query history. Option D is correct: Azure SQL Analytics (now part of Azure Monitor) provides historical performance data. Option B is incorrect: Dynamic Management Views show current state, not historical.

Option C is incorrect: Intelligent Insights provides summaries, not detailed query history. Option E is incorrect: Extended Events are for real-time tracing, not historical analysis.

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

Option D is correct because the read-only endpoint failover policy is currently Disabled; you need to enable it to allow read-only routing to the secondary. Option A is wrong because the grace period does not affect read-only routing. Option B is wrong because adding databases is unrelated.

Option C is wrong because the read-only endpoint is separate from the read-write endpoint.

Full explanation →
366
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 →
367
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

Option A is correct. A failover group between two managed instances provides automatic failover and a readable secondary. You can configure the secondary instance with a smaller compute size (e.g., fewer vCores) because it is only used for read-only workloads and DR.

Option B is wrong because you cannot change the secondary's service tier to General Purpose in a failover group; it must match the primary. Option C is wrong because the secondary must be in a different region for DR. Option D is wrong because active geo-replication is not supported for SQL Managed Instance.

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

Option C is correct because an Always On Availability Group requires at least 2 replicas (primary and secondary) for automatic failover. A witness is optional. Option A (1 VM) provides no high availability.

Option B (2 VMs) is the minimum for automatic failover. Option D (4 VMs) is unnecessary.

Full explanation →
369
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 →
370
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 I/O latency for reading data pages from disk. Option B is correct. Option A is wrong because PAGEIOLATCH_SH is for I/O, not memory.

Option C is wrong because it is not CPU-related. Option D is wrong because it is not blocking.

Full explanation →
371
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 →
372
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

Option B is correct because Azure SQL Database has a server-level setting to enforce minimum TLS version. Option A is wrong because there is no database-level TLS setting. Option C is wrong because the firewall does not control encryption protocols.

Option D is wrong because auditing is unrelated.

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

Retention of 0 means logs are not retained.

Why this answer

Option D is correct because 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 C is wrong because emailAccountAdmins true is fine.

Full explanation →
374
MCQmedium

You have an Azure SQL Database with active geo-replication. You need to monitor the replication lag to ensure the RPO is met. Which metric should you monitor?

A.DTU consumption
B.Replication lag
C.Data IO percentage
D.Deadlocks
AnswerB

Directly measures lag.

Why this answer

Option B is correct because 'Replication lag' metric directly measures the delay in seconds between primary and secondary. Option A (Data IO percentage) is a performance metric. Option C (DTU consumption) is related to resource usage.

Option D (Deadlocks) is unrelated.

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

Option B is correct because autoscale settings on the DTU or vCore model allow automatic scaling based on metrics like CPU. Option A is wrong because elastic pools are for managing multiple databases, not auto-scaling a single database. Option C is wrong because elastic jobs are for scheduling tasks.

Option D is wrong because automatic tuning optimizes performance but does not scale resources.

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 →