Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 451–525

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

Take a mock examExam hub
Page 6

Page 7 of 13

Page 8
451
Multi-Selecthard

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Select 3 answers
A.Create a server-level firewall rule on the secondary server to allow client IPs.
B.Add the primary database to the failover group.
C.Configure a grace period of at least 1 hour.
D.Create a secondary server in the target region with the same administrative login.
E.Set the failover group's read/write failover policy to 'Automatic' or 'Manual'.
AnswersB, D, E

Correct: The database must be added to the group.

Why this answer

Adding the primary database to the failover group is the essential step that associates the database with the group, enabling automated replication and failover to the secondary server. Without this step, the failover group has no database to replicate, and the readable secondary cannot be created or maintained.

Exam trap

The trap here is that candidates may think creating firewall rules on the secondary server is required for failover group setup, but those rules are only needed for direct client connections to the secondary, not for the replication or failover process itself.

Full explanation →
452
Multi-Selecteasy

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Select 2 answers
A.Increased query duration.
B.High number of deadlocks.
C.Increased transaction log usage.
D.High DTU consumption.
E.Failed authentication attempts.
AnswersA, D

Longer query duration indicates performance degradation.

Why this answer

Options A and D are correct. In Azure SQL Database's Intelligent Insights, increased query duration and high DTU consumption are key metrics that indicate performance degradation due to increased resource consumption. Increased query duration suggests that queries are taking longer, often due to resource contention.

High DTU consumption reflects high usage of CPU, memory, and I/O, which can degrade performance. Option B (high number of deadlocks) indicates concurrency issues but does not directly measure resource consumption. Option C (increased transaction log usage) is related to write activity, not a primary performance metric in Intelligent Insights.

Option E (failed authentication attempts) is a security metric, unrelated to resource consumption.

Full explanation →
453
MCQhard

You have an Azure SQL Database configured with active geo-replication to a secondary region. A regional outage occurs in the primary region. You need to manually fail over to the secondary database with the least amount of data loss. Which PowerShell cmdlet should you run?

A.Start-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ...
B.Set-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ... -PartnerResourceGroupName ... -Failover
C.Invoke-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ... -Secondary
D.Switch-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ...
AnswerB

This cmdlet initiates a planned failover with synchronization to minimize data loss.

Why this answer

Set-AzSqlDatabaseSecondary with -Failover initiates a planned failover that synchronizes data before failing over, minimizing data loss. Option A (Start-AzSqlDatabaseFailover) is for failing over a single database within the same region, not geo-failover. Option C (Invoke-AzSqlDatabaseFailover) does not exist.

Option D (Switch-AzSqlDatabaseSecondary) is not a valid cmdlet.

Full explanation →
454
Multi-Selectmedium

Which THREE are features of Azure SQL Database Hyperscale?

Select 3 answers
A.Zone redundant compute by default
B.Unlimited log rate
C.Storage up to 100 TB
D.Near-instantaneous backups
E.Fast scaling of compute resources
AnswersC, D, E

Hyperscale supports up to 100 TB.

Why this answer

Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Exam trap

The trap here is that candidates often assume 'unlimited' or 'by default' features based on marketing terms, but Azure SQL Database Hyperscale has specific limits and optional configurations that are not universally applied.

Full explanation →
455
MCQmedium

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

A.Set up log shipping to an Azure SQL Database
B.Perform an offline migration using Azure Database Migration Service
C.Export a BACPAC file from the source and import it to Azure SQL Database
D.Use Azure Database Migration Service with online mode after removing FileTables
AnswerD

Online migration minimizes downtime. Remove unsupported features first, then migrate using DMS.

Why this answer

Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Exam trap

The trap here is that candidates may assume log shipping (Option A) is viable for Azure SQL Database, but log shipping is not supported for Azure SQL Database as it requires SQL Server Agent and file-level restore operations, which are not available in that PaaS offering.

How to eliminate wrong answers

Option A is wrong because log shipping is not supported to Azure SQL Database; it only works between on-premises SQL Server instances or to SQL Server on Azure VMs, not to Azure SQL Database. Option B is wrong because an offline migration using Azure Database Migration Service would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option C is wrong because exporting a BACPAC file and importing it to Azure SQL Database is an offline process that does not support ongoing replication, leading to downtime, and it also does not handle unsupported features like FileTables automatically.

Full explanation →
456
MCQeasy

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

A.Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.
B.Create a SQL Agent alert that fires when the storage is above 80% and sends an email.
C.Configure Database Mail to send alerts automatically.
D.Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.
AnswerA

Azure Monitor alerts are the standard way to send notifications based on metrics.

Why this answer

Azure Alert Rules can monitor metrics like storage percent and trigger email actions. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Elastic Jobs execute T-SQL, not send email directly.

Option D is wrong because Database Mail is not available in Azure SQL Database.

Full explanation →
457
MCQmedium

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

A.Restore a geo-redundant backup to the secondary region
B.Configure Active Geo-Replication to the secondary region
C.Create a named replica in the secondary region and fail over manually
D.Use an Auto-Failover Group with the secondary in the paired region
AnswerD

Auto-failover groups support Hyperscale and allow failover with minimal data loss.

Why this answer

Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Exam trap

The trap here is that candidates confuse Active Geo-Replication (unsupported on Hyperscale) with Auto-Failover Groups (the correct feature for Hyperscale), or assume named replicas can be used for cross-region failover when they are strictly intra-region read-only replicas.

How to eliminate wrong answers

Option A is wrong because restoring a geo-redundant backup to the secondary region involves point-in-time recovery from backups, which can result in significant data loss (up to the last backup interval) and does not provide a readable secondary for ongoing reads or a seamless failover drill. Option B is wrong because Active Geo-Replication is not supported on Azure SQL Database Hyperscale; it is only available for other service tiers (General Purpose, Business Critical). Option C is wrong because named replicas in Hyperscale are read-only replicas within the same region and cannot be used for cross-region failover; they do not support manual failover to a secondary region.

Full explanation →
458
Multi-Selecthard

Which THREE of the following are required to configure Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault for Azure SQL Database?

Select 3 answers
A.The key encryption key must be stored in Azure Key Vault.
B.The Azure Key Vault must be in the same region as the database.
C.The Azure Key Vault must have soft-delete and purge protection enabled.
D.The Azure SQL Database server must have an Azure AD identity.
E.The SQL server must have an RBAC role assignment on the key vault.
AnswersA, C, D

Customer-managed key is stored in AKV.

Why this answer

Transparent Data Encryption (TDE) with customer-managed keys requires the key encryption key (KEK) to be stored in Azure Key Vault. This KEK is used to protect the database encryption key (DEK), which is stored in the database boot record. Without the KEK in Key Vault, the customer-managed key scenario cannot be implemented, as Azure SQL Database must be able to access the key to perform encryption operations.

Exam trap

The trap here is that candidates often confuse the requirement for the Key Vault to be in the same region as the database (Option B) with the actual requirement that the Key Vault must have soft-delete and purge protection enabled (Option C), and they may also mistakenly think an RBAC role assignment (Option E) is needed instead of the correct access policy permissions for the server's Azure AD identity.

Full explanation →
459
Multi-Selectmedium

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts an OLTP application. The application requires an RPO of 15 seconds and an RTO of 5 minutes. You need to choose a configuration that meets these requirements. Which TWO options should you consider? (Choose two.)

Select 1 answer
A.Configure a failover group with a secondary instance in a paired region, both using Business Critical service tier.
B.Deploy a zone-redundant Business Critical instance in a single region.
C.Deploy a General Purpose instance with a failover group to a secondary in another region.
D.Ensure the secondary instance has at least one readable secondary replica.
E.Use active geo-replication between two Business Critical instances in different regions.
AnswersA

Correct. Failover groups with Business Critical tier provide synchronous commit within the region and asynchronous replication across regions, enabling RPO of up to 5 seconds and RTO of approximately 1 minute, meeting the requirements.

Why this answer

(failover group with Business Critical secondary in paired region) meets the RPO of 15 seconds and RTO of 5 minutes because Business Critical tier uses synchronous commit within the region and asynchronous replication across regions, with failover group providing automated failover. Option E (active geo-replication) is not supported for Azure SQL Managed Instance; only failover groups are available. Option B does not protect against regional failure.

Option C's General Purpose tier cannot guarantee the required low RPO/RTO. Option D is not a standalone solution.

Full explanation →
460
MCQmedium

You need to automate the creation of an Azure SQL Database using Azure CLI in a CI/CD pipeline. The database name must be unique and include the build ID. How should you specify the database name in the Azure CLI command?

A.Use the $RANDOM variable in Bash
B.Use the $(uuid) function in Azure CLI
C.Use a hardcoded name like 'mydb'
D.Use an environment variable $(Build.BuildId) set by the pipeline
AnswerD

Azure DevOps provides unique build IDs that can be referenced as environment variables.

Why this answer

In Azure DevOps pipelines, $(Build.BuildId) is a predefined variable that provides a unique build ID. This can be used in Azure CLI commands to create a unique database name, e.g., 'mydb-$(Build.BuildId)'. Option A ($RANDOM) is Bash-specific and not reliable across pipeline runs.

Option B (uuid) is not a valid Azure CLI function. Option C (hardcoded name) fails on pipeline rebuilds due to name conflicts.

Full explanation →
461
MCQhard

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

A.The audit logs are being sent to Azure Monitor instead of blob storage
B.The retention days are set too high, causing logs to be truncated
C.The audit actions and groups do not include login events
D.Auditing is disabled at the server level
AnswerC

Login events are captured by 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' and 'FAILED_DATABASE_AUTHENTICATION_GROUP', which are not listed in the audit actions and groups.

Why this answer

The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Exam trap

