Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 1–75

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

Take a mock examExam hub

Page 1 of 13

Page 2
1
MCQhard

You are a database administrator for a financial services company that runs a critical application on Azure SQL Database in the Business Critical service tier. The database is named 'TransactionsDB' and has a size of 500 GB. The application experiences periodic performance degradation during end-of-month batch processing. Analysis shows that the degradation coincides with high log write activity and increased latency for write transactions. You have already verified that the log rate is within the service tier limits. The batch process performs a large number of INSERT, UPDATE, and DELETE operations on multiple tables. You need to optimize the transaction log performance without changing the application code or the service tier. The database uses the full recovery model and has a log backup every 5 minutes. What should you do?

A.Implement transactional replication to offload write operations to a secondary database.
B.Enable page compression on all tables to reduce the amount of data written to the log.
C.Enable accelerated database recovery to reduce the log space required for long-running transactions.
D.Increase the initial size of the transaction log file to 200 GB and set the auto-growth increment to a fixed size of 1 GB.
AnswerC

Accelerated Database Recovery reduces log space needed for long-running transactions, improving log write throughput during batch operations.

Why this answer

Enabling Accelerated Database Recovery (ADR) reduces the log space required for long-running transactions by using a persistent version store (PVS) to avoid writing undo logs. This helps mitigate log growth during batch operations, reducing the frequency of log autogrowth events and improving overall log write performance. Option A is incorrect because transactional replication adds overhead and does not directly optimize log performance.

Option B is incorrect because page compression reduces data storage but does not significantly reduce log writes (log records are row-level). Option D is incorrect because Azure SQL Database manages log file sizing automatically; you cannot configure initial size or auto-growth increments manually.

Full explanation →
2
MCQeasy

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

A.Always Encrypted with randomized encryption
B.Always Encrypted with deterministic encryption
C.Transparent Data Encryption
D.Dynamic Data Masking
AnswerB

Deterministic encryption supports equality searches.

Why this answer

Always Encrypted with deterministic encryption is correct because it encrypts the CreditLimit column at the client driver level, ensuring data remains encrypted at rest and in transit, while still allowing exact-match searches (e.g., WHERE CreditLimit = 5000) since deterministic encryption always produces the same ciphertext for a given plaintext. This meets the compliance requirement without requiring significant application code changes, as the Azure SQL Database driver handles encryption and decryption transparently for authorized users.

Exam trap

The trap here is that candidates confuse Dynamic Data Masking with encryption, thinking masking satisfies compliance requirements, but masking is a presentation-layer feature that does not protect data from privileged users or direct database access.

How to eliminate wrong answers

Option A is wrong because randomized encryption produces different ciphertext for the same plaintext each time, which prevents equality searches (e.g., WHERE CreditLimit = 5000) and thus fails the application requirement for exact-match queries. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level granularity or restrict access to specific columns; it protects against physical theft of files, not unauthorized viewing by database users. Option D is wrong because Dynamic Data Masking obfuscates data at query results but does not encrypt the underlying data; it can be bypassed by users with direct access to the database or through inference attacks, failing the compliance requirement for encryption.

Full explanation →
3
MCQmedium

You administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

A.Log IO percent
B.Transaction log usage
C.Average IO latency
D.Log write latency
AnswerD

Measures log write time.

Why this answer

The correct metric to examine is Log write latency (option D), as it directly measures the time taken to write to the transaction log, which can indicate slow performance due to log write latency. Option A (Log IO percent) measures the percentage of log throughput used, not latency. Option B (Transaction log usage) measures log file space used, not performance.

Option C (Average IO latency) includes both data and log I/O, so it is not specific to log writes.

Full explanation →
4
Multi-Selecteasy

You are monitoring an Azure SQL Database that is experiencing high DTU usage. Which TWO metrics should you examine to determine whether the bottleneck is CPU or I/O?

Select 2 answers
A.Log write bytes per second.
B.Average CPU percentage.
C.Number of deadlocks per second.
D.Used storage space in GB.
E.Number of active sessions.
AnswersA, B

Correct. High log write bytes per second indicates significant I/O activity from transaction log writes, helping to identify an I/O bottleneck.

Why this answer

Average CPU percentage directly measures CPU utilization, indicating a CPU bottleneck when high. Log write bytes per second measures the throughput of transaction log writes, which is a key indicator of I/O activity and can reveal an I/O bottleneck. Therefore, both A and B are correct metrics to diagnose whether the high DTU usage is caused by CPU or I/O.

Full explanation →
5
MCQhard

A manufacturing company uses Azure SQL Database Hyperscale tier for its IoT telemetry data. The database is in West Europe and must be readable in East US for reporting with a maximum lag of 10 seconds. You configure a failover group with East US as the secondary region. After setup, reporting queries in East US show data that is 5 minutes old. What is the most likely cause?

A.The failover group has 'Read/Write failover policy' set to 'Automatic' causing the lag
B.The secondary database is not readable; it is only for failover
C.The secondary database is configured with 'Geo' secondary type; you need to set it to 'Readable' to minimize lag
D.The replication mode is set to 'Async' and should be changed to 'Sync'
AnswerC

Setting the secondary type to 'Readable' ensures the secondary is maintained with lower replication lag.

Why this answer

The most likely cause is that the secondary database is configured with the default 'Geo' secondary type, which is not readable and uses asynchronous replication with potentially high lag. To minimize lag and make the secondary readable for reporting, you must set the secondary type to 'Readable' (also called 'Named' secondary in some contexts). This ensures the secondary is kept in near-synchronous sync, though still asynchronous.

Option A is incorrect because the failover policy does not affect replication lag. Option B is incorrect because the secondary can be made readable by changing the secondary type. Option D is incorrect because Hyperscale does not support synchronous replication, and changing to sync would not address the lag issue.

Full explanation →
6
Multi-Selecthard

Your company uses Azure SQL Database with active geo-replication for disaster recovery. The primary database is in the West Europe region. You need to perform a planned failover to the secondary in North Europe for a maintenance window. Which THREE actions should you take? (Choose three.)

Select 3 answers
A.Initiate a forced failover to ensure the fastest switchover.
B.Manually seed the new secondary database after failover.
C.After failover, verify that the new secondary (in West Europe) is configured as a readable secondary.
D.Update the application connection strings to point to the new primary in North Europe.
E.Initiate a planned failover (no data loss) from the Azure portal or PowerShell.
AnswersC, D, E

To restore DR capabilities, the former primary should be re-configured as a secondary.

Why this answer

The correct actions are C, D, and E. For a planned failover during a maintenance window, you should initiate a planned failover (no data loss) from the Azure portal or PowerShell (E) to ensure all transactions are synchronized before switching. After failover, verify that the new secondary (in West Europe) is configured as a readable secondary (C) to maintain disaster recovery capabilities.

Additionally, update the application connection strings to point to the new primary in North Europe (D). Option A is incorrect because a forced failover could cause data loss and is intended for unplanned outages. Option B is incorrect because seeding of the new secondary happens automatically via geo-replication.

Full explanation →
7
Multi-Selecteasy

Which TWO disaster recovery options are available for Azure SQL Database? (Choose two.)

Select 2 answers
A.Database mirroring.
B.Auto-failover groups.
C.Always On availability groups.
D.Log shipping.
E.Active geo-replication.
AnswersB, E

Failover groups provide automatic failover with multiple databases.

Why this answer

Active geo-replication and auto-failover groups are the two primary disaster recovery options for Azure SQL Database. Active geo-replication provides asynchronous replication of a database to a secondary region, while auto-failover groups extend this by allowing failover of multiple databases and providing read-only endpoints. Options A (database mirroring) and D (log shipping) are not supported for Azure SQL Database.

Option C (Always On availability groups) is a feature for SQL Server on Azure VMs, not for Azure SQL Database.

Full explanation →
8
MCQeasy

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

A.The queries suffer from parameter sniffing leading to suboptimal plans.
B.The queries are experiencing memory pressure causing excessive lazy writes.
C.The queries are waiting on transaction log writes.
D.The queries are CPU-bound due to inefficient query plans.
AnswerD

Correct. High worker time with low logical reads is a classic sign of CPU-bound queries, often caused by inefficient query plans that perform heavy computations (e.g., complex joins, aggregations, or non-sargable predicates).

Why this answer

High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing.

Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Full explanation →
9
MCQmedium

Your company uses Azure SQL Database with Microsoft Entra ID authentication. You need to ensure that only users from a specific Microsoft Entra ID tenant can access the database. What should you configure?

A.Enable Azure AD conditional access policy to restrict sign-ins to the specific tenant.
B.Set the server firewall to allow only the tenant's IP range.
C.Create a server-level firewall rule with IP range '0.0.0.0' and then use Microsoft Entra ID authentication with a conditional access policy.
D.Configure a network security group (NSG) to block all traffic except from the tenant's IP range.
AnswerC

This allows all IPs but relies on Entra ID to validate the tenant.

Why this answer

To restrict access to a specific Microsoft Entra ID tenant, you must first set the server-level firewall to allow all Azure IPs (0.0.0.0) so that Azure SQL Database can accept connections from any Azure resource, and then enforce tenant-level restrictions using a conditional access policy. This combination ensures that only users from the specified tenant can authenticate, while the firewall rule handles network-level access from Azure services.

Exam trap

The trap here is that candidates often think a conditional access policy alone can restrict tenant access, but it must be combined with the firewall rule '0.0.0.0' to allow Azure services, as the policy only controls authentication, not network connectivity.

How to eliminate wrong answers

Option A is wrong because a conditional access policy alone cannot restrict access to a specific tenant; it controls sign-in conditions (e.g., location, device) but does not filter by tenant ID. Option B is wrong because setting the server firewall to allow only the tenant's IP range is ineffective, as Microsoft Entra ID authentication relies on identity, not IP addresses, and tenant IP ranges are not static or reliably scoped. Option D is wrong because a network security group (NSG) operates at the virtual network level and cannot be applied to Azure SQL Database's public endpoint; it is used for PaaS resources within a VNet, not for restricting tenant access.

Full explanation →
10
MCQmedium

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

A.The database is out of transaction log space; increase the max log size.
B.The database storage is running out; scale up storage.
C.The database is nearing its log write IOPS limit; consider scaling up or optimizing log writes.
D.The CPU is overloaded; scale up CPU.
AnswerC

avg_log_write_percent measures log IO percentage; high value indicates IO bottleneck.

Why this answer

The avg_log_write_percent metric in sys.dm_db_resource_stats measures the percentage of the log write IOPS limit used. At 95%, the database is nearing its log write IOPS limit, which can cause transaction delays and throttling. The appropriate response is to scale up the service tier (e.g., to a higher DTU or vCore level) or optimize log writes to reduce IOPS consumption.

