Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 376–450

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

Take a mock examExam hub
Page 5

Page 6 of 13

Page 7
376
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
377
MCQmedium

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

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

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

Why this answer

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

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

Full explanation →
378
Multi-Selectmedium

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

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

Correct.

Why this answer

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

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

Full explanation →
379
MCQhard

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

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

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

Why this answer

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

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

Exam trap

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

Full explanation →
380
Multi-Selectmedium

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

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

The build pipeline compiles the schema changes into artifacts.

Why this answer

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

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

Full explanation →
381
Multi-Selectmedium

Which TWO actions should you take to minimize downtime during a planned failover of an Azure SQL Database failover group? (Choose two.)

Select 2 answers
A.Scale up the primary database to a higher service objective.
B.Terminate all active connections to the primary database.
C.Ensure the failover group is configured for manual failover.
D.Disable automatic failover on the failover group.
E.Perform a planned failover from the primary to the secondary.
AnswersC, E

Manual failover allows you to control the timing.

Why this answer

For a planned failover of an Azure SQL Database failover group, you should ensure the failover is manual (C) and then initiate a planned failover (E). This approach gives you control over the timing and ensures no data loss because the secondary is fully synchronized before the switch. Option A (scaling up) does not reduce downtime during failover; it affects performance but not failover behavior.

Option B (terminating connections) is unnecessary because the failover process handles connection redirection. Option D (disabling automatic failover) is not required for a planned failover and could actually increase risk if an unplanned event occurs.

Full explanation →
382
Multi-Selectmedium

You are implementing automation for an Azure SQL Database. Which TWO scenarios are appropriate for using elastic database jobs? (Choose two.)

Select 2 answers
A.Scaling up a database when CPU usage is high
B.Checking data consistency across multiple databases
C.Creating logins and users across multiple databases
D.Rebuilding indexes across all databases in an elastic pool
E.Performing a full database backup
AnswersB, D

Data consistency checks can be run across databases.

Why this answer

Elastic database jobs are designed for executing administrative tasks across multiple databases in Azure SQL Database. Checking data consistency (B) and rebuilding indexes (D) are common maintenance tasks that benefit from automation across many databases. Option A is incorrect because scaling up a database is handled by Azure's automatic scaling features, not elastic jobs.

Option C is incorrect because creating logins and users is better managed using contained database users or Azure AD authentication for security and simplicity. Option E is incorrect because full database backups are automatically managed by Azure SQL Database, so a job is unnecessary.

Full explanation →
383
MCQeasy

You need to create a disaster recovery plan for an Azure SQL Database that has an RPO of 1 hour and an RTO of 12 hours. Which solution should you use?

A.Failover group with automatic failover.
B.Geo-restore of automated backups.
C.Active geo-replication.
D.Zone-redundant configuration.
AnswerB

Meets the RPO and RTO requirements at lower cost.

Why this answer

Geo-restore of automated backups can achieve an RPO of 1 hour (backups are taken every 5-10 minutes, allowing point-in-time restore within that window) and an RTO of up to 12 hours, meeting the stated requirements. Option A is incorrect because failover groups with automatic failover provide much lower RPO (seconds to minutes) and RTO (minutes), which exceeds the required thresholds and typically incurs higher cost. Option C is incorrect because active geo-replication also offers very low RPO (seconds) and RTO (minutes), exceeding requirements.

Option D is incorrect because zone-redundant configuration only protects against an availability zone failure within a region, not against a regional disaster.

Full explanation →
384
MCQhard

Refer to the exhibit. You are configuring an Azure SQL Database security alert policy. What is the most significant misconfiguration?

A.The retentionDays is set to 0, which is not supported
B.Some alerts are disabled, which could leave the database vulnerable
C.Emailing account admins may not be sufficient for notification
D.Multiple email addresses are specified, which may cause duplicate notifications
AnswerA

Retention days must be between 1 and 90.

Why this answer

The most significant misconfiguration is setting `retentionDays` to 0 because Azure SQL Database security alert policies require a retention period between 1 and 365 days. A value of 0 is invalid and will cause the policy to fail validation or be rejected, meaning the alert policy cannot be created or updated. This directly prevents the security alerts from being configured at all, making it a critical blocking issue.

Exam trap

The trap here is that candidates focus on operational concerns like notification methods or alert enablement, but the exam tests knowledge of Azure's specific API validation rules, where an invalid parameter value (retentionDays=0) is a hard failure that blocks the entire configuration.

How to eliminate wrong answers

Option B is wrong because disabling some alerts is a deliberate design choice to reduce noise; it does not inherently leave the database vulnerable if the disabled alerts are not relevant to the threat model. Option C is wrong because emailing account admins is a supported and sufficient notification method for security alerts, and the question asks for the most significant misconfiguration, not a best-practice improvement. Option D is wrong because specifying multiple email addresses is allowed and does not cause duplicate notifications; each address receives the same alert once, which is the intended behavior for broad notification.

Full explanation →
385
Multi-Selectmedium

You need to automate the backup strategy for an Azure SQL Database to meet a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour. Which THREE features should you combine? (Choose three.)

Select 3 answers
A.Long-term retention (LTR) backup policy.
B.Active geo-replication.
C.Azure Backup service.
D.Auto-failover group.
E.Point-in-time restore (PITR).
AnswersA, B, D

LTR provides archival backups beyond the PITR retention.

Why this answer

Options A, B, and D are correct. Long-term retention (LTR) backup policy provides archival backups for compliance and point-in-time restore. Active geo-replication maintains a synchronized readable secondary database with a low RPO (typically less than 5 seconds).

Auto-failover groups automate failover and enable a low RTO (typically less than 1 hour). Together, these three features meet the RPO of 5 minutes and RTO of 1 hour. Option C (Azure Backup service) is not used for Azure SQL Database; it is for Azure VMs and on-premises workloads.

Option E (Point-in-time restore) can meet the RPO but does not provide automated failover and has a higher RTO, so it is insufficient alone.

Full explanation →
386
MCQhard

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

A.Apply Dynamic Data Masking (DDM) to the credit card column.
B.Implement Row-Level Security (RLS) to filter rows based on user role.
C.Implement Always Encrypted with deterministic encryption.
D.Enable Transparent Data Encryption (TDE).
AnswerA

DDM masks data for non-privileged users; db_owner sees unmasked data by default.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it allows you to obfuscate sensitive data in query results for non-privileged users (like db_datareader) while permitting users with elevated permissions (like db_owner) to see the unmasked data. DDM is applied at the column level and does not modify the underlying data; it simply masks the output based on the user's permissions. The db_owner role is exempt from masking by default, meeting the requirement exactly.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both provide role-based visibility, but Always Encrypted requires key management and does not support partial masking or role-based exemption without separate keys.

How to eliminate wrong answers

Option B is wrong because Row-Level Security (RLS) controls which rows a user can access based on a predicate function, not which columns are masked; it cannot hide the credit card number within a row. Option C is wrong because Always Encrypted with deterministic encryption encrypts data at rest and in transit, but it does not allow role-based masking—users with the encryption key see plaintext, while others see ciphertext, not a masked format. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide any per-column or per-user masking; it protects against unauthorized access to the physical files, not against authorized database users.

Full explanation →
387
MCQhard

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

A.Enable Always Encrypted with secure enclaves and use a column master key that supports enclave computations.
B.Implement row-level security (RLS) to filter rows based on user identity.
C.Change the encryption type from randomized to deterministic encryption.
D.Use Dynamic Data Masking (DDM) to mask the column for unauthorized users instead of encryption.
AnswerA

Secure enclaves enable rich computations on encrypted data, including pattern matching.

Why this answer

Always Encrypted with secure enclaves allows computations, including pattern matching (LIKE, equality, comparisons), on encrypted columns by using a trusted execution environment (e.g., Intel SGX). The column master key must support enclave computations (enclave-enabled key) to permit the SQL Server engine to offload operations to the enclave. This preserves encryption at rest and in transit while enabling rich query patterns.

Exam trap

The trap here is that candidates often confuse deterministic encryption (which enables equality) with the ability to perform pattern matching, or they mistakenly think Dynamic Data Masking or row-level security can substitute for encrypted search capabilities.

How to eliminate wrong answers

Option B is wrong because row-level security (RLS) controls which rows a user can see based on predicates, but it does not enable pattern matching on encrypted columns; the column remains encrypted and unsearchable. Option C is wrong because changing from randomized to deterministic encryption only enables equality searches (e.g., WHERE column = 'value'), not pattern matching (LIKE '%pattern%'), and deterministic encryption is more vulnerable to frequency analysis attacks. Option D is wrong because Dynamic Data Masking (DDM) only obfuscates data at query results for unauthorized users; it does not encrypt the column, so sensitive data is still stored in plaintext and accessible to privileged users, failing the core security requirement.

Full explanation →
388
MCQmedium

You run the above KQL query in Azure Monitor Log Analytics to investigate performance issues in SalesDB. What is the primary purpose of this query?

A.Identify queries with high average duration
B.Identify queries that have had plan changes
C.Find the most frequently executed queries
D.Compare query performance over different time intervals
AnswerA

Filters for avg_duration > 1000 ms and orders descending.

Why this answer

The query filters for queries with average duration > 1000 ms and orders by duration, identifying high-duration queries. Option B is wrong because plan changes are not detected. Option C is wrong because it does not sort by frequency.

Option D is wrong because it does not compare across time intervals.

Full explanation →
389
Multi-Selecthard

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Select 3 answers
A.Use Microsoft Entra ID authentication instead of SQL authentication.
B.Use Azure SQL Database firewall rules to restrict access to known IP addresses.
C.Enable Transparent Data Encryption (TDE) for all databases.
D.Enable public network access to allow flexible connectivity.
E.Grant db_owner role to developers for ease of management.
AnswersA, B, C

Entra ID provides stronger authentication and supports conditional access.

Why this answer

Microsoft Entra ID (formerly Azure AD) authentication provides centralized identity management, supports conditional access policies, and eliminates the need to manage SQL logins and passwords. It also enables features like multifactor authentication (MFA) and integration with Azure RBAC, which significantly reduces the risk of credential theft compared to SQL authentication.

Exam trap

The trap here is that candidates often confuse 'public network access' with 'flexible connectivity' and overlook that private endpoints or Azure service endpoints are the secure alternatives, while also mistakenly thinking that granting db_owner simplifies management without considering the security implications of over-privileged accounts.

Full explanation →
390
Multi-Selectmedium

Which THREE components are required to configure an auto-failover group for Azure SQL Database? (Choose three.)

Select 3 answers
A.Active geo-replication
B.A failover group name
C.Secondary logical server in a different region
D.Primary logical server
E.An availability group listener
AnswersB, C, D

The failover group defines the relationship and failover policy.

Why this answer