The trap here is that candidates assume auditing automatically captures all security events, but Azure SQL Database requires explicit inclusion of authentication action groups to log login attempts, and the JSON policy in the exhibit likely omits these groups.

How to eliminate wrong answers

Option A is wrong because the destination of audit logs (Azure Monitor vs. blob storage) does not affect which events are captured; it only changes where the logs are stored. Option B is wrong because retention days control how long logs are kept, not which events are captured; setting retention too high would not cause logs to be truncated or missing. Option D is wrong because if auditing were disabled at the server level, no audit logs would be generated at all, but the question states that other events (not login events) are being captured, implying auditing is enabled.

Full explanation →
462
Multi-Selectmedium

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Select 2 answers
A.Increase the service tier to get more storage.
B.Enable row or page compression on large tables.
C.Reduce backup retention to 1 day.
D.Enable automatic tuning to optimize query plans.
E.Archive historical data to Azure Blob Storage using external tables.
AnswersB, E

Compression reduces storage footprint.

Why this answer

To reduce storage costs for an Azure SQL Database, you can enable row or page compression on large tables (option B) to decrease data size. Archiving historical data to Azure Blob Storage using external tables (option E) moves cold data out of the database, reducing storage consumption. Option A is incorrect because increasing the service tier typically increases storage costs.

Option C is wrong because reducing backup retention does not affect storage used for data files, only backup storage. Option D is incorrect because automatic tuning optimizes query performance, not storage costs. Therefore, the correct answers are B and E.

Full explanation →
463
MCQhard

Refer to the exhibit. You notice the database db1 is currently 80% full on storage, and the service objective is S3. Which action would best prevent storage full errors while minimizing cost?

A.Enable automatic storage growth on the database.
B.Change the service objective to S4.
C.Increase the maximum database size to 250 GB.
D.Scale the database to a higher service tier like Standard S4.
AnswerC

S3 supports up to 250 GB storage. Increasing max size to 250 GB provides headroom without changing the service tier, minimizing cost.

Why this answer

The exhibit shows the database is at 80% of its current maximum size. The S3 service objective allows a maximum database size of up to 250 GB. By increasing the maximum size to 250 GB, you provide sufficient storage headroom without changing the service tier, which would incur higher costs.

Option A (automatic storage growth) is not a user-configurable feature in Azure SQL Database. Options B and D involve scaling to a higher service tier (S4 or higher), which is unnecessary and more expensive.

Full explanation →
464
MCQeasy

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

A.Azure CLI scripts
B.Azure Automation runbooks
C.Azure Policy
D.Azure Resource Manager templates
AnswerD

ARM templates define resources declaratively and can deploy database, firewall, and settings.

Why this answer

Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative.

Azure Policy is for governance, not deployment.

Full explanation →
465
MCQeasy

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

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

Masking obfuscates columns for unauthorized users.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking that encryption is needed for obfuscation, but DDM is specifically designed for on-the-fly data masking without changing the underlying storage or requiring client-side changes.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, ensuring that the database engine never sees plaintext, which prevents even authorized database users (like HR managers) from viewing the data unless they have the column encryption key, making it unsuitable for role-based obfuscation where some users need full access. Option C is wrong because Row-Level Security (RLS) restricts access to rows based on user identity or context, but it does not obfuscate column values; it either shows or hides entire rows, not partial data within a column. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (data files and backups) but does not control or obfuscate data visibility at query time for specific users or columns.

Full explanation →
466
MCQeasy

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

A.Use auto-failover groups with read-write failover policy.
B.Use geo-restore from geo-redundant backups.
C.Use active geo-replication and plan manual failover.
D.Use long-term retention (LTR) backups with daily frequency.
AnswerC

Active geo-replication provides RPO less than 5 seconds and manual failover can achieve RTO of less than 1 hour.

Why this answer

Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Exam trap

The trap here is that candidates confuse auto-failover groups (Option A) with active geo-replication, assuming automatic failover always provides a better RTO, but the question's strict RPO of less than 5 seconds and the need for manual control make active geo-replication the correct choice, as auto-failover groups introduce additional latency and automatic detection delays that can push RTO beyond 1 hour.

How to eliminate wrong answers

Option A is wrong because auto-failover groups with read-write failover policy are designed for group-level failover of multiple databases and have a default RPO of up to 5 seconds, but the failover is automatic and may not guarantee an RTO under 1 hour due to potential delays in detection and DNS propagation; more critically, the question specifies a manual failover requirement. Option B is wrong because geo-restore from geo-redundant backups has an RPO measured in hours (typically 1-12 hours) and an RTO that can exceed several hours, making it unsuitable for sub-5-second RPO. Option D is wrong because long-term retention backups are taken daily, resulting in an RPO of up to 24 hours, and restoring from them can take hours, failing both the RPO and RTO targets.

Full explanation →
467
MCQmedium

You are a database administrator for an Azure SQL Database. You need to automate the deployment of schema changes across multiple databases in an elastic pool. Which Azure service should you use to orchestrate these deployments?

A.Azure Functions
B.Azure Automation
C.Azure DevOps
D.Azure Logic Apps
AnswerC

Azure DevOps provides release pipelines for deploying schema changes across multiple databases.

Why this answer

Azure DevOps with its release pipelines can manage the deployment of schema changes across multiple databases. Option A is wrong because Azure Functions is for event-driven code, not orchestrated deployments. Option B is wrong because Azure Automation primarily manages infrastructure tasks, not database schema deployments.

Option D is wrong because Azure Logic Apps is for workflow integration, not database schema deployment.

Full explanation →
468
MCQhard

Refer to the exhibit. An Azure SQL Database has the above ARM template for long-term retention (LTR) backup policy. Which statement is true about the retention duration?

A.Monthly backups are retained for 12 months.
B.Yearly backups are retained for 10 years.
C.Yearly backups are retained for 5 years.
D.Weekly backups are retained for 1 week.
AnswerC

YearlyRetention is P5Y, which equals 5 years.

Why this answer

In the ARM template for LTR backup policy, the yearlyRetention is set to P5Y, which means 5 years. WeeklyRetention is P2W (2 weeks), and monthlyRetention is P6M (6 months). Therefore, only option C is accurate.

Option A is incorrect because monthly backups are retained for 6 months, not 12. Option B is incorrect because yearly backups are retained for 5 years, not 10. Option D is incorrect because weekly backups are retained for 2 weeks, not 1 week.

Full explanation →
469
MCQhard

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

A.Blocking due to lock contention
B.CPU pressure from high-complexity queries
C.I/O subsystem bottleneck
D.Insufficient memory allocation for the database
AnswerC

PAGEIOLATCH_SH waits indicate I/O latency.

Why this answer

The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.

Exam trap

The trap here is that candidates see PAGEIOLATCH_SH and assume it is always caused by insufficient memory, but the combination with WRITELOG waits clearly points to an I/O bottleneck, not a memory issue.

How to eliminate wrong answers

Option A is wrong because blocking due to lock contention would manifest as LCK_M_* waits (e.g., LCK_M_S, LCK_M_X), not PAGEIOLATCH_SH or WRITELOG waits. Option B is wrong because CPU pressure from high-complexity queries would show SOS_SCHEDULER_YIELD or CXPACKET waits, not I/O-related waits. Option D is wrong because insufficient memory allocation would cause PAGEIOLATCH_SH waits only if memory pressure forces excessive physical I/O, but the presence of WRITELOG waits points directly to a log write bottleneck, not a memory shortage; memory pressure alone would not cause WRITELOG waits.

Full explanation →
470
MCQeasy

A small business uses Azure SQL Database single database in the General Purpose tier for its inventory management system. The database is located in the Brazil South region. The business wants a disaster recovery solution that can automatically fail over to a secondary region in case of an outage, with an RPO of less than 1 hour and an RTO of less than 2 hours. The budget is limited, so the solution must be cost-effective. What should you recommend?

A.Create a failover group with a secondary database in a different region using the General Purpose tier.
B.Enable zone redundancy on the existing database.
C.Use geo-restore from geo-redundant backups.
D.Upgrade to Business Critical tier and configure geo-replication.
AnswerA

Cost-effective and meets RPO/RTO.

Why this answer

A failover group with a secondary in another region provides automatic failover with low RPO (<1 hour) and RTO (<2 hours), and using the General Purpose tier is cost-effective. Option B is wrong because zone redundancy provides high availability within a single region, not disaster recovery across regions. Option C is wrong because geo-restore from geo-redundant backups has an RTO of several hours (typically 7-12 hours), which exceeds the required 2 hours.

Option D is wrong because upgrading to Business Critical tier is more expensive and not necessary for the RPO/RTO requirements; geo-replication is already available in General Purpose through failover groups.

Full explanation →
471
MCQeasy

Refer to the exhibit. The exhibit shows an Azure role assignment with a condition. When user@contoso.com tries to read data from the database 'proddb', what will be the effect of this condition?

A.The user will be allowed to read all data from the database.
B.The user will be denied the ability to read data from the database.
C.The user will be able to read the database metadata but not the data.
D.The user will be able to assign the role to others.
AnswerB

The condition denies read data actions because it excludes the data read action from the role.

Why this answer

The condition in the Azure role assignment explicitly denies read access to the database 'proddb' for user@contoso.com. In Azure RBAC, a deny assignment overrides any allow assignments, so even if the user has other roles granting read permissions, this condition will block all data read operations on that specific database. Therefore, the user will be denied the ability to read data from the database.

Exam trap

The trap here is that candidates often assume a condition only restricts certain operations or that metadata reads are separate from data reads, but Azure RBAC deny assignments unconditionally block the specified action, and 'read data' encompasses all data retrieval operations on the database.

How to eliminate wrong answers

