Courseiva

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

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

Take a mock examExam hub
Page 5

Page 6 of 13

Page 7
376
MCQhard

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

A.Intelligent Insights report
B.Automatic Tuning recommendations
C.Azure Monitor metrics for the database
D.Query Store to review query execution plans and wait statistics
AnswerD

Query Store provides detailed query performance data for root cause analysis.

Why this answer

Option A is correct because Query Store captures query performance metrics and allows detailed analysis. Option B is incorrect because Intelligent Insights provides summaries, not detailed query analysis. Option C is incorrect because database-level metrics don't pinpoint queries.

Option D is incorrect because Automatic Tuning applies fixes but doesn't analyze root cause.

Full explanation →
377
Multi-Selecthard

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Select 3 answers
A.Log IO percentage (sys.dm_db_resource_stats)
B.Log backup frequency
C.Database size and growth rate
D.Wait statistics (sys.dm_os_wait_stats)
E.Query Store for query performance regressions
AnswersA, D, E

High log IO can indicate transaction throughput issues.

Why this answer

Options A, B, and D are correct. Wait statistics (A) show where queries are waiting. Query Store (B) tracks plan regression and performance.

Log IO (D) indicates transaction log throughput bottlenecks. Option C is wrong because it shows storage size, not performance. Option E is wrong because log backup frequency affects recovery, not performance.

Full explanation →
378
MCQmedium

You have an Azure SQL Database configured with active geo-replication. The primary region experiences an outage that lasts longer than the recovery time objective (RTO) of 5 minutes. You need to initiate a failover to the secondary region with minimal data loss. What should you do?

A.Initiate a planned failover to the secondary region using the Azure portal.
B.Configure an auto-failover group and let it fail over automatically.
C.Force a failover to the secondary region using a PowerShell script.
D.Use geo-restore to recover the database from the most recent geo-replicated backup.
AnswerA

Planned failover syncs data before failover, minimizing data loss.

Why this answer

Option B is correct because initiating a planned failover via the portal ensures the secondary becomes primary with minimal data loss by synchronizing data before failover. Option A is wrong because automatic failover groups are not configured here. Option C is wrong because geo-restore is for point-in-time recovery, not failover.

Option D is wrong because forced failover may cause data loss.

Full explanation →
379
MCQhard

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

A.Migrate the database to Business Critical tier.
B.Modify the batch jobs to use bulk insert with TABLOCK and batch inserts into smaller transactions.
C.Increase the managed instance storage to 2048 GB to improve log throughput.
D.Enable accelerated database recovery to reduce log I/O.
AnswerB

Minimally logged operations reduce log writes, staying within throughput limit.

Why this answer

Option C is correct because reducing the log write rate by batching transactions or using minimally logged operations (e.g., bulk insert with TABLOCK) can keep throughput under the limit. Option A is wrong because increasing storage doesn't affect log throughput. Option B is wrong because moving to Business Critical is not allowed.

Option D is wrong because enabling accelerated database recovery reduces version store I/O, not log throughput.

Full explanation →
380
MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

A.Full backups are taken every 12 hours and retained for 7 days.
B.Long-term retention (LTR) is set to 7 days.
C.Point-in-time restore (PITR) backups are retained for 7 days, and differential backups occur every 12 hours.
D.Transaction log backups are taken every 12 hours.
AnswerC

The properties directly set PITR retention and differential backup interval.

Why this answer

The ARM template configures the backup retention settings for Azure SQL Database. By default, Azure SQL Database automatically performs full backups every week, differential backups every 12 hours, and transaction log backups every 5–10 minutes. The configuration shown sets the point-in-time restore (PITR) retention period to 7 days, meaning you can restore the database to any point within the last 7 days.

Differential backups occur every 12 hours to support efficient PITR, but the retention setting directly controls how far back you can perform a point-in-time restore.

Exam trap

The trap here is that candidates confuse the PITR retention period with the frequency of backups, or assume that the retention setting controls the backup schedule (e.g., thinking full backups occur every 12 hours), when in fact it only controls how long backups are kept, not how often they are taken.

How to eliminate wrong answers

