Courseiva

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

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

Take a mock examExam hub
Page 1

Page 2 of 13

Page 3
76
Multi-Selecthard

You are configuring a new Azure SQL Database for a multi-tenant SaaS application. You need to ensure that each tenant can only access their own rows. Which THREE features can be used to achieve this?

Select 3 answers
A.Always Encrypted with deterministic encryption.
B.Row-Level Security (RLS) with a predicate function.
C.Application logic that filters queries by tenant ID.
D.Dynamic Data Masking for the tenant ID column.
E.A view that includes a WHERE clause filtering by tenant ID.
AnswersB, C, E

RLS filters rows based on the user's context.

Why this answer

Row-Level Security (RLS) allows you to control access to rows in a database table based on the characteristics of the user executing a query. By creating a security policy with a predicate function that filters rows by tenant ID (e.g., using SESSION_CONTEXT or USER_NAME()), you can ensure each tenant only sees their own data without changing the application's query logic.

Exam trap

The trap here is that candidates often confuse data masking (which only hides column values) with row-level access control, or assume that encryption alone can enforce row filtering, when in fact RLS, application logic, or views with WHERE clauses are the correct mechanisms for multi-tenant row isolation.

Full explanation →
77
Multi-Selecthard

Which THREE configurations are required to automate the deployment of database schema changes from a Git repository to Azure SQL Database using Azure Pipelines?

Select 3 answers
A.An Elastic Database Job agent to run the deployment scripts.
B.An Azure SQL Database deployment task in the pipeline.
C.An Azure Resource Manager service connection to the SQL Database.
D.An Azure Automation Runbook to execute the deployment.
E.A YAML pipeline file that defines the build and release stages.
AnswersB, C, E

The task runs the SQL scripts against the database.

Why this answer

Options A, B, and E are correct. A service connection to Azure SQL Database is needed to authenticate. A YAML pipeline definition specifies the steps.

The SQL task (e.g., Azure SQL Database deployment task) runs the scripts. Option C is wrong because an Azure Automation Runbook is not part of Azure Pipelines. Option D is wrong because an Elastic Database Job agent is not required for a single database deployment.

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

Active geo-replication with synchronous commit provides low RPO and RTO; failover group automates failover.

Why this answer

Option B is correct because a failover group with active geo-replication and automatic failover policy meets the RPO of 5 seconds and RTO of 30 seconds, as the secondary is readable and synchronous commit is used for zero data loss. Option A is incorrect because geo-restore has an RPO of 1 hour and RTO of several hours. Option C is incorrect because auto-failover groups with read-scale failover are for readable secondaries but do not guarantee synchronous commit across regions.

Option D is incorrect because zone redundancy provides HA within a region, not DR across regions.

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

Option D is correct because FORCE_LAST_GOOD_PLAN will automatically revert to the last known good plan if the new plan causes regressions. Option A is wrong because automatic tuning does not show a popup. Option B is wrong because reverting is automatic, not manual.

Option C is wrong because reverting happens automatically.

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

Option A is correct because New-AzSqlDatabaseSecondary creates a secondary database for geo-replication. However, for failover groups, you typically use New-AzSqlDatabaseFailoverGroup. But the question specifically asks for creating the secondary pool; the correct cmdlet is New-AzSqlElasticPool to create the elastic pool, then add databases to the failover group.

Since none of the options mention New-AzSqlElasticPool, the closest is New-AzSqlDatabaseSecondary which creates a secondary database within a pool. Option B is incorrect because New-AzSqlDatabaseFailoverGroup creates the failover group, not the secondary pool. Option C is incorrect because Set-AzSqlDatabaseSecondary modifies geo-replication.

Option D is incorrect because Add-AzSqlDatabaseToFailoverGroup adds databases to a failover group.

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

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

Why this order

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

Full explanation →
83
Multi-Selectmedium

Which TWO services can be used to automate the backup of Azure SQL Database to a storage account? (Choose two.)

Select 2 answers
A.Elastic Database Jobs
B.SQL Agent jobs
C.Azure Backup
D.Azure Automation with PowerShell runbooks
E.Azure Data Factory
AnswersC, D

