Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 76–150

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

Take a mock examExam hub
Page 1

Page 2 of 13

Page 3
76
MCQhard

You are the database administrator for a global e-commerce company that runs its critical order processing system on Azure SQL Database. The database is currently deployed as a single database in the West US region using the Business Critical service tier (4 vCores, 320 GB storage). The application requires a Recovery Point Objective (RPO) of no more than 5 seconds and a Recovery Time Objective (RTO) of no more than 30 seconds in the event of a regional outage. The secondary region must be in East Asia. The solution must also handle planned failovers for maintenance without data loss. You need to configure high availability and disaster recovery to meet these requirements with the lowest possible latency for writes. What should you do?

A.Configure geo-restore of the database to the East Asia region. Enable auto-failover groups with manual failover.
B.Deploy the database as a zone-redundant configuration within West US. Use Azure Traffic Manager to redirect traffic to a secondary database in East Asia.
C.Configure auto-failover groups with read-scale failover. Deploy a readable secondary in East Asia using the Business Critical tier. Set the failover policy to automatic.
D.Create a failover group with an active geo-replication secondary in East Asia. Set the failover policy to automatic with a grace period of 1 hour. Ensure the secondary is configured for synchronous commit.
AnswerC

Correct because auto-failover groups with readable secondary in East Asia meet RPO and RTO with minimal write latency.

Why this answer

Auto-failover groups with a readable secondary in East Asia using Business Critical tier provide automatic failover meeting RTO of 30 seconds, and asynchronous geo-replication typically achieves RPO of 5 seconds for Azure SQL Database. The readable secondary can also handle read-only workloads. This configuration offers the lowest write latency since writes are acknowledged locally without waiting for cross-region synchronization.

Option A is incorrect because geo-restore has RPO of 1 hour and RTO of several hours. Option B is incorrect because zone redundancy is intra-region and Traffic Manager does not ensure RPO/RTO. Option D is incorrect because active geo-replication is asynchronous, not synchronous, and synchronous cross-region replication is not supported; it would increase write latency.

Exam trap

Candidates may think synchronous replication is needed for low RPO, but Azure SQL Database does not support synchronous geo-replication. Asynchronous replication with auto-failover groups can achieve RPO of 5 seconds.

Full explanation →
77
MCQhard

You manage an Azure SQL Database that uses automatic tuning. The database has the FORCE_LAST_GOOD_PLAN option enabled. A critical query suddenly starts performing poorly after a plan change. What is the expected behavior?

A.The query will continue with the poor plan until you manually force a plan.
B.You will receive a recommendation to revert the plan change.
C.The database will automatically revert to the last good plan and log the event in sys.dm_db_tuning_recommendations.
D.The database will automatically revert to the last good plan without notification.
AnswerC

Auto-revert occurs and is logged in the tuning recommendations DMV.

Why this answer

When FORCE_LAST_GOOD_PLAN is enabled and automatic tuning detects a plan regression, Azure SQL Database automatically reverts to the last known good plan. The event is logged in sys.dm_db_tuning_recommendations for audit and analysis. This behavior is part of the automatic plan correction feature, which does not require manual intervention.

Exam trap

The trap here is that candidates may confuse FORCE_LAST_GOOD_PLAN with the default automatic tuning recommendation mode, which only suggests changes without automatic reversion, leading them to pick Option B or D.

How to eliminate wrong answers

Option A is wrong because FORCE_LAST_GOOD_PLAN enables automatic plan regression correction, so the poor plan does not persist until manual action. Option B is wrong because the database does not just send a recommendation; it automatically reverts the plan when FORCE_LAST_GOOD_PLAN is enabled. Option D is wrong because the database does log the event in sys.dm_db_tuning_recommendations, providing notification through that system view.

Full explanation →
78
MCQeasy

You have Azure SQL Database elastic pools in the primary region. You need to configure disaster recovery using a failover group. The secondary pool must be in a different region. Which PowerShell cmdlet should you use to create the secondary pool?

A.Set-AzSqlDatabaseSecondary
B.Add-AzSqlDatabaseToFailoverGroup
C.New-AzSqlDatabaseSecondary
D.New-AzSqlDatabaseFailoverGroup
AnswerC

Correct: It creates a secondary database in a specified elastic pool.

Why this answer

The correct cmdlet is New-AzSqlDatabaseSecondary (Option C). When the primary database is in an elastic pool, using the -SecondaryElasticPoolName parameter creates a new elastic pool on the secondary server in a different region if it does not already exist. This makes it suitable for creating the secondary pool as part of failover group setup.

Option A (Set-AzSqlDatabaseSecondary) modifies an existing geo-replication relationship, not create a new one. Option B (Add-AzSqlDatabaseToFailoverGroup) adds an existing database to a failover group, but the secondary pool must already be provisioned. Option D (New-AzSqlDatabaseFailoverGroup) creates the failover group itself, not the secondary elastic pool.

Full explanation →
79
MCQmedium

You are a database administrator for a large financial services company. The company is migrating its on-premises SQL Server databases to Azure SQL Managed Instance. One of the databases, FinanceDB, is 2.5 TB in size and runs a critical application that requires high availability with automatic failover. The application uses database mail and SQL Agent jobs. During migration, you need to minimize downtime. Which deployment option should you choose?

A.Deploy an Azure SQL Database Hyperscale with geo-replication
B.Deploy an Azure SQL Managed Instance with zone-redundant configuration
C.Deploy a single Azure SQL Database with Active Geo-Replication
D.Deploy an Azure SQL Managed Instance in a failover group with a secondary in another region
AnswerD

Failover group provides automatic failover, supports all required features, and allows minimal downtime migration.

Why this answer

Azure SQL Managed Instance supports database mail and SQL Agent jobs natively, and deploying it in a failover group with a secondary in another region provides automatic failover and high availability while minimizing downtime during migration. The 2.5 TB size is within Managed Instance limits, and the failover group enables a controlled, continuous-sync replication to the secondary, allowing a swift cutover with minimal data loss.

Exam trap

The trap here is that candidates may confuse Azure SQL Database with Azure SQL Managed Instance, overlooking that database mail and SQL Agent jobs are only supported in Managed Instance, not in single databases or Hyperscale.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database Hyperscale does not support database mail or SQL Agent jobs, which are required by the application. Option B is wrong because zone-redundant configuration provides high availability within a single region but does not offer cross-region failover or minimize downtime during migration, as it lacks a secondary in another region for geo-replication. Option C is wrong because a single Azure SQL Database with Active Geo-Replication does not support database mail or SQL Agent jobs, and it is not a Managed Instance, which is needed for full compatibility with the on-premises SQL Server features.

Full explanation →
80
Drag & Dropmedium

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task 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

Connect to the instance, create a job, define steps, schedule, then enable and start.

Full explanation →
81
Multi-Selecteasy

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

Select 2 answers
A.Azure Data Studio with SQLCMD mode
B.GitHub Actions with the Azure SQL Database deployment action
C.Azure DevOps release pipeline with SQL Server database project (DACPAC) deployment task
D.SQL Server Import and Export Wizard
E.SQL Server Management Studio (SSMS)
AnswersB, C

GitHub Actions marketplace offers actions to deploy DACPAC or run SQL scripts.

Why this answer

GitHub Actions with the Azure SQL Database deployment action (Option B) and Azure DevOps release pipeline with the SQL Server database project (DACPAC) deployment task (Option C) are both valid tools for automating schema deployments to Azure SQL Database in a CI/CD pipeline. Option A (Azure Data Studio SQLCMD mode) is manual and not designed for CI/CD integration. Option D (SQL Server Import and Export Wizard) is a data migration tool, not for schema changes.

Option E (SSMS) is a management tool without native CI/CD automation.

Full explanation →
82
MCQhard

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

A.Use Azure CLI or PowerShell to invoke planned failover
B.Create an Azure Automation runbook that uses REST API
C.Schedule an Elastic Database Job to execute a failover script
D.Configure Azure Traffic Manager to route traffic to secondary
AnswerA

Failover groups support automated planned failover via CLI/PowerShell.

Why this answer

Azure SQL Database failover groups support planned failover via Azure CLI or PowerShell, which can be automated. Option B is wrong because Azure Automation runbooks can also be used, but the CLI is more direct. Option C is wrong because Elastic Jobs are not for failover.

Option D is wrong because Azure Traffic Manager is for DNS-level traffic routing, not database failover.

Full explanation →
83
MCQhard

Your company uses Azure SQL Database with the Hyperscale service tier. You notice that index maintenance operations are taking longer than expected. What is the most likely reason for this performance issue?

A.The database is using the General Purpose tier
B.The index is using page compression
C.The log write throughput is insufficient
D.The transaction log is set to simple recovery
AnswerC

Hyperscale index rebuilds generate high log throughput; insufficient log throughput can cause delays.

Why this answer

In Azure SQL Database Hyperscale, index rebuild operations generate a large volume of log records. Although Hyperscale uses a distributed architecture with fast log commit, the log write throughput per service objective is finite and can become a bottleneck during intensive write operations like index rebuilds. This is the most common cause of slow index maintenance.

Option A (General Purpose tier) is irrelevant as the question specifies Hyperscale. Option B (page compression) does not significantly impact index rebuild duration. Option D (simple recovery) is not the cause; even with simple recovery, the log write throughput during rebuild can be insufficient.

Full explanation →
84
Multi-Selecteasy

Which TWO configurations are required to enable automatic failover for an Azure SQL Database configured with active geo-replication?

Select 2 answers
A.Enable read-scale out on the secondary database.
B.Configure a failover group that includes the primary and secondary databases.
C.Set the secondary database to have the same service tier as the primary.
D.Configure zone redundancy on the primary database.
E.Ensure the secondary server is in a different Azure region than the primary.
AnswersB, E

Failover group is required to orchestrate automatic failover.

Why this answer

A failover group is the Azure feature that manages automatic failover for geo-replicated databases. It coordinates the failover of the primary and all secondaries in the group, providing a single endpoint and automatic failover policy. Without a failover group, active geo-replication only supports manual failover.

Exam trap

The trap here is that candidates often confuse the prerequisites for automatic failover with the features of active geo-replication alone, forgetting that a failover group is the specific construct that enables automation.

Full explanation →
85
Multi-Selecteasy

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Select 2 answers
A.Transparent Data Encryption (TDE)
B.Dynamic Data Masking
C.Always Encrypted
D.Row-level security
E.Column-level security
AnswersC, E

Encrypts specific columns and keeps data encrypted from DB engine.

Why this answer

Always Encrypted is correct because it encrypts sensitive columns like credit card numbers at the client-side, ensuring the data remains encrypted at rest, in transit, and during processing. This prevents unauthorized users, including database administrators, from viewing the plaintext data, while authorized applications with the column encryption key can decrypt it transparently. Column-level security is also correct because it allows administrators to restrict access to specific columns (e.g., credit card numbers) by granting permissions only to authorized users or applications.