Option A is wrong because full backups in Azure SQL Database are taken once per week, not every 12 hours, and the retention setting shown does not change the full backup frequency. Option B is wrong because long-term retention (LTR) is a separate feature that retains full backups for up to 10 years, configured via a different policy, not the 7-day PITR retention setting shown. Option D is wrong because transaction log backups are taken every 5–10 minutes, not every 12 hours, and their frequency is not configurable via this retention setting.

Full explanation →
381
MCQmedium

Your company runs a critical application on Azure SQL Managed Instance in the North Europe region. The application requires an RPO of 5 minutes and an RTO of 2 hours during a regional disaster. The current setup uses a single instance with geo-redundant backup storage (RA-GRS). During a disaster recovery planning session, you discover that geo-restore from RA-GRS backups takes approximately 4 hours to complete, which exceeds the RTO. You need to modify the disaster recovery solution to meet the RTO without exceeding the budget significantly. The solution must minimize administrative overhead. What should you do?

A.Change backup storage to locally-redundant (LRS) and rely on point-in-time restore.
B.Enable zone redundancy on the primary instance.
C.Deploy a secondary instance in a different region and configure a failover group.
D.Scale the instance to a higher service tier to improve restore performance.
AnswerC

Enables fast failover.

Why this answer

Option C is correct because configuring a failover group between two instances in different regions provides fast failover (RTO minutes) and meets the RTO. Option A is wrong because scaling up doesn't reduce geo-restore time. Option B is wrong because zone redundancy doesn't protect against regional failure.

Option D is wrong because it changes backup policy, not failover speed.

Full explanation →
382
MCQmedium

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

A.Create an Azure Monitor alert based on fragmentation
B.Use Elastic Database Jobs to check fragmentation and send email
C.Configure SQL Agent job to send email
D.Use Azure Automation runbook to query sys.dm_db_index_physical_stats and send email
AnswerD

PowerShell runbooks can query the DMV and use SendGrid to send email notifications.

Why this answer

Azure Automation runbooks can query fragmentation and send emails via SendGrid. Elastic Database Jobs can check fragmentation but cannot send emails directly. Azure Monitor alerts can trigger on metrics like DTU but not on index fragmentation.

Azure Logic Apps can receive data but require custom setup.

Full explanation →
383
Drag & Dropmedium

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

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

Steps
Order

Why this order

The restore process starts by selecting the database, then choosing the restore type, specifying the point in time, naming the new database, and finally creating it.

Full explanation →
384
MCQmedium

You are a database administrator for an e-learning platform that uses Azure SQL Database with the General Purpose tier. The database stores course content and user progress. You need to automate the process of exporting the database schema (without data) to a BACPAC file every week for version control. The BACPAC file must be stored in an Azure Blob Storage container with a retention policy of 30 days. The automation must use Azure native services and should not require manual intervention. What should you do?

A.Use Azure Data Factory with a Copy activity to export the database schema to a BACPAC file in Blob Storage.
B.Use Elastic Database Jobs to run a T-SQL script that uses the BACKUP DATABASE command to a URL.
C.Create an Azure Automation runbook with PowerShell that uses the New-AzSqlDatabaseExport cmdlet to export the schema-only BACPAC to a Blob Storage container. Schedule the runbook to run weekly.
D.Create a SQL Agent job on the database to run SqlPackage.exe.
AnswerC

New-AzSqlDatabaseExport can export schema only with the -SchemaOnly parameter.

Why this answer

Option A is correct. Azure Automation runbooks can use the SqlPackage.exe utility (or the New-AzSqlDatabaseExport cmdlet) to export a BACPAC and save it to Blob Storage. You can schedule the runbook weekly.

Option B is incorrect because Data Factory is more complex than needed. Option C is incorrect because Elastic Database Jobs cannot export BACPAC. Option D is incorrect because SQL Agent is not available in Azure SQL Database.

Full explanation →
385
MCQeasy

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

A.az sql db restart --resource-group rg1 --server server1 --name db3
B.az sql db resume --resource-group rg1 --server server1 --name db3
C.az sql db start --resource-group rg1 --server server1 --name db3
D.az sql db update --resource-group rg1 --server server1 --name db3 --set status=Online
AnswerB

Correct command to resume a paused database.

Why this answer

Option B is correct because az sql db resume is the correct command to resume a paused database. Option A is wrong because az sql db start does not exist. Option C is wrong because az sql db update can change properties but not resume a paused database.