Azure Backup supports Azure SQL Database long-term retention.

Why this answer

Azure Automation with PowerShell runbooks can execute backup commands. Azure Data Factory can copy backups but not native backup. Azure Backup service supports Azure SQL Database backups.

SQL Agent is not available. Elastic jobs are for T-SQL, not backup.

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

Azure DevOps with SQL Server database projects (DACPAC) is a standard approach for schema deployment. GitHub Actions also supports deploying DACPACs or running SQL scripts. Option A and Option C are correct.

Option B is for SQL Server on-premises. Option D is for running T-SQL, not CI/CD. Option E is for data migration, not schema changes.

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

Option A is correct because 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 →
86
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

Option B is correct because in Hyperscale, index rebuild operations are logged and can cause significant log throughput, which is a common bottleneck. Option A is wrong because page compression is not directly related to index rebuild time. Option C is wrong because Hyperscale uses a distributed architecture with fast log commit.

Option D is wrong because Hyperscale does not have a fixed DTU limit; it scales compute independently.

Full explanation →
87
MCQmedium

You are responsible for automating database maintenance on multiple Azure SQL Databases in an elastic pool. You need to rebuild fragmented indexes weekly. The solution should minimize resource contention. What should you implement?

A.Enable automatic tuning and set 'INDEX_REBUILD' option.
B.Create a SQL Agent job on each database to rebuild indexes during off-peak.
C.Use Elastic Database Jobs with a T-SQL script that rebuilds indexes, and schedule it weekly.
D.Write a PowerShell script that connects to each database sequentially and rebuilds indexes.
AnswerC

Elastic Jobs are designed for this scenario and can be scheduled to run at low-usage times.

Why this answer

Option C is correct because Elastic Database Jobs can run T-SQL scripts across databases in a pool with concurrency control. Option A is wrong because SQL Agent is not available in Azure SQL Database. Option B is wrong because manual scripts are not automated.

Option D is wrong because automatic tuning does not handle index rebuilds; it manages index creation/drop.

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

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

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

Option B is correct because SQL Agent Jobs are available in Azure SQL Managed Instance. Option A is wrong because Azure Automation runbooks can be used but are external. Option C is wrong because Elastic Database Jobs are for multiple databases.

Option D is wrong because it is not a built-in automation feature.

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

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

Option D is correct because 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 →
93
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.
AnswerB

ADR reduces log volume and IO, which decreases page server IOPS utilization.

Why this answer

Option A is correct because enabling accelerated database recovery reduces the log generation and IO on page servers. Option B is wrong because increasing the number of replicas does not reduce page server IO. Option C is wrong because changing to Business Critical might not address page server IO; Hyperscale has its own architecture.

Option D is wrong because increasing max degree of parallelism might increase IO.

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

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

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

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

Option B is correct because Azure Resource Manager (ARM) templates are the standard infrastructure-as-code tool for Azure resources. Option A is wrong because PowerShell can be used but is procedural rather than declarative. Option C is wrong because Azure CLI is also procedural.

Option D is wrong because SQL Server Management Studio is a GUI tool, not for automation.

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

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

Option A is correct because adding a second FCI in another region and configuring asynchronous replication between them provides cross-region DR. Option B is wrong because adding more nodes in the same region does not protect against region failure. Option C is wrong because geo-redundant backup does not provide continuous replication.

Option D is wrong because active geo-replication is not supported for SQL Server on Azure VM.

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

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

Option D is correct because failover groups with active geo-replication provide the lowest RPO (typically 5 seconds or less) and can meet an RTO of 1 hour with automated failover. Option A is wrong because zone-redundant configuration protects only within a region, not against regional failure. Option B is wrong because auto-failover groups without geo-replication also protect only within a region.

Option C is wrong because geo-restore has a much higher RPO and RTO.

Full explanation →
103
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 →
104
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 →
105
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

Option A is correct because 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 →
106
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 →
107
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 A and C are correct. Azure DevOps Services provides pipeline capabilities for CI/CD. Azure Automation can be used with runbooks to execute T-SQL scripts.

