Courseiva

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

953 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.
AnswerD

A larger log file with proper auto-growth settings reduces the frequency of growth events and improves performance.

Why this answer

Option A is correct because increasing the log file size reduces auto-growth events and improves log throughput; the current size may be too small causing frequent growth and fragmentation. Option B is wrong because transactional replication adds overhead. Option C is wrong because enabling accelerated database recovery does not directly improve log write performance.

Option D is wrong because page compression reduces I/O but not log writes.

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

Option C is correct because 'Log write latency' directly measures the time to write to the log. Option A is wrong because 'Average IO latency' includes data and log. Option B is wrong because 'Log IO percent' measures log throughput, not latency.

Option D is wrong because 'Transaction log usage' measures log file space used.

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

High log write I/O suggests I/O bottleneck.

Why this answer

Options B and D are correct. CPU percentage directly indicates CPU usage, and log write bytes/sec indicates I/O activity for writes. Option A (deadlocks) is not a bottleneck.

Option C (session count) shows concurrency, not resource usage. Option E (storage space) is capacity, not performance.

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

Option B is correct because by default, failover groups use asynchronous replication with a secondary type 'Geo' which may have higher lag. To achieve low lag, you must enable 'Configure secondary type' to 'Readable' (or 'Named' secondary in some configurations) which ensures near-synchronous replication, though still asynchronous. Option A is incorrect because the secondary is readable by default.

Option C is not a standard setting. Option D is unrelated.

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

Options A, C, and D are correct. A planned failover ensures no data loss by synchronizing all transactions before switching. After failover, the new primary should have a readable secondary or geo-replication configured to maintain DR.

Updating the connection string is necessary for applications. Option B is wrong because you want to avoid data loss. Option E is wrong because seeding is automatic.

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

Options A and C are correct. Active geo-replication and failover groups are the two main DR options for Azure SQL Database. Option B is wrong because log shipping is not supported.

Option D is wrong because availability groups are for SQL Server VMs, not Azure SQL Database. Option E is wrong because database mirroring is deprecated.

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

High worker time with low logical reads indicates CPU-intensive operations like sorting or hash joins.

Why this answer

Option A is correct because high CPU consumption (worker time) with low I/O indicates CPU-bound queries, often due to inefficient joins, aggregations, or non-sargable predicates. Option B is wrong because low logical reads suggest not memory pressure. Option C is wrong because parameter sniffing would show varied plans.

Option D is wrong because high worker time with low I/O is not typical for transaction log writes.

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

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

Option A is correct because the metric shows log write IO usage, not CPU. Option B is wrong because it misinterprets the metric. Option C is wrong because it's not about storage size.

Option D is wrong because log write percent is about I/O, not transaction log space.

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

Option B is correct because zone-redundant configuration replicates data across three zones within a region, protecting against a single zone failure. Option A protects within a single zone. Option C is for regional disasters.

Option D does not provide HA.

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

Option C is correct because ASR replicates at the VM level, not application-consistent unless application-consistent snapshots are configured. Option A is incorrect because ASR does not require Always On AG. Option B is incorrect because ASR supports cross-region replication.

Option D is incorrect because backup retention does not affect ASR consistency.

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

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

A.Enable READ COMMITTED SNAPSHOT isolation level.
B.Configure deadlock graph in Extended Events.
C.Enable automatic tuning to force last good plan.
D.Increase the instance vCores to improve concurrency.
AnswerA

Row versioning reduces lock contention and deadlocks.

Why this answer

Option A is correct because enabling READ COMMITTED SNAPSHOT isolation reduces deadlocks by using row versioning. Option B is wrong because increasing instance size does not prevent deadlocks. Option C is wrong because automatic tuning does not handle deadlocks.

Option D is wrong because deadlock graph captures events but doesn't prevent them.

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

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

Option B is correct because zone-redundant deployment for Managed Instance is available in Business Critical tier. Option A is wrong because General Purpose does not support zone redundancy. Option C is wrong because failover groups are for regional disasters, not zonal failures.

Option D is wrong because Hyperscale is not a tier for Managed Instance.

Full explanation →
17
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 →
18
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

Option A and D are correct. Option A identifies I/O bottlenecks by analyzing wait stats (PAGEIOLATCH). Option D reduces I/O by enabling compression.

Option B is wrong because it shows CPU/memory, not I/O. Option C is wrong because it shows resource usage, not per query I/O. Option E is wrong because scaling compute doesn't directly address I/O.

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

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