The correct components required to configure an auto-failover group for Azure SQL Database are a failover group name (which acts as the logical container), a primary logical server, and a secondary logical server in a different region. Active geo-replication (Option A) is not required because the auto-failover group itself manages the replication process. An availability group listener (Option E) is not needed; the failover group automatically provides a read/write listener endpoint.

Therefore, the correct choices are B, C, and D.

Full explanation →
391
MCQmedium

Your company uses Azure SQL Database with Active Geo-Replication for disaster recovery. During a routine failover drill, you observe that after failover to the secondary region, the application experiences significantly higher latency for write operations. The secondary database is in a different Azure region and has the same service objective. What is the most likely cause of the increased write latency?

A.Geo-replication introduces additional latency for all write operations.
B.The secondary database has a lower service objective than the primary.
C.The secondary database is not configured to accept write traffic.
D.The secondary database does not have a local read-scale replica configured.
AnswerD

After failover, the new primary may not have a readable secondary, so all read-write workloads hit the primary, increasing load and latency.

Why this answer

After failover to the secondary region, the new primary (formerly the secondary) is in a different Azure region. The secondary database did not have a local read-scale replica configured before failover, so the new primary lacks a read-scale replica to offload read traffic. This can cause resource contention and increased latency for write operations, as the database handles both reads and writes without dedicated replicas.

Option A is incorrect because geo-replication does not inherently add latency to all writes—only replicating writes asynchronously may cause slight delay, but not significant latency. Option B is incorrect because the service objective is stated to be the same. Option C is incorrect because after failover, the secondary becomes writable and accepts write traffic.

Full explanation →
392
Multi-Selectmedium

You are planning to deploy Azure SQL Database for a new application. The application requires high availability and the ability to scale out read workloads. You need to choose the appropriate deployment option and feature. Which TWO options should you select? (Choose two.)

Select 2 answers
A.Use an elastic pool to group databases for resource sharing.
B.Enable zone redundancy for the database.
C.Configure a failover group with a readable secondary.
D.Deploy a single Azure SQL Database with locally redundant backup storage.
E.Configure active geo-replication with a readable secondary.
AnswersC, E

Provides high availability and read scaling.

Why this answer

A failover group with a readable secondary provides both high availability and read-scale capabilities. The readable secondary allows read-only workloads to be offloaded, while automatic failover ensures business continuity during an outage.

Exam trap

The trap here is that candidates often confuse zone redundancy (which provides HA within a region but no read scaling) with geo-replication or failover groups (which provide both HA and read scaling), leading them to select option B instead of C or E.

Full explanation →
393
MCQhard

You have a 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 and that recommendations are automatically executed. What should you do?

A.Set the server-level automatic_tuning option to INHERIT and set database-level FORCE_LAST_GOOD_PLAN to ON
B.Set the database-level automatic tuning option to INHERIT
C.Set the database-level query_store_desired_state to ON
D.Set the server-level automatic_tuning option to OFF
AnswerA

Automatic tuning must be enabled at server level and FORCE_LAST_GOOD_PLAN must be ON for automatic execution.

Why this answer

To enable automatic tuning in Azure SQL Managed Instance, you must set the server-level automatic_tuning option to INHERIT (which allows the server to inherit the default Azure tuning behavior) and then set the database-level FORCE_LAST_GOOD_PLAN to ON. This configuration ensures that the automatic tuning system can both generate recommendations and automatically apply them (specifically the FORCE_LAST_GOOD_PLAN recommendation) without manual intervention.

Exam trap

The trap here is that candidates often confuse enabling Query Store (option C) with enabling automatic tuning, not realizing that Query Store is only a prerequisite and does not itself apply tuning recommendations automatically.

How to eliminate wrong answers

Option B is wrong because setting only the database-level automatic tuning option to INHERIT does not enable automatic execution of recommendations; it merely defers to the server-level setting, which by default is OFF, so no recommendations will be applied automatically. Option C is wrong because setting query_store_desired_state to ON only enables Query Store, which is a prerequisite for automatic tuning but does not itself enable automatic tuning or the automatic execution of recommendations. Option D is wrong because setting the server-level automatic_tuning option to OFF explicitly disables automatic tuning at the server level, preventing any recommendations from being applied automatically.

Full explanation →
394
MCQeasy

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

A.Create a contained database user mapped to the Microsoft Entra service principal.
B.Enable Always Encrypted and configure column master key with the service principal.
C.Add a server-level firewall rule with the application's IP address.
D.Create a SQL authentication login and user for the application.
AnswerA

This enables the service principal to authenticate to the database.

Why this answer

A contained database user mapped to a Microsoft Entra ID service principal allows the application to authenticate directly to the database using its Microsoft Entra identity, without requiring a SQL Server login. This is the correct approach because Azure SQL Managed Instance supports Microsoft Entra authentication for service principals, enabling token-based authentication from applications that authenticate with Microsoft Entra ID.

Exam trap

The trap here is that candidates often confuse network-level controls (firewall rules) or encryption features (Always Encrypted) with authentication mechanisms, or mistakenly think SQL authentication can be used with Microsoft Entra ID service principals, when in fact a contained user mapped to the service principal is required.

How to eliminate wrong answers

Option B is wrong because Always Encrypted with a column master key protects data at rest and in transit but does not provide authentication; it is a data encryption feature, not an identity or access control mechanism. Option C is wrong because a server-level firewall rule controls network access by IP address, not authentication; the application already needs to authenticate, and firewall rules do not grant database access to a service principal. Option D is wrong because SQL authentication uses a username and password stored in the database, which is not compatible with Microsoft Entra ID service principals; the application authenticates via Microsoft Entra ID, not SQL credentials.

Full explanation →
395
MCQmedium

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

A.Run a planned failover to ensure zero data loss.
B.Wait for the primary to come back online and then failover.
C.Remove the primary database from the failover group and then failover.
D.Run a forced failover accepting potential data loss.
AnswerD

Correct: Forced failover works even if primary is down.

Why this answer

A forced failover (also called an unplanned failover) is used when the primary database is completely unavailable, and you accept potential data loss. Option A is incorrect because a planned failover requires both the primary and secondary to be online to ensure zero data loss. Option B is incorrect because waiting for the primary to come back online is not an appropriate action when immediate failover is required due to catastrophic failure.

Option C is incorrect because you cannot remove the primary database from a failover group when it is down; the failover group must be failed over as a whole.

Full explanation →
396
MCQmedium

Your company uses Azure SQL Managed Instance and wants to automate the creation of new databases for each development sprint. Each database must be a copy of a template database with specific schema and data. You need to recommend an automation solution that minimizes manual steps and integrates with your CI/CD pipeline. What should you use?

A.Deploy an Azure Logic App with a SQL connector that triggers on a schedule.
B.Use the Azure portal's 'Create database from backup' feature.
C.Create an Elastic Database Job that runs CREATE DATABASE AS COPY OF...
D.Use an Azure DevOps pipeline with a PowerShell task that runs the 'az sql db copy' command or the Restore-AzSqlDatabase cmdlet.
AnswerC

Elastic Database Jobs (Elastic Jobs) support Managed Instance and can execute 'CREATE DATABASE AS COPY OF' on a schedule or trigger, allowing automation with CI/CD by invoking the job via REST API or Azure DevOps tasks.

Why this answer

For Azure SQL Managed Instance, automating database creation with CI/CD integration can be achieved using Elastic Database Jobs (now Elastic Jobs). These jobs can run T-SQL commands such as 'CREATE DATABASE AS COPY OF' to create copies of a template database. Elastic Jobs can be triggered programmatically via REST API or Azure DevOps tasks, enabling seamless integration into a CI/CD pipeline.

Option A (Logic App) does not support database copy operations. Option B (portal feature) requires manual steps. Option D uses PowerShell cmdlets ('az sql db copy' or 'Restore-AzSqlDatabase') that are specific to Azure SQL Database, not Managed Instance; the correct cmdlets for Managed Instance would be 'Restore-AzSqlInstanceDatabase' or T-SQL commands.

Therefore, option C is the most appropriate recommendation.

Full explanation →
397
MCQeasy

You need to automate the backup of an Azure SQL Database to a storage account in a different region for disaster recovery. What should you configure?

A.Azure Backup for SQL Server in Azure VM
B.Configure geo-redundant backup storage
C.Configure long-term retention (LTR) in the same region
D.Elastic Database Job to export to BACPAC
AnswerB

Azure SQL Database offers geo-redundant storage by default.

Why this answer

Azure SQL Database automatically includes geo-redundant backup storage (RA-GRS) that replicates backups to a paired region for disaster recovery. Option A is incorrect because Azure Backup for SQL Server in Azure VM is for SQL Server installed on VMs, not for Azure SQL Database. Option C is incorrect because long-term retention (LTR) in the same region retains backups within the same region and does not provide cross-region disaster recovery.

Option D is incorrect because Elastic Database Jobs are for scheduling T-SQL scripts across multiple databases, not for automating backup to a different region.

Full explanation →
398
Multi-Selecthard

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

Select 3 answers
A.Failover groups
B.Always On availability groups
C.Active geo-replication
D.Long-term backup retention
E.Geo-restore (point-in-time restore)
AnswersA, C, E

Failover groups provide automatic failover and a readable secondary.

Why this answer

Options A, C, and E are correct. Failover groups and active geo-replication are built-in disaster recovery features for Azure SQL Database that allow failover to a secondary region. Geo-restore (point-in-time restore to a different region) is also a valid DR method by restoring from geo-replicated backups.

Option B (Always On availability groups) is a feature for SQL Server on VMs or on-premises, not for Azure SQL Database. Option D (long-term backup retention) is a backup retention capability, not a disaster recovery method.

Full explanation →
399
Multi-Selectmedium

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

Select 2 answers
A.Rebuild all indexes to improve query performance.
B.Change the database recovery model to Simple.
C.Scale the database to a higher service tier to mitigate the issue.
D.Review the Query Store Top Resource Consuming Queries report.
E.Query sys.dm_exec_query_stats to find queries with high total_worker_time.
AnswersD, E

Identifies queries consuming the most resources historically.

Why this answer

To identify the root cause of high resource consumption, you should use diagnostic tools that analyze query performance. The Query Store's Top Resource Consuming Queries report (D) provides historical insight into which queries consumed the most resources. Additionally, querying sys.dm_exec_query_stats (E) allows you to find queries with high total_worker_time, indicating CPU-intensive queries.

Options A (rebuilding indexes) and B (changing recovery model) are corrective actions, not diagnostic. Option C (scaling to a higher service tier) is a reactive mitigation that does not identify the root cause. Therefore, options D and E are the correct prioritized actions.

Full explanation →
400
MCQmedium

Your company uses Azure SQL Database and requires that all connections use Azure Active Directory (now Microsoft Entra ID) authentication. You need to ensure that no SQL authentication logins exist in the database. What should you do?

A.Set the server property 'DisableSqlAuthentication' to true.
B.Set the database property 'contained database authentication' to 0.
C.Use the Azure portal to set the firewall rule to block SQL authentication.
D.Query sys.sql_logins to identify any SQL authentication logins and drop them.
AnswerD