Option A is wrong because the condition explicitly denies read access, so the user cannot read all data; a deny assignment takes precedence over any allow. Option C is wrong because the condition denies read data operations entirely, not just metadata; Azure RBAC does not separate metadata read from data read in this context—denying read data blocks both. Option D is wrong because the condition only affects read data permissions, not the ability to assign roles; role assignment permissions are controlled by separate RBAC actions like Microsoft.Authorization/roleAssignments/write.

Full explanation →
472
MCQmedium

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

A.Disable public network access and configure a service endpoint.
B.Configure a private endpoint and disable public network access.
C.Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.
D.Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.
AnswerD

This allows specific IPs and Azure services while blocking others.

Why this answer

Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Exam trap

The trap here is that candidates often confuse network security groups (NSGs) with Azure SQL firewall rules, assuming NSGs can control access to PaaS services like Azure SQL Database, when in fact NSGs only apply to resources within a virtual network and not to the public endpoint of Azure SQL.

How to eliminate wrong answers

Option A is wrong because disabling public network access and configuring a service endpoint would block all public traffic, including the specific client IPs, and service endpoints only secure traffic to Azure SQL from a virtual network, not from arbitrary client IPs. Option B is wrong because configuring a private endpoint and disabling public network access would isolate the database to a virtual network, blocking all public client IPs and requiring clients to be on the same or peered network, which does not meet the requirement to allow specific client IP addresses. Option C is wrong because network security group (NSG) rules apply at the subnet level for resources deployed in a virtual network, but Azure SQL Database is a PaaS service with a public endpoint by default; NSGs cannot control inbound traffic to the Azure SQL Database's public endpoint directly.

Full explanation →
473
MCQeasy

Your company requires that all production databases in Azure SQL Database have an RPO of less than 5 seconds and an RTO of less than 1 minute during a regional outage. You need to recommend a high availability and disaster recovery solution. Which feature should you use?

A.Zone-redundant configuration (Hyperscale service tier)
B.Auto-failover groups with active geo-replication
C.Long-term retention (LTR) backups
D.Geo-restore from geo-redundant backups
AnswerB

Provides low RPO and RTO across regions.

Why this answer

Auto-failover groups with active geo-replication provide the lowest RPO (typically <5 seconds) and RTO (under 1 minute) for regional outages by enabling continuous data synchronization and automated failover across paired regions. Option A is incorrect because zone-redundant configuration only protects against zonal failures within a single region, not a full regional outage. Option C is wrong because long-term retention backups are for archival purposes and have much higher RPO/RTO.

Option D is incorrect because geo-restore from geo-redundant backups has an RPO of at least 1 hour and an RTO of several hours to days, failing to meet the strict requirements.

Full explanation →
474
MCQhard

Your company uses Azure SQL Managed Instance. You need to configure a server-level firewall rule to allow access from a specific Azure service, but you want to minimize the attack surface. What is the best practice?

A.Set the firewall rule to allow all IP addresses (0.0.0.0/0).
B.Disable public endpoint and use private endpoint.
C.Set the firewall rule to allow Azure services and resources to access this server.
D.Create a firewall rule with the specific IP range of the Azure service.
AnswerC

This option allows only Azure services while blocking all other public IP addresses.

Why this answer

Enabling the 'Allow Azure services and resources to access this server' firewall rule is the best practice for Azure SQL Managed Instance when you need to allow access from a specific Azure service while minimizing the attack surface. This setting uses a special firewall rule that permits connections from any Azure service (including those within the same region) without exposing the server to all public IP addresses, as it relies on Azure's internal network boundaries and does not open the server to the entire internet. It is more secure than allowing all IPs (0.0.0.0/0) because it restricts access to only Azure-originated traffic, and it is simpler than manually specifying IP ranges, which can change dynamically.

Exam trap

The trap here is that candidates often confuse the 'Allow Azure services' rule with the 'Allow all IPs' rule (0.0.0.0/0), but the former is far more restrictive because it only permits traffic from Azure's internal network, not from any public IP address.

How to eliminate wrong answers

Option A is wrong because setting the firewall rule to allow all IP addresses (0.0.0.0/0) opens the managed instance to the entire public internet, vastly increasing the attack surface and violating the principle of least privilege. Option B is wrong because disabling the public endpoint and using a private endpoint is a valid security measure, but it does not directly address the requirement to configure a server-level firewall rule to allow access from a specific Azure service; private endpoint bypasses the firewall entirely by using a private IP within a virtual network, which is a different configuration path. Option D is wrong because creating a firewall rule with the specific IP range of the Azure service is impractical and unreliable, as Azure services often use dynamic IP ranges that can change without notice, and maintaining such rules is error-prone and increases administrative overhead.

Full explanation →
475
MCQmedium

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

A.Query 2 because it has the highest average CPU per execution.
B.Query 1 because it has the highest total_cpu_time.
C.All queries should be optimized equally.
D.Query 3 because it runs most frequently.
AnswerA

High average CPU suggests inefficiency; optimizing it can reduce overall CPU significantly.

Why this answer

Query 2 is the best candidate for optimization because it has the highest average CPU per execution, indicating that each run of this query consumes significantly more CPU resources than the others. Reducing the CPU cost per execution for this query will yield the greatest per-execution savings, making it the most efficient target for reducing overall CPU usage.

Exam trap

The trap here is that candidates often focus on total CPU time or execution frequency, but the key metric for optimization efficiency is average CPU per execution, which directly measures the cost of each individual query run.

How to eliminate wrong answers

Option B is wrong because total_cpu_time alone does not account for execution count; a query with high total CPU but many executions may have a low per-execution cost, and optimizing it might yield less benefit per change. Option C is wrong because not all queries have equal impact; focusing on the query with the highest average CPU per execution provides the most efficient optimization for reducing overall CPU usage. Option D is wrong because frequency of execution does not directly correlate with CPU impact; a frequently run query with low per-execution CPU may contribute less to total CPU than a less frequent but expensive query.

Full explanation →
476
Multi-Selecteasy

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Select 2 answers
A.Internal Load Balancer
B.Azure Files share for witness
C.Windows Server Failover Cluster
D.Azure SQL Database
E.VPN gateway between regions
AnswersA, C

Required for the listener.

Why this answer

A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Exam trap

The trap here is that candidates often think a VPN gateway is required for cross-region AGs, but the question asks for required options to configure the AG, and the ILB and WSFC are the only mandatory components; the VPN gateway is optional and only relevant for specific network topologies.

Full explanation →
477
Multi-Selecthard

You are designing a high availability and disaster recovery solution for an Azure SQL Managed Instance that hosts a mission-critical database. You need to ensure zero data loss during planned maintenance and automatic failover within 30 seconds. Additionally, you must protect against a regional outage with an RPO of 5 minutes and an RTO of 1 hour. Which TWO features should you implement? (Select two.)

Select 2 answers
A.Configure long-term backup retention with geo-redundant storage.
B.Configure active geo-replication to a secondary region.
C.Configure a failover group to a secondary region.
D.Deploy the instance in the Business Critical tier with zone redundancy.
E.Deploy the instance in the General Purpose tier with zone redundancy.
AnswersC, D

Provides cross-region DR with RPO of 5 minutes and RTO of 1 hour.

Why this answer

A failover group to a secondary region provides disaster recovery with an RPO of 5 minutes and RTO of 1 hour, meeting the regional outage requirements. Option D is correct because the Business Critical tier with zone redundancy offers zero data loss and automatic failover within 30 seconds for planned maintenance within a region. Option A is incorrect because long-term backup retention with geo-redundant storage does not provide automatic failover or meet the RPO/RTO targets.

Option B is incorrect because active geo-replication is not supported for Azure SQL Managed Instance. Option E is incorrect because the General Purpose tier does not support zone redundancy, so it cannot meet the zero data loss and fast failover requirements.

Full explanation →
478
MCQeasy

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

A.Point-in-time restore (PITR) retention
B.Backup vault with Azure Backup
C.Long-term retention (LTR) policy
D.Automated backup policy
AnswerC

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

Why this answer

Long-term retention (LTR) policy is specifically designed to retain backups beyond the point-in-time restore (PITR) window, supporting daily, weekly, monthly, and yearly retention periods. Option C is correct. Option A (PITR retention) is too short and cannot retain backups for years.

Option B (Backup vault with Azure Backup) is used for Azure VM backups and not for Azure SQL Database automated backups. Option D (Automated backup policy) refers to the default backup settings but does not include long-term retention.

Full explanation →
479
MCQmedium

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

A.Configure log shipping to a secondary instance in West US.
B.Enable geo-redundant backup storage and use geo-restore.
C.Create an auto-failover group with a secondary replica in West US.
D.Deploy an Always On availability group with a synchronous secondary in West US.
AnswerC

Auto-failover groups provide automatic failover within minutes and an RTO of 1 hour is achievable.

Why this answer

SQL Managed Instance supports automatic failover groups with a readable secondary in a paired region. Option A is wrong because log shipping is not supported for Managed Instance. Option B is wrong because geo-restore takes up to 12 hours, which does not meet the 1-hour RTO requirement.

Option D is wrong because Always On availability groups on SQL Managed Instance do not support automatic failover across regions; they require manual failover.

Full explanation →
480
MCQmedium

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

A.Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.
B.Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.
C.Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.
D.Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.
AnswerD

Correct: High auto-pause delay prevents frequent cold starts, and minimum vCores guarantee baseline performance for bursts.

Why this answer

Setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Exam trap

The trap here is that candidates assume a short auto-pause delay (e.g., 1 minute) saves more cost, but they overlook the cold-start latency penalty that makes it unsuitable for bursty workloads requiring immediate responsiveness.

How to eliminate wrong answers