Option D is correct because SQL Agent jobs in Managed Instance can execute T-SQL scripts across databases using sp_send_dbmail for email, and cross-database queries can log results to a central database. Option A is wrong because elastic jobs target Azure SQL Database, not Managed Instance. Option B is wrong because Azure Automation Runbooks require external connectivity and cannot directly execute T-SQL across databases.

Option C is wrong because Logic Apps are external and require additional configuration.

Full explanation →
22
Matchingmedium

Match each Azure Database for PostgreSQL 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 Database for PostgreSQL.

Full explanation →
23
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
AnswerB

Automatic tuning can automatically create, drop, and rebuild indexes based on workload patterns.

Why this answer

Option D is correct because Azure SQL Database's automatic tuning includes index management that can be configured to fix fragmentation automatically. Option A is wrong because Azure Automation with runbooks requires custom scripts and may not be as efficient. Option B is wrong because Elastic Database Jobs are for multi-database tasks, not single-database index maintenance.

Option C is wrong because SQL Agent Jobs are not available in Azure SQL Database single databases.

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

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

Option D is correct because the serverless database auto-pauses after inactivity; disabling auto-pause keeps the database warm. Option A is incorrect because scaling vCores does not affect cold start delay. Option B is incorrect because Query Store does not speed up cold start.

Option C is incorrect because creating a job to ping the database is a workaround but not the recommended solution; disabling auto-pause is simpler.

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

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

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

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

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

Option D is correct because the target group uses membershipType 'Exclude' with members list containing one database, meaning all databases in the logical server except 'db1' will be targeted. Option A is wrong because the membership type is 'Exclude', not 'Include'. Option B is wrong because the target group is for a single database, but it excludes that database, so it targets all others.

Option C is wrong because the target group is at the server level, not database level.

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

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

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

Option C is correct because diagnostic settings can stream metrics to Log Analytics. Option A is wrong because Query Store does not send metrics to Log Analytics. Option B is wrong because DMVs do not stream automatically.

Option D is wrong because SQL Insights is a paid solution.

Full explanation →
32
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 (day of week, time, and duration). You can also set a notification email for upcoming maintenance. Option A, Option C, and Option E are correct.

Option B is not configurable per database. Option D is for Azure SQL Database, not Managed Instance.

Full explanation →
33
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 →
34
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

Option B, C, D are correct. Approximate Count Distinct is part of Intelligent Query Processing that improves performance of COUNT(DISTINCT) queries. Adaptive Joins switch join strategies dynamically.

Interleaved Execution for multi-statement table-valued functions helps avoid suboptimal plans. Option A is wrong because Columnstore indexes are not part of Intelligent Query Processing. Option E is wrong because Query Store is a feature but not part of Intelligent Query Processing.

Full explanation →
35
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 logical reads often indicate that queries are reading more data than necessary, possibly due to missing indexes or inefficient queries. Option A is correct. Option B is wrong because CPU time is measured by worker_time.

Option C is wrong because high logical reads can increase memory usage. Option D is wrong because it does not indicate I/O directly.

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

Option A is correct because Managed Instance supports PITR retention up to 35 days (7 days is fine) and LTR policies for up to 10 years. Option B is wrong because LTR is not available for Azure SQL Database single database with just Backup Storage Redundancy. Option C is wrong because Azure Backup for SQL Server in Azure VM is a different scenario.

Option D is wrong because PITR retention can be up to 35 days, not 2 hours.

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

Option A is correct because seeding data to the secondary before failover reduces the time to synchronize. Option B is wrong because increasing the service tier does not directly reduce failover time. Option C is wrong because forced failover with data loss is not recommended for planned failovers.

Option D is wrong because removing the failover group would not help.

Full explanation →
38
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, C, and D are correct. A domain controller (A) is needed for Windows Server Failover Clustering. All VMs must be in the same availability set or availability zones (C).

A load balancer (D) is required for the listener IP. Option B is incorrect because VMs can be in different regions, but then automatic failover across regions is not supported without additional configuration. Option E is incorrect because SQL Server authentication mode is not a prerequisite.

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

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

Option A is correct because creating a contained database user in each database with appropriate permissions limits the user to that database. Option B is incorrect because server-level login with db_owner on all databases gives cross-database access. Option C is incorrect because a server-level firewall rule does not control permissions.