Option D is wrong because az sql db restart does not exist.

Full explanation →
386
MCQhard

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup retention policy. The database is in a business-critical environment that requires point-in-time restore (PITR) for the last 28 days. What is the issue with this configuration?

A.The JSON syntax is invalid because property names use camelCase instead of PascalCase.
B.Long-term retention properties (weeklyRetention, monthlyRetention, yearlyRetention) are not valid in a backupShortTermRetentionPolicies resource.
C.The diffBackupIntervalInHours should be 12 for a business-critical database.
D.The retentionDays value of 28 exceeds the maximum allowed for PITR.
AnswerB

LTR properties belong to a separate resource type: backupLongTermRetentionPolicies.

Why this answer

The ARM template shows both short-term retention (PITR) and long-term retention (LTR) properties. However, PITR retention is set to 28 days (retentionDays), which is valid. The LTR properties (weeklyRetention, monthlyRetention, yearlyRetention) are part of a different resource (backupLongTermRetentionPolicies).

Mixing them in the same resource will cause a deployment error. Option C is correct. Option A is wrong because 28 days is within limits.

Option B is wrong because LTR is not part of this resource. Option D is wrong because the JSON is syntactically correct.

Full explanation →
387
MCQmedium

Refer to the exhibit. A Logic App is configured to pause an Azure SQL Database named AdventureWorks every Friday at 8 PM. However, the database is not pausing. What is the most likely reason?

A.The SQL connection is not authenticated.
B.The recurrence trigger uses a schedule that is not valid.
C.The API version specified is outdated.
D.The pause action is not supported for Azure SQL Database.
AnswerD

Pause/resume is only available for Azure Synapse dedicated SQL pools, not Azure SQL Database.

Why this answer

Azure SQL Database does not support pause/resume; only Azure SQL Data Warehouse (now Azure Synapse Analytics dedicated SQL pool) supports it. Therefore, the API operation is invalid for Azure SQL Database. Option B is correct.

Option A is incorrect because the connection is properly referenced. Option C is incorrect because the recurrence is valid. Option D is incorrect because API version is not the primary issue.

Full explanation →
388
Multi-Selecthard

You are deploying a new Azure SQL Database that will store Personally Identifiable Information (PII). You need to ensure that the data is encrypted at rest and that access to encryption keys is logged. Which THREE actions should you take? (Choose three.)

Select 3 answers
A.Use Always Encrypted to encrypt all PII columns.
B.Grant the managed identity of the database access to the key vault with 'get', 'wrapKey', and 'unwrapKey' permissions.
C.Configure TDE with service-managed keys.
D.Enable Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.
E.Enable auditing on the Azure Key Vault to log key operations.
AnswersB, D, E

This is necessary for TDE with CMK to access the encryption key.

Why this answer

Option B is correct because to use customer-managed TDE keys stored in Azure Key Vault, the Azure SQL Database's managed identity must be granted 'get', 'wrapKey', and 'unwrapKey' permissions. This allows the database to access the key for encryption and decryption operations while maintaining a secure, auditable key management chain.

Exam trap

The trap here is that candidates often confuse Always Encrypted with TDE, selecting Always Encrypted for at-rest encryption when it is actually designed for client-side encryption of sensitive columns, not for full database-level encryption at rest.

Full explanation →
389
MCQhard

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

A.The elastic pool must be in a different server
B.The database must be in the same server as the elastic pool
C.The database must be in the Basic tier
D.The database size must be less than 10 GB
AnswerB

Required for moving into a pool.

Why this answer

Option B is correct because moving a database into an elastic pool requires the database to be in the same server as the pool. Option A is wrong because the database can be any tier. Option C is wrong because the database can be any size as long as it fits in the pool.

Option D is wrong because the pool must be in the same server.

Full explanation →
390
MCQeasy

You are reviewing an Azure SQL Database automated backup retention policy. The exhibit shows the current configuration. You need to ensure that the database can be restored to a point in time within the last 7 days at a granularity of 1 minute. Which of the following is a limitation of the current configuration?

A.The backup interval of 12 hours does not allow 1-minute granularity for point-in-time restore.
B.The retention period is too short to support point-in-time restore.
C.The policy must specify a minimum retention of 30 days for point-in-time restore.
D.Geo-redundant backup must be disabled for point-in-time restore to work.
AnswerA