This allows you to remove any existing SQL logins, enforcing Microsoft Entra-only authentication.

Why this answer

The only way to ensure no SQL authentication logins exist in an Azure SQL Database is to query the `sys.sql_logins` system catalog view to identify any SQL-authenticated logins and then drop them. Azure SQL Database does not support a server-level property like `DisableSqlAuthentication` to block SQL authentication; instead, you must explicitly remove SQL logins to enforce exclusive use of Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates confuse the server-level property 'DisableSqlAuthentication' (which exists only in Azure SQL Managed Instance) with Azure SQL Database, or they mistakenly think firewall rules can filter by authentication type, when in fact firewall rules only control IP-based access.

How to eliminate wrong answers

Option A is wrong because Azure SQL Database does not have a server property named 'DisableSqlAuthentication'; this property exists in Azure SQL Managed Instance but not in Azure SQL Database. Option B is wrong because setting 'contained database authentication' to 0 controls whether contained database users can be created, but it does not affect server-level SQL logins or prevent existing SQL logins from connecting. Option C is wrong because firewall rules control network access by IP address, not authentication method; blocking SQL authentication via the portal is not a supported operation—firewall rules cannot distinguish between SQL and Entra ID authentication.

Full explanation →
401
MCQhard

You are configuring workload management for an Azure SQL Database using the JSON exhibit above for a classifier named 'MyWorkloadClassifier'. The classifier is intended to assign high importance to queries from user 'User1' in the 'SalesDB' database. However, after deployment, you notice that queries from 'User1' are not getting the expected resource guarantees. What is the most likely reason?

A.The 'importance' property is set to 'high' but the classifier requires 'importance' to be an integer.
B.The 'memberName' in the context is not correctly formatted; it should be a single user or group name without a backslash.
C.The 'min_percentile_resource' value is too low to guarantee resources.
D.The classifier is not associated with a workload group.
AnswerB

The backslash is not a valid JSON escape; memberName should be a simple user name like 'User1'.

Why this answer

The most likely reason is that the classifier's context is incorrectly formatted. In Azure SQL Database workload management, the 'memberName' property within the classifier context must be specified as a single user or group name without a backslash. If the JSON exhibit contains a backslash (e.g., 'SalesDB\User1' instead of 'User1'), the classifier fails to match the user.

Option A is incorrect because 'importance' is a string ('high'), not an integer. Option C is incorrect because 'min_percentile_resource' is not the issue; resource guarantees depend on the workload group's allocation. Option D is incorrect because a classifier does not need to be associated with a workload group directly; it is defined at the database level.

Full explanation →
402
MCQmedium

Your team uses Azure SQL Database and wants to use Microsoft Entra ID authentication. You need to create a contained database user mapped to a Microsoft Entra ID application (service principal). Which T-SQL command should you use?

A.CREATE USER [app-name] FROM LOGIN [app-name];
B.CREATE LOGIN [app-name] FROM EXTERNAL PROVIDER;
C.CREATE USER [app-name] WITH PASSWORD = 'password';
D.CREATE USER [app-name] FROM EXTERNAL PROVIDER;
AnswerD

This creates a contained user mapped to an Entra ID identity.

Why this answer

`CREATE USER [app-name] FROM EXTERNAL PROVIDER` creates a contained database user mapped to a Microsoft Entra ID identity (user, group, or application) directly in the database, without requiring a server-level login. This is the required syntax for authenticating a service principal (application) in Azure SQL Database using Microsoft Entra ID authentication.

Exam trap

The trap here is that candidates confuse `CREATE LOGIN ... FROM EXTERNAL PROVIDER` (which creates a server-level principal) with `CREATE USER ... FROM EXTERNAL PROVIDER` (which creates a contained database user), leading them to select Option B when the requirement is specifically for a contained user.

How to eliminate wrong answers

Option A is wrong because `CREATE USER ... FROM LOGIN` maps a database user to an existing SQL Server login, not to a Microsoft Entra ID identity; it does not support external providers. Option B is wrong because `CREATE LOGIN ...

FROM EXTERNAL PROVIDER` creates a server-level login for an Entra ID identity, but the question asks for a contained database user, not a login; contained users do not require a server login. Option C is wrong because `CREATE USER ... WITH PASSWORD` creates a SQL authentication user with a password, which does not use Microsoft Entra ID authentication at all.

Full explanation →
403
MCQhard

Your company has an Azure SQL Database configured with Active Geo-Replication between two regions. The primary database is experiencing performance degradation due to a sudden increase in write-intensive workloads. You need to minimize downtime and ensure no data loss. What should you do?

A.Enable read scale-out on the primary database
B.Initiate a forced failover to the secondary region
C.Increase the service tier of the primary database
D.Initiate a planned failover to the secondary region
AnswerD

Planned failover synchronizes all data before switching, ensuring zero data loss.

Why this answer

A planned failover (Option D) is correct because Active Geo-Replication supports a graceful, synchronous failover that promotes the secondary database to become the new primary without data loss. This is achieved by first replicating all pending transactions to the secondary, ensuring zero data loss, and then switching roles with minimal downtime. In contrast, a forced failover (Option B) would cause data loss because it does not wait for pending transactions to be replicated.

Exam trap

The trap here is that candidates confuse 'forced failover' (which causes data loss) with 'planned failover' (which ensures zero data loss), and they overlook that scaling the service tier does not address the need to minimize downtime during an active degradation.

How to eliminate wrong answers

Option A is wrong because enabling read scale-out on the primary database only offloads read-only queries to a secondary replica, but does not address write-intensive performance degradation or provide a failover mechanism. Option B is wrong because initiating a forced failover (also called an unplanned failover) will cause data loss, as it does not wait for pending transactions to be replicated to the secondary, violating the 'no data loss' requirement. Option C is wrong because increasing the service tier of the primary database (e.g., from S2 to S3 or from Standard to Premium) can improve performance but does not minimize downtime during an ongoing degradation; it requires a scaling operation that may take time and does not leverage geo-replication for high availability.

Full explanation →
404
MCQeasy

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

A.Use sys.dm_exec_query_stats to view current performance.
B.Review the Azure Monitor alert details.
C.Query the Query Store to compare query performance before and after the plan change.
D.Check the automatic tuning log in the Azure portal.
AnswerC

Query Store tracks performance over time, allowing comparison.

Why this answer

Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because sys.dm_exec_query_stats gives current performance metrics but does not provide historical comparison. Option B is wrong because Azure Monitor alert details only notify that a regression occurred, not the performance improvement.

Option D is wrong because the automatic tuning log shows actions taken but not detailed performance metrics for comparison.

Full explanation →
405
MCQmedium

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

A.Initiate an unplanned failover from the primary to the secondary.
B.Delete the secondary database and create a new one in the primary region.
C.Initiate a planned failover from the primary to the secondary.
D.Create a new secondary database in the same region as the primary.
AnswerA

Correct. In a full outage, you must perform an unplanned failover to promote the secondary to primary. This is the only way to restore service, though some data loss may occur.

Why this answer

During a full outage, the primary is offline, so a planned failover (which requires connectivity to the primary to synchronize) is not possible. The only option to fail over with minimal data loss is to initiate an unplanned (forced) failover, which will make the secondary the new primary. Although there may be some data loss from transactions not yet replicated, this is the best available option.

Deleting the secondary or creating a new secondary does not fail over.

Exam trap

The trap is assuming a planned failover is always preferred for minimal data loss, but in a full outage, unplanned failover is the only option.

Full explanation →
406
MCQeasy

You are designing a new Azure SQL Database for an e-commerce application. The database will contain sensitive customer data and must be isolated from other tenants. Which deployment option provides the highest level of resource isolation?

A.Elastic pool
B.Hyperscale
C.Managed Instance
D.Single database
AnswerC

Correct. Managed Instance offers dedicated compute, storage, and network isolation, providing the highest level of resource isolation.

Why this answer

Azure SQL Managed Instance provides the highest level of resource isolation among the listed options. It runs as an isolated instance with its own dedicated set of CPU, memory, and storage resources, and it is deployed into a dedicated virtual network, ensuring network isolation as well. This makes it ideal for scenarios requiring strict tenant separation, such as an e-commerce application handling sensitive customer data.

In contrast, a single database in Azure SQL Database shares some underlying resources at the logical server level and does not offer the same degree of network isolation.

Exam trap

The key trap is that candidates may assume a single database offers the highest isolation because it is not part of a pool, but they overlook that Managed Instance provides dedicated compute, memory, and network isolation with its own virtual network, making it more suitable for strict tenant isolation requirements.

How to eliminate wrong answers

Option A is wrong because an elastic pool shares resources (DTU or vCore) among multiple databases, which can lead to resource contention and reduced isolation between tenants. Option B is wrong because Hyperscale is a service tier designed for large, scalable databases but still operates within a shared infrastructure model (e.g., page servers and log service) that does not guarantee the same level of resource isolation as a single database. Option C is wrong because Managed Instance provides instance-level isolation but is a Platform-as-a-Service (PaaS) offering that still shares underlying hardware resources with other instances in the same virtual cluster, whereas a single database can be provisioned on a dedicated logical server with no resource sharing.

Full explanation →
407
MCQmedium

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

A.Create an Azure Automation runbook that calls New-AzureRmSqlDatabase and trigger it from your CI/CD pipeline.
B.Create an ARM template that defines the database and deploy it from your CI/CD pipeline.
C.Set up an Elastic Database Job that runs a CREATE DATABASE statement.
D.Configure a SQL Server Agent job on the logical server to run a CREATE DATABASE statement.
AnswerB

ARM templates are the recommended way to provision Azure resources declaratively and can be deployed via Azure DevOps.

Why this answer

B is correct because ARM (Azure Resource Manager) templates are the recommended infrastructure-as-code approach for defining and deploying Azure SQL Databases in a repeatable, declarative manner. Integrating ARM template deployment into a CI/CD pipeline ensures consistent, version-controlled database creation as part of automated workflows, aligning with DevOps best practices.

Exam trap

The trap here is that candidates may confuse operational automation (e.g., runbooks, SQL Agent jobs) with infrastructure-as-code provisioning, mistakenly choosing a scripting or T-SQL approach instead of the declarative ARM template method that natively integrates with CI/CD pipelines.

How to eliminate wrong answers

Option A is wrong because Azure Automation runbooks using the deprecated New-AzureRmSqlDatabase cmdlet (AzureRM module) are not infrastructure as code; they rely on imperative scripting, lack declarative state management, and the AzureRM module is being replaced by Az PowerShell, making this approach outdated and less reliable for CI/CD integration. Option C is wrong because Elastic Database Jobs are designed for executing T-SQL scripts across multiple databases (e.g., schema maintenance, data updates), not for provisioning new databases; they cannot create a new database as part of a CI/CD pipeline. Option D is wrong because SQL Server Agent jobs run within the context of a single logical server and are not designed for infrastructure-as-code automation; they lack integration with CI/CD pipelines, version control, and declarative deployment, and are intended for administrative tasks like maintenance, not provisioning new databases from external triggers.