This ensures that even if a user has access to the database, they cannot query the sensitive columns without explicit permission. Together, Always Encrypted provides encryption and Column-level security provides fine-grained access control, meeting all requirements.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with encryption, assuming obfuscation provides the same security as Always Encrypted, but masking does not protect data from privileged users or direct database access.

Full explanation →
86
MCQmedium

You have an Azure SQL Managed Instance. You need to automate the execution of a stored procedure every hour to clean up historical data. What is the most appropriate solution?

A.SQL Agent Job
B.Azure Logic Apps with SQL connector
C.Elastic Database Job
D.Azure Automation runbook with T-SQL
AnswerA

SQL Agent Jobs are native to SQL Managed Instance and ideal for scheduled tasks.

Why this answer

SQL Agent Jobs are a built-in feature of Azure SQL Managed Instance, providing native scheduling for T-SQL jobs like executing a stored procedure every hour. Option B is less appropriate because Azure Logic Apps is an external service that adds complexity and latency. Option C is designed for executing tasks across multiple databases, not a single instance.

Option D is external and less integrated compared to the native SQL Agent Job.

Full explanation →
87
MCQmedium

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

A.Use Azure Data Studio to import the certificate directly from the local machine store during migration.
B.Disable TDE on the source database, migrate the backup, then enable TDE on the target.
C.Back up the certificate and private key to a .pfx file, restore the .pfx to the target managed instance, then restore the database backup.
D.Create a master key in the target managed instance and then restore the database; the certificate will be imported automatically.
AnswerC

This ensures the TDE keys are available to decrypt the database after restore.

Why this answer

TDE in SQL Server relies on a certificate (or asymmetric key) that must be present in the target instance to decrypt the database backup. By backing up the certificate and private key to a .pfx file and restoring it to Azure SQL Managed Instance, you ensure the target has the necessary encryption keys to read the backup. Azure SQL Managed Instance supports restoring TDE-protected backups only if the corresponding certificate is first restored into the master database.

Exam trap

The trap here is that candidates assume TDE certificates are automatically transferred or that disabling TDE is a safe shortcut, but in reality, the certificate must be explicitly backed up and restored to the target before the database restore can succeed.

How to eliminate wrong answers

Option A is wrong because Azure Data Studio cannot import a certificate directly from the local machine store during migration; TDE certificates must be manually backed up and restored to the target instance. Option B is wrong because disabling TDE on the source database would decrypt all data, which is unnecessary and risks exposing sensitive data during migration; TDE should remain enabled to maintain encryption at rest. Option D is wrong because creating a master key in the target does not automatically import the certificate; the certificate must be explicitly backed up from the source and restored to the target before the database restore.

Full explanation →
88
MCQmedium

Your company uses Azure SQL Database for a customer-facing application. You need to ensure that only the application can access the database, and that access is restricted to specific IP ranges. The application runs on Azure App Service with a dynamic outbound IP address. What should you do?

A.Configure a VNet integration for the App Service and set the SQL firewall to allow the VNet subnet.
B.Migrate the database to Azure SQL Managed Instance and use a service endpoint.
C.Create a private endpoint for the SQL Database and connect App Service to it via VNet peering.
D.Add a firewall rule for the App Service's outbound IP address range.
AnswerD

This allows App Service to connect even with dynamic IPs, as the range is known.

Why this answer

The application runs on Azure App Service with a dynamic outbound IP address, and the requirement is to restrict access to specific IP ranges. By adding a firewall rule for the App Service's outbound IP address range (which can be found in the App Service properties or via the `WEBSITE_OUTBOUND_IP_ADDRESSES` app setting), you allow the application to connect to Azure SQL Database while blocking other traffic. This approach directly addresses the dynamic IP challenge without requiring VNet integration or private endpoints.

Exam trap

The trap here is that candidates often assume VNet integration or private endpoints are always required for secure access, but the question explicitly states the application uses a dynamic outbound IP address, making a firewall rule for the IP range the most straightforward solution that meets the requirement of restricting access to specific IP ranges.

How to eliminate wrong answers

Option A is wrong because VNet integration for App Service does not automatically allow SQL firewall access; you would still need to configure a VNet service endpoint or private endpoint for SQL Database, and the question specifically requires IP-based restriction, not VNet-based. Option B is wrong because migrating to Azure SQL Managed Instance is unnecessary and does not solve the dynamic IP issue; service endpoints still require VNet integration and do not directly address IP-based firewall rules. Option C is wrong because a private endpoint for SQL Database requires the App Service to be in the same VNet or connected via VNet peering, but App Service with dynamic outbound IPs cannot directly use a private endpoint without VNet integration, and the question explicitly states the application uses dynamic outbound IPs, not a static VNet.

Full explanation →
89
MCQhard

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

A.Increase the number of secondary replicas to distribute read workload.
B.Enable accelerated database recovery (ADR) to reduce log IO.
C.Change the service tier to Business Critical to get faster IO.
D.Increase the MAXDOP setting for the database.
AnswerA

Correct. Adding secondary replicas in Hyperscale allows read workloads to be offloaded. Each replica has its own buffer pool, so pages can be served from cache instead of repeatedly requesting them from the page server, reducing IOPS utilization.

Why this answer

In Azure SQL Database Hyperscale, increasing the number of secondary replicas can help distribute read workloads. Secondary replicas have their own buffer pool caches, which can reduce page server IOPS utilization by serving read requests from cache rather than repeatedly fetching pages from the page server. Option B is incorrect because Accelerated Database Recovery (ADR) reduces undo log space requirements but does not affect page server IOPS.

Option C is incorrect because changing to Business Critical tier moves away from the Hyperscale architecture and does not directly address page server performance. Option D is incorrect because increasing MAXDOP may increase parallelism and potentially lead to higher IOPS, not reduce it.

Full explanation →
90
MCQmedium

You have an Azure SQL Database that uses a managed identity to access Azure Key Vault for storing column master keys. You need to automate the rotation of the column master key using Azure Automation. Which steps should you include in the runbook?

A.Use PowerShell cmdlets without authentication; the runbook runs under the Automation account's identity.
B.Use the SQL Server Management Objects (SMO) to rotate the key directly from the runbook without Key Vault.
C.Authenticate using Connect-AzAccount -Identity, then use Set-AzKeyVaultKey to create a new key version, and update the column encryption key using Invoke-SqlCmd.
D.Use the Key Vault REST API with a client secret stored in the runbook.
AnswerC

Managed identity authentication is secure and automated.

Why this answer

The runbook must authenticate to Key Vault using the managed identity, create a new key, update the column encryption key, and then clean up. Options A and B miss authentication steps. Option D incorrectly uses connection strings.

Full explanation →
91
MCQmedium

Your company uses Azure SQL Database. You need to ensure that all connections to the database use TLS 1.2 or higher. Currently, some client applications are connecting using TLS 1.0. What should you do?

A.Configure the server firewall to block non-TLS traffic.
B.Set the 'Minimum TLS version' property of the logical server to 1.2.
C.Set the 'tls_version' database parameter to 1.2 in the master database.
D.Update the client applications to only use TLS 1.2.
AnswerB

Correct: This enforces TLS 1.2 for all connections.

Why this answer

Setting the 'Minimum TLS version' property of the Azure SQL logical server to 1.2 enforces that all incoming connections must use TLS 1.2 or higher. This server-level setting overrides any client-side configuration, blocking connections that attempt to use TLS 1.0 or 1.1. It is the simplest and most effective way to enforce the minimum TLS version across all client applications without requiring changes to each client.

Exam trap

The trap here is that candidates may think updating client applications (Option D) is sufficient, but the exam tests the understanding that server-side enforcement is required to guarantee compliance across all clients, especially when you cannot control or update every client application.

How to eliminate wrong answers

Option A is wrong because the server firewall controls IP-based access, not TLS protocol version enforcement; blocking non-TLS traffic does not prevent clients from connecting with TLS 1.0. Option C is wrong because Azure SQL Database does not expose a 'tls_version' database parameter in the master database; TLS version is controlled at the logical server level, not through database-scoped configuration. Option D is wrong because while updating client applications to use TLS 1.2 is a valid approach, it is not a server-side enforcement mechanism and does not guarantee that all clients will comply; the question asks what you should do to ensure all connections use TLS 1.2 or higher, which requires server-side enforcement.

Full explanation →
92
MCQhard

You are the database administrator for a healthcare company that uses Azure SQL Database to store patient records. The database is named PatientDB. The security team mandates that all database access must be audited, and any suspicious activity must be alerted in real-time. Additionally, compliance requires that all data at rest be encrypted using a customer-managed key stored in Azure Key Vault. You have configured the following: - TDE with customer-managed key in AKV (key vault name: KV-Health, key name: PatientKey) - Azure SQL Auditing enabled, writing logs to a storage account (StorageAcctLogs) - Advanced Threat Protection (ATP) enabled with alerts sent to the security team's email - Firewall rules allowing only the application server's public IP (203.0.113.50) A week later, the security team reports that they received an ATP alert about a potential SQL injection attack from IP 198.51.100.25. However, when they check the audit logs, they find no entries from that IP. They also notice that the database remains accessible. The security team wants to know why the audit logs do not contain the suspicious IP even though ATP detected it. What is the most likely reason?

A.The storage account for audit logs is not accessible, so logs are not being written.
B.The firewall rule allowing only the application server's IP is misconfigured, allowing the attacker's IP.
C.The firewall rule blocks connections from the attacker's IP, so no audit log entries are generated because the connection never reaches the database.
D.The TDE configuration with customer-managed key is interfering with auditing.
AnswerC

ATP can detect blocked attempts via network telemetry, but audit logs only record successful connections.

Why this answer

Advanced Threat Protection (ATP) can detect suspicious activity at the network level before the connection is fully established, while Azure SQL Auditing only logs events after a connection is accepted and authenticated. Since the firewall rule blocks connections from IP 198.51.100.25, the attacker's IP never reaches the database engine, so no audit log entries are generated for that IP. ATP alerts can be triggered by network-level patterns (e.g., SQL injection signatures) even when the connection is denied by the firewall, explaining the discrepancy.

Exam trap

The trap here is that candidates assume ATP and auditing share the same logging scope, but ATP can alert on blocked connections at the network layer, while auditing only logs successful or failed authentication attempts after the firewall allows the connection.

How to eliminate wrong answers

Option A is wrong because if the storage account were inaccessible, audit logs would fail to write, but the security team would likely see errors or missing logs for all IPs, not just the attacker's IP; the question states logs exist for other IPs (e.g., the application server). Option B is wrong because the firewall rule is correctly configured to allow only 203.0.113.50, and the attacker's IP 198.51.100.25 is explicitly blocked; the database remains accessible only to the allowed IP, not to the attacker. Option D is wrong because TDE with customer-managed key in Azure Key Vault encrypts data at rest and does not interfere with auditing or network-level logging; auditing operates independently of TDE.

Full explanation →
93
MCQhard

You manage an Azure SQL Managed Instance with a large database. You notice that the automatic tuning recommendations are not being applied. You need to ensure that automatic tuning is enabled for the instance. Which PowerShell cmdlet should you run?