Option B is wrong because SQL Server Management Studio (SSMS) is a manual tool. Option D is wrong because Elastic Database Jobs are for ad-hoc execution, not a full deployment pipeline. Option E is wrong because Azure Portal is manual.

Full explanation →
108
Multi-Selecthard

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: - Minimal application changes. - Support for SQL Server Agent jobs. - Ability to scale storage independently from compute. - Native support for cross-database queries. Which THREE options meet these requirements? (Choose three.)

Select 3 answers
A.Azure SQL Database (single database)
B.Azure SQL Database elastic pool
C.Azure Synapse Analytics (dedicated SQL pool)
D.SQL Server on Azure Virtual Machine
E.Azure SQL Managed Instance
AnswersB, D, E

Supports cross-database queries within pool and independent scaling, but not SQL Server Agent.

Why this answer

Azure SQL Managed Instance (E) provides full SQL Server engine compatibility, including SQL Server Agent, cross-database queries, and independent storage scaling, with minimal application changes. SQL Server on Azure Virtual Machine (D) offers full control over the SQL Server instance, supporting Agent jobs and cross-database queries, and allows independent scaling of compute and storage via VM resizing and managed disks. Azure SQL Database elastic pool (B) supports SQL Server Agent jobs (via elastic jobs), independent storage scaling per database, and cross-database queries using elastic query, while requiring minimal application changes.

Exam trap

The trap here is that candidates often assume Azure SQL Database (single database) supports SQL Server Agent natively, but it does not—only Managed Instance and VM provide full Agent support, and elastic pools require elastic jobs as a workaround.

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

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

Option B is correct because the failover group listener provides a read-write listener endpoint that automatically redirects to the new primary after failover. Option A is wrong because private endpoints do not provide automatic redirection. Option C is wrong because read scale-out is for read-only traffic.

Option D is wrong because traffic manager works at DNS level but adds complexity; failover group listener is the built-in solution.

Full explanation →
111
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 A and D are correct. Elastic Database Jobs can run T-SQL scripts across multiple databases and can be scheduled. Option B is wrong because Elastic Database Jobs cannot scale databases.

Option C is wrong because Elastic Database Jobs cannot configure firewall rules. Option E is wrong because Microsoft Entra ID user creation is not a typical use case for Elastic Database Jobs.

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

Options A, C, and D are correct. Active geo-replication supports up to 4 secondaries (A). Failover groups provide a listener endpoint (C).

Failover groups require all databases to fail over together (D). Option B is wrong because both support readable secondaries. Option E is wrong because both support the same tiers.

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

Option B is correct. The policy 'if' condition requires allOf: type is 'Microsoft.Sql/servers', version is '12.0', and anyOf: encryption protector does not exist OR is 'ServiceManaged'. This audits SQL servers that do not use a customer-managed key for TDE.

Option A is wrong because it requires both conditions. Option C is wrong because it ignores the key name condition. Option D is wrong because the policy checks for server version.

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

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

Option A is correct because 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 →
116
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

Option A is correct because SQL Agent can run T-SQL jobs on Managed Instance. Option C is correct because creating a stored procedure to check fragmentation and rebuild indexes is a standard approach. Option D is correct because logging to a table is necessary for tracking.

Option B is incorrect because Azure Automation runbooks cannot directly connect to SQL Managed Instance easily without hybrid workers. Option E is incorrect because Elastic Jobs is for Azure SQL Database, not Managed Instance.

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

Option C is correct because the autoPauseDelay property is only valid for serverless databases, but the requestedServiceObjectiveName is S2 (provisioned tier). The deployment will fail because autoPauseDelay is not applicable. But the stem says deployment succeeds, so we must assume the property is ignored or the database is serverless? Actually, autoPauseDelay is ignored for provisioned tier, and the database will be provisioned.

So option C is false. Option A is correct because the property is ignored. Option B is wrong because zoneRedundant is false.

Option D is wrong because maxSizeBytes is 5 GB, which is larger than 250 GB. Wait, 5368709120 bytes = 5 GB, which is less than 250 GB. So D is false.

The correct answer: The database is provisioned tier S2, autoPauseDelay is ignored.

Full explanation →
118
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 →
119
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 →
120
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 →
121
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