Full explanation →
408
MCQhard

You are a database administrator for a SaaS company that uses Azure SQL Database with elastic pools. The company has hundreds of databases (one per tenant). You need to automate the deployment of schema changes (e.g., adding new columns, creating indexes) across all tenant databases. The changes must be deployed in a rolling fashion to avoid affecting all tenants at once. The automation must track which databases have been updated and allow for rollback of individual tenant databases if needed. Additionally, the solution must integrate with Azure DevOps CI/CD pipelines. What should you do?

A.Use Azure Data Factory with a ForEach activity to execute stored procedures in each database.
B.Create an Azure Automation runbook that connects to each database sequentially and runs ALTER TABLE statements.
C.Configure SQL Agent jobs on each database to run the schema changes.
D.Develop an Azure SQL Database project in Visual Studio, generate a DACPAC, and use Elastic Database Jobs with a custom tracking table to apply the DACPAC to each tenant database in batches. Integrate with Azure DevOps to trigger the job after build.
AnswerD

Using a combination of Azure SQL Database project (DACPAC) for schema definition and Elastic Database Jobs for targeted deployment allows rolling updates per tenant. Azure DevOps can trigger the jobs.

Why this answer

Using a combination of Azure SQL Database project (DACPAC) for schema definition and Elastic Database Jobs for targeted deployment allows rolling updates per tenant. Azure DevOps can trigger the jobs. Option A is incorrect because Azure Data Factory is for data movement, not schema deployment.

Option B is incorrect because Azure Automation is not designed for multi-tenant schema deployment. Option C is incorrect because SQL Agent is not available in Azure SQL Database.

Full explanation →
409
MCQeasy

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

A.Bicep templates
B.SQL Server Data Tools (SSDT) database projects
C.T-SQL scripts
D.PowerShell scripts
AnswerA

Bicep is the native Azure IaC language for deploying Azure resources including SQL Database, firewall rules, and performance settings.

Why this answer

Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner.

Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Full explanation →
410
MCQhard

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

A.The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.
B.The Entra ID admin SID is invalid (all zeros).
C.The tenantId is missing in the Entra ID admin configuration.
D.The Entra ID admin type should be 'Group' not 'User'.
AnswerB

A zero GUID is not a valid SID for a user in Microsoft Entra ID.

Why this answer

The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Exam trap

The trap here is that candidates assume public network access disabled requires a firewall rule, but the actual cause is the invalid Entra ID admin SID, which is a subtle validation check that Azure performs regardless of network settings.

How to eliminate wrong answers

Option A is wrong because when publicNetworkAccess is Disabled, no firewall rules are required; the server can be created with an empty firewallRules array. Option C is wrong because the tenantId is not required in the Entra ID admin configuration; the tenant is inferred from the subscription context during deployment. Option D is wrong because the Entra ID admin type can be either 'User' or 'Group'; 'User' is valid and does not cause a deployment failure.

Full explanation →
411
MCQmedium

You are managing an Azure SQL Database that requires automated index maintenance. You want to use a solution that minimizes administrative overhead and leverages built-in Azure capabilities. Which approach should you recommend?

A.Enable automatic tuning for the database and configure the 'CREATE INDEX' and 'DROP INDEX' recommendations.
B.Deploy a third-party maintenance solution and connect it to the Azure SQL Database.
C.Create a custom PowerShell script and run it via Azure Automation Runbook on a schedule.
D.Schedule a SQL Agent job to rebuild indexes using a T-SQL script.
AnswerA

Automatic tuning handles index management automatically based on workload patterns.

Why this answer

Azure SQL Database's automatic tuning feature can automatically create and drop indexes based on workload patterns, minimizing administrative overhead and leveraging built-in Azure capabilities. Option B is incorrect because deploying a third-party solution would introduce additional complexity and cost, not leveraging built-in capabilities. Option C is incorrect because although Azure Automation can schedule scripts, it still requires custom scripting and maintenance, increasing overhead compared to fully automated built-in tuning.

Option D is incorrect because scheduling a SQL Agent job requires manual setup and is not a built-in automated feature of Azure SQL Database; SQL Agent is available only in managed instances, not in single databases or elastic pools, and requires custom T-SQL scripting.

Full explanation →
412
MCQeasy

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

A.Query Store with custom retention settings
B.SQL Server DMVs
C.Azure SQL Analytics solution in Log Analytics
D.Azure SQL Database Intelligent Insights
AnswerA

Correct. Query Store with custom retention settings provides historical query performance data and enables performance comparison before and after index changes.

Why this answer

Query Store captures historical query performance data and allows comparing performance before and after index changes, making it the correct choice for this requirement. SQL Server DMVs (Option B) only provide current state, not historical trends. Azure SQL Analytics solution (Option C) provides aggregated metrics but lacks per-query historical comparison.

Intelligent Insights (Option D) offers diagnostic analysis but not detailed historical query data.

Exam trap

Many candidates confuse Query Store with DMVs or Azure SQL Analytics. Remember that Query Store is specifically designed for historical query performance tracking and plan regression analysis.

Full explanation →
413
MCQeasy

Your Azure SQL Database has a recurring job that rebuilds indexes weekly. After a recent change, the job is taking much longer to complete. You suspect that the index fragmentation is higher than usual. What is the most efficient way to check index fragmentation across the database?

A.Query sys.indexes to check the fragmentation percentage.
B.Use the sys.dm_db_index_physical_stats dynamic management function.
C.Use SET SHOWPLAN_XML ON and run sample queries.
D.Use the sys.dm_db_missing_index_details DMV.
AnswerB

This DMF returns fragmentation details efficiently.

Why this answer

Sys.dm_db_index_physical_stats. This DMV provides fragmentation details (e.g., avg_fragmentation_percent) for all indexes in a database with minimal overhead when using limited scanning. Option A (sys.indexes) does not include fragmentation statistics.

Option C (SET SHOWPLAN_XML ON) shows query execution plans, not index fragmentation. Option D (sys.dm_db_missing_index_details) suggests new indexes to improve performance, but does not report existing index fragmentation.

Full explanation →
414
MCQmedium

You are responsible for security compliance of Azure SQL databases. You need to audit all successful and failed login attempts and store the audit logs in a Log Analytics workspace for analysis. You also want to detect potential brute-force attacks. What should you implement?

A.Configure Azure Policy to enforce auditing on all SQL databases in the subscription.
B.Enable SQL Vulnerability Assessment and schedule recurring scans.
C.Enable Azure SQL Auditing for the server, configure the audit log destination to Log Analytics, and enable Microsoft Sentinel for threat detection.
D.Enable Advanced Threat Protection (ATP) for Azure SQL Database.
AnswerC

This combination provides logging and analysis.

Why this answer

Azure SQL Auditing captures both successful and failed login attempts (audit logs) and can be configured to send them directly to a Log Analytics workspace for centralized analysis. Microsoft Sentinel, when enabled, provides built-in analytics rules to detect brute-force attacks by correlating failed login patterns across time and IP addresses, fulfilling the threat detection requirement.

Exam trap

The trap here is that candidates confuse Advanced Threat Protection (ATP) with the combination of auditing and Sentinel, assuming ATP alone covers login auditing and brute-force detection, but ATP does not capture all login attempts nor store them in Log Analytics for custom analysis.

How to eliminate wrong answers

Option A is wrong because Azure Policy enforces compliance rules (e.g., requiring auditing to be enabled) but does not itself capture login audit logs or detect brute-force attacks; it only ensures the auditing setting is applied. Option B is wrong because SQL Vulnerability Assessment identifies database misconfigurations and missing patches, not login attempts or brute-force patterns; it focuses on security vulnerabilities, not authentication events. Option D is wrong because Advanced Threat Protection (ATP) for Azure SQL Database detects anomalous activities like SQL injection or unusual access patterns, but it does not specifically audit all successful and failed login attempts nor store those logs in Log Analytics; ATP relies on telemetry separate from the audit log stream.

Full explanation →
415
MCQhard

You are the database administrator for an Azure SQL Managed Instance hosting a data warehouse workload. You notice that the storage space consumed by the database is significantly larger than expected. The database has multiple large tables with clustered columnstore indexes. You suspect that the columnstore indexes have become fragmented and that deleted rows are consuming space. You need to reclaim storage space with minimal impact on query performance during business hours. What should you do?

A.Perform an ALTER INDEX REBUILD on the affected columnstore indexes after business hours.
B.Perform an ALTER INDEX REORGANIZE with the COMPRESS_ALL_ROW_GROUPS option on the affected columnstore indexes.
C.Perform an ALTER INDEX REORGANIZE on the affected columnstore indexes.
D.Rebuild the entire database by creating a new database and copying data.
AnswerB

This online operation reorganizes and compresses all row groups, reclaiming space from deleted rows with minimal impact.

Why this answer

REORGANIZE with COMPRESS_ALL_ROW_GROUPS compresses all row groups, including those in the delta store, and removes deleted rows from columnstore indexes. This operation is online and can be performed during business hours with minimal impact on query performance, unlike a rebuild which is offline and resource-intensive. It directly addresses the fragmentation and deleted row space consumption in columnstore indexes.

Exam trap

The trap here is that candidates often assume any REORGANIZE is sufficient, but without COMPRESS_ALL_ROW_GROUPS, it does not address deleted rows or delta store row groups, so the space is not reclaimed.

How to eliminate wrong answers

Option A is wrong because ALTER INDEX REBUILD is an offline operation that requires exclusive locks and significant resources, causing major performance impact during business hours; it should be scheduled after hours. Option C is wrong because a standard ALTER INDEX REORGANIZE without COMPRESS_ALL_ROW_GROUPS only defragments compressed row groups but does not force compression of delta store row groups or remove deleted rows, so it may not reclaim the expected space. Option D is wrong because rebuilding the entire database is an extreme, unnecessary operation that causes prolonged downtime and data movement, far exceeding the minimal impact approach needed.

Full explanation →
416
MCQeasy

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

A.1 day
B.7 days
C.14 days
D.3 days
AnswerB

Minimum to restore within 7 days.

Why this answer

Point-in-time restore retention can be set from 1 to 35 days. To restore to any point within 7 days, you need at least 7 days retention. Option A (1 day) is insufficient.

Option C (14 days) is more than needed. Option D (3 days) is also insufficient.

Full explanation →
417
MCQmedium

You have an Azure SQL Database that is part of a failover group with automatic failover. The primary region experiences a complete outage. The failover group automatically fails over to the secondary region. After the primary region is restored, you need to ensure the database is operational in the primary region with minimal data loss. What should you do?