A.Set-AzSqlInstanceDatabase with -AutoTuningProperties
B.Set-AzSqlDatabase with -AutoTuningProperties
C.Set-AzSqlInstance with -DtcEnabled
D.Set-AzSqlServer with -AdministratorLogin
AnswerA

This is the correct cmdlet and parameter to enable auto-tuning on a Managed Instance database.

Why this answer

The correct cmdlet is Set-AzSqlInstanceDatabase because Azure SQL Managed Instance uses instance-level databases, and automatic tuning properties are configured at the database level within the instance. The -AutoTuningProperties parameter directly enables or configures automatic tuning recommendations for the specified database in the managed instance.

Exam trap

The trap here is that candidates confuse Azure SQL Database cmdlets (Set-AzSqlDatabase) with Azure SQL Managed Instance cmdlets (Set-AzSqlInstanceDatabase), leading them to select the wrong cmdlet for the managed instance context.

How to eliminate wrong answers

Option B is wrong because Set-AzSqlDatabase is used for Azure SQL Database (single database or elastic pool), not for Azure SQL Managed Instance databases. Option C is wrong because Set-AzSqlInstance with -DtcEnabled configures the Distributed Transaction Coordinator (DTC) setting for the managed instance, not automatic tuning. Option D is wrong because Set-AzSqlServer with -AdministratorLogin changes the administrator login for the logical server, which is irrelevant to automatic tuning on a managed instance.

Full explanation →
94
MCQeasy

You need to automate the deployment of a new Azure SQL Database with a specific performance tier, collation, and firewall rules using infrastructure as code. Which technology should you use?

A.Azure CLI script with az sql db create command.
B.SQL Server Management Studio (SSMS) with a deployment script.
C.PowerShell script with New-AzSqlDatabase cmdlet.
D.ARM template with Microsoft.Sql/servers/databases resource definition.
AnswerD

ARM templates are declarative and the standard for infrastructure as code.

Why this answer

Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure, allowing declarative deployment of Azure SQL Database with specific settings like performance tier, collation, and firewall rules. Option A (Azure CLI) and Option C (PowerShell) are procedural scripts that can be used but do not provide true declarative IaC. Option B (SSMS) is a GUI tool, not suitable for automation.

Full explanation →
95
MCQhard

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

A.Azure Purview and vulnerability assessment
B.Microsoft Sentinel and SQL Auditing
C.Azure Defender for SQL and SQL Auditing
D.SQL Server auditing and vulnerability assessment
AnswerC

Defender provides anomaly detection and auditing logs queries.

Why this answer

Azure Defender for SQL provides anomaly detection and alerts for suspicious access patterns (e.g., SQL injection, brute force), while SQL Auditing captures all queries and events for compliance logging. Together, they meet the requirements for automatic detection and full query auditing without additional services.

Exam trap

The trap here is that candidates confuse Azure Defender for SQL with vulnerability assessment or Microsoft Sentinel, assuming a SIEM is required for detection, when Azure Defender for SQL already provides built-in anomaly detection for Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because Azure Purview is a data governance and catalog service, not a security monitoring tool, and vulnerability assessment alone does not provide real-time anomaly detection or query logging. Option B is wrong because Microsoft Sentinel is a SIEM that ingests logs but does not natively perform database-level anomaly detection or replace SQL Auditing for query logging; it would require additional configuration and cost. Option D is wrong because SQL Server auditing (on-premises style) is not directly available in Azure SQL Database—Azure SQL uses SQL Auditing—and vulnerability assessment does not detect anomalous access patterns or provide alerts.

Full explanation →
96
MCQhard

You have a SQL Server on Azure VM that is part of a failover cluster instance (FCI). The cluster nodes are in an availability set. You need to ensure that the database can tolerate a complete Azure region failure. What should you add to the current architecture?

A.Add another node to the existing FCI in a different availability zone.
B.Deploy a second FCI in a different Azure region and configure SQL Server log shipping or availability group between the FCIs.
C.Configure geo-redundant backup storage for the SQL Server VMs.
D.Create an Azure SQL Database with active geo-replication and migrate the database.
AnswerB

Cross-region replication provides DR.

Why this answer

Deploying a second FCI in a different Azure region and configuring SQL Server log shipping or an availability group between the FCIs provides disaster recovery across regions. Option A is incorrect because adding a node in a different availability zone within the same region does not protect against a complete region failure. Option C is incorrect because geo-redundant backup storage only protects backups, not provide continuous replication for the database.

Option D is incorrect because active geo-replication is a feature of Azure SQL Database, not SQL Server on Azure VM.

Full explanation →
97
MCQhard

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a regional outage, you fail over to the secondary region. When the primary region recovers, you need to fail back with zero data loss. What should you do?

A.Delete the secondary database and recreate it from the primary
B.Add a new secondary in the original primary region and then fail over
C.Fail over the original primary to become the primary again
D.Perform a planned failover from the current primary to the original primary
AnswerD

Planned failover synchronizes data and ensures zero data loss.

Why this answer

A planned failover in Azure SQL Database with Active Geo-Replication ensures zero data loss by synchronizing all committed transactions from the current primary (secondary region) to the original primary before switching roles. This process uses synchronous replication during the final phase, guaranteeing that no transactions are lost when failing back to the recovered primary region.

Exam trap

The trap here is that candidates confuse 'failover' (which can be unplanned and asynchronous) with 'planned failover' (which is synchronous and guarantees zero data loss), leading them to choose Option C thinking any failover will suffice.

How to eliminate wrong answers

Option A is wrong because deleting the secondary database and recreating it from the primary would cause data loss—any transactions committed on the current primary during the outage would be lost, and the new secondary would start from an outdated snapshot. Option B is wrong because adding a new secondary in the original primary region does not fail back; it merely creates an additional replica, and failing over to it would still require a separate failover step, potentially causing data loss if not synchronized. Option C is wrong because failing over the original primary to become the primary again (i.e., performing an unplanned failover) would not guarantee zero data loss—it could drop transactions that were committed on the current primary but not yet replicated to the original primary, as unplanned failover is asynchronous.

Full explanation →
98
MCQmedium

You manage a critical application that uses Azure SQL Database in the West US region. The application requires an RPO of 5 seconds and an RTO of 1 hour in the event of a regional outage. You need to recommend a high availability solution that meets these requirements with minimal cost. What should you recommend?

A.Configure an auto-failover group with a secondary in a different availability zone within West US.
B.Enable geo-restore for the database with a backup retention of 35 days.
C.Create a failover group with a secondary replica in a paired region using active geo-replication.
D.Deploy a zone-redundant Azure SQL Database in the West US region.
AnswerC

Active geo-replication provides an RPO of 5 seconds and can achieve an RTO of 1 hour with automated failover.

Why this answer

It uses active geo-replication with a failover group to a paired region. Active geo-replication typically provides an RPO of 5 seconds or less, and the automated failover can meet an RTO of 1 hour. Option A is incorrect because an auto-failover group within the same region (different availability zone) protects only against zone failure, not a regional outage.

Option B is incorrect because geo-restore has a much higher RPO (up to 12 hours) and RTO (hours or days). Option D is incorrect because zone-redundant deployment protects within a region, not against a regional disaster.

Full explanation →
99
MCQhard

Refer to the exhibit. You are deploying an Azure SQL Database audit policy using an ARM template. What is the MOST significant security concern with the configuration shown?

A.Enabling Azure Monitor target could allow unauthorized access to logs
B.The storage account access key is exposed in the template
C.Retention of 90 days may be too short for compliance
D.Including successful authentication events may expose sensitive login activity
AnswerB

Hardcoding keys is insecure; use a managed identity or Key Vault reference.

Why this answer

The ARM template exposes the storage account access key in plaintext as a parameter value. This is a critical security concern because anyone with access to the template (e.g., in source control or deployment logs) can retrieve the key and gain unrestricted access to the storage account, including reading, modifying, or deleting audit logs. Azure SQL Database audit policies should use managed identities or Azure AD authentication to avoid embedding secrets.

Exam trap

The trap here is that candidates may focus on audit event types or retention periods as security concerns, but the real risk is the plaintext storage account key in the template, which is a classic secret exposure vulnerability.

How to eliminate wrong answers

Option A is wrong because enabling Azure Monitor target does not inherently allow unauthorized access; access is controlled by Azure RBAC and the Log Analytics workspace permissions, not by the audit policy configuration itself. Option C is wrong because retention of 90 days is a common compliance requirement (e.g., HIPAA, PCI DSS) and is not inherently a security concern; the question asks for the most significant security concern, not a compliance or operational one. Option D is wrong because including successful authentication events is a standard audit practice for security monitoring and does not expose sensitive login activity in a way that violates security; the concern is about the storage key exposure, not the event types.

Full explanation →
100
Multi-Selectmedium

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

Select 2 answers
A.Enable Transparent Data Encryption (TDE).
B.Use in-memory OLTP to reduce log writes.
C.Increase the service tier to a higher performance level.
D.Enable Query Store.
E.Increase the frequency of database backups.
AnswersB, C

In-memory OLTP reduces log generation.

Why this answer

High WRITELOG waits indicate that the transaction log is a bottleneck, often due to excessive log I/O. In-memory OLTP reduces log writes by logging only the delta changes for memory-optimized tables, rather than full row versions, which directly alleviates log pressure. This makes option B correct.

Exam trap

The trap here is that candidates often confuse WRITELOG waits with general I/O bottlenecks and select backup frequency or TDE, not realizing that only reducing log write volume (via in-memory OLTP) or increasing log write speed (via higher service tier) directly resolves the wait type.

Full explanation →
101
MCQeasy

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

A.Set the 'Force Encryption' option to Yes on the server properties.
B.Enable Transparent Data Encryption (TDE).
C.Enable Always Encrypted for sensitive columns.
D.Configure a firewall rule to allow only specific IP addresses.
AnswerA

This enforces encrypted connections.

Why this answer

Setting 'Force Encryption' to Yes on the Azure SQL Managed Instance server properties enforces the use of TLS (Transport Layer Security) for all client connections. This configuration ensures that any client attempting to connect without encryption will be rejected, thereby meeting the requirement that all connections use encrypted connections. The setting is applied at the instance level and overrides client-side encryption preferences.

Exam trap

The trap here is that candidates often confuse encryption in transit (Force Encryption) with encryption at rest (TDE) or column-level encryption (Always Encrypted), leading them to select a security feature that does not address the specific requirement of encrypting all connections.

How to eliminate wrong answers

Option B is wrong because Transparent Data Encryption (TDE) encrypts data at rest (the database files on disk), not data in transit between the client and the server; it does not enforce encrypted connections. Option C is wrong because Always Encrypted protects sensitive columns by encrypting data at the client side and keeping the encryption keys from the database engine, but it does not enforce encryption for the entire connection or for all data transmitted. Option D is wrong because configuring a firewall rule to allow only specific IP addresses controls network access based on source IP, but it does not enforce encryption on the connections that are allowed through.