Option A is wrong because the serverless compute tier does not support read scale-out; read scale-out is a feature of the provisioned Hyperscale and Business Critical tiers, not General Purpose. Option B is wrong because setting auto-pause delay to 0 (disabled) is not a valid configuration; the minimum auto-pause delay is 60 minutes, and disabling auto-pause entirely would prevent the cost-saving benefits of serverless. Option C is wrong because a 1-minute auto-pause delay would cause the database to pause frequently during idle periods, leading to cold-start latency when bursts occur, which contradicts the requirement to handle sudden activity without significant latency.

Full explanation →
481
MCQhard

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

A.ALTER INDEX Orders ON Orders REBUILD;
B.ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);
C.ALTER INDEX ALL ON Orders REORGANIZE;
D.ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);
AnswerB

Online rebuild minimizes downtime.

Why this answer

It uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Exam trap

The trap here is that candidates often confuse `REORGANIZE` with `REBUILD` for high fragmentation, or they overlook the `ONLINE = ON` option, assuming all rebuilds are online by default in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because it only rebuilds the index named 'Orders' (which is likely the table name, not an index name) and defaults to offline mode, causing downtime. Option C is wrong because `REORGANIZE` is used for defragmentation below 30% and does not rebuild the index, so it is ineffective for fragmentation above 50%. Option D is wrong because it specifies `ONLINE = OFF`, which performs an offline rebuild, blocking user access and violating the requirement to minimize downtime.

Full explanation →
482
Multi-Selecthard

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Select 3 answers
A.Increase MAXDOP for the database to 8.
B.Create nonclustered columnstore indexes on large reporting tables.
C.Configure a read-scale replica and direct reporting queries to it.
D.Disable automatic tuning to prevent plan changes.
E.Enable result set caching for the database.
AnswersB, C, E

Columnstore indexes significantly improve aggregation and scan performance.

Why this answer

Creating nonclustered columnstore indexes (option B) improves aggregation and reporting query performance by using columnar storage and batch processing. Configuring a read-scale replica (option C) offloads reporting queries to a secondary replica, reducing contention on the primary for OLTP operations. Enabling result set caching (option E) caches query results in the Premium/Business Critical tiers, reducing repeated reads for static reporting data.

Option A (increasing MAXDOP to 8) can lead to parallel query contention and negatively impact OLTP performance, especially on smaller instances. Option D (disabling automatic tuning) removes beneficial plan corrections and is not recommended for improving performance.

Full explanation →
483
MCQhard

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

A.Blocking
B.CPU bottleneck
C.I/O subsystem bottleneck
D.Memory pressure
AnswerC

`PAGEIOLATCH_SH` indicates slow I/O for reading pages.

Why this answer

The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Exam trap

The trap here is that candidates confuse `PAGEIOLATCH_SH` with memory pressure or blocking, but the key distinction is that this wait type specifically measures I/O latency for reading pages from disk, not memory availability or lock contention.

How to eliminate wrong answers

Option A is wrong because blocking is indicated by wait types like `LCK_M_*` (e.g., `LCK_M_S` or `LCK_M_X`), not by `PAGEIOLATCH_SH`. Option B is wrong because a CPU bottleneck typically manifests as high `SOS_SCHEDULER_YIELD` or `CXPACKET` waits, not I/O-related latches. Option D is wrong because memory pressure usually shows as `PAGEIOLATCH_EX` (for writes) or `RESOURCE_SEMAPHORE` waits, and while `PAGEIOLATCH_SH` can be exacerbated by insufficient memory, the primary indicator here is an I/O subsystem issue.

Full explanation →
484
MCQeasy

You are planning the migration of a large on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB in size and has a high transaction rate. You need to minimize downtime during the migration. Which migration approach should you recommend?

A.Set up transactional replication to replicate data to SQL Managed Instance.
B.Perform an offline migration using Azure Database Migration Service.
C.Export the database as a BACPAC file and import to SQL Managed Instance.
D.Use Azure Database Migration Service with online migration mode.
AnswerD

Online mode uses log shipping to minimize downtime.

Why this answer

Azure Database Migration Service (DMS) with online migration mode uses transactional log shipping to continuously synchronize changes from the source SQL Server to Azure SQL Managed Instance, enabling near-zero downtime. This is essential for a 500 GB database with a high transaction rate, as it allows the source to remain operational during the migration and only requires a final cutover with minimal interruption.

Exam trap

The trap here is that candidates often confuse 'online migration' with 'replication' (Option A), assuming transactional replication is the only way to achieve minimal downtime, but DMS online mode is the native, integrated service designed specifically for this scenario with simpler management and lower overhead.

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema and data to be initially synchronized, and while it can support minimal downtime, it introduces additional complexity and overhead for a large, high-transaction database, and is not the recommended primary migration tool for SQL Managed Instance. Option B is wrong because offline migration using DMS requires the source database to be taken offline during the data copy, causing significant downtime, which contradicts the requirement to minimize downtime. Option C is wrong because exporting a 500 GB database as a BACPAC file is a slow, offline process that does not support ongoing replication, leading to extended downtime and potential data loss for a high-transaction workload.

Full explanation →
485
Multi-Selecteasy

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

Select 2 answers
A.Set an Microsoft Entra ID administrator for the logical server
B.Create a login in the master database for each Entra ID user
C.Assign a system-assigned managed identity to the logical server
D.Create a contained database user mapped to an Entra ID identity
E.Enable 'contained database authentication' on the server
AnswersA, D

Required to enable Entra ID authentication at the server level.

Why this answer

Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Exam trap

The trap here is that candidates confuse the on-premises SQL Server requirement of enabling 'contained database authentication' with Azure SQL Database, which always has this enabled, and they mistakenly think creating logins in master is necessary for Entra ID users when contained database users are the correct approach.

Full explanation →
486
MCQeasy

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

A.Details of table schemas.
B.List of backup retention policies.
C.List of users and their permissions.
D.Service tier and performance level of the database.
AnswerD

sys.database_service_objectives provides service tier info.

Why this answer

The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Exam trap

The trap here is that candidates may confuse Get-AzSqlDatabase with other cmdlets that return schema, backup, or permission details, leading them to select a plausible but incorrect option based on the cmdlet's name alone.

How to eliminate wrong answers

Option A is wrong because Get-AzSqlDatabase does not return schema details; schema information is obtained via T-SQL queries or tools like Get-AzSqlDatabaseSchema. Option B is wrong because backup retention policies are managed with Get-AzSqlDatabaseBackupLongTermRetentionPolicy or Get-AzSqlDatabaseShortTermRetentionPolicy, not Get-AzSqlDatabase. Option C is wrong because user permissions are listed using T-SQL commands (e.g., sys.database_permissions) or Get-AzSqlDatabaseUser, not the database-level cmdlet.

Full explanation →
487
Matchingmedium

Match each Azure SQL Database performance tuning feature to its description.

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

Concepts
Matches

Provides analysis of query performance and resource consumption

Automatically adjusts indexes and query plans

Proactive diagnostics and performance recommendations

Provides recommendations for index and query optimization

Why these pairings

Azure SQL Database provides several performance tuning features. Query Performance Insight offers query-level analysis, Automatic Tuning automatically applies index and plan changes, Intelligent Insights gives proactive diagnostics, and Database Advisor provides configuration recommendations. Common confusions involve swapping the automatic tuning and insight features.

Full explanation →
488
Matchingmedium

Match each Azure SQL Database monitoring metric to its meaning.

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

Concepts
Matches

Percentage of DTU or CPU used

Percentage of data I/O limit used

Percentage of log write limit used

Number of deadlocks occurring per minute

Why these pairings

These metrics are used to monitor resource usage and performance in Azure SQL Database.

Full explanation →
489
MCQmedium

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

A.Read scale-out.
B.Automatic tuning.
C.Connection pooling.
D.Active geo-replication.
AnswerA

Read scale-out provides a read-only replica for offloading reads.

Why this answer

Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Exam trap

The trap here is that candidates often confuse Active geo-replication with read scale-out, but the key distinction is that read scale-out is a single-database feature within the same region that transparently routes read-only connections, whereas geo-replication requires explicit connection string changes and is designed for cross-region disaster recovery.

How to eliminate wrong answers

Option B is wrong because Automatic tuning is a feature that uses AI to optimize query performance (e.g., index creation, plan forcing), but it does not offload read workloads to a separate replica. Option C is wrong because Connection pooling is a client-side technique that reuses database connections to reduce latency, but it does not redirect read traffic to a secondary database. Option D is wrong because Active geo-replication creates readable secondary replicas in different regions for disaster recovery and geo-distributed reads, but it requires manual connection string changes or application logic to route read traffic, unlike the transparent, built-in read scale-out feature.

Full explanation →
490
MCQhard

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

A.Configure per-database max eDTU for the critical database
B.Increase the eDTU of the elastic pool
C.Move the critical database to a dedicated service tier
D.Configure per-database min eDTU for the critical database
AnswerD

Min eDTU guarantees a minimum amount of resources.

Why this answer

Per-database min eDTU guarantees a minimum level of resources for a specific database within an elastic pool, ensuring that critical databases get the required resources even when the pool is under high utilization. Option A is incorrect because per-database max eDTU only limits the maximum resource usage, not guaranteeing any minimum. Option B is incorrect because increasing the pool's eDTU provides more total resources but does not guarantee that any single database receives a specific amount.

Option C is incorrect because while moving to a dedicated service tier would guarantee resources, it is more expensive and unnecessary if the critical database only needs a minimum resource guarantee within the existing pool.

Full explanation →
491
MCQeasy

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

A.Use Azure Data Factory to run stored procedures for schema changes.
B.Use SQL Server Agent jobs to run deployment scripts on schedule.
C.Use Azure DevOps with a database project and release pipelines.
D.Use Azure Automation with PowerShell scripts to execute T-SQL scripts.
AnswerC

Provides CI/CD, version control, and ability to roll back changes.