Long-term retention policy automates backup retention beyond the default 7-35 days.

Why this answer

Azure SQL Database has automated backups enabled by default. Long-term retention (LTR) policies can be configured to keep backups for up to 10 years. Option A is wrong because SQL Server Agent is not available in Azure SQL Database.

Option C is wrong because Azure Backup is for IaaS VMs, not PaaS databases. Option D is wrong because elastic jobs are for scheduling tasks across multiple databases, not for backup automation.

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

Option C is correct because 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 →
123
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.
AnswerB

Automatic failover reduces failover time by eliminating manual steps; read-scale failover does not affect failover time but helps read workloads.

Why this answer

Option D is correct because creating a failover group with automatic failover policy reduces manual steps and failover time. Option A is incorrect because scaling up the primary does not improve failover time. Option B is incorrect because manual failover is slower than automatic.

Option C is incorrect because cross-region VNet peering may add latency but failover time is more dependent on synchronization and DNS propagation.

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

Option B is correct because you can manually revert the plan correction via the Azure portal or T-SQL. Option A is wrong because disabling automatic tuning would prevent future corrections but not revert the current one. Option C is wrong because Query Store retains plans but reverting requires explicit action.

Option D is wrong because index recreation is not related to plan correction.

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

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

You have an Azure SQL Managed Instance in the East US region. To meet a 1-hour RPO and 2-hour RTO, you configure a failover group with a secondary in West US using automatic failover. During a test, you notice that the RTO is consistently 10 minutes longer than required. What is the most likely cause?

A.The failover group uses Microsoft Entra ID authentication, which adds latency.
B.The secondary database is configured with asynchronous commit, causing delay.
C.The failover group has a grace period of 20 minutes configured.
D.The secondary database is not seeded and needs to be restored.
AnswerC

Correct: The grace period adds to RTO.

Why this answer

Option C is correct because the failover group's automatic failover includes a grace period (default 20 minutes) that delays failover to allow for transient issues. This grace period contributes to RTO. Option A is incorrect because synchronous commit is used in failover groups by default for the primary-secondary pair.

Option B is incorrect because the secondary is already seeded. Option D is incorrect because Microsoft Entra ID authentication does not significantly impact failover time.

Full explanation →
127
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 →
128
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

Option A is correct because 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 →
129
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