PITR granularity is limited by backup frequency.

Why this answer

The current configuration uses a backup interval of 12 hours, which means automated backups are taken only every 12 hours. Point-in-time restore (PITR) in Azure SQL Database relies on full, differential, and transaction log backups to allow restoration to any second within the retention period. With a 12-hour backup interval, transaction log backups are not frequent enough to achieve 1-minute granularity; the minimum granularity is limited by the frequency of log backups, which defaults to every 5-10 minutes but can be configured.

A 12-hour interval indicates that log backups are not being taken frequently enough, thus preventing 1-minute restore granularity.

Exam trap

The trap here is that candidates often confuse the backup interval (which controls full/differential backups) with transaction log backup frequency, assuming that a 12-hour interval still allows fine-grained point-in-time restore, but in reality, log backup frequency is the key factor for granularity.

How to eliminate wrong answers

Option B is wrong because the retention period (e.g., 7 days) is sufficient for point-in-time restore; PITR requires a minimum retention of 1 day for Basic tier and 7 days for other tiers, so 7 days meets the requirement. Option C is wrong because Azure SQL Database does not mandate a minimum retention of 30 days for point-in-time restore; the default retention is 7 days, and it can be configured up to 35 days, but 7 days is valid. Option D is wrong because geo-redundant backup storage (RA-GRS) does not need to be disabled for point-in-time restore; PITR works with both locally redundant (LRS) and geo-redundant (RA-GRS) backup storage, and disabling it is not a requirement.

Full explanation →
391
MCQhard

Refer to the exhibit. You are configuring a backup policy for an Azure SQL Database. The database is in the East US region. You need to ensure that the database can be restored to a different region in the event of a regional disaster. Which setting in the exhibit must be correctly configured?

A.The geoBackupPolicy state is Enabled.
B.The geoBackupPolicy location is set to eastus2.
C.The backupStorageRedundancy is set to Geo.
D.The weeklyRetention is set to P4W.
AnswerC

Geo-redundant backups are required for geo-restore.

Why this answer

Option B is correct because backupStorageRedundancy must be set to 'Geo' to enable geo-redundant backups that can be restored to a paired region. Option A is wrong because retention settings do not affect cross-region restores. Option C is wrong because geoBackupPolicy state being Enabled does not guarantee geo-redundancy if storage redundancy is not geo.

Option D is wrong because the location in geoBackupPolicy is where the backup is stored, but the key is storage redundancy.

Full explanation →
392
MCQmedium

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

A.Change the retentionDays property to 30.
B.Change the diffBackupIntervalInHours to 24.
C.No action is needed; the current policy meets the requirement.
D.Add a long-term retention policy with a weekly retention of 30 days.
AnswerA

retentionDays controls PITR retention; setting to 30 meets the requirement.

Why this answer

Option B is correct because the retentionDays property is set to 14, which is less than the required 30 days. The diffBackupIntervalInHours is not relevant for PITR retention. Option A is wrong because PITR retention is not set by the diff backup interval.

Option C is wrong because PITR retention is not set by long-term retention. Option D is wrong because 14 days does not meet the requirement.

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

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

Why this answer

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

Option B is correct because during a disaster, you can perform a forced failover with data loss in a failover group. The command 'az sql mi failover-group force-failover-allow-data-loss' initiates the failover. Option A would fail because the primary is unavailable.

Option C is for elastic pools, not MI. Option D is for single databases.

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

Options B and D are correct. Zone-redundant configuration protects against failures within a region at the zone level (B) and provides higher availability than single-zone deployment (D). Option A is incorrect because zone redundancy does not protect against region-wide outages.

Option C is incorrect because it does not reduce latency. Option E is incorrect because it does not provide geo-replication, only zonal redundancy.

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

Large transaction log files can lead to slower I/O due to fragmentation; shrinking may help.

Why this answer

Option A is correct because a large log file can cause log write latency due to file fragmentation and long I/O paths. Option B is wrong because transaction log is sequential write, not random I/O. Option C is wrong because log write latency is primarily about storage, not CPU.

Option D is wrong because increasing log file size further would exacerbate the issue.