A.Initiate a manual failover of the failover group back to the primary region.
B.Wait for automatic failback to occur.
C.Restore the database from a geo-redundant backup.
D.Delete the failover group and recreate it with the original primary as the new primary.
AnswerA

Manual failback re-establishes the primary in the original region with zero data loss.

Why this answer

After automatic failover occurs, the database is now hosted in the secondary region. When the primary region is restored, you must initiate a manual failover to make the primary region the new primary again. This ensures minimal data loss because the failover group synchronizes data during the manual failover.

Option B is incorrect because Azure SQL Database does not support automatic failback; you must manually fail over. Option C is incorrect because restoring from geo-redundant backup would result in data loss and is not necessary. Option D is incorrect because deleting and recreating the failover group could cause data loss and is not the recommended procedure.

Full explanation →
418
MCQhard

You are the database administrator for a large e-commerce company. The company has an Azure SQL Database named SalesDB that stores sensitive customer data including credit card numbers and personal details. The security team has mandated the following requirements: 1. All customer credit card numbers must be encrypted at rest and in transit between the application and the database. The encryption keys must be stored in a hardware security module (HSM) managed by the company. 2. All access to the database must be authenticated using Microsoft Entra ID, and multi-factor authentication (MFA) must be enforced for all administrative users. 3. Any attempts to access the database from unusual geographic locations must be detected and automatically blocked for 24 hours. 4. All schema changes must be audited, and the audit logs must be sent to a central Log Analytics workspace for analysis. Currently, the database uses SQL authentication, no encryption, and no auditing. You need to implement the required security controls with minimal downtime. What should you do?

A.Enable Always Encrypted for the credit card columns with keys stored in Azure Key Vault. Keep SQL authentication but require users to use MFA via a VPN. Enable TDE for at-rest encryption.
B.Enable Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault. Configure Azure SQL Auditing to send logs to Log Analytics. Enable Microsoft Defender for SQL.
C.Use Dynamic Data Masking to mask credit card numbers. Enable TDE and use Azure SQL Auditing with a storage account. Enable Microsoft Defender for SQL.
D.Enable Always Encrypted for the credit card columns using Azure Key Vault Managed HSM for column master keys. Switch to Microsoft Entra ID authentication and configure Conditional Access policy to require MFA for administrators. Enable Microsoft Defender for SQL with anomaly detection and automatic blocking. Enable Azure SQL Auditing with Log Analytics destination.
AnswerD

Always Encrypted encrypts data at rest and in transit; Entra ID with MFA meets authentication; Defender for SQL detects and blocks anomalies; Auditing sends to Log Analytics.

Why this answer

It directly addresses all mandated requirements: Always Encrypted with Azure Key Vault Managed HSM ensures credit card numbers are encrypted at rest and in transit with customer-managed HSM keys; switching to Microsoft Entra ID authentication with a Conditional Access policy enforces MFA for administrators; Microsoft Defender for SQL provides anomaly detection that can automatically block access from unusual geographic locations for 24 hours; and Azure SQL Auditing configured to send logs to Log Analytics meets the central audit requirement. This combination provides the required security controls with minimal downtime as Always Encrypted can be enabled online.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with Always Encrypted, assuming TDE alone satisfies encryption at rest and in transit, but TDE only protects data at rest and does not encrypt data in transit or provide client-side encryption for sensitive columns like credit card numbers.

How to eliminate wrong answers

Option A is wrong because it keeps SQL authentication, which violates the requirement to use Microsoft Entra ID authentication, and requiring MFA via VPN is not a substitute for native Entra ID MFA enforcement; also, TDE alone does not encrypt data in transit between the application and database. Option B is wrong because TDE encrypts data at rest but does not encrypt data in transit or provide column-level encryption for credit card numbers, and it does not address the requirement for MFA or automatic blocking of unusual geographic locations. Option C is wrong because Dynamic Data Masking only obscures data from non-privileged users, it does not encrypt data at rest or in transit, and it does not meet the requirement for HSM-managed keys or MFA enforcement.

Full explanation →
419
MCQmedium

Your company uses Azure SQL Database Hyperscale tier for a large OLTP workload. You need to implement a disaster recovery solution that provides a readable secondary in a different Azure region with an RPO of less than 5 seconds and automatic failover. What should you configure?

A.Create an auto-failover group with a secondary in the paired region.
B.Enable geo-redundant backup storage and use geo-restore.
C.Configure active geo-replication to a secondary server in another region.
D.Create a named replica in the target region and configure application failover logic.
AnswerD

Named replicas in Hyperscale can be placed in different regions and used for failover with low RPO.

Why this answer

Hyperscale supports named replicas that can be created in a different region and can be used for failover. Option A is wrong because Hyperscale does not support auto-failover groups. Option B is wrong because Hyperscale does not support active geo-replication.

Option C is wrong because geo-restore does not provide a readable secondary.

Full explanation →
420
MCQhard

Refer to the exhibit. You are configuring an Azure Automation schedule for a runbook that backs up Azure SQL Databases. The runbook should run daily at midnight. However, the runbook runs twice a day. What is the most likely cause?

A.The startTime is set to a past date
B.The schedule does not specify a time zone
C.Both interval and schedule.interval are defined, causing two triggers per day
D.The intervalUnit is set to Minutes instead of Days
AnswerC

Duplicate interval definitions cause the schedule to fire twice.

Why this answer

The configuration has both 'interval' (1440 minutes = 1 day) and 'schedule.interval' (P1D = 1 day), causing the runbook to trigger twice daily. Option A is wrong because time zone doesn't cause double firing. Option B is wrong because the start time is set.

Option D is wrong because the interval unit is minutes, not days.

Full explanation →
421
MCQhard

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

A.DROP_INDEX
B.CREATE_INDEX
C.CORRECT_INDEX
D.FORCE_LAST_GOOD_PLAN
AnswerD

Identifies and forces the last good plan to avoid regressions.

Why this answer

FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Exam trap

The trap here is that candidates often confuse index tuning options (CREATE_INDEX, DROP_INDEX) with query plan regression correction, mistakenly thinking that creating or dropping indexes will fix a plan choice regression, when in fact FORCE_LAST_GOOD_PLAN is the specific feature designed for that purpose.

How to eliminate wrong answers

Option A is wrong because DROP_INDEX is an automatic tuning option that identifies and drops unused or duplicate indexes to improve write performance and reduce storage, but it does not address query plan regressions. Option B is wrong because CREATE_INDEX automatically creates missing indexes that improve query performance based on the workload, but it does not correct query plan choice regressions. Option C is wrong because CORRECT_INDEX is not a valid automatic tuning option in Azure SQL Database; the valid index-related options are CREATE_INDEX and DROP_INDEX only.

Full explanation →
422
MCQmedium

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

A.Increase the yearly retention to P10Y.
B.Change the weekOfYear to 10.
C.Increase the monthly retention to P120M.
D.Increase the weekly retention to P10W.
AnswerA

Yearly retention covers the 10-year requirement.

Why this answer

The current yearly retention is P3Y (3 years), which is insufficient for the 10-year compliance requirement. Increasing it to P10Y retains yearly backups for 10 years. Option B is incorrect because weekOfYear specifies which week's backup is retained for yearly retention, not the retention period.

Option C is incorrect because monthly retention at P120M (120 months = 10 years) would retain 120 monthly backups, but the requirement is for yearly retention, and monthly retention doesn't cover full 10-year compliance on its own. Option D is incorrect because weekly retention at P10W (10 weeks) is far less than 10 years.

Full explanation →
423
MCQhard

You are the Azure DBA for a global e-commerce company. The company uses Azure SQL Database for its product catalog. The database is in the Business Critical tier with 16 vCores and 1 TB of storage. During a major sales event, the application experiences severe performance degradation. Analysis reveals that the database is experiencing high write latency on the secondary replicas due to heavy read workload routed to them. The primary replica is not overloaded. The application uses read-only routing to direct queries to readable secondaries. The DBA needs to reduce write latency on the secondaries without affecting the read workload or incurring additional cost. What should the DBA do?

A.Scale up the service tier to Hyperscale to offload writes
B.Disable read-scale out for non-critical queries and use the primary for read-write
C.Add more replicas to distribute the read load
D.Remove the secondary replicas and use only the primary
AnswerB

Reducing read load on secondaries decreases write latency on replicas.

Why this answer

Disabling read-scale out for non-critical queries forces those queries to use the primary replica, reducing the read workload on the secondaries. This directly lowers the write latency on secondaries, as fewer reads compete for resources with the log apply process. The change does not affect the read workload for critical queries that still use read-only routing, and it incurs no additional cost.

Exam trap

The trap here is that candidates may assume adding more replicas or changing tiers is necessary, when the real solution is to selectively route read traffic away from secondaries to reduce contention on the log apply process without incurring cost.

How to eliminate wrong answers

Option A is wrong because scaling up to Hyperscale does not offload writes; Hyperscale uses a different architecture with a log service and page servers, but write latency on secondaries is not addressed by this tier change, and it would increase cost. Option C is wrong because adding more replicas would increase cost and, while it distributes the read load, it does not reduce the write latency on existing secondaries caused by heavy reads competing with log apply. Option D is wrong because removing secondary replicas eliminates read-scale out entirely, forcing all read workload to the primary, which could overload it and does not address the requirement to maintain the read workload.

Full explanation →
424
Multi-Selecteasy

Your company is deploying Azure SQL Database for a new application. The security policy requires that all queries be encrypted in transit and that the database be protected against accidental deletion. Which THREE configurations should you implement? (Select THREE.)

Select 3 answers
A.Set the minimum TLS version to 1.2
B.Configure firewall rules to allow only specific IP addresses
C.Enable Microsoft Entra ID authentication
D.Enable Transparent Data Encryption (TDE)
E.Enable a resource lock on the Azure SQL Database logical server
AnswersA, C, E

Ensures encrypted connections.

Why this answer

Setting the minimum TLS version to 1.2 ensures all connections are encrypted in transit. Enabling Microsoft Entra ID authentication provides secure identity-based access, a fundamental security practice even if not explicitly stated. A resource lock on the logical server prevents accidental deletion of the database and server.

Exam trap

Candidates often confuse encryption in transit (TLS) with encryption at rest (TDE) and may overlook the resource lock as a separate, required configuration for deletion protection. TDE does not protect against deletion, and TLS does not lock the resource.

Full explanation →
425
Multi-Selecteasy

You need to configure high availability for an Azure SQL Database that supports a critical application. The database must be resilient to a single compute node failure. Which TWO options should you consider? (Choose two.)

Select 2 answers
A.Configure Hyperscale read replicas.
B.Enable active geo-replication.
C.Create an auto-failover group.
D.Use the Business Critical service tier.
E.Enable zone redundancy.
AnswersD, E

Business Critical provides automatic failover on node failure.

Why this answer

Options D and E are correct. The Business Critical service tier provides synchronous replicas that automatically failover on node failure, ensuring high availability within a single region. Zone redundancy, when enabled in the Business Critical tier, places replicas across different availability zones, adding resilience to zone-level failures.