Full explanation →
102
Multi-Selecteasy

Which TWO actions should you perform to enable automatic tuning for an Azure SQL Database?

Select 2 answers
A.Set the database compatibility level to 150
B.Enable automatic tuning at the Azure SQL Database server level
C.Enable automatic tuning at the database level
D.Enable Query Store
E.Configure performance recommendations
AnswersB, C

Server-level inheritance can apply settings to all databases.

Why this answer

Automatic tuning in Azure SQL Database requires enabling it at both the server level (to set the inheritance policy) and the database level (to apply tuning options like FORCE_LAST_GOOD_PLAN or CREATE_INDEX). Option B is correct because enabling at the server level allows you to define a default tuning policy that can be inherited by all databases, which is a prerequisite for per-database tuning. Option C is correct because you must also explicitly enable automatic tuning at the database level to override or confirm the inherited settings for that specific database.

Exam trap

The trap here is that candidates often think enabling Query Store alone (Option D) is sufficient for automatic tuning, but Query Store is merely a data collection mechanism—automatic tuning requires explicit enablement at both the server and database levels to apply corrective actions.

Full explanation →
103
Multi-Selectmedium

Which TWO Azure services can be used to automate the deployment of schema changes to Azure SQL Database?

Select 2 answers
A.Azure Portal
B.SQL Server Management Studio (SSMS)
C.Elastic Database Jobs
D.Azure DevOps Services
E.Azure Automation
AnswersD, E

Supports CI/CD pipelines for database deployments.

Why this answer

Options D and E are correct. Azure DevOps Services provides pipeline capabilities for CI/CD to automate deployment of schema changes. Azure Automation can be used with runbooks to execute T-SQL scripts on a schedule or event trigger.

Option A (Azure Portal) is a manual interface. Option B (SSMS) is a manual tool. Option C (Elastic Database Jobs) is for ad-hoc execution, not a full deployment pipeline.

Full explanation →
104
MCQhard

Your company uses Azure SQL Database with Microsoft Defender for Cloud. You receive an alert indicating a potential SQL injection attack. The alert shows that the attack originated from the IP address of your company's VPN gateway. You have verified that no legitimate users are using the VPN at that time. What should you do to immediately stop the attack while preserving legitimate access?

A.Disable the public endpoint on the SQL Database
B.Change the SQL Database server admin password
C.Remove the firewall rule that allows the VPN gateway IP address
D.Add the attacker's IP address to a block list in Microsoft Defender for Cloud
AnswerC

This will block the attacker while allowing other legitimate IPs.

Why this answer

The attack is originating from the VPN gateway's IP address, and the firewall rule allowing that IP is the only entry point for the attacker. By removing that specific firewall rule, you immediately block the malicious traffic while preserving legitimate access through other firewall rules (e.g., for on-premises or other VPN ranges). This directly stops the SQL injection attack at the network layer without affecting other users or services.

Exam trap

The trap here is that candidates may think changing the admin password or disabling the public endpoint is the fastest fix, but the question specifically asks to 'immediately stop the attack while preserving legitimate access,' which requires a targeted network-level block rather than a broad or authentication-based change.

How to eliminate wrong answers

Option A is wrong because disabling the public endpoint would block all external access to the database, including legitimate users who rely on the public endpoint (e.g., via other IPs or Azure services), which is an overly broad and disruptive action. Option B is wrong because changing the server admin password does not stop an ongoing SQL injection attack that exploits application-layer vulnerabilities; the attacker is already authenticated or bypassing authentication via injection, so a password change is ineffective. Option D is wrong because Microsoft Defender for Cloud does not support adding IP addresses to a block list; it provides alerts and recommendations but relies on Azure Firewall, NSGs, or SQL firewall rules for actual blocking, and the alert's IP is the VPN gateway (not the attacker's true IP), so blocking it would also block legitimate VPN users.

Full explanation →
105
MCQhard

Your Azure SQL Database is configured with a failover group across two regions. You need to ensure that client connections automatically redirect to the secondary region during a regional outage, without changing the connection string. What should you implement?

A.Enable read scale-out and configure application retry logic.
B.Deploy Azure Traffic Manager in front of the SQL Database and configure failover routing.
C.Configure the failover group with a listener endpoint and use that in the connection string.
D.Use private endpoints in both regions and configure DNS with a CNAME record.
AnswerC

The listener endpoint automatically points to the current primary, enabling transparent failover.

Why this answer

A failover group in Azure SQL Database provides a listener endpoint (e.g., `server.database.windows.net`) that automatically resolves to the primary or secondary region based on the current role. By using this listener in the connection string, client connections are redirected transparently during a regional outage without any manual changes to the connection string, as the DNS record is updated by the failover group to point to the secondary region.

Exam trap

The trap here is that candidates often confuse the failover group listener with a Traffic Manager profile or assume that private endpoints alone provide automatic cross-region failover, overlooking that the listener is the specific Azure feature designed for transparent, connection-string-free redirection during regional outages.

How to eliminate wrong answers

Option A is wrong because read scale-out only redirects read-only queries to a secondary replica, not failover traffic, and application retry logic alone cannot change the DNS resolution of the original server name. Option B is wrong because Azure Traffic Manager operates at the DNS level and cannot be placed in front of Azure SQL Database's listener endpoint; the failover group already provides the required DNS-based redirection, and Traffic Manager would add unnecessary complexity and latency. Option D is wrong because private endpoints provide private IP connectivity but do not inherently support automatic DNS failover across regions; a CNAME record would require manual updates or additional DNS management, not the built-in automatic redirection of a failover group listener.

Full explanation →
106
Multi-Selectmedium

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

Select 2 answers
A.Add a firewall rule to allow access from a specific IP address.
B.Schedule a job to rebuild indexes on a set of databases.
C.Create users in Microsoft Entra ID for database access.
D.Automatically scale the service tier of a database based on CPU usage.
E.Run a T-SQL script to update statistics on all databases in an elastic pool.
AnswersB, E

Elastic Database Jobs can be scheduled to run T-SQL maintenance.

Why this answer

Options B and E are correct. Elastic Database Jobs in Azure SQL Database allow you to schedule and run T-SQL scripts across multiple databases, including those in an elastic pool. You can use them to schedule index rebuilding (B) and to update statistics via T-SQL (E).

Option A is incorrect because firewall rules are managed through Azure SQL Server firewall settings, not Elastic Database Jobs. Option C is incorrect because creating users in Microsoft Entra ID (formerly Azure AD) is done through Active Directory, not Elastic Database Jobs. Option D is incorrect because scaling service tiers is an administrative operation handled by Azure SQL Database scaling commands or automation, not by Elastic Database Jobs.

Full explanation →
107
Multi-Selecthard

Which THREE factors should you consider when choosing between active geo-replication and failover groups for an Azure SQL Database disaster recovery solution?

Select 3 answers
A.Failover groups require all databases in the group to fail over together.
B.Active geo-replication is only available in the Business Critical tier.
C.Failover groups provide a single listener endpoint for multiple databases.
D.Failover groups do not support readable secondaries.
E.Active geo-replication allows up to 4 readable secondary replicas.
AnswersA, C, E

Active geo-replication allows per-database failover.

Why this answer

The correct answers are A, C, and E. Option A is correct because failover groups fail over all databases in the group together. Option C is correct because failover groups provide a single readable/writable listener endpoint.

Option E is correct because active geo-replication supports up to 4 readable secondaries. Option B is incorrect: active geo-replication is available in both the Premium and Business Critical tiers, not just Business Critical. Option D is incorrect: failover groups support readable secondaries.

Full explanation →
108
MCQhard

You are reviewing an Azure Policy definition that audits SQL Servers. Based on the exhibit, which condition triggers the audit effect?

A.The SQL Server uses service-managed TDE or has no encryption protector configured
B.The SQL Server uses a customer-managed key from Azure Key Vault
C.The SQL Server does not have an encryption protector set to a customer-managed key
D.The SQL Server is version 12.0 and uses a customer-managed key
AnswerA

The anyOf condition catches both cases: missing key or ServiceManaged key.

Why this answer

The policy 'if' condition requires allOf: type is 'Microsoft.Sql/servers', and anyOf: encryption protector does not exist OR encryption protector is 'ServiceManaged'. This audits SQL servers that either use service-managed TDE or have no encryption protector configured. Option B is incorrect because the policy does not trigger when a customer-managed key is used; it specifically targets those without a customer-managed key.

Option C is incorrect because the condition checks for absence of encryption protector or service-managed, not the absence of customer-managed key. Option D is incorrect because version 12.0 is checked but it is not the trigger; the trigger is based on encryption protector type.

Full explanation →
109
MCQhard

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

A.Create a geo-replica of the MI in North Europe and manually failover when needed
B.Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync
C.Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit
D.Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable
AnswerC

This meets all requirements.

Why this answer

Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Exam trap

The trap here is that candidates may confuse geo-replication (which requires manual failover) with auto-failover groups (which support automatic failover and readable secondaries), or they may overlook that transactional replication cannot meet the strict RPO requirement due to asynchronous nature and potential lag.

How to eliminate wrong answers

Option A is wrong because a geo-replica of Azure SQL Managed Instance does not support automatic failover; it requires manual initiation, which violates the requirement for automatic failover. Option B is wrong because transactional replication introduces additional latency and complexity, and it cannot guarantee an RPO of less than 5 seconds due to potential replication lag and the need for manual failover. Option D is wrong because it creates a secondary that is not readable, failing to meet the requirement to serve read-only reporting traffic for European users with low latency.

Full explanation →
110
Multi-Selectmedium

Your company wants to implement transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key stored in Azure Key Vault. Which TWO prerequisites must be met? (Choose two.)

Select 2 answers
A.The Azure SQL Server must have a system-assigned managed identity.
B.The Key Vault must have an access policy granting necessary permissions to the SQL Server identity.
C.The database must contain a column master key.
D.The Key Vault must be in a different region than the SQL Server.
E.The database must be taken offline during key configuration.
AnswersA, B

Managed identity is used to authenticate to Key Vault.

Why this answer

Azure SQL Database uses the server's system-assigned managed identity to authenticate to Azure Key Vault when accessing the customer-managed key for TDE. Without this identity, the SQL Server cannot prove its identity to Key Vault to retrieve the key. Option B is correct because the Key Vault must have an access policy that grants the SQL Server's managed identity the 'get', 'wrapKey', and 'unwrapKey' permissions, which are required for TDE operations.

Exam trap

The trap here is that candidates often confuse TDE prerequisites with Always Encrypted prerequisites, mistakenly thinking a column master key (Option C) is needed, or they assume the database must be offline (Option E) for key configuration, which is not the case for TDE.

Full explanation →
111
Multi-Selecthard

Your company uses Azure SQL Managed Instance for a critical application. You need to automate the process of rebuilding all indexes that have fragmentation above 30% across all databases on the instance. The automation must run every Sunday at 2:00 AM and must log the results to a table. Which three steps should you take to implement this automation?