Full explanation →
397
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 release pipeline defines the deployment steps, a build pipeline produces the artifacts (e.g., .dacpac), and a variable group stores configuration values like connection strings. Option B is wrong because an Azure Automation runbook is not part of DevOps CI/CD. Option D is wrong because elastic jobs are not used for schema deployment.

Full explanation →
398
MCQmedium

Refer to the exhibit. You run this KQL query in Azure Monitor Log Analytics for an Azure SQL Database. What type of events does the query return?

A.Failed logins
B.Schema changes
C.Audit log modifications
D.Data manipulation statements
AnswerC

AUSC indicates audit change events.

Why this answer

Option C is correct because action_id_s == 'AUSC' corresponds to AUDIT_CHANGE events in SQL Server audit logs. Option A is wrong because 'AUSC' is not for schema changes. Option B is wrong because it is not for logins.

Option D is wrong because it is not for data modifications.

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

Option A and D are correct. Setting the failover group to manual and performing a planned failover ensures no data loss and minimizes downtime. Option B is wrong because increasing performance does not affect failover.

Option C is wrong because disabling automatic failover is not necessary. Option E is wrong because terminating connections manually is not recommended; the failover process handles it.

Full explanation →
400
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 jobs are designed for executing administrative tasks across multiple databases. Index maintenance and data consistency checks are common use cases. Option C is wrong because scaling is better done with other automation.

Option D is wrong because backup is automatically managed. Option E is wrong because user management across databases is better done with contained users or Entra ID.

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

Option B is correct because geo-restore of automated backups can achieve RPO of 1 hour (backup frequency) and RTO of 12 hours (restore time). Option A is wrong because active geo-replication has lower RPO/RTO. Option C is wrong because failover groups have lower RTO.

Option D is wrong because zone-redundancy does not protect against regional failure.

Full explanation →
402
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 →
403
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, C, and D are correct. Active geo-replication provides automatic failover with low RPO, long-term retention for archival, and auto-failover groups for automated failover. Option B is wrong because point-in-time restore does not provide automated failover.

Option E is wrong because Azure Backup service is not used for Azure SQL Database.

Full explanation →
404
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 →
405
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 →
406
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

Option C is correct because the query filters for queries with average duration > 1000 ms and orders by duration. Option A is wrong because it doesn't identify plan changes. Option B is wrong because it doesn't sort by frequency.

Option D is wrong because it doesn't compare over time intervals.

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

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

Options A, B, and C are correct. The primary and secondary servers are essential, and the failover group is the logical container. Option D is incorrect because a listener is created automatically as part of the failover group, not separately.

Option E is incorrect because geo-replication is not required; the failover group itself provides the replication.

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

Option B is correct because geo-replicated secondaries are readable and do not accept write traffic until failover; after failover, the new primary has no geo-secondary configured initially, so writes are not replicated asynchronously, but latency is likely due to the lack of a local secondary. Option A is wrong because service objective is same. Option C is wrong because geo-replication delay affects read scalability, not write latency.

Option D is wrong because the secondary is readable but not writable before failover.

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

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

Option D is correct because automatic tuning must be enabled at the server level for Managed Instance, and the FORCE_LAST_GOOD_PLAN option must be set to ON. Option A is incorrect because automatic tuning is not a database-level setting for Managed Instance. Option B is incorrect because query_store_desired_state controls Query Store, not automatic tuning.

Option C is incorrect because automatic_tuning set to OFF disables tuning.

Full explanation →
412
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 →
413
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

Option C is correct because when the primary is completely unavailable, you must perform a forced failover with data loss, which is allowed even if the primary is down. Option A is incorrect because a planned failover requires the primary to be accessible. Option B is incorrect because you cannot remove the primary if it is down.

Option D is incorrect because you should not wait indefinitely.

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

Azure DevOps integrates with Azure CLI or PowerShell to automate database copy, and can be triggered by commits, fitting CI/CD needs.

Why this answer

Azure SQL Managed Instance supports database copy via T-SQL, which can be automated with Elastic Jobs or DevOps pipelines. However, for CI/CD integration, Azure DevOps with the SQL Server database project (DACPAC) and Azure CLI is the most standard approach. Option C is correct.

Option A is unsupported. Option B is for Azure SQL Database. Option D requires more custom scripting.

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