Option A (Hyperscale read replicas) are for read scaling, not high availability. Option B (active geo-replication) is for disaster recovery across regions, not single node failure. Option C (auto-failover group) is for managing failover of multiple databases, not directly for compute node resilience.

Full explanation →
426
MCQhard

You have an Azure SQL Database that uses the SQL Server Agent to run a daily maintenance job. The job fails intermittently with the error 'Login failed for user'. The job uses a SQL Server authentication login. What is the most likely cause and solution?

A.The database is in a failover group and the secondary is read-only; connect to the primary.
B.The login password has expired; update the password in the job step.
C.The job schedule is conflicting with another job; change the schedule.
D.The SQL Server Agent is not running; start the agent.
AnswerB

SQL Server password expiration can cause intermittent login failures.

Why this answer

The intermittent 'Login failed for user' error with a SQL Server authentication login strongly indicates a password expiration issue. Azure SQL Database enforces password expiration policies by default, and if the password for the SQL authentication login used by the job step has expired, the job will fail until the password is updated. This is the most likely cause because the failure is intermittent (occurring after the password expires) and the job uses SQL authentication, which is subject to password policies.

Exam trap

The trap here is that candidates may overlook password expiration as a cause for intermittent failures and instead focus on connectivity or agent issues, but the specific 'Login failed for user' error with SQL authentication points directly to credential expiration.

How to eliminate wrong answers

Option A is wrong because a failover group with a read-only secondary would cause connection failures when trying to write, but the error 'Login failed for user' is an authentication error, not a write-permission error; also, the job could be configured to connect to the primary listener. Option C is wrong because a schedule conflict would not produce a 'Login failed for user' error; it would typically result in a job being skipped or a concurrency error. Option D is wrong because if the SQL Server Agent were not running, the job would not run at all (not intermittently fail), and the error would be about the Agent service, not a login failure.

Full explanation →
427
Multi-Selectmedium

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

Select 3 answers
A.Create contained database users instead of server-level logins.
B.Assign users to custom database roles with specific permissions.
C.Add users to the db_datareader role.
D.Configure firewall rules to restrict IP addresses.
E.Grant permissions at the object level (e.g., SELECT on specific tables) rather than at the schema level.
AnswersA, B, E

Contained users reduce server-level privilege.

Why this answer

Contained database users are authenticated directly within the database, independent of the server-level logins. This aligns with the principle of least privilege by avoiding the need for server-level permissions, which would grant broader access across the server. In Azure SQL Database, contained users are the recommended approach for database-level access control, as they limit the blast radius of a compromised credential to a single database.

Exam trap

The trap here is that candidates may confuse network security controls (firewall rules) with database access controls, or assume that built-in roles like db_datareader are acceptable for least privilege, when in fact they grant excessive permissions.

Full explanation →
428
MCQmedium

You are reviewing an ARM template snippet for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' in resource group 'rg1'. The source database 'db1' is in the same server 'server1' and is currently active. What will be the result of deploying this template?

A.The deployment will fail because sourceDatabaseId is specified but not used.
B.It will create a copy of db1 in the same server.
C.It will create a new empty database with the specified service objective.
D.It will perform a point-in-time restore of db1 to the current time.
AnswerC

Default createMode creates an empty database.

Why this answer

The ARM template snippet specifies `createMode: Default` and a `requestedServiceObjectiveName`, but does not include a `sourceDatabaseId` or `restorePointInTime` property. With `createMode: Default`, Azure SQL Database creates a new empty database using the specified service objective, regardless of the presence of a `sourceDatabaseId` in the template (which is ignored when `createMode` is not set to `Copy`, `PointInTimeRestore`, or `Secondary`). Therefore, the deployment succeeds and creates a new empty database named 'db2' with the defined performance tier.

Exam trap

The trap here is that candidates assume specifying a `sourceDatabaseId` automatically triggers a copy or restore operation, but Azure SQL Database only uses that property when the `createMode` is explicitly set to `Copy`, `PointInTimeRestore`, or `Secondary`; otherwise, it is silently ignored.

How to eliminate wrong answers

Option A is wrong because `sourceDatabaseId` is not used only when `createMode` is `Default`; the deployment does not fail—it simply ignores the property. Option B is wrong because creating a copy requires `createMode: Copy` and a valid `sourceDatabaseId`; with `createMode: Default`, no copy operation occurs. Option D is wrong because a point-in-time restore requires `createMode: PointInTimeRestore` and a `restorePointInTime` property; neither is present in the template.

Full explanation →
429
MCQmedium

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

A.Query sys.dm_exec_requests with a filter on blocking.
B.Open the deadlock graph file in SQL Server Management Studio (SSMS).
C.Azure Monitor for SQL and view deadlock metrics.
D.Query Store and review the regressed queries.
AnswerB

SSMS can display deadlock graphs captured via extended events.

Why this answer

To analyze a deadlock graph captured via extended events, you should open the .xdl file in SQL Server Management Studio (SSMS), which provides a graphical representation of the deadlock, showing the processes involved, the resources, and the queries. Option B is correct. Option A is incorrect because sys.dm_exec_requests with a blocking filter shows current blocking, not historical deadlock details.

Option C is incorrect because Azure Monitor for SQL provides metrics but not the detailed deadlock graph. Option D is incorrect because Query Store tracks query performance over time but does not capture deadlock events or graphs.

Full explanation →
430
MCQmedium

Refer to the exhibit. A user reports being unable to connect to the database. What is the most likely cause?

A.The user account is locked out due to too many failed login attempts.
B.The user does not have permission to access the database.
C.The server firewall is blocking the IP address.
D.The user is using an incorrect password.
AnswerD

The error 'Password did not match' directly indicates an incorrect password.

Why this answer

The error logs show multiple login failures for user 'appuser' from IP 192.168.1.100, with reasons indicating password issues. The combination of error messages suggests the password is incorrect or expired.

Full explanation →
431
MCQeasy

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

A.Scale up the database to a higher service objective (e.g., S3).
B.Enable Query Store and run the Performance Dashboard.
C.Enable read scale-out to offload read queries.
D.Create nonclustered indexes on all tables.
AnswerA

Increases DTU limit, providing more resources.

Why this answer

Scaling up to a higher service objective (e.g., S3) increases DTUs, providing more resources to handle peak loads without requiring any application code changes. Option B is wrong: Query Store aids in performance monitoring and troubleshooting but does not directly improve performance. Option C is wrong: read scale-out offloads read-only workloads to a readable secondary, but the issue is general performance degradation, not specifically read-heavy.

Option D is wrong: creating nonclustered indexes may improve query performance but often requires application or query adjustments, and does not guarantee improvement without code changes.

Full explanation →
432
MCQhard

You are the database administrator for a large e-commerce company. The company uses Azure SQL Database for its product catalog, which is updated frequently during business hours. To maintain query performance, you have automated index maintenance using Elastic Database Jobs. The job runs every night at 2:00 AM and rebuilds indexes with a fill factor of 80. However, you notice that the job is taking longer than expected, often running until 6:00 AM, and sometimes it fails due to timeouts. You also observe that index fragmentation is still high after the job completes. The database is in the General Purpose service tier with 100 DTUs. Which action should you take to improve the situation?

A.Create a second Elastic Database Job to run at 4:00 AM to rebuild indexes that failed.
B.Change the fill factor to 70 to reduce future fragmentation.
C.Modify the job to reorganize indexes instead of rebuilding them, and use a lower degree of parallelism.
D.Increase the DTU level of the database to 200 DTUs to speed up the index rebuild.
AnswerC

Reorganizing indexes is an online operation that uses fewer resources and can complete faster; reducing parallelism further reduces resource contention, and the job is less likely to timeout.

Why this answer

Reorganizing indexes is a less resource-intensive operation than rebuilding, which reduces the job duration and avoids timeouts. Additionally, using a lower degree of parallelism (MAXDOP) prevents excessive resource contention, which is critical for a General Purpose database with only 100 DTUs. Reorganizing also addresses fragmentation without the overhead of a full rebuild, making it more suitable for the limited resources and nightly maintenance window.

Exam trap

The trap here is that candidates assume rebuilding indexes is always the best maintenance strategy, but for resource-constrained databases with frequent updates, reorganizing with controlled parallelism is more efficient and avoids timeouts.

How to eliminate wrong answers

Option A is wrong because creating a second job to rebuild failed indexes does not address the root cause of timeouts or high fragmentation; it only adds another maintenance window that may also fail under the same resource constraints. Option B is wrong because lowering the fill factor to 70 increases page splitting and fragmentation over time, exacerbating the problem rather than solving it; fill factor should be set based on update patterns, not as a fragmentation fix. Option D is wrong because increasing DTUs to 200 would speed up the rebuild but does not resolve the underlying issue of the job taking too long and failing; it also incurs additional cost without addressing the fragmentation persistence or the job's timeout configuration.

Full explanation →
433
MCQhard

Your company has a policy to automatically pause Azure SQL Databases during non-business hours to save costs. You need to implement this with minimal administrative overhead. What should you use?

A.Elastic Database Job that pauses the database
B.Serverless compute tier with auto-pause enabled
C.Azure Logic Apps with SQL connector to execute pause/resume
D.Azure Automation runbook with a schedule to pause/resume
AnswerB

Serverless automatically pauses during inactivity, meeting the requirement with no overhead.

Why this answer

The serverless compute tier can be configured to automatically pause the database after a period of inactivity, meeting the requirement with minimal overhead. Option A is incorrect because Elastic Database Jobs require creating a job to pause the database, adding overhead. Option C is incorrect because Azure Logic Apps with the SQL connector would require building a workflow, adding complexity.

Option D is incorrect because Azure Automation runbooks require scripting and maintenance, which is more overhead than the serverless auto-pause feature.

Full explanation →
434
MCQmedium

You have an Azure SQL Managed Instance that needs to be accessed from an on-premises application. The connection must be encrypted and the on-premises network uses an ExpressRoute circuit. You need to configure the managed instance to only accept connections from the on-premises network. What should you do?

A.Enable a public endpoint and restrict access using a firewall rule.
B.Configure a server-level firewall rule to allow the on-premises IP range.
C.Connect the on-premises network to the managed instance's virtual network using ExpressRoute and configure network security group (NSG) rules to restrict inbound traffic to the on-premises subnet.
D.Create a private endpoint for the managed instance and configure DNS.
AnswerC

ExpressRoute provides private connectivity, and NSGs control traffic within the VNet.

Why this answer

Azure SQL Managed Instance is deployed into a virtual network, and the most secure way to allow only on-premises traffic is to extend the on-premises network into that VNet via ExpressRoute (private peering) and then use Network Security Group (NSG) rules to restrict inbound connections to only the on-premises subnet. This ensures the connection remains private, encrypted (via the ExpressRoute circuit), and does not expose a public endpoint.