Option A is incorrect because the metric does not indicate running out of log space; log space is separate. Option B is incorrect because storage scale (size) does not directly affect log write IOPS. Option D is incorrect because this metric is specifically about log I/O, not CPU.

Full explanation →
11
MCQeasy

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

A.Configure zone-redundant availability for the database
B.Use active geo-replication to a different region
C.Deploy the database with locally redundant storage
D.Configure read scale-out with a secondary replica
AnswerA

Zone redundancy replicates across zones within a region.

Why this answer

Azure SQL Database zone-redundant availability replicates the database across multiple availability zones within the same region, providing high availability in the event of a single zone failure. Option B (active geo-replication) replicates to a different region, which protects against regional outages but not specifically a zone failure. Option C (locally redundant storage) stores data within a single data center, offering no zone-level protection.

Option D (read scale-out) provides read-only replicas but does not ensure write availability during a zone failure.

Full explanation →
12
MCQhard

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

A.The secondary region is not in the same geo as the primary.
B.Application-consistent snapshots are not enabled in the ASR replication policy.
C.The backup retention period is too short.
D.The database is not part of an Always On availability group.
AnswerB

Without application-consistent snapshots, database consistency may be lost.

Why this answer

Azure Site Recovery (ASR) replicates at the VM level, and without application-consistent snapshots enabled in the replication policy, the recovered database may not be transactionally consistent. Option A is incorrect because ASR supports cross-region replication to a secondary region that may be in a different geo. Option C is incorrect because backup retention period does not affect ASR replication consistency.

Option D is incorrect because ASR does not require the database to be part of an Always On availability group.

Full explanation →
13
MCQmedium

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

A.Row-level security (RLS) to restrict access based on user role.
B.Dynamic data masking (DDM) to mask SSNs for unauthorized users.
C.Transparent Data Encryption (TDE) with customer-managed keys.
D.Always Encrypted with column master key stored in Azure Key Vault.
AnswerD

Always Encrypted encrypts column data at rest and in transit, and only clients with access to the column master key can decrypt.

Why this answer

Always Encrypted is the correct choice because it ensures that sensitive data, such as SSNs, is encrypted both at rest and in transit, and the encryption keys are stored client-side (e.g., in Azure Key Vault). This design ensures that only authorized client applications with access to the column master key can decrypt the data, preventing even database administrators or cloud operators from viewing the plaintext values.

Exam trap

The trap here is that candidates often confuse Transparent Data Encryption (TDE) with column-level encryption, mistakenly thinking TDE protects data from all unauthorized access, when in fact TDE only encrypts data at rest and does not prevent authorized database users from reading sensitive columns in plaintext.

How to eliminate wrong answers

Option A is wrong because Row-Level Security (RLS) controls which rows a user can access based on predicates, but it does not encrypt data or protect it in transit; it only filters rows at query time. Option B is wrong because Dynamic Data Masking (DDM) obfuscates data for unauthorized users at the application layer but does not encrypt the underlying data, leaving it vulnerable to unauthorized decryption or exposure in backups and logs. Option C is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest but does not protect data in transit or prevent authorized database users (e.g., DBAs) from reading the plaintext SSNs; it also does not support client-side key control for granular column-level encryption.

Full explanation →
14
MCQhard

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

A.Use Azure RBAC to assign roles to the Entra ID groups.
B.Configure a SQL Server Agent job to update database roles based on group membership.
C.Create database users from Microsoft Entra ID groups and grant roles to those users.
D.Create a DDL trigger that assigns roles when users log in.
AnswerC

You can create a contained database user for each Entra ID group and grant database roles to that user.

Why this answer

Azure SQL Database supports creating database users mapped to Microsoft Entra ID (formerly Azure AD) groups. By creating a user for the Entra ID group and then granting database roles to that group user, all members of the group automatically inherit the assigned permissions. This directly satisfies the requirement for role assignment based on group membership without custom scripting or triggers.

Exam trap

The trap here is that candidates confuse Azure RBAC (management-plane access) with database-level permissions (data-plane access), or assume that SQL Server Agent or DDL triggers are available in Azure SQL Database, leading them to choose options that are either not applicable or unsupported in the PaaS environment.

How to eliminate wrong answers

Option A is wrong because Azure RBAC controls access to Azure resources (e.g., the logical server or database) at the management plane, not database-level permissions within the SQL engine; it cannot assign database roles like db_datareader. Option B is wrong because SQL Server Agent is not available in Azure SQL Database (it is a PaaS service with no Agent support), and even if it were, polling group membership would be inefficient and not real-time. Option D is wrong because DDL triggers fire on schema changes (e.g., CREATE TABLE), not on login events; logon triggers are not supported in Azure SQL Database, and they cannot dynamically assign database roles based on group membership.

Full explanation →
15
MCQeasy

You need to ensure that an Azure SQL Managed Instance remains available in the event of a single availability zone failure in a region that supports availability zones. What should you configure?

A.Configure a failover group to another region.
B.Deploy the instance in General Purpose tier with zone redundancy.
C.Use a Hyperscale tier instance with zone redundancy.
D.Deploy the instance in Business Critical tier with zone-redundant configuration.
AnswerD

Business Critical supports zone redundancy for Managed Instance.

Why this answer

For zone redundancy in Azure SQL Managed Instance, the Business Critical tier is required. Option D is correct because it specifies deploying in Business Critical tier with zone-redundant configuration, which provides resilience within a single region across availability zones. Option A is incorrect because a failover group to another region addresses regional disasters, not zonal failures.

Option B is incorrect because the General Purpose tier does not support zone redundancy. Option C is incorrect because Hyperscale is a service tier for Azure SQL Database, not for Managed Instance.

Full explanation →
16
Multi-Selecthard

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

Select 3 answers
A.Run a T-SQL script to update statistics across multiple databases.
B.Collect metadata about databases and store it in a table.
C.Create a new Azure SQL Database.
D.Change the service tier objective (SLO) of a database.
E.Rebuild indexes on all databases in an elastic pool.
AnswersA, B, E

Elastic Database Jobs can execute any T-SQL script, including updating statistics.

Why this answer

Elastic Database Jobs in Azure SQL Database are designed to automate administrative tasks across multiple databases. Running a T-SQL script to update statistics is a common maintenance operation that can be executed in parallel across a target group of databases, making option A correct.

Exam trap

The trap here is that candidates may assume Elastic Database Jobs can perform any administrative task, but they are strictly limited to executing T-SQL scripts and cannot perform resource-level operations like creating databases or changing service tiers.

Full explanation →
17
Multi-Selectmedium

Which TWO actions can help you identify and resolve performance bottlenecks related to I/O in an Azure SQL Database?

Select 2 answers
A.Query sys.dm_exec_requests and filter on wait_type like PAGEIOLATCH.
B.Enable data compression on large tables to reduce I/O.
C.Increase the database service tier to add more compute resources.
D.Use sys.dm_db_resource_stats to see average I/O per minute.
E.Monitor sys.dm_os_performance_counters for CPU usage.
AnswersA, B

PAGEIOLATCH waits indicate I/O bottlenecks.

Why this answer

Options A and B are correct. A: Querying sys.dm_exec_requests with a filter on PAGEIOLATCH wait type identifies queries waiting for I/O, pinpointing I/O bottlenecks. B: Data compression reduces the size of data pages, decreasing I/O operations for large tables.

C: Increasing the service tier adds compute resources (CPU/memory) but does not directly address I/O bottlenecks; it may only mask the issue. D: sys.dm_db_resource_stats provides average I/O per minute at the database level, which is useful for monitoring but not for per-query I/O bottleneck identification. E: sys.dm_os_performance_counters for CPU usage tracks CPU performance, not I/O.

Full explanation →
18
MCQmedium

You are a database administrator for a healthcare company that uses Azure SQL Database for its electronic health records (EHR) system. The database is in the West Europe region using the General Purpose service tier. The company is expanding to the United States and wants to set up disaster recovery with the secondary in East US. The requirements are: RPO of 5 minutes and RTO of 1 hour. The application should automatically failover without manual intervention. Additionally, you must ensure that the secondary database is not used for read traffic to avoid any performance impact on the primary. What should you configure?

A.Configure active geo-replication to a secondary database in East US and set up a custom monitoring script to trigger failover.
B.Create a failover group with a readable secondary in East US and enable auto-failover.
C.Create a failover group with a non-readable secondary in East US and enable auto-failover.
D.Deploy a zone-redundant General Purpose database in West Europe and use geo-restore to East US.
AnswerC

Auto-failover group meets the RPO/RTO, and non-readable secondary prevents read traffic.

Why this answer

A failover group with auto-failover and a non-readable secondary meets all requirements: RPO of 5 minutes can be achieved with async replication, RTO of 1 hour with automatic failover, and no read traffic. Option A is wrong because active geo-replication alone does not provide automatic failover. Option B is wrong because a readable secondary would be used for read traffic.

Option D is wrong because zone-redundancy doesn't protect regionally.

Full explanation →
19
MCQhard

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

A.There is a blocking chain due to unoptimized queries.
B.The disk IOPS limit is being reached, causing queuing.
C.The DTU limit is being reached, causing CPU throttling.
D.The database is experiencing memory pressure due to concurrent queries exceeding available memory.
AnswerD

RESOURCE_SEMAPHORE specifically indicates waiting for memory grant; common in under-provisioned tiers.

Why this answer

RESOURCE_SEMAPHORE waits indicate that queries are waiting for memory grants to execute. In Azure SQL Database General Purpose tier with 100 DTUs, memory is shared between the buffer pool and query execution. During peak hours, concurrent queries can exhaust the available memory, forcing queries to wait for memory grants.

This is a classic sign of memory pressure, not CPU or IO throttling.

Exam trap

The trap here is that candidates often confuse DTU throttling (which affects CPU and IO) with memory pressure, but RESOURCE_SEMAPHORE is a memory-specific wait type that is not directly tied to DTU limits.

How to eliminate wrong answers

Option A is wrong because blocking chains typically manifest as LCK_M_* waits, not RESOURCE_SEMAPHORE waits. Option B is wrong because disk IOPS limits cause PAGEIOLATCH_* waits, not RESOURCE_SEMAPHORE waits. Option C is wrong because DTU limits being reached cause CPU throttling, which appears as SOS_SCHEDULER_YIELD waits, not RESOURCE_SEMAPHORE waits.

Full explanation →
20
MCQmedium

You are a senior database administrator for a financial services company that uses Azure SQL Managed Instance to host multiple customer databases. The company has a requirement to automatically execute a series of compliance scripts every Sunday at 2:00 AM against all databases in the instance. The scripts include checking for orphaned users, verifying data encryption, and auditing login attempts. The solution must log the execution results in a central table and send an email summary to the compliance team. You have been asked to implement this automation using built-in Azure features without relying on external tools like Power Automate or custom schedulers. What should you use?