Select 3 answers
A.Create a logging table in a database to record start time, end time, and status.
B.Use an Azure Automation runbook with the PowerShell module SqlServer to connect and run the maintenance.
C.Create a SQL Agent job on the Managed Instance scheduled for Sunday at 2:00 AM.
D.Use Elastic Database Jobs to run the script across all databases.
E.Write a T-SQL script that uses sys.dm_db_index_physical_stats and ALTER INDEX REBUILD.
AnswersA, C, E

Logging is required for tracking the automation.

Why this answer

Creating a logging table is necessary to record the results of the index maintenance operations. Option C is correct because SQL Agent jobs can be scheduled on a Managed Instance to run tasks at specific times, such as every Sunday at 2:00 AM. Option E is correct because a T-SQL script using sys.dm_db_index_physical_stats to identify fragmented indexes and ALTER INDEX REBUILD to rebuild them is the standard approach.

Option B is incorrect because Azure Automation runbooks require additional setup (like Hybrid Runbook Worker) to connect to SQL Managed Instance, and it is not the simplest way to schedule a recurring task on the instance. Option D is incorrect because Elastic Database Jobs is designed for Azure SQL Database and is not supported on Azure SQL Managed Instance.

Full explanation →
112
MCQmedium

Refer to the exhibit. You deploy this ARM template for an Azure SQL Database. The deployment succeeds. Which statement about the deployed database is true?

A.The database is provisioned tier
B.The database is in serverless tier
C.The database is zone redundant
D.The database maximum size is 250 GB
AnswerA

S2 is a provisioned tier service objective.

Why this answer

The ARM template specifies `requestedServiceObjectiveName: S2`, which corresponds to a provisioned tier (S2). The `autoPauseDelay` property is only applicable to serverless databases; when used with a provisioned tier, it is ignored and does not cause the deployment to fail. Therefore, the database is deployed as a provisioned tier S2 database.

Option A is correct. Option B is incorrect because the database is not serverless. Option C is incorrect because `zoneRedundant` is set to false.

Option D is incorrect because `maxSizeBytes` is 5 GB, which is less than 250 GB.

Full explanation →
113
MCQhard

Your organization uses Azure SQL Database and wants to implement a defense-in-depth strategy. You have already enabled Transparent Data Encryption (TDE) and firewall rules. You need to add an additional layer of security that protects against unauthorized access to the physical storage files (e.g., if someone gains access to the storage account). What should you enable?

A.Infrastructure encryption (double encryption) using platform-managed keys
B.Dynamic Data Masking
C.Microsoft Defender for SQL
D.Always Encrypted with secure enclaves
AnswerA

Adds a second layer of encryption at the storage infrastructure level.

Why this answer

Infrastructure encryption (double encryption) using platform-managed keys adds a second layer of encryption at the storage infrastructure level, below the TDE layer. This protects data at rest even if the physical storage files (e.g., Azure Storage blobs) are accessed directly, because the storage infrastructure itself is encrypted with a separate, platform-managed key. This fulfills the defense-in-depth requirement for protecting against unauthorized access to physical storage.

Exam trap

The trap here is that candidates often confuse TDE with infrastructure encryption, assuming TDE alone protects against physical storage access, but TDE only encrypts database pages, not the underlying storage infrastructure, which is why double encryption is needed for defense-in-depth.

How to eliminate wrong answers

Option B is wrong because Dynamic Data Masking only obfuscates data in query results for non-privileged users; it does not encrypt data at rest or protect physical storage files. Option C is wrong because Microsoft Defender for SQL provides threat detection and vulnerability assessments, not encryption of data at rest or physical storage protection. Option D is wrong because Always Encrypted with secure enclaves protects data in use and in transit by encrypting columns with client-held keys, but it does not encrypt the underlying storage files or protect against physical storage access.

Full explanation →
114
MCQmedium

Your organization has a regulatory requirement to audit all data modifications in an Azure SQL Database. You enable Azure SQL Database auditing and configure it to send logs to a Log Analytics workspace. However, you notice that DELETE operations on a specific table are not being audited. What is the most likely cause?

A.The diagnostic settings for the database are misconfigured
B.The audit action group does not include 'DATABASE_OPERATION_GROUP'
C.Audit logs are being written to a storage account instead of Log Analytics
D.The table has been configured to ignore auditing for read operations
AnswerB

Data modification auditing requires appropriate action groups.

Why this answer

Azure SQL Database auditing uses audit action groups to define which operations are logged. By default, the audit policy includes 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP', 'FAILED_DATABASE_AUTHENTICATION_GROUP', and 'BATCH_COMPLETED_GROUP', but 'DATABASE_OPERATION_GROUP' is required to capture DDL and DML operations like DELETE. If this group is not explicitly added to the audit specification, DELETE operations on specific tables will not be recorded, even though general auditing is enabled.

Exam trap

The trap here is that candidates often confuse the destination of audit logs (diagnostic settings) with the scope of audited actions (audit action groups), leading them to incorrectly blame misconfigured diagnostic settings or the log destination for missing DELETE operations.

How to eliminate wrong answers

Option A is wrong because diagnostic settings control the destination of logs (e.g., Log Analytics, storage, Event Hubs), not which operations are audited; misconfiguration there would affect log delivery, not the scope of audited actions. Option C is wrong because the destination of audit logs (storage account vs. Log Analytics) does not change which operations are audited; the audit action groups determine what is captured, regardless of where logs are sent.

Option D is wrong because Azure SQL Database does not support table-level configuration to ignore auditing for read operations; auditing is applied at the database level via action groups, and DELETE is a write operation, not a read operation.

Full explanation →
115
MCQeasy

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. The source databases use SQL Server Agent jobs, cross-database queries, and SQL Server Integration Services (SSIS) packages. Which migration approach minimizes the need to modify existing code or processes?

A.Migrate to SQL Server on Azure Virtual Machines and keep all existing functionality unchanged.
B.Migrate to Azure SQL Managed Instance and use Azure Data Factory to run SSIS packages with an Azure-SSIS integration runtime.
C.Migrate to Azure Database for PostgreSQL and rewrite all SQL Agent jobs as pg_cron tasks.
D.Migrate to Azure SQL Database and use elastic jobs to replace SQL Server Agent.
AnswerB

Correct: Managed Instance supports SQL Server Agent, cross-database queries, and SSIS via Azure Data Factory.

Why this answer

Azure SQL Managed Instance provides near-100% compatibility with on-premises SQL Server, including SQL Server Agent and cross-database queries. For SSIS packages, the Azure-SSIS integration runtime in Azure Data Factory allows you to lift-and-shift existing SSIS packages without rewriting them, minimizing code changes.

Exam trap

The trap here is that candidates often assume Azure SQL Database can replace SQL Server Agent with elastic jobs, but elastic jobs lack many Agent features (e.g., job schedules, operators, alerts) and do not support SSIS packages or cross-database queries, making Managed Instance the correct choice for minimal code changes.

How to eliminate wrong answers

Option A is wrong because migrating to SQL Server on Azure VMs keeps all functionality unchanged but requires you to manage the VM, patching, and backups, which is not the most minimal-effort approach for code changes—it's a valid option but not the best answer for minimizing code modifications. Option C is wrong because migrating to Azure Database for PostgreSQL requires rewriting SQL Agent jobs as pg_cron tasks and likely rewriting cross-database queries and SSIS packages, which contradicts the goal of minimizing code changes. Option D is wrong because Azure SQL Database does not support SQL Server Agent or cross-database queries natively, and elastic jobs only partially replace Agent functionality, requiring significant code modifications.

Full explanation →
116
MCQhard

Refer to the exhibit. You observe DTU usage reaching 100% at 11:00 AM. Which recommendation would best resolve the performance issue?

A.Schedule the recurring job to run at a different time, such as midnight.
B.Enable read scale-out to offload reporting queries.
C.Increase the service tier to S3 to provide more DTUs.
D.Create an index on the tables used by the job.
AnswerA

Moving the job to off-peak hours can reduce contention and prevent DTU exhaustion.

Why this answer

The exhibit shows a pattern of increasing DTU usage. The recurring job runs from 10:00 to 10:30, but the peak at 11:00 suggests another process causing full usage. The best solution is to schedule resource-intensive operations during off-peak hours.

Increasing the service tier may help but is not the most targeted approach.

Full explanation →
117
MCQeasy

You need to automate the backup of an Azure SQL Database to a storage account. Which built-in feature should you configure?

A.Use elastic jobs to schedule backups.
B.Use Azure Backup to create a backup policy.
C.Configure a SQL Server Agent job to perform backups.
D.Configure a long-term retention policy for the database.
AnswerD

Configuring a long-term retention policy is the correct built-in feature to retain automatic backups for longer periods, up to 10 years.

Why this answer

Azure SQL Database has automated backups enabled by default, which are managed by Microsoft. To retain backups beyond the default retention period (7-35 days), you can configure a long-term retention (LTR) policy to store backups for up to 10 years. Option A is incorrect because elastic jobs are used for performing administrative tasks across multiple databases, not for scheduling backups (backups are automatic and cannot be scheduled via elastic jobs).

Option B is incorrect because Azure Backup is designed for IaaS VMs and Azure Files, not for Azure SQL Database, which has its own built-in backup mechanism. Option C is incorrect because SQL Server Agent is not available in Azure SQL Database (it is a feature of SQL Server on-premises or on IaaS VMs); therefore, you cannot use SQL Server Agent jobs for backup automation. Option D is correct because a long-term retention policy is the built-in feature that allows you to keep automated backups for extended periods.

Full explanation →
118
MCQmedium

You manage a critical database in Azure SQL Database that must recover within 5 minutes in case of a regional outage. You configure geo-replication. Which SKU guarantees the fastest failover time with the least data loss?

A.General Purpose (GP)
B.Hyperscale (HS)
C.Business Critical (BC)
D.Serverless
AnswerC

Business Critical provides the lowest RPO and fastest failover.

Why this answer

Azure SQL Database Business Critical tier uses premium SSDs and in-memory technologies, providing the lowest RPO and fastest failover (typically <30 seconds). Hyperscale (A) has longer failover and higher RPO. General Purpose (B) has higher RPO.

Serverless (D) is not suitable for geo-replication.

Full explanation →
119
MCQmedium

Your company has an Azure SQL Managed Instance deployed in the East US region. The database hosts a financial application that requires a Recovery Time Objective (RTO) of 15 minutes and a Recovery Point Objective (RPO) of 15 seconds. The solution must use a secondary region in West US. The budget allows for a single secondary instance but must minimize compute costs during normal operations. You have configured a failover group between the primary and a secondary managed instance in West US. During a recent disaster recovery drill, you observed that the failover took 20 minutes to complete, exceeding the RTO. You need to improve the failover time to meet the 15-minute RTO without increasing costs. What should you do?