Exam trap

The trap here is that candidates often confuse SQL Managed Instance with Azure SQL Database, incorrectly assuming that server-level firewall rules or private endpoints apply to Managed Instance, when in fact Managed Instance relies entirely on VNet integration and NSG rules for network security.

How to eliminate wrong answers

Option A is wrong because enabling a public endpoint would expose the managed instance to the internet, contradicting the requirement to accept connections only from the on-premises network, and firewall rules on a public endpoint cannot guarantee private-only traffic. Option B is wrong because server-level firewall rules apply only to public endpoints or Azure SQL Database/Server, not to SQL Managed Instance, which is always deployed in a VNet and does not support server-level IP firewall rules for inbound traffic. Option D is wrong because creating a private endpoint for a managed instance is not supported—SQL Managed Instance already resides in a VNet and uses a native VNet endpoint; a private endpoint is used for Azure SQL Database or SQL Server, not for Managed Instance.

Full explanation →
435
MCQhard

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

A.Azure SQL Managed Instance
B.Azure SQL Database
C.SQL Server on Azure Virtual Machine
D.Azure SQL Database with elastic query
AnswerA

SQL Managed Instance is a PaaS offering that supports SQL Server Agent, Service Broker, and cross-database queries.

Why this answer

Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. These features are not available in Azure SQL Database, which is a PaaS offering with a more restricted surface area. SQL Server on Azure VM is IaaS and requires manual management, while elastic query in Azure SQL Database only supports cross-database queries across different databases, not the full Service Broker or Agent functionality.

Exam trap

The trap here is that candidates often confuse Azure SQL Database with Azure SQL Managed Instance, assuming that all PaaS SQL offerings support the same features, but Azure SQL Database deliberately omits instance-scoped features like Agent and Service Broker to maintain a multi-tenant architecture.

How to eliminate wrong answers

Option B is wrong because Azure SQL Database does not support SQL Server Agent jobs, Service Broker, or cross-database queries within the same instance; it uses elastic jobs and has limited cross-database query capabilities via elastic query. Option C is wrong because SQL Server on Azure Virtual Machine is an IaaS solution, not a PaaS option, and requires you to manage the underlying OS and SQL Server instance, including high availability and backups. Option D is wrong because Azure SQL Database with elastic query only enables cross-database queries across different databases in the same logical server, but it still lacks SQL Server Agent and Service Broker support, and is not a separate PaaS offering.

Full explanation →
436
MCQmedium

Your SQL Server is experiencing deadlocks. You enable trace flag 1222 to capture deadlock graphs in the error log. Where can you retrieve the deadlock information?

A.sys.dm_exec_sessions
B.sys.query_store_query_text
C.sys.dm_exec_requests
D.The SQL Server error log, viewable using sp_readerrorlog.
AnswerD

Trace flag 1222 outputs deadlock graphs to the SQL Server error log, viewable via sp_readerrorlog or similar tools.

Why this answer

Trace flag 1222 writes deadlock information to the SQL Server error log. This log can be read using sp_readerrorlog, xp_readerrorlog, or the Log File Viewer in SQL Server Management Studio. Option D refers to this error log, though retrieving it via sp_readerrorlog is the direct method; the original mention of sys.messages and sys.fn_get_audit_file is incorrect and replaced.

Exam trap

Candidates may confuse trace flag 1222 with Extended Events or assume deadlock info is accessible via DMVs like sys.dm_exec_requests. However, trace flag 1222 outputs specifically to the error log.

Full explanation →
437
MCQhard

You manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

A.Configure the maintenance window for the Managed Instance.
B.Use an Elastic Job agent to run a script that applies updates.
C.Schedule a manual patching using the Azure portal.
D.Use Azure Update Manager to schedule patching.
AnswerA

Managed Instance automatically applies updates during the configured maintenance window.

Why this answer

Azure SQL Managed Instance automatically applies OS and SQL Server engine updates during the user-configured maintenance window, minimizing downtime by performing patching during off-peak hours. Option B is wrong because Elastic Job agents are used for scheduling T-SQL jobs across multiple databases, not for patching the instance or OS. Option C is wrong because manual patching via the Azure portal is not an automated solution and would require manual effort and cause downtime.

Option D is wrong because Azure Update Manager is designed for Azure VMs and Arc-enabled servers, not for Azure SQL Managed Instance, which has its own built-in patching mechanism.

Full explanation →
438
MCQmedium

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

A.The secondary is fully synchronized with minimal lag.
B.The secondary role indicates a failover has occurred.
C.The secondary is 5 seconds behind, indicating a problem.
D.The replication is in the seeding phase.
AnswerA

Correct.

Why this answer

The replicationLag of 5 seconds is minimal and normal for active geo-replication, indicating the secondary is nearly synchronized. The replicationState 'CATCH_UP' confirms that the secondary is fully caught up with the primary. Options B, C, and D are incorrect: B is wrong because a secondary role is expected in geo-replication; C is wrong because 5 seconds lag is not a problem; D is wrong because the state is CATCH_UP, not SEEDING.

Full explanation →
439
MCQeasy

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

A.Configure a vulnerability assessment recurring scan and email the report.
B.Create an Azure Monitor alert rule for the 'SQL database threat detection' metric.
C.In the Microsoft Defender for SQL settings, enable 'Email notifications to admins and subscription owners'.
D.Enable SQL auditing and stream logs to a Log Analytics workspace.
AnswerC

This directly sends email alerts for detected threats.

Why this answer

Microsoft Defender for SQL includes a dedicated 'Email notifications to admins and subscription owners' setting under its threat detection policy. When enabled, this sends email alerts to Azure subscription owners and administrators whenever Defender detects suspicious activities such as SQL injection, brute-force attacks, or anomalous access patterns. This is the direct, built-in mechanism for email-based alerting on threat detections.

Exam trap

The trap here is that candidates often confuse the purpose of vulnerability assessment (periodic scanning) with real-time threat detection, or assume that Azure Monitor metric alerts are the correct way to receive email notifications for Defender for SQL alerts, when in fact the email notification is configured directly within the Defender for SQL settings.

How to eliminate wrong answers

Option A is wrong because vulnerability assessment recurring scans generate periodic reports on database vulnerabilities, not real-time email notifications for suspicious activity detection. Option B is wrong because there is no Azure Monitor metric named 'SQL database threat detection'; threat detection alerts are surfaced through Defender for SQL's own alerting system, not via Azure Monitor metric alerts. Option D is wrong because enabling SQL auditing and streaming logs to Log Analytics enables log collection and analysis, but does not by itself configure email notifications for suspicious activity; that requires an additional alert rule or action group.

Full explanation →
440
Multi-Selectmedium

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

Select 2 answers
A.Azure Sentinel
B.Log Analytics Workspace
C.Azure Monitor Alerts
D.Application Insights
E.Azure Advisor
AnswersB, C

Stores and queries diagnostic logs from SQL Database.

Why this answer

Options B and C are correct because Azure Monitor Alerts (C) can be configured to monitor performance metrics of Azure SQL Database and trigger actions when thresholds are exceeded. Log Analytics Workspace (B) collects and analyzes the diagnostic logs and metrics, enabling deeper analysis and alert rules. Option A (Azure Sentinel) is a SIEM tool for security monitoring, not primarily for performance alerts.

Option D (Application Insights) is for application-level telemetry, not database-specific performance monitoring. Option E (Azure Advisor) provides recommendations but does not generate real-time alerts.

Full explanation →
441
MCQmedium

A company uses Azure SQL Managed Instance for its line-of-business applications. They need to ensure that backups are retained for 35 days for compliance purposes. Which configuration should the DBA set?

A.Create a backup policy in Azure Backup Vault
B.Enable geo-redundant storage (GRS)
C.Set the backup retention period to 35 days in the backup settings
D.Configure long-term retention (LTR) policy
AnswerC

The backup retention can be set up to 35 days.

Why this answer

Azure SQL Managed Instance allows you to configure the backup retention period directly in the backup settings, with a maximum of 35 days for point-in-time restore (PITR). This meets the compliance requirement without needing additional services or policies.

Exam trap

The trap here is that candidates confuse long-term retention (LTR) with the standard backup retention period, assuming LTR is required for any retention beyond the default 7 days, when in fact the 35-day maximum is configurable directly in the backup settings.

How to eliminate wrong answers

Option A is wrong because Azure Backup Vault is used for backing up Azure VMs, files, and workloads, not for managing native SQL Managed Instance backups, which are handled internally by the SQL engine. Option B is wrong because geo-redundant storage (GRS) is a storage redundancy option for backup files, not a retention period setting; it does not control how long backups are kept. Option D is wrong because long-term retention (LTR) is for retaining backups beyond 35 days (e.g., years), not for setting a 35-day retention, and it requires additional configuration and costs.

Full explanation →
442
MCQhard

You are reviewing the encryption protector for an Azure SQL Server as shown in the exhibit. The server hosts multiple databases. You need to ensure that the databases are encrypted using the customer-managed key from Azure Key Vault. However, you find that the databases are not using this key. What is the most likely reason?

A.The key version '123456' is incorrect.
B.The key vault is in a different subscription and not accessible.
C.The databases still have the service-managed key as protector and need to be updated.
D.The 'serverKeyName' property is not set correctly.
AnswerC

Existing databases may not automatically switch to CMK.

Why this answer

The exhibit shows that the server's encryption protector is set to a customer-managed key from Azure Key Vault, but this setting only applies to newly created databases. Existing databases retain their original encryption protector, which is typically the service-managed key. To enforce the customer-managed key on existing databases, you must manually update each database's encryption protector using the ALTER DATABASE ENCRYPTION KEY statement or through the Azure portal.

Option C correctly identifies that the databases still have the service-managed key as protector and need to be updated.

Exam trap

The trap here is that candidates assume setting the server-level encryption protector automatically applies to all existing databases, but Azure SQL Database requires explicit per-database updates to change the protector.

How to eliminate wrong answers

Option A is wrong because the key version '123456' is a placeholder in the exhibit; if the key version were incorrect, the server would fail to set the encryption protector or show an error, but the exhibit shows the protector is set successfully. Option B is wrong because the exhibit does not indicate any cross-subscription access issues; if the key vault were in a different subscription and inaccessible, the server would display an error or the protector would not be set. Option D is wrong because the 'serverKeyName' property is correctly set to the customer-managed key name as shown in the exhibit; if it were incorrect, the server would not be able to use the key as the protector.

Full explanation →
443
MCQhard

An Azure SQL Database contains personally identifiable information (PII). You need to mask the PII columns from non-administrative users while allowing administrators to see the actual data. Which feature should you use?

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

DDM masks data for non-privileged users and can be bypassed for administrators.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it selectively obscures sensitive PII columns in query results for non-administrative users, while leaving the data unmasked for users with elevated permissions (e.g., db_owner or the UNMASK permission). This is achieved by defining masking rules on specific columns, such as email or phone number, without altering the underlying stored data.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking both hide data from the database engine, but DDM only masks output while Always Encrypted encrypts data at the client and prevents the server from ever seeing plaintext.