A.Schedule a Logic App that connects to each database via the SQL connector and runs the scripts.
B.Use Azure Automation Runbooks with the PowerShell module for SQL Server to execute scripts on each database.
C.Create a SQL Agent job that runs a T-SQL script using sp_foreachdb to execute the compliance scripts on each database, store results in a central database, and send an email using Database Mail.
D.Create an Elastic Job agent with a job that runs the scripts against each database and logs results to a central database.
AnswerC

SQL Agent is built-in and supports cross-database execution and email.

Why this answer

SQL Agent jobs in Azure SQL Managed Instance can execute T-SQL scripts across databases using sp_foreachdb (or a cursor-based loop) to run compliance scripts on each database. Results can be inserted into a central database using three-part names (or cross-database queries). Email notifications can be sent using Database Mail (sp_send_dbmail).

Option A is incorrect because Logic Apps are external tools and not built-in Azure features for this purpose. Option B is incorrect because Azure Automation Runbooks require external connectivity and are not directly integrated with SQL Managed Instance for cross-database execution. Option D is incorrect because Elastic Jobs are designed for Azure SQL Database, not for SQL Managed Instance.

Full explanation →
21
Matchingmedium

Match each Azure SQL Database pricing tier to its key feature.

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

Concepts
Matches

Single node, suitable for development and small workloads

Balanced compute and memory for most production workloads

High memory-to-core ratio for memory-intensive workloads

Low-cost option with ability to burst CPU performance

Why these pairings

These tiers define the compute and memory resources available for Azure SQL Database.

Full explanation →
22
MCQmedium

You have an Azure SQL Database that runs a critical workload. You need to automate index maintenance to reduce fragmentation without impacting performance. What should you use?

A.Elastic Database Jobs with T-SQL script
B.Automatic tuning for index management
C.SQL Agent Job with OLE Automation
D.Azure Automation runbook with T-SQL script
AnswerA

Correct. Elastic Database Jobs allow you to automate T-SQL scripts for index maintenance across Azure SQL databases, enabling scheduling of index rebuilds to reduce fragmentation without performance impact.

Why this answer

Elastic Database Jobs can be used to schedule and run T-SQL scripts for index maintenance across one or more databases in Azure SQL Database. This allows you to automate index rebuilds or reorganizations during off-peak hours to reduce fragmentation without impacting performance. Option B is incorrect because automatic tuning in Azure SQL Database focuses on creating and dropping indexes based on query patterns, not on rebuilding fragmented indexes.

Options C and D are incorrect: SQL Agent Jobs are not available in Azure SQL Database single databases, and while Azure Automation runbooks could be used, Elastic Database Jobs provide a more integrated and seamless solution for database-specific tasks.

Full explanation →
23
MCQmedium

Your company uses Azure SQL Database with Azure Active Directory (now Microsoft Entra ID) authentication. A new security policy requires that all database users must be authenticated via Microsoft Entra ID only. You need to disable SQL authentication for an Azure SQL Database logical server. What should you do?

A.Remove the Azure AD administrator for the server.
B.Remove all SQL logins from the master database.
C.Set the 'Azure AD-only authentication' property to Enabled for the logical server.
D.Set the 'Azure AD-only authentication' property to Enabled for each database.
AnswerC

This is the correct server-level setting to disable SQL authentication.

Why this answer

Enabling the 'Azure AD-only authentication' property at the logical server level explicitly blocks all SQL authentication attempts, forcing every database user to authenticate via Microsoft Entra ID. This property is a server-level toggle that overrides any existing SQL logins or contained database users, ensuring compliance with the policy without needing to manually remove logins.

Exam trap

The trap here is that candidates mistakenly think disabling SQL authentication requires manually removing SQL logins or that the setting can be applied per database, when in fact the 'Azure AD-only authentication' property is a server-level toggle that automatically blocks all SQL authentication attempts without needing to delete any logins.

How to eliminate wrong answers

Option A is wrong because removing the Azure AD administrator only disables Entra ID authentication, leaving SQL authentication intact—the opposite of the required outcome. Option B is wrong because removing SQL logins from the master database does not disable SQL authentication for contained database users or future connections; the server still accepts SQL authentication attempts, and contained database users in user databases remain unaffected. Option D is wrong because the 'Azure AD-only authentication' property is only available at the logical server level, not per database; setting it per database is not a supported operation in Azure SQL Database.

Full explanation →
24
MCQhard

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

A.Increase the maximum vCores
B.Create a SQL Agent job to ping the database every hour
C.Disable auto-pause for the serverless database
D.Enable Query Store
AnswerC

Disabling auto-pause prevents the database from pausing, avoiding cold start delays.

Why this answer

The slow first query after auto-resume is caused by the cold-start latency of the serverless compute tier, which includes provisioning resources and warming the buffer pool. Disabling auto-pause ensures the database remains online and the buffer pool stays populated, eliminating the cold-start delay for the first query.

Exam trap

The trap here is that candidates may think increasing vCores or using a ping job solves the cold-start problem, but these options either do not address the root cause or are inefficient workarounds, while disabling auto-pause directly eliminates the latency by keeping the database always active.

How to eliminate wrong answers

Option A is wrong because increasing the maximum vCores does not prevent auto-pause or reduce cold-start latency; it only scales compute resources during active periods. Option B is wrong because a SQL Agent job that pings the database every hour would keep the database from auto-pausing only if the ping interval is shorter than the auto-pause delay (default 1 hour), but this is a workaround that does not address the root cause and can incur unnecessary compute costs. Option D is wrong because enabling Query Store captures query performance data but does not affect the auto-pause behavior or the cold-start latency of the first query after resume.

Full explanation →
25
MCQmedium

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

A.Out-of-date statistics
B.Insufficient IOPS or throughput at the database level
C.Missing indexes
D.Blocking from long-running transactions
AnswerB

PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput.

Why this answer

PAGEIOLATCH_SH waits indicate I/O subsystem pressure, often due to insufficient IOPS or throughput. Option A is incorrect because out-of-date statistics cause cardinality estimation errors, not I/O waits. Option C is incorrect because missing indexes typically cause table scans but not necessarily PAGEIOLATCH waits.

Option D is incorrect because blocking causes waits like LCK_M_*, not PAGEIOLATCH.

Full explanation →
26
Multi-Selecthard

Which THREE actions are required to configure Microsoft Entra ID authentication for an Azure SQL Database? (Choose three.)

Select 3 answers
A.Configure a firewall rule to allow connections from the Microsoft Entra ID service.
B.Set a Microsoft Entra ID administrator for the Azure SQL Database server.
C.Create contained database users in the database mapped to Microsoft Entra identities.
D.Ensure that the Microsoft Entra identity used to connect is a member of the same Azure AD tenant as the server.
E.Ensure that SQL authentication is enabled as a fallback.
AnswersB, C, D

Required to enable Entra ID authentication at the server level.

Why this answer

Setting a Microsoft Entra ID administrator for the Azure SQL Database server is a mandatory step to enable Entra ID authentication at the server level. This action configures the server to accept authentication tokens from the specified Entra ID tenant and allows the creation of contained database users mapped to Entra identities.

Exam trap

The trap here is that candidates often confuse network-level firewall rules with authentication configuration, incorrectly assuming that a special firewall rule is needed for Entra ID traffic, when in fact only IP-based rules are required for network access.

Full explanation →
27
MCQhard

Refer to the exhibit. You are reviewing an Elastic Database Job definition for Azure SQL Database. The job is scheduled to run once on January 15, 2026. Which statement about the job's target is correct?

A.The job will target only the database named 'db1'.
B.The job will target all databases in the server except 'db1'.
C.The job will target the entire logical server.
D.The job will target the server and all its databases.
AnswerB

Exclude membership with a list excludes those databases from the server.

Why this answer

The target group uses membershipType 'Exclude' with a members list containing only 'db1'. This means that the job will target all databases in the logical server except 'db1'. Option A is incorrect because the membership type is 'Exclude', not 'Include', so it does not target only 'db1'.

Option C is incorrect because the target group is at the database level within a server, not the entire server itself. Option D is incorrect because the target group excludes 'db1', so it targets all databases on the server, but not the server as a whole.

Full explanation →
28
MCQhard

Your Azure SQL Managed Instance stores sensitive financial data. You must prevent unauthorized access from Azure services and ensure that only specific virtual networks can connect. You also need to audit all failed login attempts. Which combination of configurations should you implement?

A.Enable Microsoft Defender for SQL and set up anomaly detection alerts.
B.Create a private endpoint, set 'Public network access' to 'Deny', and enable Azure SQL Auditing with a log analytics workspace.
C.Enable SQL vulnerability assessment and configure email notifications.
D.Configure VNet service endpoints and set the firewall to allow only your VNet.
AnswerB

Private endpoint with public access denied secures network; auditing logs failed logins.

Why this answer

Creating a private endpoint for Azure SQL Managed Instance ensures that traffic to the instance traverses the Microsoft backbone network and is only accessible from within the specified virtual network. Setting 'Public network access' to 'Deny' explicitly blocks all public internet traffic, including traffic from other Azure services that do not originate from the approved VNet. Enabling Azure SQL Auditing with a Log Analytics workspace captures all failed login attempts, meeting the audit requirement.

This combination directly addresses the need to prevent unauthorized access from Azure services and enforce VNet-only connectivity while auditing failed logins.

Exam trap

The trap here is that candidates often confuse VNet service endpoints with private endpoints, assuming service endpoints provide the same level of isolation and access control, but service endpoints still leave the resource publicly resolvable and do not block all Azure service traffic.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for SQL and anomaly detection alerts are security monitoring and threat detection features, not network access controls; they do not restrict which virtual networks can connect or block Azure services. Option C is wrong because SQL vulnerability assessment and email notifications are for identifying and reporting database vulnerabilities, not for controlling network access or auditing failed login attempts. Option D is wrong because VNet service endpoints do not provide the same level of isolation as private endpoints; they still expose the instance to the public endpoint and do not prevent access from other Azure services that are not in the VNet, and they do not inherently audit failed logins.

Full explanation →
29
MCQmedium

Refer to the exhibit. A DBA is creating an Azure SQL Managed Instance using the ARM template snippet shown. The DBA needs to ensure the instance can survive a regional outage without data loss. What change should be made to the template?

A.Set collation to SQL_Latin1_General_CP1_CS_AS
B.Set zoneRedundant to true
C.Set zoneRedundant to true and configure an auto-failover group with a secondary region
D.Change storageAccountType to RA-GRS
AnswerC