Option D is incorrect because database-level firewall rule controls network access, not permissions.

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

Option B is correct because 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 →
42
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 B is correct. Option A is wrong because SQL Agent cannot send alerts based on DTU.

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

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

Even with SQL authentication disabled, users with appropriate permissions (e.g., 'loginmanager' role) can still create SQL logins if allowed. To prevent this, you must revoke the 'CREATE LOGIN' permission from all non-admin users. Option B is correct.

Option A is wrong because the 'DisallowSqlAuthentication' property does not affect permissions. Option C is wrong because users with 'loginmanager' can create logins. Option D is wrong because auditing does not prevent creation.

Full explanation →
44
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 →
45
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

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

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

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

Option D is correct because 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 →
49
Multi-Selecteasy

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

Select 2 answers
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
AnswersC, E

Supports long-term retention for SQL Server on VMs.

Why this answer

Options A and B are correct because Azure Backup for SQL Server in Azure VMs supports long-term retention (LTR) up to 10 years, and Azure SQL Database's built-in long-term retention (LTR) policy can be configured for up to 10 years. Options C, D, and E do not provide long-term backup retention for SQL databases.

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

Options B and D are correct. Creating appropriate indexes reduces table scans. Updating statistics helps the optimizer choose better plans.

Option A is wrong because increasing MAXDOP might not help and could cause issues. Option C is wrong because it increases tier cost. Option E is wrong because it does not address table scans.

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

Option D is correct because it addresses all remaining requirements: (1) Conditional Access policy enforces MFA for the service principal; (2) EXECUTE permission on specific stored procedures limits operations; (3) Audit action groups for failed logins and schema changes; (4) Parameterized queries prevent SQL injection. Option A is incorrect because dynamic data masking does not prevent SQL injection. Option B is incorrect because row-level security does not prevent SQL injection.

Option C is incorrect because Always Encrypted does not prevent SQL injection and does not enforce MFA.

Full explanation →
52
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 →
53
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 →
54
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 can reduce logical reads by enabling seeks instead of scans. Updating statistics helps the optimizer choose efficient plans. Option A and C are correct.

Option B is wrong because increasing memory does not reduce logical reads directly. Option D is wrong because rewriting as a stored procedure does not reduce logical reads.

Full explanation →
55
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 and high log write percent indicate the database is hitting resource limits. Option A and D are correct. Option B is wrong because low data IO is not an indicator.

Option C is wrong because low memory indicates underutilization. Option E is wrong because low session percent is not a scaling trigger.

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

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

Option A is correct because active geo-replication provides asynchronous replication with a typical RPO of 5 seconds and RTO of 1 hour (including failover time). Option B (failover group) is for managed instances. Option C (geo-restore) has RPO of hours and RTO of hours.

Option D (copy-only backups) is not a DR solution.

Full explanation →
58
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 →
59
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

Option A is correct because Azure SQL Database's export to BACPAC can be automated using Azure Automation runbooks with the Export-AzSqlDatabase cmdlet, and can be scheduled. Options B and C require additional services and complexity. Option D is not automated.

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

Options A, B, and D are correct. ARM templates define infrastructure, Azure DevOps pipelines automate deployment, and Bicep is a declarative language for ARM. Option C is wrong because the Azure portal is manual.

Option E is wrong because SQL Server Management Studio is not for infrastructure deployment.

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

Option D is correct because creating a Microsoft Entra ID service principal for each application, assigning a client secret (which can be rotated automatically via Azure Key Vault), and creating a contained database user mapped to the service principal meets all requirements. Option A is wrong because SQL authentication is not using Microsoft Entra ID. Option B is wrong because managed identity is limited to Azure-hosted applications and cannot be used for all applications.

Option C is wrong because a single service principal would not allow per-application permissions.

Full explanation →
62
MCQhard

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

A.Manually create the dropped index and mark it as a required index.
B.Enable Query Store to track index usage.
C.Set the dropIndex option state to Disabled in the tuning policy.
D.Disable automatic tuning entirely.
AnswerC

Prevents automatic index drops while retaining other tuning features.

Why this answer

Option C is correct because disabling the dropIndex option prevents automatic index drops while keeping forcePlan and createIndex enabled. Option A is wrong because disabling all automatic tuning removes all benefits. Option B is wrong because reverting by creating a manual index does not prevent future drops.

Option D is wrong because Query Store does not prevent index drops.

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