Why this answer

Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Azure Automation as valid automation tools for schema changes, overlooking that they lack the version control and rollback capabilities that are explicitly required by the question.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is an ETL and data orchestration service, not a schema deployment tool; it lacks native version control for schema changes and cannot perform rollback of DDL operations. Option B is wrong because SQL Server Agent jobs run on a single SQL Server instance and cannot be centrally managed for multi-region Azure SQL Databases; they also lack version control and rollback support. Option D is wrong because Azure Automation with PowerShell scripts executes ad-hoc or scheduled scripts but does not provide integrated version control, release pipeline gating, or automated rollback mechanisms for schema changes across multiple databases.

Full explanation →
492
Multi-Selectmedium

You need to design a disaster recovery solution for an Azure SQL Database that uses the General Purpose service tier. The solution must have an RTO of 1 hour and an RPO of 15 minutes. Which TWO options can achieve these requirements?

Select 2 answers
A.Point-in-time restore (PITR) using geo-redundant backup storage.
B.Long-term retention (LTR) backups.
C.Auto-failover group with a secondary in a different region.
D.Active geo-replication to a secondary server in a paired region.
E.Zone redundancy on the primary database.
AnswersC, D

Auto-failover groups use active geo-replication and provide automatic failover. The RPO is the same as active geo-replication (seconds to minutes) and failover completes within minutes, meeting both targets.

Why this answer

Options C and D can meet the RTO of 1 hour and RPO of 15 minutes. Active geo-replication (D) provides asynchronous replication to a secondary region with an RPO typically seconds to minutes, and failover can be initiated manually within minutes, meeting both targets. Auto-failover groups (C) build on active geo-replication and add automatic failover, also meeting the requirements.

Option A (PITR with geo-redundant backup storage) cannot reliably meet the 1-hour RTO because restoring from backups can take several hours depending on database size and activity. Option B (LTR) is for archival and not suitable for fast recovery. Option E (zone redundancy) provides high availability within a region, not disaster recovery across regions.

Full explanation →
493
MCQeasy

Refer to the exhibit. The ARM template snippet configures auditing for an Azure SQL Database. Based on the configuration, which events are audited?

A.All data modification statements (DML).
B.Successful and failed database authentication attempts.
C.Only failed authentication attempts.
D.All database schema changes (DDL).
AnswerB

Both successful and failed authentication attempts are included, as per the groups listed.

Why this answer

The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because data modification statements (DML) are not included. Option C is wrong because data modifications are not included.

Option D is wrong because only authentication events are listed.

Full explanation →
494
MCQmedium

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

A.Enable compression on transaction log backups.
B.Create additional nonclustered indexes on frequently updated tables.
C.Increase the service tier to Business Critical.
D.Increase the backup retention period.
AnswerC

Increasing the service tier to Business Critical provides better log write performance through local SSD storage, reducing the log generation rate. This option is correct.

Why this answer

Increasing the service tier to Business Critical does not reduce the transaction log generation rate; it only improves log write throughput. The log generation rate is workload‑dependent and is not changed by moving to a higher tier. None of the provided options achieve the goal of reducing the log generation rate while maintaining durability.

To reduce the rate, you would need to optimize the application (e.g., batching, reducing transactions), which is not offered among the choices.

Exam trap

A common trap is to think that compressing log backups reduces log generation, but it only reduces backup size. The actual solution is to upgrade to a higher tier with better log write performance.

Full explanation →
495
Multi-Selectmedium

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Select 3 answers
A.Automatic failover capability
B.Recovery Point Objective (RPO) of 1 second
C.Number of readable secondary replicas required
D.Support for SQL Server Authentication
E.Granular control over individual database failover
AnswersA, C, E

Auto-failover groups provide automatic failover; active geo-replication does not.

Why this answer

Auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.

Exam trap

The trap here is that candidates often assume a lower RPO is always a deciding factor, but both technologies provide the same RPO, so the key differentiators are automatic failover, the number of readable secondaries, and granular failover control.

Full explanation →
496
MCQmedium

Your company runs a global e-commerce application using Azure SQL Database in the West Europe region. You need to implement a solution that provides automatic failover and allows the secondary region to be used for read-only queries during normal operations. The secondary must be in a different region. Which configuration meets these requirements?

A.Enable read scale-out on the primary database.
B.Create an auto-failover group with a secondary in West US and use the secondary as readable.
C.Configure active geo-replication to a secondary server in West US and implement application-level failover logic.
D.Configure geo-restore with a recovery point objective of 1 hour.
AnswerB

Correct. Auto-failover groups provide automatic failover and support readable secondaries, meeting both requirements.

Why this answer

An auto-failover group with a readable secondary in a different region provides automatic failover and allows read-only queries on the secondary during normal operations. Option C is incorrect because active geo-replication requires application-level failover logic, which is not automatic, and thus does not meet the requirement for automatic failover. Option A is incorrect because read scale-out is limited to the same region.

Option D is incorrect because geo-restore is a backup/restore mechanism, not a continuous replication solution.

Full explanation →
497
Multi-Selecthard

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

Select 2 answers
A.Service level objective (SLO)
B.Number of vCores
C.Backup retention period
D.Number of log files
E.Page compression level
AnswersA, B

Correct. The service level objective (SLO) defines the resource limits, including the maximum log rate.

Why this answer

The maximum log rate in Azure SQL Database Business Critical tier is determined by the service level objective (SLO) which includes a preset log rate limit, and the number of vCores as part of that SLO. The number of log files does not affect the log rate; log write throughput is governed by the storage performance tied to the SLO. Backup retention period and page compression level have no impact on log rate.

Full explanation →
498
MCQhard

Your company uses Azure SQL Managed Instance with transactional replication. You need to automate the monitoring of replication latency and send an alert if latency exceeds 5 minutes. You want to use Azure Monitoring capabilities. What is the most efficient solution?

A.Use Elastic Database Jobs to run a query on the distributor and log results to a table, then set up a logic app to check the table.
B.Monitor the 'Replication: Dist Delivery Latency' performance counter.
C.Create a SQL Agent job on the publisher that checks sys.dm_repl_sync_status and sends an email.
D.Create an Azure Monitor metric alert on the 'Log Send Queue Size' metric with a threshold of 300000 KB (approximately 5 minutes).
AnswerD

This metric directly reflects replication latency and can trigger alerts.

Why this answer

The most efficient solution because Azure Monitor provides built-in metrics for transactional replication on Azure SQL Managed Instance, such as 'Log Send Queue Size'. By creating a metric alert with a threshold of 300000 KB (which corresponds to approximately 5 minutes of latency at typical throughput), you can be notified automatically when latency exceeds this threshold. This approach uses native Azure Monitoring capabilities without requiring custom jobs or scripts.

Options A, B, and C are less efficient: A requires Elastic Database Jobs and a logic app; B uses a performance counter that is not directly exposed in Azure Monitor for SQL Managed Instance and is more complex to set up; C requires creating a SQL Agent job to check a DMV and send email, which is manual and not as scalable as Azure Monitor alerts.

Full explanation →
499
MCQmedium

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

A.Use the Azure portal to revert all automatic tuning recommendations for the past week.
B.Manually create the missing indexes that were dropped by automatic tuning.
C.Disable automatic tuning for the entire database.
D.Manually drop the problematic index using a DROP INDEX command.
AnswerD

You can manually revert a specific index while leaving automatic tuning active for other indexes.

Why this answer

Manually dropping the problematic index allows you to resolve the specific performance issue caused by increased write latency while retaining the benefits of automatic tuning for other indexes. The Automatic Tuning feature in Azure SQL Database can create indexes to improve read performance, but these indexes may introduce overhead on write operations. By issuing a DROP INDEX command, you surgically remove only the offending index without disabling the overall tuning mechanism.

Exam trap

The trap here is that candidates may think disabling automatic tuning entirely or reverting all recommendations is necessary, but the correct approach is to manually drop only the problematic index to preserve the benefits of automatic tuning for other indexes.

How to eliminate wrong answers

Option A is wrong because reverting all automatic tuning recommendations for the past week would undo all index changes, including beneficial ones, and does not target the specific problematic index. Option B is wrong because manually creating missing indexes that were dropped by automatic tuning is irrelevant; the issue is a newly created index causing write latency, not missing indexes. Option C is wrong because disabling automatic tuning for the entire database would stop all future tuning recommendations and lose the benefits of automatic index management for other queries, which is an overreaction to a single problematic index.

Full explanation →
500
MCQmedium

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

A.Rebuild the clustered columnstore index
B.Increase the service tier to get more IOPS
C.Drop and recreate the clustered columnstore index
D.Reorganize the clustered columnstore index
AnswerA

Rebuild reduces fragmentation and improves scan performance.

Why this answer

Rebuilding the clustered columnstore index reduces fragmentation and improves query performance. Rebuilding is the recommended method to defragment a columnstore index. Option B is incorrect because increasing the service tier addresses performance issues related to IOPS but does not resolve fragmentation.

Option C is incorrect because dropping and recreating the index is more disruptive and unnecessary; a rebuild achieves the same result with less overhead. Option D is incorrect because reorganize is not supported for columnstore indexes; only rebuild is.

Full explanation →
501
Multi-Selecthard

You are troubleshooting a transaction log growth issue on an Azure SQL Database. Which THREE conditions can cause the transaction log to grow unexpectedly?

Select 3 answers
A.Replication that has not delivered transactions to the subscriber
B.A memory-optimized table with a large number of rows
C.A long-running transaction that has not been committed
D.A missing index on a large table that causes excessive updates
E.Page compression enabled on tables
AnswersA, C, D

Log space held for replication.

Why this answer

Options A, C, and D are correct. A long-running uncommitted transaction (C) prevents log truncation, causing growth. Replication with undelivered transactions (A) holds log space until delivery completes.