Failover group with a secondary region provides regional disaster recovery.

Why this answer

Azure SQL Managed Instance does not natively support zone redundancy for regional outage protection; instead, it requires configuring an auto-failover group with a secondary instance in a paired region. Setting zoneRedundant to true alone only protects against zone failures within a single region, not a full regional outage. The auto-failover group ensures continuous replication and automatic failover to the secondary region, meeting the requirement of surviving a regional outage without data loss.

Exam trap

The trap here is that candidates often confuse zone redundancy (which protects against datacenter failures within a region) with regional disaster recovery, and incorrectly assume that setting zoneRedundant to true is sufficient for surviving a regional outage, when in fact Azure SQL Managed Instance requires an auto-failover group with a secondary region for that purpose.

How to eliminate wrong answers

Option A is wrong because changing collation to SQL_Latin1_General_CP1_CS_AS affects character set and case sensitivity, not high availability or disaster recovery; it has no impact on regional outage survival. Option B is wrong because setting zoneRedundant to true only provides zone-level redundancy within a single region, which does not protect against a complete regional outage; it is also not supported for Azure SQL Managed Instance (zone redundancy is available only for Azure SQL Database, not Managed Instance). Option D is wrong because storageAccountType is not a property of Azure SQL Managed Instance; it applies to Azure Storage accounts, and changing it to RA-GRS would not affect the managed instance's replication or failover behavior.

Full explanation →
30
MCQeasy

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

A.Enable Azure SQL Insights (preview) for the database.
B.Enable Query Store and configure it to export to Log Analytics.
C.In the Azure portal, add a diagnostic setting for the database to stream 'AllMetrics' to a Log Analytics workspace.
D.Create a T-SQL job that periodically inserts sys.dm_db_resource_stats into a table in Log Analytics.
AnswerC

Diagnostic settings can stream metrics to Log Analytics for cost-effective long-term analysis.

Why this answer

Diagnostic settings in the Azure portal allow streaming of platform metrics (including 'AllMetrics' for CPU percentage, data IO, log IO) to a Log Analytics workspace cost-effectively. Option A is incorrect because Azure SQL Insights is a paid, advanced monitoring solution that is not necessary for basic metric streaming. Option B is incorrect because Query Store captures query performance data, not resource metrics, and cannot export directly to Log Analytics.

Option D is incorrect because manually inserting from sys.dm_db_resource_stats via a T-SQL job is inefficient, not built-in, and lacks the integration of diagnostic settings.

Full explanation →
31
Multi-Selectmedium

Which THREE components are part of an Azure SQL Managed Instance automated maintenance window configuration? (Choose three.)

Select 3 answers
A.Start time (e.g., 2:00 AM local time)
B.Day of week (e.g., Sunday)
C.Database-level maintenance schedule
D.Maximum number of concurrent databases
E.Notification email for upcoming maintenance
AnswersA, B, E

You can specify the start time of the maintenance window.

Why this answer

Azure SQL Managed Instance allows you to configure a maintenance window including the day of week (e.g., Sunday), start time (e.g., 2:00 AM local time), and a notification email for upcoming maintenance. Option C is incorrect because maintenance windows are instance-level, not per database. Option D is incorrect because it applies to Azure SQL Database, not Managed Instance.

Full explanation →
32
MCQmedium

Refer to the exhibit. You are reviewing a script that is executed as part of a data migration to Azure SQL Database. The script attempts to insert a row with a specific OrderID into an Orders table that has an identity column. The script runs successfully in a test environment but fails in production with an error. The production environment has the same schema. What is the most likely cause?

A.The user running the script does not have ALTER permission on the table
B.The table in production does not have an identity column
C.A different session is already using SET IDENTITY_INSERT ON for the same table
D.The identity column in production has a different seed value
AnswerA

SET IDENTITY_INSERT requires ALTER permission on the table.

Why this answer

The script uses SET IDENTITY_INSERT ON to allow explicit insertion into an identity column. In Azure SQL Database, this requires ALTER permission on the table, not just INSERT. The test environment likely granted the user ALTER, but production did not, causing the failure despite identical schema.

Exam trap

The trap here is that candidates assume INSERT permission is sufficient for inserting into an identity column, overlooking that SET IDENTITY_INSERT ON is a schema-modifying operation requiring ALTER permission.

How to eliminate wrong answers

Option B is wrong because the exhibit shows the script uses SET IDENTITY_INSERT ON, which only applies to tables with an identity column; if production lacked one, the script would fail with a different error (e.g., 'Cannot insert explicit value for identity column in table...') or the SET statement would be invalid. Option C is wrong because SET IDENTITY_INSERT ON is session-scoped; multiple sessions can use it simultaneously on different tables, but only one session at a time can have it ON for the same table—if another session already had it ON, the script would fail immediately with an error about a conflicting session, not a permission error. Option D is wrong because a different seed value does not cause an error; it only affects the next auto-generated value, and explicit inserts are allowed regardless of seed.

Full explanation →
33
Multi-Selecthard

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

Select 3 answers
A.Enable adaptive joins
B.Enable interleaved execution for MSTVFs
C.Enable Query Store
D.Enable columnstore indexes
E.Use approximate count distinct
AnswersA, B, E

Part of IQP for dynamic join strategy selection.

Why this answer

Adaptive joins (A), interleaved execution for multi-statement table-valued functions (B), and approximate count distinct (E) are all features of Intelligent Query Processing (IQP) in Azure SQL Database. They improve query performance by dynamically adjusting join strategies, avoiding suboptimal plans for MSTVFs, and efficiently handling COUNT(DISTINCT) queries. Query Store (C) is a monitoring and tuning feature but it is not part of IQP.

Columnstore indexes (D) are a storage and indexing technique, not part of IQP.

Full explanation →
34
MCQeasy

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

A.The query is experiencing I/O latency
B.The query is using a lot of CPU time
C.The query is using a lot of memory
D.The query is reading many pages from the buffer pool, possibly due to missing indexes
AnswerD

Logical reads are page reads from the buffer pool, high values indicate excessive data access.

Why this answer

High total_logical_reads typically indicates that the query is reading many pages from the buffer pool, which often points to missing or inefficient indexes. Option D is correct. Option A is incorrect because high logical reads relate to buffer pool access, not necessarily I/O latency (which is indicated by high physical reads).

Option B is incorrect because CPU time is measured by worker_time, not logical reads. Option C is incorrect because while logical reads can increase memory usage, the primary indicator of memory usage is the memory grant, not logical reads.

Full explanation →
35
MCQhard

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

A.Use Azure Backup for SQL Server in Azure VM to back up the managed instance.
B.Set PITR retention to 7 days and use geo-redundant backup storage for LTR.
C.Set PITR retention to 2 hours and configure a custom backup job using Elastic Database Jobs.
D.Set PITR retention to 7 days (default) and configure LTR backup policy with yearly backups for 5 years.
AnswerD

Managed Instance supports both PITR and LTR.

Why this answer

Azure SQL Managed Instance supports point-in-time restore (PITR) with a configurable retention period from 1 to 35 days; the default is 7 days, which satisfies the requirement to restore within 2 hours for the last 7 days (the 2-hour target is a recovery point objective, not retention). For long-term retention (LTR) of 5 years, Managed Instance supports LTR backup policies with yearly backups that can retain backups for up to 10 years. Therefore, setting PITR retention to 7 days (default) and configuring an LTR policy with yearly backups for 5 years meets both requirements.

Option A is wrong because Azure Backup for SQL Server in Azure VM is for SQL Server on Azure VMs, not for Managed Instance. Option B is wrong because "geo-redundant backup storage for LTR" is a storage redundancy option, not an LTR retention policy; LTR requires explicit backup frequency and retention configuration. Option C is wrong because PITR retention cannot be set to 2 hours (minimum is 1 day) and custom backup jobs using Elastic Database Jobs are unnecessary; Managed Instance automates backups natively.

Full explanation →
36
MCQhard

You have an Azure SQL Database that is configured with automatic failover groups. During a planned failover, you notice that the failover takes longer than expected. You need to minimize downtime during future planned failovers. What should you do?

A.Increase the service tier of the secondary to match the primary.
B.Remove the failover group and use a different disaster recovery strategy.
C.Pre-seed the secondary replica by initiating a manual sync before the planned failover.
D.Use a forced failover with data loss to speed up the process.
AnswerC

Reduces synchronization time during failover.

Why this answer

Pre-seeding the secondary replica by initiating a manual sync before the planned failover ensures that the secondary is fully up to date, reducing the synchronization time during the actual failover and thereby minimizing downtime. Option A is incorrect because increasing the service tier of the secondary does not reduce failover time; it only improves performance. Option B is incorrect because removing the failover group would eliminate the failover capability, not improve planned failover speed.

Option D is incorrect because a forced failover with data loss is for unplanned scenarios and would cause data loss, which is not recommended for planned failovers.

Full explanation →
37
Multi-Selecthard

Which THREE of the following are prerequisites for configuring an Always On Availability Group on Azure VMs with automatic failover? (Choose Three.)

Select 3 answers
A.All VMs must be in the same availability set or availability zones.
B.A domain controller in the same virtual network.
C.All VMs must use SQL Server authentication.
D.All VMs must be in the same Azure region.
E.An internal load balancer for the availability group listener.
AnswersA, B, E

Required for high availability.

Why this answer

Options A, B, and E are correct. A domain controller (B) is needed for Windows Server Failover Clustering. All VMs must be in the same availability set or availability zones (A) to ensure high availability.

An internal load balancer (E) is required for the availability group listener to support automatic failover. Option C is incorrect because SQL Server authentication is not a prerequisite; Windows Authentication is used for cluster communication. Option D is incorrect because VMs can be in different regions, but automatic failover across regions is not supported without additional configuration (e.g., Azure Site Recovery).

Full explanation →
38
Multi-Selecthard

Which THREE factors should you consider when choosing between Azure SQL Database and Azure SQL Managed Instance for a new workload?

Select 3 answers
A.Need for a static IP address
B.Lift-and-shift migration of on-premises databases
C.Requirement for cross-database queries
D.Both support automated backups
E.Use of SQL Server Agent jobs
AnswersB, C, E

Managed Instance offers higher compatibility for existing databases.

Why this answer

Azure SQL Managed Instance is designed for lift-and-shift migrations of on-premises databases, offering near 100% compatibility with SQL Server features like instance-level configurations, linked servers, and SQL Server Agent. Azure SQL Database, as a Platform-as-a-Service (PaaS) offering, lacks these instance-scoped capabilities, making Managed Instance the preferred choice for migrating existing databases with minimal changes.

Exam trap