A.Ensure the secondary managed instance is in a different virtual network and configure cross-region VNet peering for faster synchronization.
B.Create a failover group with automatic failover policy and set the grace period to 1 hour. Also, enable read-scale failover for the secondary.
C.Scale up the primary managed instance to a higher service tier (e.g., from General Purpose to Business Critical) to improve performance.
D.Configure the failover group to use manual failover instead of automatic; this reduces failover time because you can control the exact moment.
AnswerD

Manual failover allows immediate initiation of the failover process, avoiding the grace period delay. This can reduce total failover time to less than the 15-minute RTO.

Why this answer

Configuring manual failover allows the database administrator to initiate failover immediately without waiting for a grace period. This reduces the overall failover time, as the 20-minute failover observed likely included time waiting for automatic failover to trigger after the grace period. With manual failover, the switch can be performed in less than 15 minutes.

Option B is incorrect because setting the grace period to 1 hour would actually increase the time before automatic failover occurs, and enabling read-scale failover does not reduce failover time. Option C increases cost and does not directly affect failover time. Option A does not significantly reduce failover time.

Full explanation →
120
MCQhard

You have an Azure SQL Database with automatic tuning enabled. You notice that a query that previously ran quickly is now running slower. Automatic tuning has implemented a plan correction. However, the new plan is performing worse. What should you do to revert to the previous behavior?

A.Drop and recreate the index used by the query
B.Wait for automatic tuning to revert the plan automatically
C.Disable automatic tuning for the database
D.Revert the automatic tuning plan correction using the Azure portal or T-SQL
AnswerD

You can revert the plan to the previous one manually.

Why this answer

When automatic tuning implements a plan correction that performs worse, you can manually revert the plan correction using the Azure portal or T-SQL commands (e.g., `ALTER DATABASE ... SET AUTOMATIC_TUNING ...` or querying `sys.dm_db_tuning_recommendations` and using the `revert` action). Option A is incorrect because dropping and recreating the index does not revert the plan correction; it may force a new plan but is not the direct revert method.

Option B is incorrect because automatic tuning does not automatically revert a plan correction that is performing worse; it may later adjust, but manual intervention is the reliable way. Option C is incorrect because disabling automatic tuning would only prevent future corrections but not revert the current one; the plan would remain until manually reverted or statistics change.

Full explanation →
121
MCQeasy

You need to ensure that all connections to an Azure SQL Database are encrypted. Which setting should you enforce?

A.Configure firewall rules to allow only specific IP addresses.
B.Set 'Force encryption' to 'Yes' on the server.
C.Enable Transparent Data Encryption (TDE).
D.Enable SQL Auditing.
AnswerB

This setting enforces encrypted connections to the database.

Why this answer

Setting 'Force encryption' to 'Yes' on the Azure SQL Database server enforces TLS encryption for all client connections. This setting ensures that any client connecting to the database must use an encrypted connection, rejecting any unencrypted attempts. It is the direct mechanism to mandate encryption in transit, as opposed to encryption at rest or auditing.

Exam trap

The trap here is confusing encryption in transit (TLS/SSL) with encryption at rest (TDE), leading candidates to select TDE when the question specifically asks about securing connections.

How to eliminate wrong answers

Option A is wrong because configuring firewall rules to allow only specific IP addresses controls network access but does not enforce encryption of the connection itself. Option C is wrong because Transparent Data Encryption (TDE) encrypts data at rest on disk, not data in transit between the client and the database. Option D is wrong because SQL Auditing logs database events for compliance and monitoring, but it does not enforce or require encrypted connections.

Full explanation →
122
Matchingmedium

Match each Azure SQL Database backup type to its description.

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

Concepts
Matches

Complete copy of the database

Changes since the last full backup

All log records since the last log backup

Why these pairings

These are the three types of backups used for point-in-time restore in Azure SQL Database.

Full explanation →
123
Multi-Selecthard

You need to ensure that all queries accessing a specific column containing credit card numbers are encrypted both at rest and in transit between the application and Azure SQL Database. Which THREE technologies should you implement? (Choose three.)

Select 3 answers
A.Enforce TLS 1.2 or higher for client connections
B.Always Encrypted with column encryption key stored in Azure Key Vault
C.Transparent Data Encryption (TDE)
D.Row-Level Security
E.Dynamic Data Masking
AnswersA, B, C

TLS encrypts data in transit between application and database.

Why this answer

Enforcing TLS 1.2 or higher for client connections encrypts data in transit between the application and Azure SQL Database. TLS ensures that all queries and their results, including the credit card column, are protected from interception during network transmission.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking or Row-Level Security with encryption, but neither provides actual encryption of data at rest or in transit, which is explicitly required by the question.

Full explanation →
124
MCQmedium

You have an Azure SQL Managed Instance configured with a failover group across two regions. The primary region experiences a complete outage. You perform a manual failover to the secondary region. After the primary region is restored, you need to bring the original primary back into the failover group. What is the correct order of actions?

A.Remove the original primary from the failover group and then add it back as a secondary.
B.Perform a planned failover back to the original primary region.
C.Create a new SQL Managed Instance in the original region and add it to the failover group.
D.Perform a forced failover to the original primary region.
AnswerA

Correct: This re-establishes the failover relationship.

Why this answer

After a manual failover in an Azure SQL Managed Instance failover group, the original primary becomes a secondary. To restore it to the failover group, you must first remove the original primary from the group if it is still listed, then add it back as a secondary. This allows the original primary to rejoin as a readable secondary and resync data.

Option B is incorrect because a planned failover can only be initiated when the primary is healthy, which it may not be after an outage. Option C is incorrect because creating a new instance is unnecessary and wasteful. Option D is incorrect because a forced failover is not appropriate for restoring a previously failed instance; forced failover is used for urgent failover scenarios and may cause data loss.

Full explanation →
125
MCQeasy

Your company has an Azure SQL Database that uses the Business Critical service tier with three replicas. You need to ensure that during a regional outage, the database can be failed over to a secondary region with minimal data loss. What should you configure?

A.Configure zone-redundant replicas in the same region.
B.Create an auto-failover group with a secondary server in a different region.
C.Enable read scale-out on the database.
D.Use geo-redundant backup storage and perform geo-restore.
AnswerB

Auto-failover groups provide cross-region DR with minimal data loss.

Why this answer

Auto-failover groups with a secondary server in a different region provide cross-region disaster recovery with minimal data loss. Azure SQL Database supports configuring an auto-failover group that includes a primary and secondary server in different regions. When using the Business Critical service tier, synchronous replication is used within the primary region, but for cross-region failover, asynchronous replication is used, resulting in minimal data loss (typically a few seconds).

Option A is wrong because zone-redundant replicas provide high availability within a single region, not cross-region DR. Option C is wrong because read scale-out allows offloading read workloads to a replica, but does not provide failover capability. Option D is wrong because geo-redundant backup storage and geo-restore can restore the database in a different region, but it involves longer recovery time (hours) and potential data loss, and is not a failover mechanism.

Full explanation →
126
MCQhard

You have an Azure SQL Database configured with a failover group that includes a secondary in another region. The primary region experiences a complete outage. You initiate a forced failover. After the outage is resolved, you want to bring the original primary back as a secondary without data loss. What should you do?

A.Use the 'az sql db failover-group re-sync' command to re-synchronize
B.Configure replication from the new primary to the old primary using active geo-replication
C.Restore the original primary from backup and re-add it to the failover group
D.Remove the original primary from the failover group, drop the database, and recreate it as a new secondary
AnswerD

This is the recommended approach to rejoin a failed primary after a forced failover.

Why this answer

After a forced failover, the original primary is in a failed state. To bring it back as a secondary without data loss, you must remove it from the failover group, drop the original database, and then add a new secondary database (which will be seeded from the current primary). This is the only method that ensures no data loss.

Option A is incorrect because there is no 'az sql db failover-group re-sync' command. Option B is incorrect because active geo-replication is not required; failover groups automatically handle replication. Option C is incorrect because restoring from backup would not reflect changes made after the failover, resulting in data loss.

Full explanation →
127
MCQmedium

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

A.Enable read committed snapshot isolation (RCSI) on the database.
B.Create a resource governor workload group with a maximum grant time of 5 seconds.
C.Set the DEADLOCK_PRIORITY to LOW on the blocking session and set LOCK_TIMEOUT to 5000.
D.Set the transaction isolation level to SNAPSHOT for all sessions.
AnswerC

Setting DEADLOCK_PRIORITY to LOW and LOCK_TIMEOUT to 5000 does not terminate the blocking transaction; LOCK_TIMEOUT kills the waiting query. Also, DEADLOCK_PRIORITY is a session-level setting, requiring application changes, which violates the requirement.

Why this answer

LOCK_TIMEOUT can be used to automatically terminate a query that has been waiting for a lock longer than a specified number of milliseconds. Setting LOCK_TIMEOUT to 5000 will kill the waiting (blocked) session after 5 seconds, which mitigates blocking. While LOCK_TIMEOUT is a session-level setting, it can be applied database-wide by using a logon trigger or by setting server-level defaults without modifying application code.

Resource Governor does not have a MAX_GRANT_TIME setting for T-SQL queries, so option B is invalid. RCSI and snapshot isolation reduce blocking but do not terminate long-running transactions, and setting the isolation level for all sessions requires application changes or connection settings.

Exam trap

A common trap is to assume LOCK_TIMEOUT terminates the blocking transaction, but it actually kills the waiting query. The blocker continues to hold locks.

Full explanation →
128
MCQmedium

Your Azure SQL Database is experiencing high CPU usage. You suspect a specific query is causing the issue. You have enabled Query Store. How can you identify the query that has consumed the most cumulative CPU time over the last hour?

A.Use sys.dm_exec_query_stats to sort by total_worker_time.
B.Use the Query Store Top Resource Consuming Queries report and filter by the last hour.
C.Use sys.dm_exec_requests to find queries with high CPU.
D.Use the Query Store Regressed Queries report.
AnswerB

This report is designed for this purpose, with time filter and cumulative metrics.

Why this answer

The Query Store Top Resource Consuming Queries report allows you to filter by time interval and shows cumulative CPU time, making it easy to identify the query consuming the most CPU over the last hour. Option A is wrong because sys.dm_exec_query_stats shows cached query plans and statistics since the last plan compilation, not cumulative over a specific time window. Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative CPU usage.

Option D is wrong because the Regressed Queries report shows queries whose performance has changed due to plan changes, not simply high CPU consumption.

Full explanation →
129
MCQhard

You have a SQL Server on Azure Virtual Machine running a critical OLTP workload. You need to achieve high availability with automatic failover and no shared storage. The solution must use Azure features and avoid complex clustering configurations. What should you implement?

A.Configure an Always On Availability Group with automatic failover.
B.Set up log shipping to a secondary VM.
C.Use database mirroring with automatic failover.
D.Deploy a Windows Server Failover Cluster Instance (FCI) using Azure Shared Disks.
AnswerA

Availability Groups provide automatic failover without shared storage.

Why this answer

Always On Availability Groups provide high availability and automatic failover without requiring shared storage, and they leverage Azure features like internal load balancer. Option B (log shipping) offers disaster recovery but not automatic failover. Option C (database mirroring) is deprecated and not recommended for new implementations.