Option D is correct because Azure SQL Database automatically provides geo-redundant backup storage (RA-GRS) which replicates to a paired region. Option A is wrong because Azure Backup is for VMs, not Azure SQL Database. Option B is wrong because it only copies to a single region.

Option C is wrong because Elastic Jobs can copy but not automatically.

Full explanation →
416
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, B, and D are correct. Failover groups, active geo-replication, and geo-restore (point-in-time restore to another region) are built-in DR methods. Option C is for SQL Server on VMs.

Option E is not a DR feature; backup retention is for retention, not disaster recovery.

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

Options A and C are correct. Query Store identifies top resource-consuming queries, and sys.dm_exec_query_stats shows current CPU/memory usage. Option B (rebuilding indexes) is a fix, not diagnostic.

Option D (scaling up) is reactive. Option E (changing recovery model) is not relevant to resource consumption.

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

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

Option C is correct because the 'context' should be 'memberName' as a string, but the value has a backslash which is not escaped in JSON; also the classifier name is not referenced in the context correctly. Option A is wrong because importance is correctly set. Option B is wrong because resource percentiles are valid.

Option D is wrong because the classifier is not a built-in role.

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

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

Option C is correct because Query Store provides detailed query performance data, including plan regressions and improvements. Option A is wrong because Azure Monitor alerts only notify that a regression occurred, not the improvement. Option B is wrong because the automatic tuning log shows actions taken but not performance metrics.

Option D is wrong because dynamic management views give current stats but not historical comparison.

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

Planned failover syncs data before failover, minimizing data loss.

Why this answer

Option B is correct because a planned failover (with data synchronization) ensures minimal data loss by syncing data before switching. Option A is incorrect because an unplanned failover may lose transactions not yet replicated. Option C is incorrect because you need to failover, not delete the secondary.

Option D is incorrect because creating a new secondary does not failover.

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

Single database provides dedicated resources and full isolation.

Why this answer

A single database in Azure SQL Database runs on a dedicated logical server and, when configured with the appropriate service tier and compute size, provides the highest level of resource isolation because it does not share CPU, memory, or IO resources with any other database. This ensures that the sensitive customer data is isolated from other tenants, meeting strict compliance and performance requirements.

Exam trap

The trap here is that candidates often confuse 'resource isolation' with 'scalability' or 'manageability,' leading them to choose Elastic Pool or Managed Instance, which offer shared resources or broader management features but not the highest level of isolation.

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 →
425
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 →
426
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

DACPAC provides version-controlled schema deployment, and Elastic Jobs allows rolling out to specific databases with tracking.

Why this answer

Option D is correct. 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 Automation is not designed for multi-tenant schema deployment. Option B is incorrect because Data Factory is for data movement, not schema deployment. Option C is incorrect because SQL Agent is not available in Azure SQL Database.

Full explanation →
427
MCQhard

What will be the result of this command?

A.The database will log all successful and failed authentication attempts to the storage account for 90 days.
B.The database will log only failed authentication attempts to the storage account for 90 days.
C.The command will fail because the StorageAccountResourceId parameter is incorrect.
D.The command will enable auditing for the server rather than the database.
AnswerA

AuditActionGroup specifies the events to log, and RetentionInDays sets the retention.

Why this answer

The command configures SQL Database auditing to send audit logs to an Azure Storage account. By default, when auditing is enabled with the AUDIT_SPECIFICATION set to AUDIT_ALL (or the default audit action group), both successful and failed authentication attempts are logged. The RETENTION_DAYS parameter set to 90 ensures logs are retained for 90 days before being automatically deleted.

Option A correctly describes this behavior.

Exam trap

The trap here is that candidates often assume auditing only logs failures by default, but the default audit action group includes both successful and failed logins, making Option A correct and Option B a common distractor.

How to eliminate wrong answers

Option B is wrong because the default audit action group includes both successful and failed logins (DATABASE_LOGOUT_GROUP, DATABASE_LOGIN_GROUP, etc.), not just failed attempts. Option C is wrong because the StorageAccountResourceId parameter is correctly formatted as a full Azure Resource Manager resource ID; the command will not fail due to this parameter unless the storage account does not exist or permissions are missing. Option D is wrong because the command targets the database level (ALTER DATABASE ...

SET AUDIT_SPECIFICATION), not the server level; server-level auditing is configured via different T-SQL commands or Azure portal settings.