The trap here is that candidates often assume static IP addresses are a key differentiator, but Azure SQL Managed Instance does not provide a static public IP by default, and both services can use Private Link for stable private IPs, making this a red herring.

Full explanation →
39
MCQmedium

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

A.Use a server-level firewall rule to restrict access to the tenant's IP.
B.Create a contained database user with db_owner role in each tenant's database and use Microsoft Entra authentication.
C.Create a server-level login and assign it as db_owner on all databases.
D.Create a database-level firewall rule for each tenant database.
AnswerB

Contained users are scoped to the database.

Why this answer

Creating a contained database user with the db_owner role in each tenant's database, using Microsoft Entra authentication, ensures that the tenant admin can only manage their own database. Contained database users are scoped to the individual database, not the logical server, so they cannot access other databases on the same server. This aligns with the principle of least privilege for multi-tenant isolation.

Exam trap

The trap here is that candidates often confuse server-level logins with database-level contained users, assuming that assigning db_owner via a server login is sufficient for isolation, but it actually grants cross-database access.

How to eliminate wrong answers

Option A is wrong because a server-level firewall rule restricts access based on IP address, not database-level permissions; it would allow or block network access to the entire server, not isolate tenant admins to their own database. Option C is wrong because creating a server-level login and assigning it as db_owner on all databases would grant the tenant admin full control over every database on the server, violating multi-tenant isolation. Option D is wrong because a database-level firewall rule controls network access at the database level but does not manage authentication or authorization; it cannot prevent a user from connecting to other databases if they have server-level credentials.

Full explanation →
40
MCQeasy

You are planning a disaster recovery strategy for an Azure SQL Database that is used by a non-critical application. The database is in the Standard tier. You need to minimize cost while ensuring the database can be restored to a different region within 12 hours of a regional disaster. What should you use?

A.Enable cross-region replication on the database.
B.Use geo-restore from geo-redundant backups.
C.Configure active geo-replication to a secondary region.
D.Create a failover group with the secondary in a paired region.
AnswerB

Geo-restore is available for Standard tier and meets the 12-hour requirement.

Why this answer

Geo-redundant backup storage (RA-GRS) is automatically enabled for databases in the Standard tier, allowing geo-restore to any region within 12 hours. Option A is wrong because active geo-replication is not available in Standard tier. Option C is wrong because failover groups require Premium or Business Critical.

Option D is wrong because cross-region replication is a setting for backup storage, not a separate feature.

Full explanation →
41
MCQmedium

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

A.Configure a SQL Agent alert
B.Configure Azure SQL Auditing
C.Use Azure Advisor recommendations
D.Create a metric alert in Azure Monitor
AnswerD

Azure Monitor supports metric alerts for DTU consumption.

Why this answer

Azure Monitor can create metric alerts based on DTU consumption. Option D is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU.

Option B is wrong because Azure SQL Auditing is for compliance. Option C is wrong because Azure Advisor provides recommendations but not real-time alerts.

Full explanation →
42
MCQeasy

You need to ensure that an Azure SQL Database uses Microsoft Entra-only authentication. You have configured the server to disallow SQL authentication. What additional step is required to prevent users from creating SQL logins?

A.Revoke the 'CREATE LOGIN' permission from all users except the Entra admin.
B.Remove the 'loginmanager' fixed server role from all users.
C.Enable auditing to detect login creation attempts and alert the security team.
D.No further action needed; disallowing SQL authentication prevents any SQL login creation.
AnswerA

This prevents users from creating new SQL logins.

Why this answer

Disallowing SQL authentication at the server level prevents users from authenticating with SQL logins, but it does not revoke the ability to create new SQL logins. Users with the 'loginmanager' fixed server role or the 'CREATE LOGIN' permission can still create SQL logins, which would exist in the master database even though they cannot be used for authentication. To fully enforce Microsoft Entra-only authentication, you must explicitly revoke the 'CREATE LOGIN' permission from all users except the Entra admin, ensuring no new SQL logins can be created.

Exam trap

The trap here is that candidates assume disallowing SQL authentication implicitly prevents SQL login creation, but in Azure SQL Database, authentication and authorization are separate concerns—the setting only controls authentication, not the ability to create logins.

How to eliminate wrong answers

Option B is wrong because removing the 'loginmanager' fixed server role does not prevent users who have been individually granted the 'CREATE LOGIN' permission from creating logins; it only removes the role-based grant. Option C is wrong because enabling auditing only detects login creation attempts but does not prevent them, leaving the security gap open. Option D is wrong because disallowing SQL authentication only blocks authentication attempts, not the creation of SQL logins; users with appropriate permissions can still create logins that remain dormant but could be exploited if authentication is later re-enabled.

Full explanation →
43
Multi-Selectmedium

Which THREE of the following are features that help protect against data exfiltration in Azure SQL Database? (Choose three.)

Select 3 answers
A.Always Encrypted
B.Dynamic Data Masking (DDM)
C.Transparent Data Encryption (TDE)
D.Azure SQL Database firewall
E.Row-Level Security (RLS)
AnswersA, D, E

Prevents the database engine from seeing plaintext data.

Why this answer

Always Encrypted is correct because it encrypts sensitive data at the client-side, ensuring that the encryption keys are never revealed to the database engine. This prevents even database administrators or attackers with access to the server from reading the plaintext data, directly protecting against data exfiltration.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking (DDM) with encryption, thinking it prevents data exfiltration, when in fact it only hides data from specific users and does not protect against direct database access or file theft.

Full explanation →
44
Multi-Selecthard

Which THREE considerations are important when planning a migration from SQL Server on-premises to Azure SQL Managed Instance?

Select 3 answers
A.File tables and FILESTREAM.
B.Cross-database queries using three-part names.
C.SQL Server Integration Services (SSIS) packages.
D.Use of contained databases.
E.SQL Server Agent jobs that use PowerShell.
AnswersA, B, E

File tables are not supported in SQL Managed Instance.

Why this answer

Azure SQL Managed Instance does not support FILESTREAM or FileTables. These features rely on the NTFS file system for storing BLOB data, which is not available in the managed instance environment. Any migration using FILESTREAM or FileTables must be refactored to use alternative storage, such as Azure Blob Storage with the remote BLOB store (RBS) or separate file storage.

Exam trap

The trap here is that candidates often assume SSIS packages are unsupported in Azure SQL Managed Instance, but they are actually supported via Azure Data Factory, while the real blockers are the less obvious features like FILESTREAM, cross-database three-part names, and PowerShell-based SQL Agent jobs that rely on on-premises Windows authentication.

Full explanation →
45
MCQeasy

You need to automatically scale an Azure SQL Database based on workload patterns. Which Azure feature should you use?

A.Serverless compute tier
B.Provisioned tier with auto-scale setting
C.Azure Automation runbook that modifies the service tier
D.Elastic Database Jobs to add replicas
AnswerA

Serverless automatically scales compute resources based on workload.

Why this answer

Azure SQL Database's serverless compute tier automatically scales based on workload. Option B is wrong because auto-scaling is not available in provisioned tier without manual intervention. Option C is wrong because Azure Automation with runbooks can perform scaling but is less efficient than serverless.

Option D is wrong because Elastic Database Jobs are for multi-database operations, not scaling.

Full explanation →
46
Multi-Selecteasy

Which TWO conditions must be met to use Azure SQL Database serverless compute tier?

Select 2 answers
A.The database must use the DTU purchasing model
B.The database must be in the Hyperscale service tier
C.The database must be a single database, not part of an elastic pool
D.The database must be in the General Purpose service tier
E.The database must be on a Gen5 hardware
AnswersC, E

Serverless is only for single databases.

Why this answer

Azure SQL Database serverless compute tier is designed only for single databases, not for databases in elastic pools. The serverless tier auto-scales compute resources based on workload demand and pauses during idle periods, which is incompatible with the shared resource model of elastic pools where multiple databases share a fixed set of resources.

Exam trap

The trap here is that candidates often assume serverless is tied to a specific service tier (like Hyperscale) or purchasing model (like DTU), when in fact it requires the vCore model, General Purpose tier, single database, and Gen5 hardware—and the question asks for two conditions, so you must pick both C and E, not just one.

Full explanation →
47
MCQeasy

You are implementing a new Azure SQL Database and need to ensure that connections from client applications are encrypted using TLS 1.2 or higher. Which server-level firewall rule setting should you configure?

A.Deny public network access
B.Enable contained database authentication
C.Allow Azure Services and resources to access this server
D.Set minimal TLS version to 1.2
AnswerD

Enforces TLS 1.2 or higher for all connections.

Why this answer

The 'Minimal TLS version' server-level firewall rule setting enforces the minimum TLS version for encrypted connections to Azure SQL Database. By setting this to 1.2, the server will reject any connection attempts using TLS 1.0 or 1.1, ensuring all client applications use TLS 1.2 or higher. This setting is configured in the Azure portal under the 'Networking' blade of the SQL server resource.

Exam trap

The trap here is that candidates confuse network access controls (like firewall rules or service endpoints) with encryption protocol enforcement, assuming that 'Allow Azure Services' or 'Deny public network access' somehow impacts TLS version requirements.

How to eliminate wrong answers

Option A is wrong because 'Deny public network access' controls whether connections from the public internet are allowed, not the encryption protocol version used for those connections. Option B is wrong because 'Enable contained database authentication' relates to authentication of users within the database itself, not to encryption or TLS version enforcement. Option C is wrong because 'Allow Azure Services and resources to access this server' is a firewall rule that permits traffic from other Azure services, but it does not enforce any specific TLS version for those connections.

Full explanation →
48
Multi-Selecteasy

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

Select 1 answer
A.Azure Site Recovery
B.Azure Blob Storage snapshots
C.Azure Backup for SQL Server in Azure VMs
D.Azure Storage lifecycle management
E.Azure SQL Database long-term retention (LTR) backup policy
AnswersE

Azure SQL Database long-term retention (LTR) backup policy is the built-in feature that allows configuring retention up to 10 years, beyond the default 7-35 days.

Why this answer

Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

Full explanation →
49
Multi-Selectmedium

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Select 2 answers
A.Update statistics on the tables.
B.Upgrade to Business Critical tier.
C.Increase MAXDOP to 8.
D.Enable automatic tuning.
E.Create nonclustered indexes on columns used in WHERE clauses.
AnswersA, E

Updated statistics help the optimizer choose better execution plans, potentially avoiding scans.

Why this answer

Updating statistics (A) helps the query optimizer generate more accurate execution plans, potentially avoiding table scans. Creating nonclustered indexes on columns used in WHERE clauses (E) can provide direct access paths, reducing the need for full table scans. Both actions improve performance without increasing costs.