Excessive updates due to a missing index (D) generate many log records, increasing log size. Option B is incorrect because memory-optimized tables use a different logging mechanism (e.g., native redo) that does not inherently cause more log growth than disk-based tables. Option E is incorrect because page compression reduces the size of data pages, which can decrease log record size for changes, not cause growth.

Full explanation →
502
MCQeasy

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

A.Azure Monitor alert with action group
B.Change Data Capture (CDC) with Logic Apps
C.Elastic Database Job with sp_send_dbmail
D.SQL Agent Mail
AnswerA

Azure Monitor can alert on storage metrics and trigger email notifications.

Why this answer

Azure Monitor alerts can monitor metrics like 'storage_percent' for Azure SQL Database and trigger an action group configured to send email notifications. Option B (CDC with Logic Apps) captures data changes, not storage metrics. Option C (Elastic Database Job) is for executing T-SQL scripts across databases, not for alerts.

Option D (SQL Agent Mail) is not available in Azure SQL Database.

Full explanation →
503
MCQmedium

You are deploying Azure SQL Database for a critical application. The compliance policy requires that the database be configured for zone redundancy in the primary region and have automated failover to a secondary region with no data loss. What Azure SQL Database offering and feature should you choose?

A.Basic tier with geo-replication
B.Business Critical tier with zone redundancy and active geo-replication
C.General Purpose tier with geo-replication
D.Hyperscale tier with zone redundancy
AnswerB

Business Critical provides zone redundancy and active geo-replication with zero data loss.

Why this answer

The Business Critical tier is the only Azure SQL Database tier that supports both zone redundancy within a region and active geo-replication with synchronous data replication to a secondary region. Zone redundancy ensures high availability within the primary region by distributing replicas across availability zones, while active geo-replication provides automated failover to a secondary region with no data loss because it uses synchronous commit at the primary and asynchronous replication to the secondary, but with the ability to fail over without data loss if the secondary is fully synchronized. This combination meets the compliance requirement for zero data loss during a regional failover.

Exam trap

The trap here is that candidates often confuse the General Purpose tier's geo-replication (which is asynchronous and can lose data) with the Business Critical tier's active geo-replication (which supports synchronous replication for zero data loss), and they overlook that zone redundancy is only available in Business Critical and Hyperscale tiers, but Hyperscale lacks automated failover geo-replication.

How to eliminate wrong answers

Option A is wrong because the Basic tier does not support zone redundancy or geo-replication; it is designed for development and testing with no high availability or disaster recovery capabilities. Option C is wrong because the General Purpose tier uses asynchronous data replication and does not support zone redundancy; it can have geo-replication but cannot guarantee zero data loss during failover due to asynchronous replication. Option D is wrong because the Hyperscale tier supports zone redundancy but does not support active geo-replication for automated failover to a secondary region; its geo-replication is limited to named replicas with manual failover, not automated failover with no data loss.

Full explanation →
504
MCQmedium

Refer to the exhibit. You have an Azure SQL Managed Instance configured with the failover group properties shown. The primary region experiences an outage. After 30 minutes, the failover group has not failed over. You need to force failover immediately. What should you do?

A.Change the failover policy to Manual and then fail over.
B.Set the secondaryType to Standby.
C.Use the Switch-AzSqlDatabaseFailoverGroup cmdlet with -AllowDataLoss.
D.Wait for the grace period to expire.
AnswerC

Even though automatic failover is waiting for the grace period, you can force failover with data loss.

Why this answer

When the failover group has an automatic failover policy and the primary region experiences an outage, the automatic failover will only occur after the grace period (typically 1 hour) expires. Since only 30 minutes have passed, the failover has not yet triggered automatically. To force an immediate failover, you can use the Switch-AzSqlDatabaseFailoverGroup cmdlet with the -AllowDataLoss parameter, which initiates a forced failover that may result in data loss.

Option A is incorrect because changing the failover policy to Manual does not force a failover; it only changes the policy for future failovers. Option B is incorrect because setting secondaryType to Standby is not relevant to initiating a failover. Option D is incorrect because waiting for the grace period to expire would eventually result in automatic failover, but the requirement is to force failover immediately.

Full explanation →
505
MCQhard

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

A.Enable Microsoft Purview Data Map for the Managed Instance
B.Configure Microsoft Defender XDR for SQL
C.Use Microsoft Intune to manage SQL security policies
D.Enable Microsoft Defender for SQL on the Managed Instance
AnswerD

Defender for SQL provides SQL injection alerts and integrates with Sentinel.

Why this answer

Microsoft Defender for SQL on Azure SQL Managed Instance provides built-in SQL injection detection and alerting, which can be integrated directly with Microsoft Sentinel for automated incident response and investigation. This is the correct solution because it offers native vulnerability assessment and threat detection tailored to SQL databases, meeting the requirement for both detection and Sentinel integration.

Exam trap

The trap here is that candidates may confuse Microsoft Defender XDR (a broader security suite) with Microsoft Defender for SQL (the specific service for SQL threat detection), leading them to select Option B instead of the correct D.

How to eliminate wrong answers

Option A is wrong because Microsoft Purview Data Map is a data governance and cataloging service, not a security detection or alerting tool for SQL injection. Option B is wrong because Microsoft Defender XDR (Extended Detection and Response) is a unified security platform for endpoints, identities, and cloud apps, but it does not directly provide SQL injection detection for Azure SQL Managed Instance; that capability is part of Defender for SQL. Option C is wrong because Microsoft Intune is a mobile device management (MDM) and mobile application management (MAM) service, not a tool for configuring SQL security policies or detecting SQL injection attacks.

Full explanation →
506
MCQhard

Your company is migrating a mission-critical application from SQL Server 2019 on-premises to Azure SQL Managed Instance. The database is 1.5 TB and has 50 databases that need to be migrated. The application uses SQL Server Agent jobs, Service Broker, and cross-database queries. The security requirements mandate that the migration must use Microsoft Entra ID authentication, and all data must be encrypted at rest using customer-managed keys in Azure Key Vault. The migration window is limited to 4 hours of downtime. You need to choose the migration method and configure the target environment. What should you do?

A.Use Azure Database Migration Service offline migration with automatic cutover.
B.Deploy a new Managed Instance, configure TDE with customer-managed keys, and use transactional replication to synchronize data.
C.Use Azure Database Migration Service online migration, configure Managed Instance with customer-managed TDE, and enable Entra ID authentication.
D.Use Azure backup to URL and restore to Managed Instance, then configure TDE with customer-managed keys.
AnswerC

Online migration minimizes downtime, and Managed Instance supports all required features.

Why this answer

Azure Database Migration Service (DMS) online migration supports minimal downtime for large databases (1.5 TB) and 50 databases, while maintaining transactional consistency for cross-database queries and Service Broker. Configuring the Managed Instance with customer-managed TDE (Transparent Data Encryption) using Azure Key Vault meets the encryption-at-rest requirement, and enabling Microsoft Entra ID authentication satisfies the security mandate. The online migration mode allows continuous synchronization during the migration window, with a final cutover within the 4-hour downtime limit.

Exam trap

The trap here is that candidates often assume offline migration (Option A or D) is acceptable for large databases, but the 4-hour downtime window makes online migration (Option C) the only viable choice, and they may overlook that transactional replication (Option B) cannot handle cross-database dependencies and Service Broker messages during migration.

How to eliminate wrong answers

Option A is wrong because offline migration with automatic cutover requires the source database to be taken offline for the entire duration of data copy, which would exceed the 4-hour downtime window for a 1.5 TB database. Option B is wrong because transactional replication does not natively support Service Broker or cross-database queries as a migration method; it is designed for ongoing replication of specific tables, not full database migration with schema and dependencies. Option D is wrong because Azure backup to URL and restore to Managed Instance is an offline method that would require the database to be offline for the entire backup and restore process, exceeding the 4-hour window, and it does not inherently configure TDE with customer-managed keys or Entra ID authentication.

Full explanation →
507
MCQhard

You are configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

A.No changes needed; the configuration already meets the requirement.
B.Add an IP rule to allow the subnet's IP range.
C.Set ignoreMissingVnetServiceEndpoint to true.
D.Change defaultAction to Allow.
AnswerA

Default deny with a VNet rule for the subnet allows only that subnet.

Why this answer

The exhibit shows that the private endpoint is configured with a network ACL that has a deny-all default action and an explicit allow rule for the specific subnet in VNet1. Since private endpoints use network policies (like NSG rules) to filter traffic, and the ACL already denies all traffic except the allowed subnet, no changes are needed. The configuration meets the requirement because the private endpoint's network ACLs are evaluated in order, and the explicit allow for the subnet overrides the default deny for all other traffic.

Exam trap

The trap here is that candidates may think they need to add an IP rule for the subnet's IP range (Option B) or change the default action to allow (Option D), not realizing that private endpoints use virtual network rules that already implicitly allow traffic from the subnet, and the default deny action is correct for restricting all other traffic.

How to eliminate wrong answers

Option B is wrong because adding an IP rule to allow the subnet's IP range is unnecessary; the private endpoint already uses the subnet's virtual network identifier, not a raw IP range, and the ACL already allows the subnet via a virtual network rule. Option C is wrong because 'ignoreMissingVnetServiceEndpoint' is a property for Azure SQL Database firewall rules when using service endpoints, not for private endpoint ACLs; it does not apply here. Option D is wrong because changing 'defaultAction' to 'Allow' would permit all traffic, including traffic from outside the specified subnet, which contradicts the requirement to deny all other traffic.

Full explanation →
508
MCQmedium

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

A.Use TLS 1.2 for all connections to the database.
B.Configure server-level firewall rules to allow only the application VM IP addresses.
C.Enable forced tunneling on the application VMs to route all traffic through the on-premises network.
D.Create a private endpoint for Azure SQL Database in the same virtual network as the application VMs.
AnswerD