The trap here is that candidates confuse I/O-related metrics (like avg_io_stall_ms or avg_pending_disk_io) with memory pressure, but those metrics indicate storage performance issues, not insufficient memory for the buffer pool.

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

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

Option A is correct because Microsoft Defender for SQL needs to be enabled at the subscription or server level. Option D is correct because it requires a Log Analytics workspace to store security logs. Option B is wrong because Azure Key Vault is not required.

Option C is wrong because SQL Server Agent is not required. Option E is wrong because it is not related to Defender for SQL.

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

Option D is correct because 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 →
67
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 →
68
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 can schedule PowerShell runbooks that connect to the managed instance and run T-SQL maintenance.

Why this answer

Azure Automation runbooks can run maintenance scripts but require custom coding. SQL Agent is not available in managed instance. Elastic Database Jobs cannot be used with managed instance.

Azure Logic Apps can trigger but are not ideal for long-running T-SQL. The correct approach is to use Azure Automation with PowerShell runbooks executing T-SQL.

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

Option D is correct because the grace period for data loss is set to 1 hour (3600 seconds); after 30 minutes, the grace period has not expired, so automatic failover is not triggered. Option A is wrong because the outage duration is less than the grace period. Option B is wrong because the grace period is designed to allow time for the outage to resolve.

Option C is wrong because the issue is not about configuration but about timing.

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

Option C is correct because a self-hosted integration runtime is required for on-premises access, and the copy activity can be configured for incremental loads using watermarks. Option A lacks incremental loading. Option B uses the wrong IR (Azure IR cannot access on-premises).

Option D is not a Data Factory activity.

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

Option D is correct because Azure Logic Apps can query metrics via the Azure Monitor connector and send emails via Office 365 connector, all in a scheduled workflow. Option A is wrong because Azure Automation Runbooks can do it but require more scripting for email. Option B is wrong because Elastic Database Jobs are for T-SQL, not metrics.

Option C is wrong because Azure Data Factory is for data movement, not reporting.

Full explanation →
72
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.
AnswersC, E

Zone-redundant replica can be used for read-only workloads.

Why this answer

Option A is correct because adding a zone-redundant replica makes the secondary readable without changing the failover group. Option D is correct because setting the failover group's secondary type to readable enables read-only access. Option B is wrong because active geo-replication is separate and would create a different relationship.

Option C is wrong because enabling read scale-out on the primary does not affect the secondary. Option E is wrong because changing the secondary to Premium tier is not necessary; it already is Premium.

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

Option D is correct because a failover group with a readable secondary in a paired region using Business Critical provides synchronous replication within the primary region and asynchronous geo-replication for DR. However, to achieve zero data loss (RPO=0), you need planned failover, but for unplanned, there is always some data loss unless you use synchronous geo-replication, which is not possible across regions. The question says 'zero data loss' – this can only be achieved with planned failover.

The best approach is to use a failover group with a readable secondary in a paired region and ensure that during a disaster, a planned failover is initiated if possible. But typically, zero data loss across regions is not possible with async replication. However, Business Critical with zone-redundant configuration within the same region can achieve zero data loss, but that's not across regions.

The requirement is for regional disaster. The only way to guarantee zero data loss is to have synchronous replication across regions, which is not supported. So the question might be tricky.

But among the options, D is the only one that includes a readable secondary in a different region. Option A is wrong because zone-redundancy doesn't protect regionally. Option B is wrong because active geo-replication is async, not zero data loss.

Option C is wrong because no readable secondary. Option D is the best choice, even though zero data loss across regions is not achievable with unplanned failover. Perhaps the exam expects that the solution uses a failover group and relies on planned failover for zero data loss.

I'll go with D.

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

Option C is correct because differences in the cardinality estimation model (e.g., legacy CE vs. default CE) can cause different plan choices, even with identical schema and stats. Option A is wrong because DTU vs. vCore is a purchasing model, not a performance feature that inherently slows queries. Option B is wrong because if indexes are identical, fragmentation impact should be similar.

Option D is wrong because intelligent query processing is generally beneficial, not detrimental, and differences would be minimal.

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

Option B is correct because Azure SQL Database auditing can be configured to log DDL events and store them in a storage account with long-term retention. Option A is wrong because server-level audit policy requires SQL Server on-premises or VM. Option C is wrong because extended events are more for performance troubleshooting.

Option D is wrong because change tracking is for data changes, not schema changes.

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 →