Option B (upgrading to Business Critical) increases cost and may not directly address table scans. Option C (increasing MAXDOP) can cause parallelism issues and is not guaranteed to reduce scans. Option D (enabling automatic tuning) is a feature that can suggest index and plan changes but is not as direct as updating statistics or creating indexes.

Full explanation →
50
MCQhard

You are a database administrator for a financial services company that uses Azure SQL Database for a critical trading application. The application connects using a service principal (Microsoft Entra application) and executes stored procedures. You need to implement the following security requirements: 1. All connections must use Microsoft Entra authentication with MFA enforced for the service principal. 2. The application should only be able to execute specific stored procedures (usp_Trade, usp_GetQuote) and no other operations. 3. All data at rest must be encrypted using customer-managed keys stored in Azure Key Vault. 4. Auditing must capture all failed login attempts and all changes to the database schema. 5. The database must be protected against SQL injection attacks from the application layer. You have already configured Microsoft Entra authentication and enabled TDE with customer-managed key in Azure Key Vault. Which additional steps should you take to meet all remaining requirements?

A.Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.
B.Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.
C.Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.
D.Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.
AnswerA

All requirements met.

Why this answer

It addresses all remaining requirements: (1) A database role with EXECUTE permission on the required stored procedures restricts the service principal to only executing those procedures. (2) A Conditional Access policy enforcing MFA for the service principal meets the MFA requirement. (3) Audit enabled for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes. (4) Using parameterized queries prevents SQL injection. Option B is incorrect because dynamic data masking does not prevent SQL injection and does not enforce MFA. Option C is incorrect because a contained database user with EXECUTE permission does not enforce MFA, and Always Encrypted does not prevent SQL injection.

Option D is incorrect because row-level security restricts data access but does not prevent SQL injection, and Microsoft Defender for SQL only detects but does not prevent SQL injection; also, no MFA enforcement is mentioned.

Full explanation →
51
MCQeasy

A company has an Azure SQL Database that experiences periodic performance degradation. The database uses the General Purpose service tier. You need to identify the most common performance bottlenecks. You enable the Query Store and collect data for a week. Which Query Store view should you query to find queries that have the highest total resource consumption over time?

A.sys.query_store_query_stats
B.sys.query_store_plan
C.sys.dm_exec_query_stats
D.sys.dm_db_resource_stats
AnswerA

This view provides aggregated runtime statistics like total CPU, IO, and duration for each query.

Why this answer

A is correct because sys.query_store_query_stats aggregates runtime statistics per query across all plans and time intervals, making it the ideal view to identify queries with the highest total resource consumption (e.g., CPU, I/O, duration) over the collected week. Query Store captures historical execution data, and this view provides the cumulative metrics needed to pinpoint the most resource-intensive queries for performance bottleneck analysis.

Exam trap

The trap here is that candidates confuse sys.dm_exec_query_stats (a live, cache-dependent DMV) with the Query Store's historical views, assuming both provide the same aggregated data, but only Query Store views retain data across plan evictions and time intervals for long-term analysis.

How to eliminate wrong answers

Option B is wrong because sys.query_store_plan stores plan-level metadata (e.g., plan ID, compilation parameters) but does not contain aggregated runtime statistics like total CPU or duration, so it cannot show highest resource consumption. Option C is wrong because sys.dm_exec_query_stats is a dynamic management view that shows cached query execution statistics only for currently cached plans, not historical data over a week; it resets on plan eviction or service restart, making it unsuitable for long-term trend analysis. Option D is wrong because sys.dm_db_resource_stats provides per-minute resource usage metrics for the database (e.g., DTU, CPU, I/O) at the database level, not per-query, so it cannot identify specific queries with high resource consumption.

Full explanation →
52
MCQhard

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

A.It configures the database to pause during a disaster recovery scenario.
B.It controls the automatic pausing of the database after a period of inactivity to save costs.
C.It sets the maximum duration for which the database can be paused.
D.It determines how long the database takes to resume after a pause.
AnswerB

Serverless databases pause after inactivity to reduce costs.

Why this answer

The AutoPauseDelayInMinutes parameter is used with Azure SQL Database serverless compute tier. It specifies the number of minutes of inactivity (no CPU usage or active sessions) after which the database automatically pauses, stopping compute billing while storage remains billed. This makes the database cost-effective for intermittent development workloads because it eliminates compute costs during idle periods and automatically resumes on the first connection.

Exam trap

The trap here is that candidates confuse the auto-pause feature with a manual pause/resume operation or with a scheduled shutdown, and they incorrectly assume AutoPauseDelayInMinutes controls resume speed or maximum pause duration, when in fact it only defines the inactivity threshold before automatic pausing occurs.

How to eliminate wrong answers

Option A is wrong because AutoPauseDelayInMinutes does not relate to disaster recovery; disaster recovery is handled by geo-replication, failover groups, or backup/restore, not by pausing. Option C is wrong because the parameter does not set a maximum pause duration; the database remains paused indefinitely until a connection or activity triggers an automatic resume, and there is no configurable maximum pause time. Option D is wrong because the resume time after a pause is not controlled by AutoPauseDelayInMinutes; resume latency is determined by the underlying serverless infrastructure (typically 30-60 seconds) and is not configurable via this parameter.

Full explanation →
53
Multi-Selectmedium

You are tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Select 2 answers
A.Add query hints to force index usage
B.Create a nonclustered index on the columns used in WHERE clause
C.Rewrite the query as a stored procedure
D.Increase the database max memory setting
E.Update statistics on the tables involved
AnswersB, E

Index can reduce the number of rows read.

Why this answer

Creating appropriate indexes, such as a nonclustered index on columns used in WHERE clauses, can reduce logical reads by enabling index seeks instead of full table scans. Updating statistics helps the query optimizer choose efficient execution plans, potentially reducing logical reads. Adding query hints may force an index but does not guarantee reduced reads and can be counterproductive.

Rewriting as a stored procedure does not directly reduce logical reads. Increasing database max memory does not reduce logical reads.

Full explanation →
54
Multi-Selecteasy

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

Select 2 answers
A.Data IO percentage consistently below 20%
B.Session percent consistently below 10%
C.Log write percent consistently above 90%
D.Memory consumption consistently below 30%
E.DTU/CPU consumption consistently above 90%
AnswersC, E

High log write percent indicates the log rate is throttled.

Why this answer

High DTU/CPU consumption (E) and high log write percent (C) both indicate that the database is approaching or hitting resource limits, suggesting a need to scale up. Options A, B, and D are incorrect because consistently low metrics (data IO, session percent, memory) indicate underutilization, not a need to scale up.

Full explanation →
55
MCQeasy

You have an Azure SQL Database that stores financial data. You need to audit all SELECT operations on the 'Transactions' table and store the audit logs in an Azure Storage account. What should you use?

A.Enable Microsoft Defender for Cloud on the server and review security insights.
B.Create a server audit using SQL Server Audit and specify a file destination.
C.Enable diagnostic settings for the database and send to a Log Analytics workspace.
D.Configure server-level auditing to log to an Azure Storage account.
AnswerD

Server-level auditing can capture all events including SELECT on specific tables and store them in Azure Storage.

Why this answer

Azure SQL Database supports server-level auditing that can write audit logs directly to an Azure Storage account. This meets the requirement to audit SELECT operations on the 'Transactions' table and store logs in Azure Storage. Server-level auditing captures all database events, including SELECT statements, and can be configured to target a storage container.

Exam trap

The trap here is that candidates often confuse diagnostic settings (which send metrics and logs to Log Analytics) with server-level auditing (which captures detailed database activity like SELECT operations), leading them to choose Option C instead of the correct server-level audit configuration.

How to eliminate wrong answers

Option A is wrong because Microsoft Defender for Cloud provides security alerts and vulnerability assessments, not granular audit logging of specific table operations like SELECT. Option B is wrong because SQL Server Audit with a file destination is not supported in Azure SQL Database; it only supports Azure Storage, Event Hubs, or Log Analytics as targets. Option C is wrong because diagnostic settings send metrics and resource logs to Log Analytics, not audit logs for specific table-level SELECT operations, and they do not provide the same level of granular auditing as server-level auditing.

Full explanation →
56
MCQmedium

You have an Azure SQL Database in the General Purpose tier. You need to implement a disaster recovery solution that provides an RPO of 5 seconds and an RTO of 1 hour. The solution must use Azure PaaS features only. What should you do?

A.Create a failover group between two servers in different regions.
B.Configure active geo-replication to a secondary server in a different region.
C.Use geo-restore to recover the database from the latest geo-replicated backup.
D.Configure copy-only backups to a secondary region.
AnswerB

Active geo-replication provides low RPO (seconds) and RTO within 1 hour.

Why this answer

Active geo-replication provides asynchronous replication with a typical RPO of 5 seconds, and manual failover can be completed within the 1‑hour RTO window. Failover groups (Option A) are fully supported for Azure SQL Database (not limited to managed instances) and also meet the RPO/RTO targets, but they are designed to manage multiple databases as a group and introduce additional overhead for a single‑database scenario. Geo‑restore (Option C) has an RPO of up to one hour and a much longer RTO.

Copy‑only backups (Option D) are not a disaster recovery solution.

Full explanation →
57
MCQeasy

You need to deploy an Azure SQL Database that complies with a regulatory requirement to encrypt data at rest using a customer-managed key stored in Azure Key Vault. The database will be used by a line-of-business application. Which feature should you enable?

A.Transparent Data Encryption (TDE) with customer-managed key
B.Azure Defender for SQL
C.Dynamic Data Masking
D.Always Encrypted
AnswerA

TDE encrypts data at rest and supports customer-managed keys.

Why this answer

Transparent Data Encryption (TDE) with a customer-managed key (CMK) in Azure Key Vault encrypts the database at rest, meeting the regulatory requirement for customer-controlled encryption keys. TDE performs real-time I/O encryption and decryption of data files, logs, and backups, and when configured with a CMK (also known as Bring Your Own Key or BYOK), the encryption key is stored and managed in Azure Key Vault, giving the customer full control over key rotation and revocation.

Exam trap

The trap here is that candidates often confuse Always Encrypted (which encrypts data at rest and in transit but at the column level with client-side keys) with TDE (which encrypts the entire database at rest with server-managed or customer-managed keys), leading them to select Always Encrypted when the requirement is for full database-at-rest encryption with a customer-managed key.

How to eliminate wrong answers

Option B is wrong because Azure Defender for SQL provides advanced security monitoring, vulnerability assessments, and threat detection, but it does not encrypt data at rest or manage encryption keys. Option C is wrong because Dynamic Data Masking limits exposure of sensitive data by obfuscating it in query results to non-privileged users, but it does not encrypt data at rest. Option D is wrong because Always Encrypted protects sensitive data in transit and at rest by encrypting columns with client-side keys, but it does not use a customer-managed key stored in Azure Key Vault for the entire database at rest encryption; it is designed for column-level encryption with keys managed by the client application.