Option C is correct because after a manual failover, the original primary becomes a secondary. To bring it back, you must first remove it from the failover group (if it's still listed), then re-add it as a secondary. Option A is wrong because automatic failover is not configured.

Option B is wrong because you should not create a new instance. Option D is wrong because you should perform a planned failover to restore the original primary, but only after re-adding it.

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

Option C is correct because auto-failover groups allow failover to a secondary region with minimal data loss when using synchronous replication. Option A is wrong because read scale-out does not provide DR. Option B is wrong because zone redundancy provides availability within a region, not cross-region.

Option D is wrong because backup redundancy does not provide failover.

Full explanation →
131
MCQmedium

You manage an Azure SQL Database named SalesDB that is used by a sales application. The application connects using a SQL login named 'sales_user' with a password. Recently, the security team discovered that 'sales_user' has been compromised. They have reset the password in Azure SQL Database. However, the application continues to connect successfully using the old credentials. You suspect the application might be caching the password. The security team wants to immediately revoke access for the compromised login and ensure that only a new login with a complex password is used. You also want to minimize downtime. What should you do first?

A.Revoke the CONNECT permission from 'sales_user' using REVOKE CONNECT FROM sales_user; then create a new login and update the application connection string.
B.Change the password again and ensure the application is restarted to clear the cache.
C.Enable auditing to monitor future logins and leave the login as is.
D.Drop the 'sales_user' login using DROP LOGIN sales_user; then create a new login and update the application.
AnswerA

Revoking CONNECT immediately blocks the login without deleting it, allowing time to update.

Why this answer

Option A is correct because immediately revoking the CONNECT permission from the compromised login 'sales_user' terminates any existing active sessions and prevents new connections using the old credentials, without deleting the login object. This allows you to create a new login with a complex password and update the application connection string with minimal downtime, as the database and other server-level objects remain intact.

Exam trap

The trap here is that candidates often assume dropping the login (Option D) is the most secure and immediate action, but they overlook that revoking CONNECT permission achieves the same security goal with less risk of breaking dependent objects and allows for a smoother transition to a new login.

How to eliminate wrong answers

Option B is wrong because simply changing the password again does not invalidate already established connections; the application may still be using a cached connection pool with the old password, and restarting the application does not guarantee that all cached credentials are cleared at the SQL Server level. Option C is wrong because enabling auditing only logs login attempts but does not revoke access; the compromised login would still be able to connect. Option D is wrong because dropping the login immediately terminates all active connections and removes the server-level principal, which could cause longer downtime if the application connection string is not updated simultaneously, and it also removes any associated permissions that might need to be recreated.

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

Option C is correct. After a forced failover, the original primary becomes unavailable. To rejoin it without data loss, you must remove the failed primary from the failover group, drop it, and then add a new secondary database (which will be seeded from the current primary).

Restoring from backup (A) will cause data loss. Re-synchronizing (B) is not possible as the original primary is in a broken state. Forcing replication (D) is not a valid operation.

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

This combination causes the session to be killed if it cannot acquire a lock within 5 seconds.

Why this answer

Option A is correct because setting the DEADLOCK_PRIORITY to LOW and using a lock timeout of 5 seconds will cause the blocking session to be killed if it exceeds the timeout. Option B is wrong because resource governor is not available in Managed Instance at the database level. Option C is wrong because read committed snapshot isolation does not kill blocking processes.

Option D is wrong because changing transaction isolation level does not automatically terminate processes.

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

Option D is correct because the Top Resource Consuming Queries report in Query Store shows cumulative CPU time, duration, and other metrics, allowing easy identification. Option A is wrong because sys.dm_exec_query_stats shows current cached plans but not cumulative over a specific time window. Option B is wrong because the Regressed Queries report shows plan changes, not CPU consumption.

Option C is wrong because sys.dm_exec_requests shows currently running queries, not historical cumulative use.

Full explanation →
135
MCQmedium

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

A.Hyperscale tier with 4 vCores
B.Provisioned tier with General Purpose and 2 vCores
C.Serverless tier with General Purpose and auto-pause enabled
D.DTU-based elastic pool with 200 DTUs
AnswerC

Serverless tier auto-scales and auto-pauses, reducing costs for variable workloads.

Why this answer

Option C is correct because the Serverless tier with General Purpose and auto-pause enabled is the most cost-effective choice for a pool of 50 databases with varying workloads. Serverless automatically scales compute based on demand and pauses databases during inactivity, charging only for storage and used vCores, which minimizes cost while meeting baseline performance requirements.

Exam trap

The trap here is that candidates often assume the Provisioned tier (Option B) is always cheaper for predictable workloads, but they overlook that the Serverless tier with auto-pause can dramatically reduce costs for variable or idle databases, which is the key requirement in this scenario.

How to eliminate wrong answers

Option A is wrong because the Hyperscale tier is designed for very large databases (up to 100 TB) with high throughput and rapid scaling, which is over-provisioned and unnecessarily expensive for 50 databases with varying workloads that only need baseline performance. Option B is wrong because the Provisioned tier with General Purpose and 2 vCores incurs continuous compute charges even when databases are idle, leading to higher costs compared to serverless for intermittent or variable workloads. Option D is wrong because a DTU-based elastic pool with 200 DTUs uses a fixed resource model that cannot scale down to zero during inactivity, and DTU pools are generally less cost-efficient than vCore-based serverless for workloads with significant idle periods.

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

Option B is correct because SQL Server on Azure VMs supports Always On Availability Groups, which provide automatic failover with no shared storage. Option A (Failover Cluster Instance) requires shared storage. Option C (Log shipping) does not provide automatic failover.

Option D (Database mirroring) is deprecated and not recommended.

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

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

Option B is correct because the failover group listener provides a constant connection endpoint. Option D is correct because the failover group orchestrates replication and failover. Option E is correct because the secondary database must be seeded and synchronized.

Option A is wrong because geo-redundant backup is not used during failover. Option C is wrong because Traffic Manager is not needed when using failover groups.

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

The 'autoPauseDelay' property is used for serverless databases. For serverless, 'minCapacity' is also required. The snippet is missing the 'sku' property with 'Serverless' tier.

Without the serverless tier, auto-pause is not available. Option A is wrong because zone redundancy does not affect auto-pause. Option B is wrong because the properties are correct but the tier is missing.

Option D is wrong because the API version supports serverless.

Full explanation →
140
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
AnswerA

Provides IO statistics per database.

Why this answer

Option A is correct because sys.dm_db_io_stats provides IO statistics per database. Option B is wrong because sys.dm_exec_query_stats provides query execution stats but not IOPS specifically. Option C is wrong because sys.dm_db_index_usage_stats provides index usage, not IOPS.

Option D is wrong because sys.dm_os_performance_counters provides OS-level counters, not per-query IOPS.

Full explanation →
141
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 is available in both General Purpose and Business Critical tiers (though for Business Critical it is used differently). Option A is incorrect because it is supported in both tiers.

Option C is incorrect because there is no additional charge for zone-redundant configuration in General Purpose tier; Business Critical has additional cost. Option E is incorrect because zone-redundant configuration does not require a failover group.

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

Option B is correct because Azure Monitor alerts can be configured to trigger an Automation runbook when a metric (like CPU) crosses a threshold. Option A is for analyzing logs, not triggering actions. Option C is for server monitoring, not directly triggering runbooks.

Option D is a query language, not a service.

Full explanation →
143
MCQmedium

What does this query return?

A.All security audit events for the database.
B.Failed login events at the server level.
C.Failed login attempts to the database, including client IP and application name.
D.Successful login events with client IP and application name.
AnswerC

LGIF indicates login failure; the query returns the specified fields for those events.

Why this answer

The query returns failed login attempts to the database, including client IP and application name. This is because the query filters for events where `action_id` = 'LGIF' (login failed) and `state` = 'ABORT' (indicating the login was aborted), and selects the `client_ip` and `application_name` columns from the `sys.dm_exec_sessions` DMV joined with `sys.dm_exec_connections` to capture client connection details. The `state` = 'ABORT' specifically identifies failed attempts, not successful logins or server-level events.

Exam trap

The trap here is that candidates confuse the `state` = 'ABORT' filter with a general session state indicator, assuming it means the session was aborted for any reason (e.g., user disconnect), rather than recognizing it specifically denotes a failed login attempt in the context of `action_id` = 'LGIF'.

How to eliminate wrong answers

Option A is wrong because the query specifically filters for login failures (`action_id` = 'LGIF' and `state` = 'ABORT'), not all security audit events, which would include a broader set of actions like DDL changes, permission changes, or successful logins. Option B is wrong because the query uses `sys.dm_exec_sessions` and `sys.dm_exec_connections`, which are database-level DMVs that capture session and connection information for the current database context, not server-level login events (which would require server-scoped views like `sys.dm_exec_sessions` at the server level or the `sys.server_principals` catalog view). Option D is wrong because the query filters for `state` = 'ABORT', which indicates a failed login, not a successful one; successful logins would have a different `state` value (e.g., 'CONNECTED' or no abort state).

Full explanation →
144
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 →
145
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

Option D is correct because geo-replication with a secondary in another region provides RPO of seconds and RTO of minutes, and using General Purpose tier minimizes cost. Option A is wrong because it doesn't cover regional DR. Option B is wrong because Managed Instance zones are not available in all regions.

Option C is wrong because it's expensive and might not meet RTO.

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

Option A is correct because 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 →
147
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

Option A is correct because failover groups support Azure SQL Managed Instance and provide automatic failover, a readable secondary, and minimal data loss with a synchronization mode that prioritizes data durability. Option B (geo-replication) is for single databases and elastic pools, not managed instances. Option C (auto-failover groups with active geo-replication) is redundant wording; failover groups are the feature.

Option D (log shipping) is not built-in for managed instances and does not provide automatic failover or readable secondary.

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

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

Option A is correct because 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 →
150
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 →
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 →