Private endpoints provide a private IP address within the VNet, keeping traffic off the public internet.

Why this answer

Creating a private endpoint for Azure SQL Database places the database service on a private IP address within the same virtual network as the application VMs. This ensures that all traffic between the VMs and the database stays entirely within the Microsoft Azure backbone network, never traversing the public internet, while also providing encryption in transit via TLS by default.

Exam trap

The trap here is that candidates often confuse encryption (TLS) with network isolation, assuming that encrypting traffic alone prevents it from traversing the public internet, or they mistakenly believe that IP-based firewall rules create a private network path.

How to eliminate wrong answers

Option A is wrong because using TLS 1.2 only encrypts the connection but does not prevent traffic from traversing the public internet; the database endpoint remains publicly accessible. Option B is wrong because server-level firewall rules restrict access by IP address but still allow traffic over the public internet; they do not provide a private network path. Option C is wrong because forced tunneling routes all VM traffic through an on-premises network, which adds latency and does not keep traffic within Azure; it also does not create a private connection to Azure SQL Database.

Full explanation →
509
Multi-Selecthard

Which THREE features can help protect Azure SQL Database from data exfiltration?

Select 3 answers
A.Microsoft Defender for SQL
B.Always Encrypted
C.Azure SQL Database Ledger
D.Dynamic Data Masking
E.Azure Policy with deny effect for public network access
AnswersA, B, E

Correct: Provides threat detection and alerts for suspicious data extraction activities.

Why this answer

Microsoft Defender for SQL detects anomalous activities that may indicate data exfiltration attempts. Azure Policy with deny effect for public network access blocks external connectivity, preventing unauthorized data transfer. Always Encrypted ensures that sensitive data remains encrypted even when accessed by unauthorized users, so if data is exfiltrated, it is unusable without the encryption keys.

Together, these three features protect Azure SQL Database from data exfiltration.

Exam trap

Candidates often overlook Always Encrypted as a data exfiltration prevention feature because it primarily protects data in use, but it also prevents exfiltration of readable data.

Full explanation →
510
MCQhard

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

A.Enable zone redundancy on the primary and secondary instances.
B.Create a failover group and configure the secondary instance to allow read-only connections.
C.Configure log shipping between the primary and secondary instances.
D.Use active geo-replication instead of a failover group.
AnswerB

Failover groups support readable secondary and minimize data loss.

Why this answer

Failover groups for Azure SQL Managed Instance allow you to configure the secondary instance to allow read-only connections, enabling it to serve read-only workloads. This minimizes data loss by using automatic replication with a Recovery Point Objective (RPO) of less than 1 second. Option A (zone redundancy) provides high availability within a region, not disaster recovery across regions.

Option C (log shipping) is not a built-in feature for managed instances and does not support automatic failover. Option D (active geo-replication) is not supported for managed instances; failover groups are the recommended solution.

Full explanation →
511
MCQmedium

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

A.sys.dm_exec_requests
B.sys.dm_exec_sessions
C.sys.dm_exec_query_stats
D.sys.dm_os_performance_counters
AnswerC

Provides aggregated CPU time for queries over time.

Why this answer

sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Exam trap

The trap here is that candidates confuse sys.dm_exec_requests (current activity) with sys.dm_exec_query_stats (historical aggregated stats), leading them to choose Option A because they think 'requests' implies all recent queries, but it only shows currently running queries.

How to eliminate wrong answers

Option A is wrong because sys.dm_exec_requests shows currently executing requests at the moment of querying, not historical CPU consumption over the last 24 hours. Option B is wrong because sys.dm_exec_sessions provides session-level information such as login time and host details, but does not contain per-query CPU usage statistics. Option D is wrong because sys.dm_os_performance_counters returns operating system performance counter values (e.g., CPU utilization percentage), not the specific queries responsible for high CPU usage.

Full explanation →
512
Multi-Selecthard

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

Select 2 answers
A.Create a user-assigned managed identity for the SQL Database server
B.Set the TDE protector to the Key Vault key via the Azure portal or T-SQL
C.Grant the SQL Database server's managed identity Get, Wrap Key, and Unwrap Key permissions on the Key Vault key
D.Store the encryption key in the SQL Database server's hardware security module (HSM)
E.Ensure the SQL Database server is inside the same virtual network as the Key Vault
AnswersB, C

This configures the server to use the CMK.

Why this answer

Setting the TDE protector to the Key Vault key is the step that actually enables customer-managed key (CMK) encryption for Azure SQL Database. This can be done via the Azure portal, PowerShell, Azure CLI, or T-SQL (using ALTER DATABASE SCOPED CONFIGURATION SET TDE_PROTECTOR). Without this step, the database continues to use a service-managed key.

Exam trap

The trap here is that candidates often confuse the required managed identity type (system-assigned vs. user-assigned) or assume network-level constraints (like VNet integration) are mandatory, when in fact only identity and key permissions are strictly required.

Full explanation →
513
MCQeasy

You are monitoring a critical production Azure SQL Database that is experiencing intermittent query timeouts. The database is configured with the General Purpose service tier. You need to identify the root cause of the timeouts with minimal overhead. What should you review first?

A.Implement automatic tuning to force plan regression fixes.
B.Increase the database service tier to Business Critical.
C.Use sys.dm_exec_query_stats to identify queries with high wait statistics.
D.Enable Query Store and review the Regressed Queries report.
AnswerC

Low-overhead way to find problematic queries and their wait types.

Why this answer

Sys.dm_exec_query_stats provides aggregated query performance data, including wait statistics, with minimal overhead. High wait stats often point to resource contention (e.g., CPU, IO) that can cause timeouts. Option A is incorrect: automatic tuning is for addressing plan regression after it's identified, not for initial troubleshooting.

Option B is incorrect: increasing the service tier is a reactive scaling measure, not a diagnostic step. Option D is incorrect: Query Store must be enabled to use the Regressed Queries report, and it may not be the first step if not already enabled; sys.dm_exec_query_stats is a lightweight dynamic management view available by default.

Full explanation →
514
MCQeasy

You are planning to deploy Azure SQL Database for a new application. The application requires a predictable performance with reserved resources and the ability to scale up during peak season. You want to minimize costs. Which purchasing model should you choose?

A.vCore-based purchasing model with reserved capacity
B.DTU-based purchasing model
C.Hyperscale service tier
D.Serverless compute tier
AnswerA

vCore model allows manual scaling and reserved instances reduce costs.

Why this answer

The vCore-based purchasing model with reserved capacity is correct because it allows you to reserve compute resources for a one- or three-year term, providing a significant discount (up to 40-60%) compared to pay-as-you-go pricing. This model also supports predictable performance with dedicated resources and the ability to scale up (e.g., increase vCores or storage) during peak seasons, while the reserved capacity commitment minimizes costs for the baseline workload.

Exam trap

The trap here is that candidates often confuse the DTU model as the default cost-saving option, but the vCore model with reserved capacity actually provides deeper discounts for predictable workloads, while the DTU model lacks such reservation flexibility.

How to eliminate wrong answers

Option B is wrong because the DTU-based purchasing model bundles compute, storage, and I/O into fixed tiers, offering less granular control over resources and no reserved capacity option, making it less cost-effective for predictable workloads that need to scale. Option C is wrong because the Hyperscale service tier is designed for large databases with high storage and rapid scaling needs, but it uses a pay-as-you-go model without reserved capacity discounts, leading to higher costs for predictable, moderate workloads. Option D is wrong because the Serverless compute tier auto-pauses and scales based on demand, which is ideal for intermittent workloads but not for predictable performance with reserved resources, and it does not offer reserved capacity pricing.

Full explanation →
515
Drag & Dropmedium

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

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

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

Geo-replication requires a secondary server in another region, then configuring the secondary database, initiating replication, and verifying the link. Failover is a separate step.

Full explanation →
516
Multi-Selecthard

You have an Azure SQL Database that stores personally identifiable information (PII). You need to classify and label the sensitive columns using Microsoft Purview. Additionally, you want to automatically mask these columns for a specific application user. Which two actions should you take? (Choose two.)

Select 2 answers
A.Apply Dynamic Data Masking (DDM) rules to mask the classified columns for the application user.
B.Enable Always Encrypted on the classified columns.
C.Configure Microsoft Sentinel to monitor access to classified columns.
D.Use Microsoft Purview Data Map to scan and classify the database.
E.Enable Transparent Data Encryption (TDE) on the database.
AnswersA, D

DDM masks data for unauthorized users.

Why this answer

Dynamic Data Masking (DDM) can be applied to classified columns to obfuscate sensitive data for a specific application user without altering the underlying data. DDM rules are defined at the column level and can be configured to mask data for designated users while allowing full access to others, meeting the requirement to automatically mask PII for the application user.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted or TDE, thinking any encryption feature can mask data for specific users, but DDM is the only option that provides user-specific obfuscation without altering the underlying data or requiring application changes.

Full explanation →
517
MCQmedium

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

A.Microsoft Purview Information Protection
B.Azure Policy with custom policy definition
C.Dynamic Data Masking
D.Azure Automation with PowerShell script to run sp_addsensitivityclassification
AnswerA

Purview can automatically scan and classify sensitive data.

Why this answer

Microsoft Purview Information Protection enables automatic classification and labeling of sensitive data in Azure SQL Database through its data classification capabilities. It can be configured to automatically detect and apply sensitivity labels to new columns based on built-in or custom rules. Option B (Azure Policy) can enforce compliance but does not perform automatic classification of data within the database.

Option C (Dynamic Data Masking) obscures sensitive data but does not classify or label it. Option D (Azure Automation with PowerShell) requires custom scripting and does not provide built-in automatic classification integration for new columns.