How to eliminate wrong answers

Option A is wrong because Always Encrypted encrypts data at the client side, preventing the database engine from seeing plaintext values, which would block administrators from reading the actual data unless they have the column encryption key. Option B is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (on disk), but does not control access or mask data for specific users during queries. Option D is wrong because Row-Level Security (RLS) restricts which rows a user can see based on a predicate function, but it does not mask or obfuscate column values within visible rows.

Full explanation →
444
MCQhard

You are migrating a 10-TB SQL Server database to Azure SQL Database Hyperscale. The source database has multiple filegroups and uses full-text indexing. The migration must be completed with minimal downtime. Which migration method should you use?

A.Set up transactional replication from on-premises to Azure
B.Use Azure Data Factory to copy data
C.Use the Azure SQL Migration extension in Azure Data Studio with online migration mode
D.Export a BACPAC file and import it to Azure
AnswerC

Supports online migration to Hyperscale with minimal downtime.

Why this answer

The Azure SQL Migration extension in Azure Data Studio with online migration mode is the correct choice because it supports minimal-downtime migrations for large databases (10 TB) with complex features like multiple filegroups and full-text indexing. It uses the Data Migration Service (DMS) to continuously replicate changes from the source to the target Azure SQL Database Hyperscale, allowing you to cut over with only a brief pause.

Exam trap

The trap here is that candidates often choose transactional replication (Option A) because it is associated with minimal downtime, but they overlook that it is not designed for one-time migrations of databases with full-text indexing and multiple filegroups, and it requires ongoing synchronization that is unnecessary for a migration cutover.

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema changes (e.g., adding replication triggers) and does not natively support full-text indexing or multiple filegroups without complex manual configuration, and it introduces ongoing replication overhead that is not ideal for a one-time migration. Option B is wrong because Azure Data Factory is a data integration tool for bulk copy operations, not a migration service; it cannot perform online (minimal-downtime) replication of a live database with transactional consistency, and it lacks support for full-text indexes and filegroup structures. Option D is wrong because exporting a BACPAC file for a 10-TB database would take an extremely long time, and the import process requires the database to be offline or in read-only mode, causing significant downtime; BACPAC also does not preserve full-text indexes or multiple filegroups in Azure SQL Database Hyperscale.

Full explanation →
445
MCQhard

Refer to the exhibit. The SalesDB database is experiencing log space full errors. Based on the exhibit, what is the most likely reason?

A.The database storage is almost full, preventing log growth
B.The transaction log is not being truncated, possibly due to an active transaction or replication
C.The log rate limit is being throttled due to high log IO percentage
D.The database should be scaled to BusinessCritical tier for faster log writes
AnswerB

High used log space with high write rate suggests truncation issue.

Why this answer

The exhibit shows a transaction log with very low free space (0.01%) and a high log used percentage (99.99%), but the data file has ample free space. This indicates the transaction log is not being truncated, likely due to an active transaction preventing log reuse or a replication scenario that marks log records as needed. In Azure SQL Database, log space is managed automatically, but long-running transactions or replication can block log truncation, causing log space full errors even when storage is not full.

Exam trap

The trap here is that candidates see 'log space full' and immediately think of storage capacity issues (Option A) or performance throttling (Option C), but the exhibit clearly shows the data file has free space, directing the focus to log truncation failure as the root cause.

How to eliminate wrong answers

Option A is wrong because the exhibit shows the data file has 99.99% free space, so database storage is not almost full; the issue is specifically with the transaction log, not overall storage. Option C is wrong because the log rate limit is a performance throttle that slows log writes but does not cause log space full errors; the log used percentage is high due to lack of truncation, not throttling. Option D is wrong because scaling to BusinessCritical tier improves IO performance but does not resolve log truncation issues; the root cause is an active transaction or replication blocking log reuse, not insufficient write speed.

Full explanation →
446
MCQeasy

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

A.SQL Server Agent
B.Elastic Database Jobs
C.Azure Automation Runbook
D.Azure Logic Apps
AnswerB

Elastic Database Jobs are specifically designed to run T-SQL scripts on a schedule across one or more Azure SQL databases.

Why this answer

Elastic Database Jobs (B) is the correct service for automating T-SQL script execution across Azure SQL Database on a recurring schedule. It is specifically designed for Azure SQL Database and Azure SQL Managed Instance, providing a job scheduler that can run T-SQL scripts against multiple databases, handle retries, and manage job history. SQL Server Agent is not available in Azure SQL Database (only in SQL Server on-premises or Azure SQL Managed Instance), making Elastic Database Jobs the appropriate choice for this PaaS scenario.

Exam trap

The trap here is that candidates confuse SQL Server Agent (available in Azure SQL Managed Instance) with Azure SQL Database (single database/elastic pool), mistakenly assuming Agent is available for all Azure SQL offerings, when in fact Elastic Database Jobs is the correct scheduler for the PaaS Azure SQL Database service.

How to eliminate wrong answers

Option A is wrong because SQL Server Agent is not available in Azure SQL Database (single database or elastic pool); it is only supported in SQL Server on-premises, Azure SQL Managed Instance, and SQL Server on Azure VMs. Option C is wrong because Azure Automation Runbooks are designed for PowerShell or Python workflows, not for direct T-SQL execution against Azure SQL Database; they would require additional modules and connection management, making them less suitable for simple recurring T-SQL scripts. Option D is wrong because Azure Logic Apps are orchestration services for integrating apps and data, not a native T-SQL scheduler; they can execute SQL queries via connectors but lack the built-in job scheduling, retry policies, and database-targeting features of Elastic Database Jobs.

Full explanation →
447
MCQeasy

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical service tier. The database is 2 TB. You need to ensure that the secondary replica in a different Azure region is readable for reporting queries. What should you configure?

A.Create an auto-failover group with a secondary in the paired region.
B.Enable zone-redundant backup storage.
C.Configure active geo-replication to a secondary server in the target region.
D.Use geo-restore from geo-redundant backups.
AnswerC

Active geo-replication provides a readable secondary database.

Why this answer

Active geo-replication creates a readable secondary database in a different region that can be used for reporting. Option A is wrong: auto-failover groups do not automatically provide a readable secondary; they are designed for failover, and while you can configure a readable secondary, active geo-replication is the primary method for cross-region read-scale. Option B is wrong because zone-redundant backup storage provides redundancy for backups, not a readable secondary.

Option D is wrong because geo-restore creates a new database from a backup and is not a continuously available readable replica.

Full explanation →
448
MCQmedium

You are designing a disaster recovery plan for an Azure SQL Database that is used by a critical application. The database is currently in the West US region. You need to ensure that if a regional outage occurs, the database can be failed over to another region with minimal data loss. The solution must also minimize costs for the secondary replica. Which deployment option should you recommend?

A.Configure active geo-replication to a secondary in East US using a lower service tier.
B.Deploy an auto-failover group with a secondary in East US using the same service tier.
C.Deploy a zone-redundant database in West US with a failover group to East US.
D.Use a failover group with a secondary in East US and enable zone redundancy on both.
AnswerA

Active geo-replication allows different service tiers for secondary, reducing costs.

Why this answer

Active geo-replication allows you to create a readable secondary database in a different region (East US) with a lower service tier, which reduces costs while still providing a disaster recovery target. In the event of a regional outage, you can manually initiate a failover to the secondary, and because replication is asynchronous, data loss is limited to the replication lag (typically a few seconds). This meets the requirement of minimal data loss and cost minimization.

Exam trap

The trap here is that candidates assume auto-failover groups are always the best choice for disaster recovery, but they fail to recognize that auto-failover groups require the secondary to have the same service tier, which conflicts with the cost minimization requirement.

How to eliminate wrong answers

Option B is wrong because deploying an auto-failover group with the same service tier on the secondary does not minimize costs; the secondary must match the primary's service tier for auto-failover groups, eliminating any cost savings. Option C is wrong because zone redundancy in West US only protects against zonal failures within the same region, not a regional outage; it does not provide a secondary in a different region for cross-region failover. Option D is wrong because enabling zone redundancy on both the primary and secondary increases costs unnecessarily and does not allow a lower service tier for the secondary, contradicting the cost minimization requirement.

Full explanation →
449
MCQmedium

You are configuring a failover group for a pair of Azure SQL Managed Instances in different regions for disaster recovery. The primary instance experiences a regional outage. You need to ensure that after a forced failover, the secondary instance automatically becomes the primary without any manual intervention and that applications can connect using the same listener endpoint. What should you configure?

A.Use Azure Traffic Manager with priority routing to the secondary instance.
B.Enable auto-failover groups on the primary instance.
C.Configure active geo-replication between the instances.
D.Create a failover group with automatic failover policy and add the databases.
AnswerD

A failover group with automatic failover provides a listener endpoint and automatic failover during an outage.

Why this answer

A failover group with automatic failover policy ensures that during a regional outage, the secondary database becomes the primary automatically and the listener endpoint remains unchanged. Option A is wrong because Azure Traffic Manager is not needed; the failover group listener provides the endpoint. Option B is wrong because 'enable auto-failover groups' is not a separate setting; a failover group with automatic failover policy is already the required configuration.

Option C is wrong because active geo-replication does not provide a single listener endpoint and requires manual failover.

Full explanation →
450
MCQmedium

Your company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance. You enable SQL Database auditing and configure a storage account for logs. However, you notice that some DELETE operations are not being audited. What could be the cause?

A.The diagnostic setting is configured incorrectly.
B.The storage account firewall is blocking the audit logs.
C.The audit is configured to capture only successful operations.
D.The database has Transparent Data Encryption (TDE) enabled.
AnswerC

Correct: Auditing can be set to capture both success and failure; if only success, some operations may not be logged if they fail.

Why this answer

Azure SQL Database auditing can be configured to capture only successful operations or only failed operations, or both. If the audit is set to capture only successful operations, DELETE operations that fail (e.g., due to permissions or constraint violations) will not be logged. The question states that some DELETE operations are missing, which aligns with a filter that excludes failed operations.

Exam trap

The trap here is that candidates assume all data modification operations are always audited by default, overlooking that Azure SQL Database auditing allows filtering by operation outcome (success/failure), which can cause specific operations to be omitted from logs.

How to eliminate wrong answers

Option A is wrong because the diagnostic setting controls the export of audit logs to destinations (e.g., storage, Event Hub, Log Analytics), not which operations are audited; a misconfigured diagnostic setting would cause all logs to be missing, not just some DELETE operations. Option B is wrong because the storage account firewall blocks the writing of audit logs at the network level, which would prevent all audit logs from being written, not selectively omit DELETE operations. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest and does not affect auditing behavior; TDE has no interaction with audit log capture.

Full explanation →
Page 5

Page 6 of 13

Page 7

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 →