Option D (Failover Cluster Instance) requires shared storage, which is not desired.

Full explanation →
130
MCQhard

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

A.Perform a native backup and restore to Azure SQL Managed Instance
B.Use the Data Migration Assistant (DMA) to migrate to Azure SQL Database
C.Use Azure Database Migration Service (DMS) with offline migration to Azure SQL Database
D.Use Azure Database Migration Service (DMS) with online migration to Azure SQL Managed Instance
AnswerD

Azure SQL Managed Instance supports Service Broker and online migration minimizes downtime.

Why this answer

Azure SQL Managed Instance fully supports Service Broker, and the Azure Database Migration Service (DMS) with online migration mode enables minimal downtime by continuously replicating changes from the source SQL Server to the target Managed Instance until a cutover. This satisfies both the Service Broker feature requirement and the compliance mandate for minimal downtime.

Exam trap

The trap here is that candidates may confuse Azure SQL Database with Azure SQL Managed Instance regarding Service Broker support, or assume that any offline migration method can achieve minimal downtime, but the key differentiator is that only online migration to SQL Managed Instance meets both the feature and downtime requirements.

How to eliminate wrong answers

Option A is wrong because a native backup and restore to Azure SQL Managed Instance is an offline method that requires the source database to be taken offline during the backup and restore process, causing significant downtime, and does not support minimal downtime. Option B is wrong because the Data Migration Assistant (DMA) can assess and migrate to Azure SQL Database, but Azure SQL Database does not support Service Broker for cross-database messaging, so it fails the feature requirement. Option C is wrong because using DMS with offline migration to Azure SQL Database also targets Azure SQL Database, which lacks Service Broker support, and offline migration inherently involves downtime, violating the minimal downtime requirement.

Full explanation →
131
Multi-Selectmedium

You are planning a disaster recovery strategy for an Azure SQL Database that is part of a failover group. The application requires that after a failover, the database is accessible with minimal downtime and without data loss. Which THREE components are essential for this configuration? (Select three.)

Select 3 answers
A.Geo-redundant backup storage
B.Secondary database in a paired region with data synchronization
C.Failover group listener endpoint
D.Azure Traffic Manager with priority routing
E.Failover group configured between primary and secondary servers
AnswersB, C, E

The secondary must be synchronized to ensure no data loss.

Why this answer

A secondary database in a paired region with data synchronization ensures that data is replicated to another region, enabling failover with minimal data loss. Option C is correct: The failover group listener endpoint provides a constant connection string that automatically routes to the current primary database, ensuring application connectivity after failover. Option E is correct: The failover group configured between primary and secondary servers orchestrates the replication and automatic failover process.

Option A is incorrect: Geo-redundant backup storage is for point-in-time restore, not for failover. Option D is incorrect: Azure Traffic Manager with priority routing is not needed when using failover groups; the failover group listener handles routing.

Full explanation →
132
MCQmedium

You are reviewing an ARM template snippet for an Azure SQL Database. The database should be configured to automatically pause after 60 minutes of inactivity and resume with a minimum capacity of 0.5 vCores. However, the database is not pausing as expected. What is the most likely cause?

A.The 'zoneRedundant' property is set to false, which prevents auto-pause.
B.The database is not configured with the 'Serverless' compute tier.
C.The 'autoPauseDelay' value is too low; it must be at least 360 minutes.
D.The API version does not support auto-pause.
AnswerB

Auto-pause is only available in the serverless compute tier.

Why this answer

Auto-pause functionality is only available for Azure SQL Database in the serverless compute tier. Without configuring the database as serverless (by setting the 'sku' property to include 'Serverless' tier), the auto-pause and auto-resume features will not work, regardless of other property settings. Option A is incorrect because the 'zoneRedundant' property does not affect auto-pause.

Option C is incorrect because the minimum 'autoPauseDelay' for serverless is 60 minutes, not 360. Option D is incorrect because the API version used supports serverless features.

Full explanation →
133
MCQmedium

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

A.sys.dm_db_io_stats
B.sys.dm_os_performance_counters
C.sys.dm_exec_query_stats
D.sys.dm_db_index_usage_stats
AnswerC

Provides per-query execution statistics including I/O metrics like total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS.

Why this answer

Sys.dm_exec_query_stats provides per-query execution statistics, including I/O metrics such as total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS. Option A is incorrect because sys.dm_db_io_stats is not a valid DMV in Azure SQL Managed Instance and does not provide per-query I/O information. Option B is incorrect because sys.dm_os_performance_counters provides OS-level performance counters, not per-query I/O.

Option D is incorrect because sys.dm_db_index_usage_stats tracks index usage statistics, not query I/O.

Full explanation →
134
Multi-Selecthard

Which TWO statements about Azure SQL Database zone-redundant configuration are true? (Choose two.)

Select 2 answers
A.It requires a failover group to be configured.
B.It can be enabled during database creation or by updating an existing database.
C.It is only available in the Business Critical service tier.
D.It provides resilience within a single Azure region across multiple zones.
E.It incurs additional cost compared to non-zone-redundant configurations.
AnswersB, D

Zone-redundant can be configured at creation or via update.

Why this answer

Options B and D are correct. Zone-redundant configuration can be enabled during database creation or by updating an existing database (B), and it provides resilience within a single Azure region across multiple availability zones (D). Option A is incorrect because zone-redundant configuration does not require a failover group; failover groups are used for geo-replication, not for zone redundancy.

Option C is incorrect because zone-redundant configuration is available in both the General Purpose and Business Critical service tiers. Option E is incorrect because the cost impact varies by tier: General Purpose tier does not incur additional cost for zone redundancy, while Business Critical does; thus, the blanket statement that it always incurs additional cost is false.

Full explanation →
135
MCQmedium

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

A.Azure SQL Analytics
B.Azure Log Analytics
C.Kusto Query Language (KQL)
D.Azure Monitor metric alerts
AnswerD

Metric alerts can trigger Automation runbooks when conditions are met.

Why this answer

Azure Monitor metric alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A (Azure SQL Analytics) is for analyzing logs, not triggering actions. Option B (Azure Log Analytics) is for log analytics, not for direct metric alerts triggering runbooks.

Option C (Kusto Query Language) is a query language, not a service.

Full explanation →
136
Multi-Selecthard

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

Select 2 answers
A.Disable parallelism for the query using the MAXDOP 1 hint.
B.Increase the DTU or vCore count of the database.
C.Create appropriate indexes on the columns used in joins and filters.
D.Rewrite the query using table hints to force a specific join order.
E.Update statistics on all tables involved in the query.
AnswersC, E

Proper indexing can allow the optimizer to use index seeks instead of scanning and spooling.

Why this answer

An Index Spool (Lazy Spool) operator in an execution plan indicates that the query engine is creating a temporary index on the fly to support join or filter operations, which is a sign of missing or inadequate permanent indexes. Creating appropriate indexes on the columns used in joins and filters (Option C) eliminates the need for the spool, reducing I/O and improving performance. Updating statistics (Option E) ensures the query optimizer has accurate distribution information to generate efficient plans, which can also help avoid spool operations.

Exam trap

The trap here is that candidates often assume an Index Spool is always a performance booster (like a regular index seek) or that increasing hardware resources (Option B) is the quick fix, when in fact the spool is a costly workaround for missing permanent indexes and stale statistics.

Full explanation →
137
MCQhard

You are designing a disaster recovery plan for an Azure SQL Managed Instance in the East US region. The plan must meet an RPO of 15 minutes and an RTO of 1 hour. You need to minimize costs while meeting these requirements. Which solution should you recommend?

A.Use a zone-redundant Business Critical instance.
B.Deploy a second instance in the same region with zone redundancy.
C.Configure a failover group between two General Purpose instances in different regions.
D.Configure a failover group between two Business Critical instances in different regions.
AnswerC

Meets RPO/RTO at lower cost.

Why this answer

A failover group between two General Purpose instances in different regions provides geo-replication with an RPO of seconds to minutes and an RTO of about 1 hour, meeting the requirements at minimal cost. Option A is incorrect because zone-redundant redundancy only protects against zonal failures within the same region, not regional disasters. Option B is incorrect because deploying a second instance with zone redundancy in the same region does not provide cross-region DR.

Option D is incorrect because using Business Critical instances is more expensive and not required for the specified RPO/RTO.

Full explanation →
138
MCQeasy

You have an Azure SQL Managed Instance configured with an auto-failover group between two regions. You need to ensure that client applications can automatically connect to the secondary instance after a failover without changing connection strings. What should you configure?

A.Use the failover group listener endpoint in the connection string.
B.Set up a load balancer with health probes.
C.Deploy an Application Gateway with backend pools for each region.
D.Configure a Traffic Manager profile with endpoint monitoring.
AnswerA

The listener automatically routes to the current primary.

Why this answer

The failover group listener provides a read-write listener endpoint that automatically points to the current primary. Option B is wrong because a Traffic Manager profile would require manual intervention. Option C is wrong because an Application Gateway does not provide automatic failover redirection.

Option D is wrong because a load balancer does not handle failover group redirection.

Full explanation →
139
MCQmedium

Your company has an Azure SQL Managed Instance in the West US region. You need to configure a disaster recovery solution that provides automatic failover to a secondary region with minimal data loss. The solution must also allow read-only access to the secondary database for reporting purposes. Which option should you use?

A.Create an auto-failover group with active geo-replication.
B.Configure a failover group between the primary and secondary managed instances.
C.Use log shipping to a secondary managed instance.
D.Enable active geo-replication on the database.
AnswerB

Failover groups provide automatic failover, readable secondary, and minimal data loss for managed instances.

Why this answer

Failover groups are the native disaster recovery feature for Azure SQL Managed Instance, providing automatic failover, a readable secondary (via read-only intent), and minimal data loss with a synchronization mode that prioritizes data durability. Option A (auto-failover group with active geo-replication) is incorrect because active geo-replication is a separate feature for single databases and elastic pools, not managed instances; failover groups themselves include the auto-failover capability. Option C (log shipping) is not a built-in managed instance feature, does not provide automatic failover, and the secondary is not readable during normal operation.

Option D (active geo-replication) is for single databases and elastic pools, not managed instances, and does not provide automatic failover.

Full explanation →
140
MCQhard

You are deploying an Azure SQL Database that will be used by a critical financial application. The database must be protected against accidental deletion or modification of data by administrators. You need to implement a solution that allows point-in-time restore to any point within the last 35 days, even if an administrator accidentally drops a table. Which feature should you enable?

A.Configure long-term backup retention (LTR) policies.
B.Enable the Azure SQL Database recycle bin feature.
C.Enable automated backups and configure point-in-time restore with a retention period of 35 days.
D.Set up Active Geo-Replication with a failover group.
AnswerC

Point-in-time restore can recover from accidental data modification.

Why this answer

Azure SQL Database's automated backups with point-in-time restore (PITR) allow you to restore a database to any point within the configured retention period, up to 35 days. This includes recovery from accidental table drops or data modifications, as PITR operates at the database level and can recover to a specific timestamp before the incident occurred.