Full explanation →
518
MCQmedium

You are implementing row-level security (RLS) in Azure SQL Database to enforce data isolation for multiple tenants. Each tenant has a TenantID. The RLS predicate function uses the SESSION_CONTEXT variable set by the application. You need to ensure that users cannot bypass the RLS by setting arbitrary SESSION_CONTEXT values. What should you do?

A.Create a stored procedure that sets the SESSION_CONTEXT and grant the application only execute permission on that procedure; revoke SET permission from users
B.Use dynamic data masking to hide the TenantID column
C.Encrypt the SESSION_CONTEXT values using Always Encrypted
D.Use a firewall rule to restrict access to the database to only the application IP addresses
AnswerA

This ensures that only the stored procedure can set the SESSION_CONTEXT, preventing users from setting arbitrary values.

Why this answer

By creating a stored procedure that sets the SESSION_CONTEXT and granting the application only EXECUTE permission on that procedure while revoking SET permission from users, you prevent users from arbitrarily setting SESSION_CONTEXT values. This enforces that only the trusted application code can set the context used by the RLS predicate, ensuring tenants cannot impersonate other tenants.

Exam trap

The trap here is that candidates may confuse data protection features like dynamic data masking or Always Encrypted with access control mechanisms, failing to realize that RLS relies on controlling who can set the session context, not on encrypting or masking the data itself.

How to eliminate wrong answers

Option B is wrong because dynamic data masking only obfuscates data at query results; it does not prevent users from setting arbitrary SESSION_CONTEXT values or bypassing RLS. Option C is wrong because Always Encrypt protects data at rest and in transit but does not control who can set SESSION_CONTEXT values; it does not enforce RLS isolation. Option D is wrong because a firewall rule restricts network access but does not prevent an authenticated user from setting arbitrary SESSION_CONTEXT values within the database; it does not address the authorization of SESSION_CONTEXT manipulation.

Full explanation →
519
MCQmedium

Your company has an Azure SQL Database that uses active geo-replication to a secondary region. The primary database is hit by a logical corruption error. You need to restore the database to a point before the corruption occurred with minimal data loss. What should you do?

A.Fail over to the secondary region and then fail back.
B.Use the geo-replicated secondary to recover the data.
C.Seeding the secondary from a backup of the primary.
D.Restore the primary database to a point in time before the corruption using automated backups.
AnswerD

Point-in-time restore can recover to a state before the corruption.

Why this answer

Geo-replication replicates changes that may include the corruption; you cannot revert to a point before corruption without a backup. Option A is wrong because failover would bring the corrupted data to the secondary. Option B is wrong because geo-replication replicates the corruption.

Option C is wrong because point-in-time restore to a time before corruption is the correct approach.

Full explanation →
520
MCQmedium

Your company has a policy that all Azure SQL Databases must have their performance data (DTU/CPU, memory, IO) monitored and analyzed weekly. You need to automate the generation of a weekly report summarizing the top 10 queries by average CPU time. What should you use?

A.Configure a Power BI dashboard that connects directly to Azure SQL Database Query Store.
B.Create an Azure Monitor Workbook that queries the Query Store.
C.Set up a SQL Agent job to run queries against sys.dm_exec_query_stats and email the results.
D.Use the Query Performance Insight blade in the Azure portal and schedule an export to a storage account.
AnswerA

Correct. Power BI dashboards can connect to Query Store, and scheduled email subscriptions can automate report distribution.

Why this answer

SQL Agent is not available in Azure SQL Database, making Option C incorrect. Option A is correct because Power BI can connect directly to Azure SQL Database's Query Store, and with a Power BI Pro license, you can schedule email subscriptions of the report, automating the weekly generation and distribution of the top 10 queries report.

Full explanation →
521
MCQhard

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

A.Increase max degree of parallelism
B.Increase the log rate limit by scaling up the service level objective
C.Reduce backup retention period
D.Add more compute replicas
AnswerB

Higher SLOs provide higher log rate limits.

Why this answer

Increasing the log rate limit by scaling up the service level objective (SLO) allows more transactions per second, reducing log rate throttling. Option B is correct. Option A is wrong because increasing max degree of parallelism does not directly affect log rate; it may even increase log generation.

Option C is wrong because backup retention period does not influence log rate. Option D is wrong because adding compute replicas does not increase the log rate limit; log rate is tied to the primary replica's SLO.

Full explanation →
522
MCQeasy

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

A.Add 'hostNameInCertificate=*.database.windows.net'
B.Add 'encrypt=false' and 'trustServerCertificate=true'
C.Add 'encrypt=true' and 'trustServerCertificate=false'
D.Add 'useEncryption=true' and 'validateServerCertificate=true'
AnswerC

This forces encryption and validates the server certificate.

Why this answer

For JDBC connections to Azure SQL Database, setting 'encrypt=true' enforces TLS encryption for data in transit, and 'trustServerCertificate=false' ensures that the server's TLS certificate is validated against the trusted certificate authority (CA) chain, preventing man-in-the-middle attacks. This is the recommended configuration for secure connections to Azure SQL Database.

Exam trap

The trap here is that candidates often confuse the JDBC properties with those of other drivers (like ODBC or .NET SqlClient), where 'TrustServerCertificate' or 'Encrypt' may have different default behaviors, leading them to select Option B or D incorrectly.

How to eliminate wrong answers

Option A is wrong because 'hostNameInCertificate=*.database.windows.net' is used to specify the expected hostname in the server's certificate when the server name in the connection string does not match the certificate's subject, but it does not enable or enforce encryption itself. Option B is wrong because 'encrypt=false' disables encryption, and 'trustServerCertificate=true' bypasses certificate validation, which together create an insecure connection vulnerable to eavesdropping. Option D is wrong because 'useEncryption=true' and 'validateServerCertificate=true' are not valid JDBC connection properties; the correct JDBC properties are 'encrypt' and 'trustServerCertificate'.

Full explanation →
523
MCQhard

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts a critical OLTP workload. The primary instance is deployed in West Europe. You need to ensure that failover to a secondary region occurs automatically when the primary becomes unavailable due to a regional outage, and that the secondary database is readable during normal operations. You also want to minimize storage costs. What should you configure?

A.Deploy a secondary instance using a Bicep template with geo-zone-redundant storage.
B.Implement auto-failover groups with a readable secondary in another region.
C.Configure active geo-replication between two managed instances.
D.Create a failover group between the primary and a secondary managed instance in a paired region.
AnswerD

Failover groups for SQL Managed Instance provide automatic failover and a readable secondary replica, meeting all requirements.

Why this answer

Failover groups for Azure SQL Managed Instance provide automatic failover to a secondary instance in a paired region, and the secondary is readable for read-only workloads, meeting the requirements. Option B is incorrect because 'auto-failover groups' is a term used for Azure SQL Database, not SQL Managed Instance; SQL MI uses failover groups, which also provide automatic failover. Option A is incorrect because geo-zone-redundant storage is a storage redundancy option, not a disaster recovery configuration for SQL MI.

Option C is incorrect because active geo-replication is available for Azure SQL Database, not for SQL Managed Instance.

Full explanation →
524
MCQeasy

You need to automate the execution of a T-SQL script against an Azure SQL Database every hour. Which Azure service should you use?

A.Azure Data Factory
B.Elastic Database Jobs
C.Azure Logic Apps
D.Azure Automation
AnswerD

Azure Automation with a runbook can schedule and execute T-SQL scripts.

Why this answer

Azure Automation with a runbook can run PowerShell or Python scripts that execute T-SQL against Azure SQL Database on a schedule. Option A is wrong because Azure Data Factory is focused on data movement and transformation, not direct T-SQL execution. Option B is wrong because Elastic Database Jobs require a job agent and are designed for management tasks across multiple databases, not simple hourly script execution.

Option C is wrong because Azure Logic Apps is geared towards integration workflows, and while it can execute SQL, it is less efficient than Azure Automation for this straightforward scheduling task.

Full explanation →
525
MCQmedium

You are migrating an on-premises SQL Server database to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. Which step is required to enable TDE in the target Managed Instance?

A.Export the certificate to a PFX file, import it to Azure Key Vault, and configure TDE with a key vault key
B.Create a self-signed certificate in the Managed Instance and restore the database with KEEP_TDE
C.Use the same certificate file by uploading it to the Managed Instance via SQL Server Management Studio
D.Disable TDE on the source database before migration, then enable it after migration
AnswerA

Managed Instance supports customer-managed keys in Azure Key Vault.

Why this answer

Azure SQL Managed Instance does not support importing user-provided certificates for TDE. Instead, it relies on Azure Key Vault as the external key store. Therefore, you must export the on-premises TDE certificate to a PFX file, import it into Azure Key Vault, and then configure the Managed Instance to use that key vault key for TDE.

This ensures the database encryption key can be unwrapped and the database remains encrypted during and after migration.

Exam trap

The trap here is that candidates assume on-premises TDE certificates can be directly reused in Azure SQL Managed Instance, but Azure requires all TDE keys to be managed through Azure Key Vault, not via certificate files or self-signed certificates.

How to eliminate wrong answers

Option B is wrong because Managed Instance does not support the KEEP_TDE option; TDE must be configured using Azure Key Vault, not a self-signed certificate. Option C is wrong because you cannot upload a certificate file directly to a Managed Instance via SSMS; TDE in Azure SQL Managed Instance is managed exclusively through Azure Key Vault. Option D is wrong because disabling TDE on the source database would require decrypting the entire database, which is unnecessary and introduces significant downtime and risk; the correct approach is to migrate the TDE-protected database and re-enable TDE using a key vault key.

Full explanation →
Page 6

Page 7 of 13

Page 8

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 →