Full explanation →
58
MCQeasy

You are responsible for managing a fleet of 20 Azure SQL Databases used by different departments. Each database has its own schema and data. You need to automate the creation of weekly exports of each database to a BACPAC file stored in a specific Azure Storage container. The exports should be done outside business hours (Sundays at 2 AM). You also need to ensure that the export process does not impact production performance. What is the most straightforward and cost-effective solution?

A.Use Elastic Database Jobs to run a SQL script that exports the database using the BACPAC export command.
B.Use Azure Data Factory with a copy activity to export the database to BACPAC via the SQL Server Import/Export service.
C.Create an Azure Automation runbook that uses the Export-AzSqlDatabase cmdlet for each database, and schedule the runbook to run weekly on Sundays at 2 AM.
D.Manually export each database using Azure Portal once a week.
AnswerC

Simple, cost-effective, and uses built-in cmdlets.

Why this answer

Azure Automation runbooks can use the Export-AzSqlDatabase cmdlet to export each database to BACPAC, and scheduling the runbook for Sundays at 2 AM meets the automation and off-hours requirement, minimizing performance impact. This is straightforward and cost-effective as it uses built-in Azure Automation without extra services. Option A (Elastic Database Jobs) is designed for running T-SQL scripts across databases, not for exporting BACPAC files.

Option B (Azure Data Factory) adds unnecessary complexity and cost for this simple export task. Option D is manual and not automated.

Full explanation →
59
Multi-Selecthard

You are responsible for automating the deployment of an Azure SQL Database with associated firewall rules, a Microsoft Entra ID admin, and a vulnerability assessment baseline. The deployment must be idempotent and repeatable. Which THREE tools or approaches should you use? (Choose three.)

Select 3 answers
A.SQL Server Management Studio.
B.Bicep template.
C.ARM template.
D.Azure DevOps release pipeline.
E.Azure portal.
AnswersB, C, D

Bicep is a declarative language that compiles to ARM templates.

Why this answer

Bicep templates (B) and ARM templates (C) are both declarative Infrastructure as Code (IaC) tools for defining Azure resources, including Azure SQL Database, firewall rules, Entra ID admin, and vulnerability assessment baselines. An Azure DevOps release pipeline (D) can automate the deployment of these templates, ensuring idempotent and repeatable deployments. SQL Server Management Studio (A) is a management tool for SQL Server, not for IaC or automated deployment.

The Azure portal (E) is a manual GUI and not suitable for repeatable automation.

Full explanation →
60
MCQmedium

Your company has an Azure SQL Database that is accessed by multiple applications. You need to implement a security solution that meets the following requirements: - Each application must have its own database user with specific permissions. - All authentication must use Microsoft Entra ID. - You need to be able to rotate credentials for each application without impacting other applications. - The solution must support automatic credential rotation for service principals. What should you do?

A.Use managed identities for each Azure resource and assign permissions to the database.
B.Create a single Microsoft Entra ID service principal for all applications and assign different database roles.
C.Create SQL logins and users for each application with strong passwords, and configure password rotation policies.
D.Create a Microsoft Entra ID service principal for each application, store the client secret in Azure Key Vault, and create a contained database user mapped to each service principal.
AnswerD

This provides per-application identity, supports credential rotation, and uses Entra ID authentication.

Why this answer

It creates a separate Microsoft Entra ID service principal for each application, stores the client secret in Azure Key Vault for automatic rotation, and creates a contained database user mapped to each service principal. This meets all requirements: per-application users, Microsoft Entra ID authentication, independent credential rotation, and support for automatic rotation via Key Vault. Option A is incorrect because managed identities are tied to specific Azure resources (e.g., VMs, App Services) and cannot be used for all applications (e.g., on-premises).

Additionally, they do not provide a separate credential that can be rotated independently for each application. Option B is incorrect because a single service principal would share the same identity across all applications, preventing individual permissions and making credential rotation affect all applications. Option C is incorrect because SQL logins do not use Microsoft Entra ID authentication.

Full explanation →
61
Multi-Selecthard

Which THREE metrics should you monitor to detect a memory pressure issue in Azure SQL Database?

Select 3 answers
A.avg_io_stall_ms
B.avg_page_life_expectancy
C.log_bytes_used
D.avg_pending_disk_io
E.page_cache_hit_ratio
AnswersB, D, E

Lower page life expectancy indicates memory pressure.

Why this answer

B (avg_page_life_expectancy) is correct because it measures how long (in seconds) a data page stays in the buffer pool before being evicted. A low value (typically below 300 seconds) indicates that pages are being flushed quickly due to memory pressure, forcing more physical I/O. This is a direct indicator of insufficient memory for the buffer cache.

Exam trap

Candidates often mistakenly think that only avg_page_life_expectancy (PLE) and page_cache_hit_ratio indicate memory pressure. However, avg_pending_disk_io is also a key metric: high pending I/O can result from insufficient memory causing frequent buffer pool evictions and increased disk activity. The real trap is confusing avg_io_stall_ms (which measures disk latency) with memory pressure—avg_io_stall_ms is a storage performance metric, not a direct memory pressure indicator.

Full explanation →
62
Multi-Selecteasy

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

Select 2 answers
A.Use Always Encrypted
B.Use a public endpoint with a firewall rule
C.Use a service endpoint
D.Use a site-to-site VPN gateway and connect to public endpoint
E.Use a private endpoint
AnswersC, E

Service endpoint secures traffic to Azure SQL from your VNet without a public IP.

Why this answer

A service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection on the Azure backbone network. This allows you to secure your logical SQL server to accept traffic only from a specific subnet, eliminating the need for a public endpoint while still using the public endpoint's DNS name internally.

Exam trap

The trap here is that candidates confuse network-level access controls (firewall rules, VPNs) with endpoint exposure, mistakenly thinking that encrypting traffic or routing through a VPN eliminates the public endpoint's existence, when in fact the public DNS name and IP remain reachable from the internet.

Full explanation →
63
Multi-Selecteasy

Which TWO of the following are required to enable Microsoft Defender for SQL for Azure SQL Database?

Select 2 answers
A.Configure a Log Analytics workspace.
B.Enable Azure SQL Database auditing.
C.Enable SQL Server Agent.
D.Configure an Azure Key Vault for storing encryption keys.
E.Enable Microsoft Defender for Cloud on the subscription.
AnswersA, E

Defender for SQL uses Log Analytics for data collection.

Why this answer

Microsoft Defender for SQL requires a Log Analytics workspace to store security alerts and vulnerability assessment data. Option E is correct because Microsoft Defender for SQL must be enabled at the subscription level via Microsoft Defender for Cloud, which provides the security center integration. Options B, C, and D are not required for enabling Defender for SQL: Azure SQL Database auditing is separate, SQL Server Agent is not needed, and Azure Key Vault is for encryption key management, not a prerequisite for Defender for SQL.

Full explanation →
64
MCQhard

Your Azure SQL Database is configured with Advanced Threat Protection (ATP). You receive an alert about a SQL injection attack. After investigation, you confirm the attack was blocked. However, you need to ensure that future similar attacks are automatically prevented without manual intervention. What should you configure?

A.Enable Transparent Data Encryption (TDE).
B.Run SQL Vulnerability Assessment weekly.
C.Enable Azure SQL Auditing to log all queries.
D.Configure the firewall to automatically block the attacker's IP address.
AnswerD

The firewall can be set to block IPs after a detected attack.

Why this answer

Configuring the Azure SQL Database firewall to automatically block the attacker's IP address provides a proactive, automated defense against future SQL injection attempts from the same source. Advanced Threat Protection (ATP) can be integrated with Azure Logic Apps or other automation to trigger a firewall rule update that denies traffic from the offending IP, thereby preventing manual intervention. This directly addresses the requirement for automatic prevention of similar attacks.

Exam trap

The trap here is that candidates often confuse detection and logging mechanisms (like auditing or vulnerability assessment) with active prevention controls, failing to recognize that only a firewall rule or network security group can automatically block an IP address without manual intervention.

How to eliminate wrong answers

Option A is wrong because Transparent Data Encryption (TDE) protects data at rest by encrypting the database files, but it does not prevent or block SQL injection attacks, which target the query layer. Option B is wrong because running SQL Vulnerability Assessment weekly identifies configuration weaknesses and missing patches, but it does not provide real-time or automated blocking of attack traffic. Option C is wrong because enabling Azure SQL Auditing logs all queries for forensic analysis and compliance, but it does not actively block or prevent future attacks; it only records them after the fact.

Full explanation →
65
MCQmedium

You manage an Azure SQL Database (General Purpose, S2) used by a reporting application. The database has a table `FactSales` with 500 million rows. Queries that aggregate sales by date are slow. The execution plan shows a clustered index scan on `FactSales`. The table has a clustered index on `SaleID` and a nonclustered index on `DateKey`. The queries filter by `DateKey` and `ProductKey`. You need to improve query performance without changing the service tier. Which action should you take?

A.Create a nonclustered index on ProductKey only.
B.Partition the table by DateKey.
C.Create a clustered columnstore index on the table.
D.Create a covering index on DateKey and ProductKey including the aggregated columns.
AnswerD

Covering index provides index seeks and avoids lookups.

Why this answer

The query filters by DateKey and ProductKey and aggregates sales data. A covering index on DateKey and ProductKey that includes the aggregated columns (e.g., SUM(SalesAmount)) allows the query to be satisfied entirely from the index without touching the clustered index, eliminating the costly clustered index scan. This is the most direct and effective way to improve performance without changing the service tier.

Exam trap

The trap here is that candidates may choose partitioning (Option B) thinking it speeds up all queries by date, but without a covering index, partitioning alone does not eliminate the scan; it only reduces the data scanned to a single partition.

How to eliminate wrong answers

Option A is wrong because creating a nonclustered index on ProductKey only does not address the DateKey filter and would still require key lookups or scans to retrieve the aggregated data, failing to cover the query. Option B is wrong because partitioning the table by DateKey can improve manageability and partition elimination for range scans, but it does not eliminate the need for a covering index; the query would still scan the entire partition(s) unless a suitable index exists. Option C is wrong because a clustered columnstore index is optimized for large-scale data warehousing and analytics workloads, but it is not supported on the General Purpose S2 tier (columnstore requires S3 or higher or Premium tiers), and it would disrupt the existing clustered index on SaleID, potentially harming other workloads.

Full explanation →
66
MCQhard

A company uses Azure SQL Managed Instance and needs to automatically rebuild fragmented indexes weekly during low usage. They also need to update statistics. Which approach should they take?