Exam trap

The trap here is that candidates often confuse the 'recycle bin' feature from on-premises SQL Server with Azure SQL Database, or mistakenly think LTR or geo-replication can undo accidental data changes, when in fact only PITR with the correct retention period provides this capability.

How to eliminate wrong answers

Option A is wrong because long-term backup retention (LTR) is designed for archival purposes beyond 35 days (up to 10 years), not for short-term recovery from accidental deletion or modification within the 35-day window. Option B is wrong because Azure SQL Database does not have a 'recycle bin' feature; this concept exists in on-premises SQL Server (e.g., dropped objects go to the recycle bin) but is not available in Azure SQL Database. Option D is wrong because Active Geo-Replication with a failover group provides high availability and disaster recovery by maintaining readable replicas in different regions, but it does not protect against accidental data deletion or modification—it replicates all changes, including destructive ones, to the replica.

Full explanation →
141
MCQhard

You manage an Azure SQL Database that uses a Business Critical tier with a zone-redundant configuration. During a planned maintenance event, you notice a significant increase in query latency. You suspect that the read-only intent routing is not working as expected. What should you check first to ensure read-only connections are using the secondary replica?

A.Verify that the application connection string includes 'ApplicationIntent=ReadOnly' for read-only queries.
B.Review the maintenance window schedule to see if the primary replica was being updated.
C.Check the 'ReadScale' property of the database to ensure it is set to 'Enabled'.
D.Confirm that the database is in a zone-redundant configuration by checking the 'ZoneRedundant' property.
AnswerA

Correct: Read scale-out requires the application to specify read-only intent; otherwise, connections go to the primary replica.

Why this answer

The read-only intent routing in Azure SQL Database relies on the client connection string including `ApplicationIntent=ReadOnly`. Without this parameter, all connections, including those intended for read-only workloads, will be directed to the primary replica, even if a readable secondary replica is available. This is the first and most fundamental check to ensure read-only queries are routed to the secondary.

Exam trap

The trap here is that candidates may confuse the `ReadScale` property (which is only for Hyperscale) with the Business Critical tier's built-in read-only routing, or they may think that zone-redundancy or maintenance schedules directly control read-only routing, when in fact the connection string's `ApplicationIntent` is the sole determinant.

How to eliminate wrong answers

Option B is wrong because reviewing the maintenance window schedule does not directly address the read-only routing issue; maintenance events may cause latency but do not change the routing behavior based on ApplicationIntent. Option C is wrong because the `ReadScale` property is only relevant for the Hyperscale service tier, not for Business Critical tier, where read-only routing is always enabled when a readable secondary exists. Option D is wrong because confirming zone-redundant configuration ensures high availability but does not affect the read-only routing mechanism, which depends on the connection string's ApplicationIntent parameter.

Full explanation →
142
MCQmedium

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure SQL Database backup long-term retention policy. The template is deployed successfully. Which statement is true about the resulting backup retention?

A.Monthly backups are retained for 24 months.
B.Weekly backups are retained for 8 weeks.
C.Yearly backups are retained for 1 year.
D.Weekly backups are retained for 4 weeks.
AnswerD

The weeklyRetention property is set to P4W, which is 4 weeks.

Why this answer

The ARM template snippet defines a backup long-term retention policy for Azure SQL Database. The `weeklyRetention` property is set to `P4W`, which in ISO 8601 duration format means a period of 4 weeks. Therefore, weekly backups are retained for exactly 4 weeks.

Option D correctly reflects this configuration.

Exam trap

The trap here is that candidates often misread the ISO 8601 duration values (e.g., confusing P4W with 4 months or assuming P12M means 24 months) or incorrectly associate the retention period with a different backup frequency (e.g., thinking weekly retention applies to monthly backups).

How to eliminate wrong answers

Option A is wrong because the `monthlyRetention` property is set to `P12M`, which retains monthly backups for 12 months, not 24. Option B is wrong because the `weeklyRetention` property is set to `P4W`, which retains weekly backups for 4 weeks, not 8. Option C is wrong because the `yearlyRetention` property is set to `P1Y`, which retains yearly backups for 1 year, but the question asks for the resulting backup retention based on the template, and the correct statement is about weekly retention, not yearly; additionally, the yearly retention is indeed 1 year, but that is not the correct answer because the question expects the specific true statement among the options, and D is the only one that matches the template's weekly retention value.

Full explanation →
143
MCQeasy

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

A.Hyperscale tier with geo-replication and auto-failover group.
B.Hyperscale tier with zone redundancy.
C.General Purpose tier with active geo-replication.
D.Business Critical tier with zone redundancy.
AnswerD

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

Why this answer

Business Critical tier with zone redundancy provides automatic failover within seconds and zero data loss, meeting the RPO of 5 seconds and RTO of 1 minute. Option A is incorrect because Hyperscale tier with geo-replication and auto-failover group typically has a higher RTO (often up to 1 hour) and RPO may exceed 5 seconds. Option B is incorrect because Hyperscale tier with zone redundancy does not provide automatic failover on its own; it only offers local resilience.

Option C is incorrect because General Purpose tier with active geo-replication has a higher RTO (up to 1 hour) and does not guarantee an RPO of 5 seconds.

Full explanation →
144
MCQeasy

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

A.Use the Basic tier with a failover group.
B.Configure active geo-replication to a secondary region.
C.Upgrade to the Premium tier with zone-redundant configuration.
D.Upgrade to the Standard tier and enable zone redundancy.
AnswerC

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

Why this answer

The Premium tier supports zone-redundant configuration, which replicates data across availability zones within the same region, providing high availability and minimizing downtime during planned maintenance or unplanned failures. Option A is wrong: the Basic tier does not support failover groups or zone redundancy. Option B is wrong: active geo-replication is designed for disaster recovery across regions, not for high availability within a region, and incurs higher cost and complexity.

Option D is wrong: the Standard tier does not support zone redundancy; zone redundancy is only available in the Premium or Business Critical tiers.

Full explanation →
145
MCQmedium

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

A.Always Encrypted with a column encryption key.
B.Row-Level Security to filter rows for non-admin users.
C.Dynamic Data Masking with a masking function configured on the column.
D.Azure Policy to enforce masking rules on the database.
AnswerC

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

Why this answer

Dynamic Data Masking (DDM) is the correct feature for masking a specific column for non-admin users. DDM can be configured on a column using a masking function (e.g., default, email, custom) and automatically obfuscates the data in query results for users without the UNMASK permission. Option A (Always Encrypted) encrypts the column but does not mask it selectively; non-admin users cannot see the data at all unless they have the key.

Option B (Row-Level Security) filters entire rows based on a predicate, not columns. Option D (Azure Policy) applies governance rules at the subscription/resource level and cannot mask database columns.

Full explanation →
146
Multi-Selectmedium

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

Select 2 answers
A.Rewrite queries to use sargable predicates.
B.Upgrade to a higher service tier.
C.Enable row-level security on sensitive tables.
D.Create missing indexes identified by the Database Engine Tuning Advisor.
E.Update statistics on tables with outdated statistics.
AnswersD, E

Index creation can improve query performance without code changes.

Why this answer

Creating missing indexes (option D) and updating statistics (option E) are performance optimization techniques that do not require changes to application code. Indexes recommended by the Database Engine Tuning Advisor can reduce query execution time by enabling efficient data access paths, while updating statistics helps the query optimizer generate better execution plans. Options A and B require code changes or direct query modifications, option C is a security feature that may impact performance but not optimize it, and upgrading the service tier (option B) is a scaling action that increases resources but is not a targeted query optimization.

Full explanation →
147
MCQmedium

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

A.Azure Automation with PowerShell runbooks
B.Azure SQL Database elastic jobs
C.Azure Machine Learning
D.Azure DevOps with Azure SQL Database deployment task
AnswerD

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

Why this answer

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

Azure Machine Learning is irrelevant.

Full explanation →
148
MCQhard

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

A.Use Azure Key Vault to store the TDE protector
B.Use the service-managed TDE key provided by Azure
C.Bring your own certificate and store it in the managed instance
D.Disable TDE after migration and use Always Encrypted instead
AnswerA

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

Why this answer

Using Azure Key Vault to store the TDE protector provides centralized management, automatic key rotation, and high availability without manual intervention. Option B is incorrect because service-managed keys offer less control and are not recommended for scenarios requiring bring your own key (BYOK) compliance. Option C is incorrect because SQL Managed Instance does not support storing TDE certificates directly in the instance; instead, you can use Azure Key Vault to bring your own key.

Option D is incorrect because TDE cannot be disabled after migration without decrypting the database, and Always Encrypted is a different feature for column-level encryption, not a replacement for TDE.

Full explanation →
149
MCQeasy

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

A.Scale up to a higher service tier or increase DTUs
B.Scale down to a lower service tier
C.Enable geo-replication
D.Enable read scale-out
AnswerA

Scaling up provides more resources, reducing DTU consumption percentage.

Why this answer

When DTU consumption consistently exceeds 90%, the database is resource-constrained, leading to performance degradation. Scaling up to a higher service tier or increasing DTUs directly provides more CPU, memory, and I/O resources, alleviating the bottleneck and reducing DTU utilization percentage. This is the standard corrective action for sustained high DTU usage in Azure SQL Database.

Exam trap

The trap here is that candidates may confuse high DTU consumption with a need for high availability or read scaling, but the correct response is to increase resource capacity via scaling up.

How to eliminate wrong answers

Option B is wrong because scaling down reduces available resources, which would worsen the high DTU consumption and likely cause performance failures. Option C is wrong because geo-replication provides disaster recovery and read-only replicas, but does not reduce DTU consumption on the primary database. Option D is wrong because read scale-out offloads read-only workloads to a replica, but the primary database's DTU consumption remains unchanged for write operations and other workloads.

Full explanation →
150
MCQhard

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

A.Set up an Azure Logic App with a failover trigger from Azure Monitor.
B.Configure automatic failover policy in the failover group.
C.Use a T-SQL script with ALTER DATABASE FAILOVER scheduled via SQL Server Agent.
D.Create an Azure Automation Runbook that uses the Start-AzSqlDatabaseFailover command with the -AllowDataLoss parameter set to false.
AnswerD

Runbook can automate planned failover without data loss.

Why this answer

Azure Automation Runbooks can execute the Start-AzSqlDatabaseFailover cmdlet with -AllowDataLoss:$false to perform a planned failover without data loss. Option A (Logic App with failover trigger) is designed for reacting to Azure Monitor alerts, not for scheduled failsafe testing. Option B (configuring automatic failover policy) is for unplanned failovers and would result in data loss if used for testing because automatic failover is asynchronous.

Option C (T-SQL ALTER DATABASE FAILOVER scheduled via SQL Server Agent) is invalid because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it's only available in Azure SQL Managed Instance. Therefore, D is the correct choice for automated planned failover.

Full explanation →
Page 1

Page 2 of 13

Page 3

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 →