Full explanation →
428
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 B is correct.

Option A is for SQL Server deployments only. Option C is a scripting language, not IaC. Option D is for database schema changes, not resource deployment.

Full explanation →
429
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 →
430
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

Option C is correct because Azure SQL Database's automatic tuning can automatically create and drop indexes based on workload patterns, minimizing manual overhead. Option A is wrong because Elastic Database Jobs require custom scripting and are not fully automated. Option B is wrong because it still requires manual scheduling and maintenance.

Option D is wrong because it is a third-party tool and not built-in.

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

Query Store captures query execution plans and performance metrics over time, enabling before/after comparisons.

Why this answer

Option B is correct because Query Store captures historical query performance and allows plan comparison. Option A is wrong because Azure SQL Analytics provides aggregated metrics but not per-query historical comparison. Option C is wrong because Intelligent Insights provides diagnostic analysis but not detailed historical data.

Option D is wrong because Dynamic Management Views (DMVs) provide current state, not historical trends.

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

Option C is correct because sys.dm_db_index_physical_stats returns fragmentation details for all indexes in a database with minimal overhead when used with limited scanning. Option A is wrong because SHOWPLAN_XML shows query plans, not fragmentation. Option B is wrong because sys.indexes does not include fragmentation info.

Option D is wrong because missing index DMVs suggest new indexes, not fragmentation.

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

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

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

Option C is correct because 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 B (3 days) is also insufficient. Option D (14 days) is more than needed.

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

Option C is correct because after automatic failover, you must manually fail back to re-establish the primary in the original region. Option A is wrong because automatic failover would have already occurred. Option B is wrong because restoring from backup would cause data loss.

Option D is wrong because you cannot simply delete and recreate the failover group without data loss.

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

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

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

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

Option A is correct because FORCE_LAST_GOOD_PLAN automatically forces the last known good plan when a regression is detected. Option B is wrong because it creates indexes automatically, which is not about plan regression. Option C is wrong because it drops unused indexes.

Option D is wrong because it corrects nonclustered indexes, not query plans.

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

Option C is correct because the yearly retention is currently 3 years (P3Y), which is less than 10 years. Option A is wrong because weekly retention is 4 weeks, which is correct for weekly. Option B is wrong because monthly retention is 12 months, which is correct for monthly.

Option D is wrong because the weekOfYear is used for yearly retention, but the retention period itself needs to be increased.

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

Option B is correct because 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 →
443
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, D, E

Ensures encrypted connections.

Why this answer

Setting the minimum TLS version to 1.2 ensures that all connections to Azure SQL Database use a secure, encrypted channel, enforcing encryption in transit as required by the security policy. TLS 1.2 is the minimum recommended version, as older versions (1.0, 1.1) are deprecated and vulnerable to attacks like POODLE and BEAST.

Exam trap

The trap here is that candidates often confuse encryption in transit (TLS) with encryption at rest (TDE) or access control (firewall rules, Entra ID), and overlook the resource lock as a separate, required configuration for deletion protection.

Full explanation →
444
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 A and D are correct. The Business Critical tier provides synchronous replicas that automatically failover on node failure. Zone redundancy places replicas in different zones for additional resilience.

Option B (active geo-replication) is for DR across regions. Option C (auto-failover group) is for managed instances. Option E (Hyperscale read replicas) are for read scale, not HA.

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

Option B is correct because the password may have expired (if SQL Server password policy is enforced). Azure SQL Database does not support Windows authentication for SQL Agent jobs. Option A would not cause login failure.

Option C is irrelevant. Option D is wrong because the job already uses SQL authentication.

Full explanation →
446
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 →
447
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 →
448
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

Option D is correct because SSMS can graphically open deadlock graphs from extended events. Option A is wrong because Query Store does not capture deadlocks. Option B is wrong because Azure Monitor collects metrics but not deadlock details.

Option C is wrong because sys.dm_exec_requests does not show deadlock history.

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

Option D is correct because scaling up to a higher service objective (e.g., S3) provides more DTUs, which directly improves performance without code changes. Option A is wrong because creating indexes requires code changes. Option B is wrong because query store is a monitoring tool, not a performance fix.

Option C is wrong because read scale-out is for read-only workloads.

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

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