A.Use Elastic Database Jobs to run index maintenance
B.Use Azure Automation runbook with Invoke-SqlCmd to run maintenance scripts
C.Use Azure Logic Apps with SQL connector
D.Create a SQL Agent job on the managed instance
AnswerB

Azure Automation runbooks with Invoke-SqlCmd allow flexible scheduling and execution of T-SQL scripts for maintenance tasks like index rebuild and statistics update.

Why this answer

Azure Automation runbooks with Invoke-SqlCmd allow custom scheduling and execution of T-SQL scripts for index maintenance and statistics updates. Option A (Elastic Database Jobs) is designed for Azure SQL Database, not Managed Instance. Option C (Azure Logic Apps) can trigger but is not optimal for long-running maintenance tasks.

Option D (SQL Agent job) is available in Azure SQL Managed Instance, but it is less flexible for automated scheduling during low usage windows and may impact performance; Azure Automation provides better orchestration and monitoring capabilities. Therefore, the best approach is to use Azure Automation runbook.

Full explanation →
67
MCQhard

You are troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

A.The failover policy is set to manual.
B.The outage duration is less than the grace period for data loss.
C.The grace period for data loss is too short.
D.The secondary region is also experiencing an outage.
AnswerB

Automatic failover only occurs after the grace period expires.

Why this answer

The grace period for data loss is set to 3600 seconds (1 hour), and the outage lasted only 30 minutes. Automatic failover for Azure SQL Database failover groups will not occur until the grace period expires, as the system waits for the outage to resolve within that time to avoid unnecessary failover and potential data loss. Option A is incorrect because the failover policy is not specified as manual; if it were manual, the automatic failover would not occur regardless of the grace period.

Option C is incorrect because the grace period is actually longer than the outage duration, so it is not too short. Option D is incorrect because there is no indication that the secondary region is also experiencing an outage.

Full explanation →
68
MCQhard

You are designing an automated data movement solution using Azure Data Factory. Data must be copied from an on-premises SQL Server to Azure SQL Database. The solution must support incremental loads and handle schema changes. Which approach should you use?

A.Use Self-Hosted Integration Runtime with a Copy activity configured with incremental loading
B.Use Azure-SSIS Integration Runtime with a Data Flow activity
C.Use Azure Integration Runtime with a Copy activity
D.Use a Stored Procedure activity in Data Factory
AnswerA

Self-hosted IR enables on-premises access, and Copy activity supports incremental loads.

Why this answer

A Self-Hosted Integration Runtime is required to access an on-premises SQL Server, and the Copy activity can be configured for incremental loading using a watermark column. This supports the requirement for incremental loads and schema changes (by using auto-create table). Option B is incorrect because Azure-SSIS IR is for SSIS package execution, not for data copy with incremental loading.

Option C is incorrect because Azure IR cannot access on-premises networks without a self-hosted IR. Option D is incorrect because a Stored Procedure activity is used for executing stored procedures, not for copying data with incremental loading.

Full explanation →
69
MCQeasy

You need to automatically send a weekly email report summarizing the performance metrics (DTU consumption, storage used) of all Azure SQL Databases in a subscription. Which Azure service should you use?

A.Azure Automation Runbook with PowerShell to collect metrics and send email via Send-MailMessage.
B.Azure Logic App with a recurrence trigger, using Azure Monitor connector to get metrics and Office 365 Outlook connector to send email.
C.Elastic Database Job that queries sys.dm_db_resource_stats and sends email via sp_send_dbmail.
D.Azure Data Factory pipeline that runs weekly and sends an email with the report.
AnswerB

Logic Apps have built-in connectors for metrics and email.

Why this answer

Azure Logic Apps provide a no-code/low-code solution with a recurrence trigger to run weekly, the Azure Monitor connector to retrieve metrics (DTU consumption, storage) from Azure SQL Databases, and the Office 365 Outlook connector to send the email report. Option A is less ideal because Azure Automation Runbooks require custom scripting for email and are more complex. Option C is incorrect because Elastic Database Jobs are designed for T-SQL administration tasks across databases, not for gathering metrics via Azure Monitor.

Option D is incorrect because Azure Data Factory focuses on data movement and transformation, not on scheduling email reports with live metrics.

Full explanation →
70
Multi-Selecteasy

You have an Azure SQL Database in the Premium tier configured with a failover group to a secondary region. The secondary region is not readable. You need to ensure that the secondary database is readable for reporting purposes without compromising the failover group's DR capabilities. Which TWO actions should you take? (Select two.)

Select 2 answers
A.Configure active geo-replication in addition to the failover group.
B.Enable read scale-out on the primary database.
C.Add a zone-redundant replica to the secondary region.
D.Change the secondary database's service tier to Business Critical.
E.Set the failover group's secondary type to readable.
AnswersA, E

Correct: Active geo-replication creates a separate readable secondary database that does not impact the existing failover group's DR capabilities, providing a read-only target for reporting.

Why this answer

Setting the failover group's secondary type to 'readable' enables read-only access to the secondary database for reporting, while preserving the failover group's DR capabilities. Option A is correct because configuring active geo-replication in addition to the failover group creates a separate readable secondary database that does not interfere with the existing failover group's DR, providing an alternative method for read-only workloads. Option C is incorrect because adding a zone-redundant replica (a high-availability feature across availability zones) does not affect the readability of the secondary in a failover group.

Option B is incorrect because read scale-out on the primary only allows read-only connections to the primary, not the secondary. Option D is incorrect because changing the secondary's service tier to Business Critical does not automatically make it readable; readability is controlled by the failover group's secondary type.

Full explanation →
71
MCQhard

You are a database architect for a multinational corporation that uses Azure SQL Database for a customer relationship management (CRM) system. The primary database is in the East US region using the Business Critical service tier. The compliance team requires that in the event of a regional disaster, the database can be failed over to a secondary region with zero data loss and an RTO of 30 seconds. Additionally, the secondary region must be able to handle read-only queries during normal operations to reduce load on the primary. You need to design a solution that meets these requirements with the lowest possible latency for write operations. What should you do?

A.Create a secondary database in a paired region using active geo-replication and configure it as readable.
B.Deploy a failover group with a secondary database in a paired region using Business Critical, but do not make the secondary readable.
C.Deploy a failover group with a readable secondary in a paired region using Business Critical, and ensure the failover is planned to achieve zero data loss.
D.Configure zone redundancy on the existing Business Critical database in East US.
AnswerC

Failover group with planned failover can achieve zero data loss, and a readable secondary allows read traffic.

Why this answer

To meet the requirements of zero data loss (RPO=0) and RTO of 30 seconds across regions, a failover group with a readable secondary in a paired region using Business Critical service tier is the best option. Business Critical provides synchronous replication within a region but asynchronous geo-replication across regions; however, a planned failover ensures zero data loss by synchronizing all changes before failover. The readable secondary reduces read load on the primary.

Option A is incorrect because active geo-replication is asynchronous and cannot guarantee zero data loss. Option B is incorrect because it does not make the secondary readable, missing the read workload requirement. Option D is incorrect because zone redundancy protects within a region but not against regional disasters.

Full explanation →
72
MCQhard

You are the database administrator for a hybrid environment with on-premises SQL Server 2022 and Azure SQL Database. All databases use the AdventureWorks schema. You notice that a critical stored procedure runs slower on Azure SQL Database than on-premises. Both have identical indexes and statistics. What is the most likely cause?

A.The version of the cardinality estimator differs between environments.
B.Azure SQL Database uses a different resource governance model (DTU vs vCore).
C.The index fragmentation level is higher on Azure SQL Database due to automatic tuning.
D.Azure SQL Database has intelligent query processing features that are not available on-premises.
AnswerA

On-prem SQL Server 2022 may use legacy CE, while Azure SQL Database uses default CE, leading to different plans.

Why this answer

The cardinality estimator (CE) version can differ between on-premises SQL Server and Azure SQL Database, even with identical indexes and statistics. The CE version is determined by the database compatibility level, and Azure SQL Database may have a different default compatibility level than on-prem, leading to different query plans and performance. Option B is incorrect because DTU vs vCore is a purchasing model that does not inherently slow queries; it affects resource limits but not plan choices.

Option C is incorrect because automatic tuning in Azure SQL Database typically helps performance, and index fragmentation levels would be similar with identical indexes. Option D is incorrect because intelligent query processing features are available on both platforms and generally beneficial, not detrimental.

Full explanation →
73
MCQmedium

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. What should you do?

A.Enable auditing on the database, log to a storage account, and set the retention policy to 7 years.
B.Create an extended events session to capture DDL events and save to a file.
C.Enable change tracking on the database and query the change tracking tables.
D.Enable SQL Server Audit at the server level and specify a file destination.
AnswerA

Auditing captures DDL events and can retain logs for up to 10 years in storage.

Why this answer

Azure SQL Database auditing can be configured at the database level to log all database events, including DDL changes, to a storage account. The retention policy can be set to 7 years directly in the audit settings, ensuring compliance with long-term retention requirements. This is the native, supported method for auditing schema changes on Azure SQL Database.

Exam trap

The trap here is that candidates confuse SQL Server Audit (which supports file destinations on-premises) with Azure SQL Database auditing, which does not support file destinations and requires a storage account, Log Analytics, or Event Hub; they also mistakenly think change tracking or extended events can serve as a compliance audit solution.

How to eliminate wrong answers

Option B is wrong because extended events sessions are primarily for performance monitoring and troubleshooting, not for long-term compliance auditing; they lack built-in retention policies and are not designed for 7-year archival. Option C is wrong because change tracking is designed to track DML changes (INSERT, UPDATE, DELETE) for synchronization scenarios, not DDL schema changes, and it does not provide audit logs with retention. Option D is wrong because SQL Server Audit at the server level with a file destination is not supported on Azure SQL Database; Azure SQL Database only supports database-level auditing, and the file destination is not available—only storage account, Log Analytics, or Event Hub destinations are supported.

Full explanation →
74
Multi-Selecthard

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

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

RLS filters rows based on the user's context.

Why this answer

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

Exam trap

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

Full explanation →
75
Multi-Selecthard

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

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

The task runs the SQL scripts against the database.

Why this answer

Options B, C, and E are correct. An Azure SQL Database deployment task (e.g., SqlAzureSqlDatabaseDeployment task) executes the schema change scripts. An Azure Resource Manager service connection to the SQL Database is required for authentication and permissions.

A YAML pipeline file defines the build and release stages including the deployment steps. Option A is incorrect because an Elastic Database Job agent is used for multi-database management, not for automated deployment from Git for a single database. Option D is incorrect because an Azure Automation Runbook is not part of Azure Pipelines; it is used for process automation outside of CI/CD.

Full explanation →

Page 1 of 13

Page 2

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 →