Courseiva

Microsoft Azure Database Administrator Associate DP-300 (DP-300) — Questions 451–525

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

Take a mock examExam hub
Page 6

Page 7 of 13

Page 8
451
MCQhard

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

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

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

Why this answer

Option A is correct because the serverless compute tier can be configured to auto-pause after a period of inactivity. Option B is wrong because Elastic Jobs require creating a job. Option C is wrong because Azure Automation runbooks require scripting and maintenance.

Option D is wrong because Logic Apps add complexity.

Full explanation →
452
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
453
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
454
MCQmedium

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

A.Azure SQL Database with active geo-replication
B.Azure SQL Managed Instance with failover groups
C.Azure SQL Database with failover groups
D.Azure SQL Database Hyperscale with geo-replication
AnswerD

Hyperscale geo-replication supports multiple readable secondaries across regions.

Why this answer

Azure SQL Database Hyperscale with geo-replication is the correct choice because it provides a single writable primary endpoint while supporting geo-distributed readable secondaries with low-latency reads for global users. Hyperscale's distributed architecture allows fast scaling and read scale-out, and geo-replication enables asynchronous replication to multiple regions, meeting the OLTP workload's low-latency read requirement without compromising the single write endpoint.

Exam trap

The trap here is that candidates often confuse active geo-replication (Option A) with failover groups (Option C), not realizing that Hyperscale's geo-replication (Option D) is the only option that combines a single write endpoint with the distributed read scale-out needed for global low-latency reads.

How to eliminate wrong answers

Option A is wrong because active geo-replication for Azure SQL Database supports readable secondaries but does not provide automatic failover groups with a listener endpoint, making it less suitable for a global application requiring managed failover and low-latency reads. Option B is wrong because Azure SQL Managed Instance with failover groups supports geo-replication but is not optimized for the Hyperscale distributed storage architecture that provides the fastest read scale-out for global OLTP workloads. Option C is wrong because Azure SQL Database with failover groups (non-Hyperscale) uses the standard tier, which has limited storage and performance scaling compared to Hyperscale, and does not offer the same level of read replica distribution for global low-latency reads.

Full explanation →
455
MCQmedium

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

A.sys.dm_exec_sessions
B.sys.query_store_query_text
C.sys.dm_exec_requests
D.sys.messages or the SQL Server error log (viewable via sys.fn_get_audit_file)
AnswerD

Trace flag 1222 outputs to error log.

Why this answer

Option B is correct because deadlock graphs captured by trace flag 1222 are written to the error log. Option A is wrong because deadlocks are not in Query Store by default. Option C is wrong because sys.dm_exec_requests shows current requests, not deadlock history.

Option D is wrong because those DMVs show current sessions, not deadlocks.

Full explanation →
456
MCQhard

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

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

Managed Instance automatically applies updates during the configured maintenance window.

Why this answer

Option A is correct because Managed Instance automatically handles patching during the configured maintenance window. Option B is wrong because Azure Update Manager is not applicable to Managed Instance. Option C is wrong because manual patching is not automated.

Option D is wrong because elastic jobs are for database-level tasks, not patching.

Full explanation →
457
MCQmedium

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

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

Correct.

Why this answer

Option C is correct because replicationLag of 5 seconds indicates the secondary is catching up with minimal lag, and replicationState of CATCH_UP means it is fully synchronized. Option A is incorrect because 5 seconds lag is normal. Option B is incorrect because secondary role is expected.

Option D is incorrect because the state is CATCH_UP, not SEEDING.

Full explanation →
458
MCQeasy

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

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

This directly sends email alerts for detected threats.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
459
MCQmedium

You are the DBA for a company that uses Azure SQL Managed Instance to host multiple databases for different departments. The security team has mandated that: - All connections to the managed instance must be encrypted using TLS 1.2 or higher. - SQL Server authentication must be disabled; only Microsoft Entra authentication is allowed. - A dedicated audit log must be created for each database to track all DDL changes and all failed login attempts. - The audit logs must be stored in a central Azure Storage account with 180-day retention. - Database administrators (DBAs) should not be able to view or modify the audit logs. You have already set the minimal TLS version to 1.2 and disabled SQL Server authentication. What should you do next to meet the remaining requirements?

A.Create a SQL Server Agent job on the managed instance that captures DDL changes and failed logins into a table. Grant DBAs access to the table.
B.Enable server-level auditing with the same action groups and send logs to a central storage account. Use Azure RBAC to deny DBAs access to the storage account.
C.Enable database-level auditing on each database with audit action groups SCHEMA_OBJECT_CHANGE_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP. Configure the audit log destination to be a central Azure Storage account. Set retention to 180 days. Grant the DBAs 'Storage Blob Data Reader' role on the storage account but not 'Contributor' or 'Owner'.
D.Use Azure SQL Auditing with Azure Monitor Log Analytics workspace. Configure the workspace with 180-day retention. Grant DBAs 'Log Analytics Reader' role.
AnswerC

Meets all requirements.

Why this answer

Option C is correct because database-level auditing with the specified action groups (SCHEMA_OBJECT_CHANGE_GROUP for DDL changes and FAILED_DATABASE_AUTHENTICATION_GROUP for failed logins) meets the requirement to track DDL changes and failed login attempts per database. Configuring the audit log destination to a central Azure Storage account with 180-day retention satisfies the storage and retention requirement. Granting DBAs only the 'Storage Blob Data Reader' role prevents them from viewing or modifying the audit logs, as they cannot delete or overwrite blobs, while still allowing read access if needed.

Exam trap

The trap here is that candidates might confuse server-level auditing with database-level auditing for Azure SQL Managed Instance, or assume that granting DBAs 'Storage Blob Data Reader' is insufficient to prevent access, when in fact it only allows read access and not modification or deletion, meeting the requirement that DBAs cannot view or modify audit logs.

How to eliminate wrong answers

Option A is wrong because using a SQL Server Agent job to capture DDL changes and failed logins into a table does not meet the requirement for a dedicated audit log stored in a central Azure Storage account, and granting DBAs access to the table violates the requirement that DBAs should not be able to view or modify the audit logs. Option B is wrong because server-level auditing cannot be configured to send logs to a central storage account for Azure SQL Managed Instance; server-level auditing is not supported for managed instances, and the requirement specifies a dedicated audit log for each database, not a single server-level audit. Option D is wrong because using Azure Monitor Log Analytics workspace does not meet the requirement for storing audit logs in a central Azure Storage account; Log Analytics is a separate service for log analytics, not a storage account, and granting DBAs 'Log Analytics Reader' role would allow them to view the audit logs, violating the requirement that DBAs should not be able to view or modify them.

Full explanation →
460
Multi-Selectmedium

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

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

Stores and queries diagnostic logs from SQL Database.

Why this answer

Options B and D are correct because Azure Monitor collects metrics and logs from Azure SQL Database, and Log Analytics can analyze the data; Azure Monitor Alerts can trigger actions. Option A (Application Insights) is for application-level monitoring. Option C (Azure Sentinel) is for security.

Option E (Azure Advisor) provides recommendations but not real-time alerts.

Full explanation →
461
MCQmedium

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

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

The backup retention can be set up to 35 days.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
462
MCQhard

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

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

Existing databases may not automatically switch to CMK.

Why this answer

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

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
463
MCQhard

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
464
MCQhard

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

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

Supports online migration to Hyperscale with minimal downtime.

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
465
MCQhard

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

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

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

Why this answer

The log space is nearly full (95% used), and the log write rate is high, indicating transactions are not being committed or log is not being truncated due to long-running transactions or replication.

Full explanation →
466
MCQeasy

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
467
MCQeasy

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

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

Active geo-replication provides a readable secondary database.

Why this answer

Option B is correct because active geo-replication creates a readable secondary database in a different region. Option A is wrong because auto-failover groups require a secondary in the same region for readable secondaries. Option C is wrong because geo-restore is not readable during recovery.

Option D is wrong because backup redundancy does not provide a readable secondary.

Full explanation →
468
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
469
MCQmedium

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the General Purpose tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

A.Increase the service tier to S3 during the end-of-month period
B.Add nonclustered indexes to the tables used in reports
C.Convert the tables to clustered columnstore indexes
D.Create a read-only replica and direct reports to it
AnswerD

Offloads reporting without affecting primary; cost-effective.

Why this answer

Option D is correct because creating a read-only replica allows you to offload reporting queries to the replica, which can be at a lower service tier, reducing load on the primary and improving performance. Option A is wrong because scaling up the primary to S3 during reporting hours increases cost. Option B is wrong because adding indexes helps but may not be sufficient.

Option C is wrong because columnstore indexes are not ideal for OLTP workloads and may affect write performance.

Full explanation →
470
MCQmedium

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

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

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

Why this answer

Option C is correct because a failover group with automatic failover policy ensures that during a regional outage, the secondary database becomes the primary automatically and the listener endpoint remains unchanged. Option A is wrong because active geo-replication does not provide a single listener endpoint and requires manual failover. Option B is wrong because auto-failover groups are the correct feature, not a separate setting.

Option D is wrong because Azure Traffic Manager is not needed; the failover group listener provides the endpoint.

Full explanation →
471
MCQmedium

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

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

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

Why this answer

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

Exam trap

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

How to eliminate wrong answers

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

Full explanation →
472
MCQhard

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

A.Invite the consultants as guest users in your Microsoft Entra ID tenant using B2B collaboration, then create a contained database user for each guest user
B.Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)
C.Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant
D.Create a SQL Server authentication login and user for the consultants
AnswerA

Guest users can authenticate to Azure SQL Database using their home tenant credentials and are then granted database permissions.

Why this answer

Option A is correct because external consultants from a different Microsoft Entra ID tenant must first be invited as guest users via B2B collaboration to your tenant. Once they are guest users, you can create contained database users in Azure SQL Database mapped to their guest user identities (e.g., their UPN in your tenant) and grant them read-only permissions (e.g., db_datareader role). This approach respects the isolation of the partner's tenant while enabling access through your tenant's identity.

Exam trap

The trap here is that candidates assume you can directly map a contained database user to an external UPN without first establishing cross-tenant identity via B2B collaboration, or they mistakenly think Azure SQL Database can natively trust another Entra ID tenant.

How to eliminate wrong answers

Option B is wrong because you cannot directly create a contained database user mapped to a user principal name (UPN) from an external Microsoft Entra ID tenant; Azure SQL Database only recognizes identities from the tenant it is linked to. Option C is wrong because Azure SQL Database does not support trusting an external Microsoft Entra ID tenant directly; cross-tenant trust must be established via B2B collaboration at the Microsoft Entra ID level. Option D is wrong because SQL Server authentication logins and users bypass Microsoft Entra ID authentication entirely, which violates the requirement to use Microsoft Entra ID authentication and does not leverage the partner's existing identities.

Full explanation →
473
MCQmedium

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

A.sys.dm_exec_query_stats
B.Query Store Wait Stats in SSMS
C.sys.dm_os_wait_stats
D.sys.dm_db_index_usage_stats
AnswerC

Provides cumulative wait statistics for all sessions.

Why this answer

Option C is correct because sys.dm_os_wait_stats provides cumulative wait statistics across all sessions, which can be used to identify top waits. Option A is wrong because sys.dm_db_index_usage_stats shows index usage, not waits. Option B is wrong because Query Store Wait Stats tracks waits per query, but the question asks for top resource waits overall.

Option D is wrong because sys.dm_exec_query_stats shows execution statistics, not waits.

Full explanation →
474
MCQhard

Your company uses Azure SQL Database with Elastic Jobs to run maintenance scripts. You need to ensure that the Elastic Job agent can connect to the target databases securely. The agent runs in an Azure virtual network and uses a managed identity. The target databases are in a different Azure region and have public endpoint disabled. What is the most secure way to connect the Elastic Job agent to the target databases?

A.Use virtual network service endpoints to allow the agent's subnet to connect to the target databases.
B.Configure a private endpoint for each target database and grant the Elastic Job agent's managed identity access to the databases via a contained database user.
C.Add the agent's public IP address to the target database firewall rules.
D.Create a SQL authentication login for the Elastic Job agent and use that in the job credentials.
AnswerB

Private endpoints keep traffic within Microsoft's network and managed identity avoids credential management.

Why this answer

Option B is correct because it combines Azure Private Link (private endpoint) for network-level isolation with managed identity for authentication, eliminating exposure to the public internet and removing the need for passwords. The Elastic Job agent's managed identity is mapped to a contained database user in each target database, enabling secure, passwordless connections even across regions when public endpoints are disabled.

Exam trap

The trap here is that candidates often confuse virtual network service endpoints with private endpoints, assuming service endpoints provide the same private connectivity when public endpoints are disabled, but service endpoints still rely on the public endpoint of the Azure service and cannot bypass it.

How to eliminate wrong answers

Option A is wrong because virtual network service endpoints do not provide private connectivity when the target database's public endpoint is disabled; service endpoints only secure traffic from the VNet to the Azure service's public endpoint, but with public endpoint disabled, no traffic can reach the database. Option C is wrong because the Elastic Job agent runs in a VNet and does not have a public IP address; adding a non-existent public IP to firewall rules would not allow connectivity, and exposing any public IP would reduce security. Option D is wrong because SQL authentication logins are less secure than managed identity (they require password management and are vulnerable to credential theft), and they do not address the network connectivity issue since the target database's public endpoint is disabled.

Full explanation →
475
Multi-Selecteasy

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

Select 2 answers
A.Log IO percentage
B.Memory grants pending
C.Page life expectancy
D.CPU percentage
E.Data IO percentage
AnswersB, C

High pending grants indicate memory pressure.

Why this answer

Option A is correct: Page life expectancy (PLE) indicates how long pages stay in cache; low PLE suggests memory pressure. Option B is correct: Memory grants pending indicates queries waiting for memory. Option C is incorrect: CPU percentage indicates CPU pressure.

Option D is incorrect: Data IO percentage indicates I/O pressure. Option E is incorrect: Log IO percentage indicates log write pressure.

Full explanation →
476
MCQeasy

You are the database administrator for a company that uses Azure SQL Managed Instance. The instance hosts a mission-critical database that experiences periodic performance degradation. You need to set up a proactive monitoring solution that sends alerts when the average DTU usage exceeds 80% over a 5-minute period. The solution should minimize cost. What should you do?

A.Create an Azure Monitor metric alert on the 'dtu_consumption_percent' metric for the managed instance.
B.Create a SQL Server Agent job that checks sys.dm_db_resource_stats every 5 minutes and sends an email.
C.Use Elastic Database Jobs to run a query periodically and send alerts.
D.Stream diagnostic logs to a Log Analytics workspace and create a log alert rule.
AnswerA

Azure Monitor metric alerts are low-cost and can alert when average DTU usage exceeds threshold over a period.

Why this answer

Option B is correct because Azure Monitor alerts on metrics like dtu_consumption_percent are cost-effective and meet the requirement. Option A is wrong because SQL Server Agent is for job scheduling, not metric alerts. Option C is wrong because Log Analytics workspaces cost money for ingestion.

Option D is wrong because Elastic Database Jobs are for executing T-SQL across databases, not for alerts.

Full explanation →
477
Multi-Selectmedium

You are tuning an Azure SQL Database that has a heavy write workload. You need to reduce the number of log writes. Which TWO actions should you take? (Choose two.)

Select 2 answers
A.Change the database recovery model to SIMPLE
B.Use minimal logging for bulk operations
C.Rebuild indexes during peak hours
D.Use delayed durability for transactions
E.Increase batch size in INSERT operations
AnswersD, E

Delayed durability reduces log flush frequency.

Why this answer

Option A is correct because larger batch sizes reduce log writes per row. Option D is correct because using delayed durability reduces log flush frequency. Option B is wrong because simple recovery model is not available in Azure SQL Database.

Option C is wrong because minimal logging is not fully supported. Option E is wrong because index rebuilds generate log writes.

Full explanation →
478
Multi-Selecthard

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

Select 3 answers
A.Create a server-level firewall rule on the secondary server to allow client IPs.
B.Add the primary database to the failover group.
C.Configure a grace period of at least 1 hour.
D.Create a secondary server in the target region with the same administrative login.
E.Set the failover group's read/write failover policy to 'Automatic' or 'Manual'.
AnswersB, D, E

Correct: The database must be added to the group.

Why this answer

Option B is correct because adding the primary database to the failover group is the essential step that associates the database with the group, enabling automated replication and failover to the secondary server. Without this step, the failover group has no database to replicate, and the readable secondary cannot be created or maintained.

Exam trap

The trap here is that candidates may think creating firewall rules on the secondary server is required for failover group setup, but those rules are only needed for direct client connections to the secondary, not for the replication or failover process itself.

Full explanation →
479
Multi-Selecteasy

Which TWO metrics in Azure SQL Database's Intelligent Insights can indicate a performance degradation due to increased resource consumption? (Choose two.)

Select 2 answers
A.Increased query duration.
B.High number of deadlocks.
C.Increased transaction log usage.
D.High DTU consumption.
E.Failed authentication attempts.
AnswersA, D

Longer query duration indicates performance degradation.

Why this answer

Options A and B are correct. High DTU consumption and increased query duration are direct indicators of performance issues. Option C is a security metric.

Option D is a connectivity metric. Option E is an availability metric.

Full explanation →
480
MCQhard

You have an Azure SQL Database configured with active geo-replication to a secondary region. A regional outage occurs in the primary region. You need to manually fail over to the secondary database with the least amount of data loss. Which PowerShell cmdlet should you run?

A.Start-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ...
B.Set-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ... -PartnerResourceGroupName ... -Failover
C.Invoke-AzSqlDatabaseFailover -ResourceGroupName ... -ServerName ... -DatabaseName ... -Secondary
D.Switch-AzSqlDatabaseSecondary -ResourceGroupName ... -ServerName ... -DatabaseName ...
AnswerB

This cmdlet initiates a planned failover with synchronization to minimize data loss.

Why this answer

Option B is correct because Set-AzSqlDatabaseSecondary with -Failover initiates a planned failover that synchronizes data before failing over, minimizing data loss. Option A (Start-AzSqlDatabaseFailover) is for failing over a single database within the same region, not geo-failover. Option C (Invoke-AzSqlDatabaseFailover) does not exist.

Option D (Switch-AzSqlDatabaseSecondary) is not a valid cmdlet.

Full explanation →
481
Multi-Selectmedium

Which THREE are features of Azure SQL Database Hyperscale?

Select 3 answers
A.Zone redundant compute by default
B.Unlimited log rate
C.Storage up to 100 TB
D.Near-instantaneous backups
E.Fast scaling of compute resources
AnswersC, D, E

Hyperscale supports up to 100 TB.

Why this answer

Azure SQL Database Hyperscale supports storage up to 100 TB by using a distributed architecture where data is stored across multiple page servers, each managing a subset of pages. This allows the database to scale beyond the 4 TB limit of other service tiers, making it suitable for very large databases.

Exam trap

The trap here is that candidates often assume 'unlimited' or 'by default' features based on marketing terms, but Azure SQL Database Hyperscale has specific limits and optional configurations that are not universally applied.

Full explanation →
482
MCQmedium

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

A.Set up log shipping to an Azure SQL Database
B.Perform an offline migration using Azure Database Migration Service
C.Export a BACPAC file from the source and import it to Azure SQL Database
D.Use Azure Database Migration Service with online mode after removing FileTables
AnswerD

Online migration minimizes downtime. Remove unsupported features first, then migrate using DMS.

Why this answer

Option D is correct because Azure Database Migration Service (DMS) with online mode supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database. However, FileTables are not supported in Azure SQL Database, so they must be removed from the source database before migration. This approach ensures near-zero downtime while addressing the unsupported feature.

Exam trap

The trap here is that candidates may assume log shipping (Option A) is viable for Azure SQL Database, but log shipping is not supported for Azure SQL Database as it requires SQL Server Agent and file-level restore operations, which are not available in that PaaS offering.

How to eliminate wrong answers

Option A is wrong because log shipping is not supported to Azure SQL Database; it only works between on-premises SQL Server instances or to SQL Server on Azure VMs, not to Azure SQL Database. Option B is wrong because an offline migration using Azure Database Migration Service would require taking the source database offline, causing significant downtime, which contradicts the requirement for minimal downtime. Option C is wrong because exporting a BACPAC file and importing it to Azure SQL Database is an offline process that does not support ongoing replication, leading to downtime, and it also does not handle unsupported features like FileTables automatically.

Full explanation →
483
MCQeasy

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

A.Create an Azure Monitor alert rule on the 'storage_percent' metric with an email action group.
B.Create a SQL Agent alert that fires when the storage is above 80% and sends an email.
C.Configure Database Mail to send alerts automatically.
D.Create an Elastic Database Job that checks storage and sends email via sp_send_dbmail.
AnswerA

Azure Monitor alerts are the standard way to send notifications based on metrics.

Why this answer

Option A is correct because Azure Alert Rules can monitor metrics like storage percent and trigger email actions. Option B is wrong because SQL Agent is not available in Azure SQL Database. Option C is wrong because Elastic Jobs execute T-SQL, not send email directly.

Option D is wrong because Database Mail is not available in Azure SQL Database.

Full explanation →
484
MCQmedium

A company uses Azure SQL Database Hyperscale tier for a large database. They need to perform a disaster recovery drill by failing over to a secondary region with minimal data loss. The secondary is in a paired region and is readable. Which approach should they use?

A.Restore a geo-redundant backup to the secondary region
B.Configure Active Geo-Replication to the secondary region
C.Create a named replica in the secondary region and fail over manually
D.Use an Auto-Failover Group with the secondary in the paired region
AnswerD

Auto-failover groups support Hyperscale and allow failover with minimal data loss.

Why this answer

Auto-Failover Groups with Azure SQL Database Hyperscale provide automated, orchestrated failover to a secondary region with minimal data loss by using synchronous replication for the log service. This meets the requirement for a disaster recovery drill with a readable secondary and minimal data loss, as the secondary is kept in sync and can be failed over manually or automatically.

Exam trap

The trap here is that candidates confuse Active Geo-Replication (unsupported on Hyperscale) with Auto-Failover Groups (the correct feature for Hyperscale), or assume named replicas can be used for cross-region failover when they are strictly intra-region read-only replicas.

How to eliminate wrong answers

Option A is wrong because restoring a geo-redundant backup to the secondary region involves point-in-time recovery from backups, which can result in significant data loss (up to the last backup interval) and does not provide a readable secondary for ongoing reads or a seamless failover drill. Option B is wrong because Active Geo-Replication is not supported on Azure SQL Database Hyperscale; it is only available for other service tiers (General Purpose, Business Critical). Option C is wrong because named replicas in Hyperscale are read-only replicas within the same region and cannot be used for cross-region failover; they do not support manual failover to a secondary region.

Full explanation →
485
Multi-Selecthard

Which THREE of the following are required to configure Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault for Azure SQL Database?

Select 3 answers
A.The key encryption key must be stored in Azure Key Vault.
B.The Azure Key Vault must be in the same region as the database.
C.The Azure Key Vault must have soft-delete and purge protection enabled.
D.The Azure SQL Database server must have an Azure AD identity.
E.The SQL server must have an RBAC role assignment on the key vault.
AnswersA, C, D

Customer-managed key is stored in AKV.

Why this answer

Option A is correct because Transparent Data Encryption (TDE) with customer-managed keys requires the key encryption key (KEK) to be stored in Azure Key Vault. This KEK is used to protect the database encryption key (DEK), which is stored in the database boot record. Without the KEK in Key Vault, the customer-managed key scenario cannot be implemented, as Azure SQL Database must be able to access the key to perform encryption operations.

Exam trap

The trap here is that candidates often confuse the requirement for the Key Vault to be in the same region as the database (Option B) with the actual requirement that the Key Vault must have soft-delete and purge protection enabled (Option C), and they may also mistakenly think an RBAC role assignment (Option E) is needed instead of the correct access policy permissions for the server's Azure AD identity.

Full explanation →
486
MCQeasy

A company plans to migrate an on-premises SQL Server database to Azure SQL Database Managed Instance. They require a high availability solution that provides automatic failover between replicas within the same region with an RPO of 0 and an RTO of less than 30 seconds. Which service tier should they choose?

A.Business Critical
B.General Purpose
C.Azure SQL Database (single)
D.Hyperscale
AnswerA

Provides synchronous replicas with zero data loss.

Why this answer

Option A is correct because Business Critical service tier provides Always On availability groups with synchronous replicas ensuring zero data loss and fast failover. Option B is wrong because General Purpose has asynchronous replicas. Option C is wrong because Hyperscale uses buffer pool extension.

Option D is wrong because Azure SQL Database is not Managed Instance.

Full explanation →
487
Multi-Selectmedium

You are designing a disaster recovery plan for an Azure SQL Managed Instance that hosts an OLTP application. The application requires an RPO of 15 seconds and an RTO of 5 minutes. You need to choose a configuration that meets these requirements. Which TWO options should you consider? (Choose two.)

Select 2 answers
A.Configure a failover group with a secondary instance in a paired region, both using Business Critical service tier.
B.Deploy a zone-redundant Business Critical instance in a single region.
C.Deploy a General Purpose instance with a failover group to a secondary in another region.
D.Ensure the secondary instance has at least one readable secondary replica.
E.Use active geo-replication between two Business Critical instances in different regions.
AnswersA, E

Business Critical provides synchronous replication within region and asynchronous geo-replication, meeting the RPO/RTO.

Why this answer

Options B and C are correct. Failover groups with active geo-replication between Business Critical instances provide synchronous commit within the primary region and asynchronous geo-replication, achieving RPO of ~5 seconds and RTO of ~1 minute for automated failover. Zone-redundant configuration alone does not protect against regional failure.

General Purpose does not guarantee low RPO. Option A is wrong because zone-redundancy does not protect regionally. Option D is wrong because General Purpose cannot meet the RPO.

Option E is correct.

Full explanation →
488
MCQmedium

You need to automate the creation of an Azure SQL Database using Azure CLI in a CI/CD pipeline. The database name must be unique and include the build ID. How should you specify the database name in the Azure CLI command?

A.Use the $RANDOM variable in Bash
B.Use the $(uuid) function in Azure CLI
C.Use a hardcoded name like 'mydb'
D.Use an environment variable $(Build.BuildId) set by the pipeline
AnswerD

Azure DevOps provides unique build IDs that can be referenced as environment variables.

Why this answer

Option C is correct because Azure CLI can use environment variables set by the pipeline, combined with a static prefix. Option A uses a fixed name, which fails on rebuilds. Option B is not standard.

Option D is for Bash, not cross-platform.

Full explanation →
489
MCQhard

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

A.The audit logs are being sent to Azure Monitor instead of blob storage
B.The retention days are set too high, causing logs to be truncated
C.The audit actions and groups do not include login events
D.Auditing is disabled at the server level
AnswerC

Login events are captured by 'SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP' and 'FAILED_DATABASE_AUTHENTICATION_GROUP', which are not listed in the audit actions and groups.

Why this answer

The JSON policy shown in the exhibit defines audit actions and groups, but it does not include the `SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP` or `FAILED_DATABASE_AUTHENTICATION_GROUP` action groups. These groups are required to capture both successful and failed login attempts (authentication events) in Azure SQL Database. Without them, login events are not recorded in the audit logs, regardless of other settings.

Exam trap

The trap here is that candidates assume auditing automatically captures all security events, but Azure SQL Database requires explicit inclusion of authentication action groups to log login attempts, and the JSON policy in the exhibit likely omits these groups.

How to eliminate wrong answers

Option A is wrong because the destination of audit logs (Azure Monitor vs. blob storage) does not affect which events are captured; it only changes where the logs are stored. Option B is wrong because retention days control how long logs are kept, not which events are captured; setting retention too high would not cause logs to be truncated or missing. Option D is wrong because if auditing were disabled at the server level, no audit logs would be generated at all, but the question states that other events (not login events) are being captured, implying auditing is enabled.

Full explanation →
490
Multi-Selectmedium

Which TWO actions can reduce storage costs for an Azure SQL Database? (Select two.)

Select 2 answers
A.Increase the service tier to get more storage.
B.Enable row or page compression on large tables.
C.Reduce backup retention to 1 day.
D.Enable automatic tuning to optimize query plans.
E.Archive historical data to Azure Blob Storage using external tables.
AnswersB, E

Compression reduces storage footprint.

Why this answer

Options A and C are correct. Enabling data compression reduces data size, lowering storage costs. Moving unused data to Azure Blob Storage using Elastic Database Jobs and external tables can also reduce costs.

Option B is wrong because automatic tuning does not affect storage. Option D is wrong because increasing service tier increases cost. Option E is wrong because changing backup retention does not reduce storage for data files.

Full explanation →
491
MCQeasy

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

A.Azure CLI scripts
B.Azure Automation runbooks
C.Azure Policy
D.Azure Resource Manager templates
AnswerD

ARM templates define resources declaratively and can deploy database, firewall, and settings.

Why this answer

Azure Resource Manager (ARM) templates are the native IaC for Azure. Azure Automation runbooks can deploy but are not declarative. Azure CLI can script deployments but is imperative.

Azure Policy is for governance, not deployment.

Full explanation →
492
MCQeasy

You are the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

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

Masking obfuscates columns for unauthorized users.

Why this answer

Dynamic Data Masking (DDM) is the correct choice because it obfuscates sensitive columns (e.g., salary) in query results for unauthorized users while allowing full visibility for authorized users like HR managers. DDM applies masking rules at the database level without modifying the underlying data, making it ideal for scenarios where you need to limit exposure of sensitive data to certain roles without changing the application code.

Exam trap

The trap here is that candidates often confuse Dynamic Data Masking with Always Encrypted, thinking that encryption is needed for obfuscation, but DDM is specifically designed for on-the-fly data masking without changing the underlying storage or requiring client-side changes.

How to eliminate wrong answers

Option A is wrong because Always Encrypt encrypts data at the client side, ensuring that the database engine never sees plaintext, which prevents even authorized database users (like HR managers) from viewing the data unless they have the column encryption key, making it unsuitable for role-based obfuscation where some users need full access. Option C is wrong because Row-Level Security (RLS) restricts access to rows based on user identity or context, but it does not obfuscate column values; it either shows or hides entire rows, not partial data within a column. Option D is wrong because Transparent Data Encryption (TDE) encrypts the entire database at rest (data files and backups) but does not control or obfuscate data visibility at query time for specific users or columns.

Full explanation →
493
MCQeasy

You are designing a disaster recovery strategy for an Azure SQL Database that requires a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 1 hour. Which option should you choose?

A.Use auto-failover groups with read-write failover policy.
B.Use geo-restore from geo-redundant backups.
C.Use active geo-replication and plan manual failover.
D.Use long-term retention (LTR) backups with daily frequency.
AnswerC

Active geo-replication provides RPO less than 5 seconds and manual failover can achieve RTO of less than 1 hour.

Why this answer

Active geo-replication provides a continuous replication stream with an RPO of less than 5 seconds and supports manual failover, which can achieve an RTO of less than 1 hour when the failover is initiated promptly. This option meets the strict RPO and RTO requirements by maintaining a readable secondary in a different Azure region that is kept nearly synchronized with the primary.

Exam trap

The trap here is that candidates confuse auto-failover groups (Option A) with active geo-replication, assuming automatic failover always provides a better RTO, but the question's strict RPO of less than 5 seconds and the need for manual control make active geo-replication the correct choice, as auto-failover groups introduce additional latency and automatic detection delays that can push RTO beyond 1 hour.

How to eliminate wrong answers

Option A is wrong because auto-failover groups with read-write failover policy are designed for group-level failover of multiple databases and have a default RPO of up to 5 seconds, but the failover is automatic and may not guarantee an RTO under 1 hour due to potential delays in detection and DNS propagation; more critically, the question specifies a manual failover requirement. Option B is wrong because geo-restore from geo-redundant backups has an RPO measured in hours (typically 1-12 hours) and an RTO that can exceed several hours, making it unsuitable for sub-5-second RPO. Option D is wrong because long-term retention backups are taken daily, resulting in an RPO of up to 24 hours, and restoring from them can take hours, failing both the RPO and RTO targets.

Full explanation →
494
MCQhard

You are planning a deployment of Azure SQL Database for a mission-critical application. The application requires a high level of resilience to regional failures. You need to ensure that data is replicated synchronously across two Azure regions and that automatic failover occurs with zero data loss. Which deployment option should you choose?

A.Azure SQL Database on General Purpose tier with active geo-replication.
B.Azure SQL Database on Hyperscale tier with named replicas.
C.SQL Server on Azure VMs with Always On Availability Groups.
D.Azure SQL Managed Instance on Business Critical tier with failover groups.
AnswerD

Business Critical provides synchronous replication and automatic failover with zero data loss.

Why this answer

Option D is correct because Azure SQL Managed Instance on the Business Critical tier supports failover groups with synchronous replication, ensuring zero data loss (RPO=0) and automatic failover across two Azure regions. The Business Critical tier uses a quorum-based commit mechanism and high-speed local SSDs to guarantee data consistency during regional failures.

Exam trap

The trap here is that candidates often confuse active geo-replication (asynchronous, RPO > 0) with failover groups (synchronous, RPO = 0), or assume that any tier with geo-replication guarantees zero data loss, ignoring the critical difference in replication mode.

How to eliminate wrong answers

Option A is wrong because the General Purpose tier uses asynchronous replication with active geo-replication, which cannot guarantee zero data loss (RPO > 0) during a regional failure. Option B is wrong because Hyperscale tier named replicas are read-only and do not support synchronous replication or automatic failover for write workloads. Option C is wrong because SQL Server on Azure VMs with Always On Availability Groups requires manual configuration and management of the underlying infrastructure, and while it can achieve synchronous replication, it is not a fully managed PaaS service like Azure SQL Managed Instance, and automatic failover with zero data loss is not guaranteed without additional setup.

Full explanation →
495
MCQmedium

You are a database administrator for an Azure SQL Managed Instance. You need to automate the deployment of schema changes across multiple databases in an elastic pool. Which Azure service should you use to orchestrate these deployments?

A.Azure Functions
B.Azure Automation
C.Azure DevOps
D.Azure Logic Apps
AnswerC

Azure DevOps provides release pipelines for deploying schema changes across multiple databases.

Why this answer

Azure DevOps with its release pipelines can manage the deployment of schema changes across multiple databases. Option A is wrong because Azure Automation primarily manages infrastructure tasks, not database schema deployments. Option B is wrong because Azure Logic Apps is for workflow integration, not database schema deployment.

Option D is wrong because Azure Functions is for event-driven code, not orchestrated deployments.

Full explanation →
496
MCQhard

Refer to the exhibit. An Azure SQL Database has the above ARM template for long-term retention (LTR) backup policy. Which statement is true about the retention duration?

A.Monthly backups are retained for 12 months.
B.Yearly backups are retained for 10 years.
C.Yearly backups are retained for 5 years.
D.Weekly backups are retained for 1 week.
AnswerC

YearlyRetention is P5Y, which equals 5 years.

Why this answer

Option D is correct. The yearlyRetention is P5Y (5 years), weeklyRetention is P2W (2 weeks), monthlyRetention is P6M (6 months). Option A is wrong because weeklyRetention is 2 weeks, not 1 week.

Option B is wrong because monthlyRetention is 6 months. Option C is wrong because yearlyRetention is 5 years.

Full explanation →
497
MCQhard

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

A.Blocking due to lock contention
B.CPU pressure from high-complexity queries
C.I/O subsystem bottleneck
D.Insufficient memory allocation for the database
AnswerC

PAGEIOLATCH_SH waits indicate I/O latency.

Why this answer

The high PAGEIOLATCH_SH wait time indicates I/O subsystem pressure, likely due to insufficient IOPS or throughput on the database storage.

Full explanation →
498
MCQeasy

A small business uses Azure SQL Database single database in the General Purpose tier for its inventory management system. The database is located in the Brazil South region. The business wants a disaster recovery solution that can automatically fail over to a secondary region in case of an outage, with an RPO of less than 1 hour and an RTO of less than 2 hours. The budget is limited, so the solution must be cost-effective. What should you recommend?

A.Create a failover group with a secondary database in a different region using the General Purpose tier.
B.Enable zone redundancy on the existing database.
C.Use geo-restore from geo-redundant backups.
D.Upgrade to Business Critical tier and configure geo-replication.
AnswerA

Cost-effective and meets RPO/RTO.

Why this answer

Option A is correct because a failover group with a secondary in another region provides automatic failover, and General Purpose tier is cost-effective. Option B is wrong because zone redundancy is within region. Option C is wrong because Hyperscale is more expensive.

Option D is wrong because geo-restore has higher RTO (hours).

Full explanation →
499
MCQeasy

Refer to the exhibit. The exhibit shows an Azure role assignment with a condition. When user@contoso.com tries to read data from the database 'proddb', what will be the effect of this condition?

A.The user will be allowed to read all data from the database.
B.The user will be denied the ability to read data from the database.
C.The user will be able to read the database metadata but not the data.
D.The user will be able to assign the role to others.
AnswerB

The condition denies read data actions because it excludes the data read action from the role.

Why this answer

The condition in the Azure role assignment explicitly denies read access to the database 'proddb' for user@contoso.com. In Azure RBAC, a deny assignment overrides any allow assignments, so even if the user has other roles granting read permissions, this condition will block all data read operations on that specific database. Therefore, the user will be denied the ability to read data from the database.

Exam trap

The trap here is that candidates often assume a condition only restricts certain operations or that metadata reads are separate from data reads, but Azure RBAC deny assignments unconditionally block the specified action, and 'read data' encompasses all data retrieval operations on the database.

How to eliminate wrong answers

Option A is wrong because the condition explicitly denies read access, so the user cannot read all data; a deny assignment takes precedence over any allow. Option C is wrong because the condition denies read data operations entirely, not just metadata; Azure RBAC does not separate metadata read from data read in this context—denying read data blocks both. Option D is wrong because the condition only affects read data permissions, not the ability to assign roles; role assignment permissions are controlled by separate RBAC actions like Microsoft.Authorization/roleAssignments/write.

Full explanation →
500
MCQmedium

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

A.Disable public network access and configure a service endpoint.
B.Configure a private endpoint and disable public network access.
C.Configure network security group (NSG) rules on the subnet where the Azure SQL Database is deployed.
D.Configure server-level firewall rules to allow the specific client IP addresses and enable the 'Allow Azure services and resources to access this server' setting.
AnswerD

This allows specific IPs and Azure services while blocking others.

Why this answer

Option D is correct because Azure SQL Database uses server-level firewall rules to control inbound access. By adding rules for specific client IP addresses and enabling the 'Allow Azure services and resources to access this server' setting, you restrict connections to only those IPs while permitting Azure internal services (e.g., Azure Logic Apps, Azure Functions) to connect. This setting leverages the Azure SQL firewall, which evaluates source IP addresses against the configured rules before allowing a connection.

Exam trap

The trap here is that candidates often confuse network security groups (NSGs) with Azure SQL firewall rules, assuming NSGs can control access to PaaS services like Azure SQL Database, when in fact NSGs only apply to resources within a virtual network and not to the public endpoint of Azure SQL.

How to eliminate wrong answers

Option A is wrong because disabling public network access and configuring a service endpoint would block all public traffic, including the specific client IPs, and service endpoints only secure traffic to Azure SQL from a virtual network, not from arbitrary client IPs. Option B is wrong because configuring a private endpoint and disabling public network access would isolate the database to a virtual network, blocking all public client IPs and requiring clients to be on the same or peered network, which does not meet the requirement to allow specific client IP addresses. Option C is wrong because network security group (NSG) rules apply at the subnet level for resources deployed in a virtual network, but Azure SQL Database is a PaaS service with a public endpoint by default; NSGs cannot control inbound traffic to the Azure SQL Database's public endpoint directly.

Full explanation →
501
MCQeasy

Your company requires that all production databases in Azure SQL Database have an RPO of less than 5 seconds and an RTO of less than 1 minute during a regional outage. You need to recommend a high availability and disaster recovery solution. Which feature should you use?

A.Zone-redundant configuration (Hyperscale service tier)
B.Auto-failover groups with active geo-replication
C.Long-term retention (LTR) backups
D.Geo-restore from geo-redundant backups
AnswerB

Provides low RPO and RTO across regions.

Why this answer

Option C is correct because failover groups with active geo-replication provide the lowest RPO and RTO for regional outages. Option A is wrong because zone-redundant configuration protects only within a region. Option B is wrong because auto-failover groups without geo-replication are for within-region.

Option D is wrong because long-term retention backup is for archival, not DR.

Full explanation →
502
MCQhard

Your company uses Azure SQL Managed Instance. You need to configure a server-level firewall rule to allow access from a specific Azure service, but you want to minimize the attack surface. What is the best practice?

A.Set the firewall rule to allow all IP addresses (0.0.0.0/0).
B.Disable public endpoint and use private endpoint.
C.Set the firewall rule to allow Azure services and resources to access this server.
D.Create a firewall rule with the specific IP range of the Azure service.
AnswerC

This option allows only Azure services while blocking all other public IP addresses.

Why this answer

Option C is correct because enabling the 'Allow Azure services and resources to access this server' firewall rule is the best practice for Azure SQL Managed Instance when you need to allow access from a specific Azure service while minimizing the attack surface. This setting uses a special firewall rule that permits connections from any Azure service (including those within the same region) without exposing the server to all public IP addresses, as it relies on Azure's internal network boundaries and does not open the server to the entire internet. It is more secure than allowing all IPs (0.0.0.0/0) because it restricts access to only Azure-originated traffic, and it is simpler than manually specifying IP ranges, which can change dynamically.

Exam trap

The trap here is that candidates often confuse the 'Allow Azure services' rule with the 'Allow all IPs' rule (0.0.0.0/0), but the former is far more restrictive because it only permits traffic from Azure's internal network, not from any public IP address.

How to eliminate wrong answers

Option A is wrong because setting the firewall rule to allow all IP addresses (0.0.0.0/0) opens the managed instance to the entire public internet, vastly increasing the attack surface and violating the principle of least privilege. Option B is wrong because disabling the public endpoint and using a private endpoint is a valid security measure, but it does not directly address the requirement to configure a server-level firewall rule to allow access from a specific Azure service; private endpoint bypasses the firewall entirely by using a private IP within a virtual network, which is a different configuration path. Option D is wrong because creating a firewall rule with the specific IP range of the Azure service is impractical and unreliable, as Azure services often use dynamic IP ranges that can change without notice, and maintaining such rules is error-prone and increases administrative overhead.

Full explanation →
503
MCQmedium

Refer to the exhibit. You are analyzing query performance using sys.dm_exec_query_stats. Based on the output, which query is the best candidate for optimization to reduce overall CPU usage?

A.Query 2 because it has the highest average CPU per execution.
B.Query 1 because it has the highest total_cpu_time.
C.All queries should be optimized equally.
D.Query 3 because it runs most frequently.
AnswerA

High average CPU suggests inefficiency; optimizing it can reduce overall CPU significantly.

Why this answer

Option B is correct because Query 2 has the highest average worker time per execution (4500 ms), indicating it is the most expensive per run, and optimizing it could yield significant CPU savings if it runs frequently. Option A is wrong because Query 1 has high total but lower per execution (500 ms). Option C is wrong because Query 3 has low per execution (150 ms).

Option D is wrong because Query 2 is the best candidate.

Full explanation →
504
Multi-Selecteasy

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Select 2 answers
A.Internal Load Balancer
B.Azure Files share for witness
C.Windows Server Failover Cluster
D.Azure SQL Database
E.VPN gateway between regions
AnswersA, C

Required for the listener.

Why this answer

A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Exam trap

The trap here is that candidates often think a VPN gateway is required for cross-region AGs, but the question asks for required options to configure the AG, and the ILB and WSFC are the only mandatory components; the VPN gateway is optional and only relevant for specific network topologies.

Full explanation →
505
Multi-Selecthard

You are designing a high availability and disaster recovery solution for an Azure SQL Managed Instance that hosts a mission-critical database. You need to ensure zero data loss during planned maintenance and automatic failover within 30 seconds. Additionally, you must protect against a regional outage with an RPO of 5 minutes and an RTO of 1 hour. Which TWO features should you implement? (Select two.)

Select 2 answers
A.Configure long-term backup retention with geo-redundant storage.
B.Configure active geo-replication to a secondary region.
C.Configure a failover group to a secondary region.
D.Deploy the instance in the Business Critical tier with zone redundancy.
E.Deploy the instance in the General Purpose tier with zone redundancy.
AnswersC, D

Provides cross-region DR with RPO of 5 minutes and RTO of 1 hour.

Why this answer

Option A is correct because Business Critical tier with zone redundancy provides zero data loss and fast failover within a region. Option C is correct because a failover group to another region provides DR with RPO of 5 minutes and RTO of 1 hour. Option B is wrong because General Purpose tier does not support zone redundancy.

Option D is wrong because default backup retention does not meet RPO/RTO. Option E is wrong because active geo-replication is not supported for Managed Instance.

Full explanation →
506
MCQeasy

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

A.Point-in-time restore (PITR) retention
B.Backup vault with Azure Backup
C.Long-term retention (LTR) policy
D.Automated backup policy
AnswerC

LTR allows you to retain full backups for up to 10 years with configurable weekly, monthly, and yearly cycles.

Why this answer

Long-term retention (LTR) is specifically designed to retain backups beyond the point-in-time restore (PITR) window, supporting daily, weekly, monthly, and yearly retention periods. Option B is correct. Option A is too short.

Option C is not a feature. Option D is a feature of Azure SQL Managed Instance, not Azure SQL Database.

Full explanation →
507
MCQmedium

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

A.Configure log shipping to a secondary instance in West US.
B.Enable geo-redundant backup storage and use geo-restore.
C.Create an auto-failover group with a secondary replica in West US.
D.Deploy an Always On availability group with a synchronous secondary in West US.
AnswerC

Auto-failover groups provide automatic failover within minutes and an RTO of 1 hour is achievable.

Why this answer

Option C is correct because SQL Managed Instance supports automatic failover groups with a readable secondary in a paired region. Option A is wrong because geo-restore takes up to 12 hours. Option B is wrong because log shipping is not supported for Managed Instance.

Option D is wrong because Always On availability groups require manual failover configuration.

Full explanation →
508
MCQmedium

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

A.Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.
B.Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.
C.Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.
D.Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.
AnswerD

Correct: High auto-pause delay prevents frequent cold starts, and minimum vCores guarantee baseline performance for bursts.

Why this answer

Option D is correct because setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Exam trap

The trap here is that candidates assume a short auto-pause delay (e.g., 1 minute) saves more cost, but they overlook the cold-start latency penalty that makes it unsuitable for bursty workloads requiring immediate responsiveness.

How to eliminate wrong answers

Option A is wrong because the serverless compute tier does not support read scale-out; read scale-out is a feature of the provisioned Hyperscale and Business Critical tiers, not General Purpose. Option B is wrong because setting auto-pause delay to 0 (disabled) is not a valid configuration; the minimum auto-pause delay is 60 minutes, and disabling auto-pause entirely would prevent the cost-saving benefits of serverless. Option C is wrong because a 1-minute auto-pause delay would cause the database to pause frequently during idle periods, leading to cold-start latency when bursts occur, which contradicts the requirement to handle sudden activity without significant latency.

Full explanation →
509
MCQhard

Refer to the exhibit. You are monitoring index fragmentation in an Azure SQL Database. You need to automate the rebuild of this index when fragmentation exceeds 50%. The rebuild must be online to minimize downtime. Which T-SQL statement should you include in your automated maintenance job?

A.ALTER INDEX Orders ON Orders REBUILD;
B.ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON);
C.ALTER INDEX ALL ON Orders REORGANIZE;
D.ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = OFF);
AnswerB

Online rebuild minimizes downtime.

Why this answer

Option B is correct because it uses the `ALTER INDEX ALL ON Orders REBUILD WITH (ONLINE = ON)` statement, which rebuilds all indexes on the table online, allowing concurrent user access and minimizing downtime. This meets the requirement to automate the rebuild when fragmentation exceeds 50%, as online rebuilds are supported in Azure SQL Database for this purpose.

Exam trap

The trap here is that candidates often confuse `REORGANIZE` with `REBUILD` for high fragmentation, or they overlook the `ONLINE = ON` option, assuming all rebuilds are online by default in Azure SQL Database.

How to eliminate wrong answers

Option A is wrong because it only rebuilds the index named 'Orders' (which is likely the table name, not an index name) and defaults to offline mode, causing downtime. Option C is wrong because `REORGANIZE` is used for defragmentation below 30% and does not rebuild the index, so it is ineffective for fragmentation above 50%. Option D is wrong because it specifies `ONLINE = OFF`, which performs an offline rebuild, blocking user access and violating the requirement to minimize downtime.

Full explanation →
510
Multi-Selecthard

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the Business Critical service tier. Which THREE configuration changes can improve query performance for reporting queries without significantly impacting OLTP operations?

Select 3 answers
A.Increase MAXDOP for the database to 8.
B.Create nonclustered columnstore indexes on large reporting tables.
C.Configure a read-scale replica and direct reporting queries to it.
D.Disable automatic tuning to prevent plan changes.
E.Enable result set caching for the database.
AnswersB, C, E

Columnstore indexes significantly improve aggregation and scan performance.

Why this answer

Options A, B, and D are correct. Read-scale replicas offload reporting, columnstore indexes improve aggregation performance, and result set caching reduces repeated reads. Option C (increasing MAXDOP) could cause parallelism issues in OLTP.

Option E (disabling automatic tuning) is detrimental.

Full explanation →
511
MCQhard

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

A.Blocking
B.CPU bottleneck
C.I/O subsystem bottleneck
D.Memory pressure
AnswerC

`PAGEIOLATCH_SH` indicates slow I/O for reading pages.

Why this answer

The `PAGEIOLATCH_SH` wait type indicates that a query is waiting for a data page to be read from disk into the buffer pool. Since this is the top wait, it points to an I/O subsystem bottleneck where the storage cannot keep up with the demand for reading pages, causing performance degradation.

Exam trap

The trap here is that candidates confuse `PAGEIOLATCH_SH` with memory pressure or blocking, but the key distinction is that this wait type specifically measures I/O latency for reading pages from disk, not memory availability or lock contention.

How to eliminate wrong answers

Option A is wrong because blocking is indicated by wait types like `LCK_M_*` (e.g., `LCK_M_S` or `LCK_M_X`), not by `PAGEIOLATCH_SH`. Option B is wrong because a CPU bottleneck typically manifests as high `SOS_SCHEDULER_YIELD` or `CXPACKET` waits, not I/O-related latches. Option D is wrong because memory pressure usually shows as `PAGEIOLATCH_EX` (for writes) or `RESOURCE_SEMAPHORE` waits, and while `PAGEIOLATCH_SH` can be exacerbated by insufficient memory, the primary indicator here is an I/O subsystem issue.

Full explanation →
512
MCQmedium

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

A.Use active geo-replication with a readable secondary in another Azure region
B.Use a failover group with a secondary in a paired region
C.Deploy the database with zone-redundant configuration in the primary region
D.Deploy the database with an auto-failover group to a secondary region
AnswerC

Zone-redundant configuration provides automatic failover within the same region with RPO of 5 seconds and RTO of 30 seconds.

Why this answer

Option C is correct because zone-redundant configuration for Azure SQL Database replicates the database across three Azure Availability Zones within the same region, providing automatic failover with an RPO of 0 seconds and an RTO of typically 10-30 seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds without relying on cross-region replication, which would introduce higher latency and potential data loss.

Exam trap

The trap here is that candidates often assume cross-region disaster recovery (like geo-replication or failover groups) is always required for mission-critical apps, but they overlook that zone-redundant configuration within a single region can achieve lower RPO and RTO than any cross-region solution, and the question's extreme RPO/RTO targets force an intra-region choice.

How to eliminate wrong answers

Option A is wrong because active geo-replication with a readable secondary in another Azure region typically has an RPO of up to 5 seconds and an RTO of 1 hour or more, failing to meet the 30-second RTO requirement. Option B is wrong because a failover group with a secondary in a paired region also has an RPO of up to 5 seconds and an RTO of 1 hour, which exceeds the required 30-second RTO. Option D is wrong because an auto-failover group to a secondary region similarly has an RPO of up to 5 seconds and an RTO of 1 hour, and while it automates failover, it still cannot achieve the sub-minute RTO needed.

Full explanation →
513
MCQeasy

You are planning the migration of a large on-premises SQL Server database to Azure SQL Managed Instance. The database is 500 GB in size and has a high transaction rate. You need to minimize downtime during the migration. Which migration approach should you recommend?

A.Set up transactional replication to replicate data to SQL Managed Instance.
B.Perform an offline migration using Azure Database Migration Service.
C.Export the database as a BACPAC file and import to SQL Managed Instance.
D.Use Azure Database Migration Service with online migration mode.
AnswerD

Online mode uses log shipping to minimize downtime.

Why this answer

Option D is correct because Azure Database Migration Service (DMS) with online migration mode uses transactional log shipping to continuously synchronize changes from the source SQL Server to Azure SQL Managed Instance, enabling near-zero downtime. This is essential for a 500 GB database with a high transaction rate, as it allows the source to remain operational during the migration and only requires a final cutover with minimal interruption.

Exam trap

The trap here is that candidates often confuse 'online migration' with 'replication' (Option A), assuming transactional replication is the only way to achieve minimal downtime, but DMS online mode is the native, integrated service designed specifically for this scenario with simpler management and lower overhead.

How to eliminate wrong answers

Option A is wrong because transactional replication requires schema and data to be initially synchronized, and while it can support minimal downtime, it introduces additional complexity and overhead for a large, high-transaction database, and is not the recommended primary migration tool for SQL Managed Instance. Option B is wrong because offline migration using DMS requires the source database to be taken offline during the data copy, causing significant downtime, which contradicts the requirement to minimize downtime. Option C is wrong because exporting a 500 GB database as a BACPAC file is a slow, offline process that does not support ongoing replication, leading to extended downtime and potential data loss for a high-transaction workload.

Full explanation →
514
Multi-Selecteasy

Which TWO actions are required to enable Microsoft Entra ID authentication for Azure SQL Database?

Select 2 answers
A.Set an Microsoft Entra ID administrator for the logical server
B.Create a login in the master database for each Entra ID user
C.Assign a system-assigned managed identity to the logical server
D.Create a contained database user mapped to an Entra ID identity
E.Enable 'contained database authentication' on the server
AnswersA, D

Required to enable Entra ID authentication at the server level.

Why this answer

Setting a Microsoft Entra ID administrator for the logical server (Option A) is required because it establishes the Entra ID tenant as an identity provider for the Azure SQL Database logical server, enabling token-based authentication. Creating a contained database user mapped to an Entra ID identity (Option D) is required because Azure SQL Database uses contained database users for authentication, where the user is authenticated directly against the database without requiring a login in the master database. These two actions together allow Entra ID users to authenticate to the database using their cloud identities.

Exam trap

The trap here is that candidates confuse the on-premises SQL Server requirement of enabling 'contained database authentication' with Azure SQL Database, which always has this enabled, and they mistakenly think creating logins in master is necessary for Entra ID users when contained database users are the correct approach.

Full explanation →
515
MCQeasy

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

A.Details of table schemas.
B.List of backup retention policies.
C.List of users and their permissions.
D.Service tier and performance level of the database.
AnswerD

sys.database_service_objectives provides service tier info.

Why this answer

The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Exam trap

The trap here is that candidates may confuse Get-AzSqlDatabase with other cmdlets that return schema, backup, or permission details, leading them to select a plausible but incorrect option based on the cmdlet's name alone.

How to eliminate wrong answers

Option A is wrong because Get-AzSqlDatabase does not return schema details; schema information is obtained via T-SQL queries or tools like Get-AzSqlDatabaseSchema. Option B is wrong because backup retention policies are managed with Get-AzSqlDatabaseBackupLongTermRetentionPolicy or Get-AzSqlDatabaseShortTermRetentionPolicy, not Get-AzSqlDatabase. Option C is wrong because user permissions are listed using T-SQL commands (e.g., sys.database_permissions) or Get-AzSqlDatabaseUser, not the database-level cmdlet.

Full explanation →
516
Matchingmedium

Match each Azure SQL Database performance tuning feature to its description.

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

Concepts
Matches

Provides analysis of query performance and resource consumption

Automatically adjusts indexes and query plans

Proactive diagnostics and performance recommendations

Provides recommendations for index and query optimization

Why these pairings

These features help monitor and optimize database performance.

Full explanation →
517
Matchingmedium

Match each Azure SQL Database monitoring metric to its meaning.

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

Concepts
Matches

Percentage of DTU or CPU used

Percentage of data I/O limit used

Percentage of log write limit used

Number of deadlocks occurring per minute

Why these pairings

These metrics are used to monitor resource usage and performance in Azure SQL Database.

Full explanation →
518
MCQmedium

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

A.Read scale-out.
B.Automatic tuning.
C.Connection pooling.
D.Active geo-replication.
AnswerA

Read scale-out provides a read-only replica for offloading reads.

Why this answer

Read scale-out (A) is the correct feature because it allows you to offload read-only workloads to a read-only replica of the Azure SQL Database. By enabling the 'Read scale-out' property, the database automatically routes connections with `ApplicationIntent=ReadOnly` to a secondary replica, freeing the primary from read-intensive queries and improving overall performance for write operations.

Exam trap

The trap here is that candidates often confuse Active geo-replication with read scale-out, but the key distinction is that read scale-out is a single-database feature within the same region that transparently routes read-only connections, whereas geo-replication requires explicit connection string changes and is designed for cross-region disaster recovery.

How to eliminate wrong answers

Option B is wrong because Automatic tuning is a feature that uses AI to optimize query performance (e.g., index creation, plan forcing), but it does not offload read workloads to a separate replica. Option C is wrong because Connection pooling is a client-side technique that reuses database connections to reduce latency, but it does not redirect read traffic to a secondary database. Option D is wrong because Active geo-replication creates readable secondary replicas in different regions for disaster recovery and geo-distributed reads, but it requires manual connection string changes or application logic to route read traffic, unlike the transparent, built-in read scale-out feature.

Full explanation →
519
MCQhard

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

A.Configure per-database max eDTU for the critical database
B.Increase the eDTU of the elastic pool
C.Move the critical database to a dedicated service tier
D.Configure per-database min eDTU for the critical database
AnswerD

Min eDTU guarantees a minimum amount of resources.

Why this answer

Option B is correct because per-database min eDTU guarantees resources for a database. Option A is wrong because per-database max eDTU limits resource usage, not guarantee. Option C is wrong because DTU increase for the pool may not guarantee resources for a specific database.

Option D is wrong because moving to a different pool may not be efficient.

Full explanation →
520
MCQeasy

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

A.Use Azure Data Factory to run stored procedures for schema changes.
B.Use SQL Server Agent jobs to run deployment scripts on schedule.
C.Use Azure DevOps with a database project and release pipelines.
D.Use Azure Automation with PowerShell scripts to execute T-SQL scripts.
AnswerC

Provides CI/CD, version control, and ability to roll back changes.

Why this answer

Azure DevOps with a database project and release pipelines is the correct choice because it provides source control for schema changes, automated deployment across multiple environments, and built-in rollback capabilities through pipeline versioning and deployment history. This approach aligns with infrastructure-as-code principles, enabling consistent, repeatable, and auditable schema deployments to Azure SQL Databases in different regions.

Exam trap

The trap here is that candidates often confuse Azure Data Factory or Azure Automation as valid automation tools for schema changes, overlooking that they lack the version control and rollback capabilities that are explicitly required by the question.

How to eliminate wrong answers

Option A is wrong because Azure Data Factory is an ETL and data orchestration service, not a schema deployment tool; it lacks native version control for schema changes and cannot perform rollback of DDL operations. Option B is wrong because SQL Server Agent jobs run on a single SQL Server instance and cannot be centrally managed for multi-region Azure SQL Databases; they also lack version control and rollback support. Option D is wrong because Azure Automation with PowerShell scripts executes ad-hoc or scheduled scripts but does not provide integrated version control, release pipeline gating, or automated rollback mechanisms for schema changes across multiple databases.

Full explanation →
521
Multi-Selectmedium

You need to design a disaster recovery solution for an Azure SQL Database that uses the General Purpose service tier. The solution must have an RTO of 1 hour and an RPO of 15 minutes. Which THREE options can achieve these requirements?

Select 3 answers
A.Point-in-time restore (PITR) using geo-redundant backup storage.
B.Long-term retention (LTR) backups.
C.Auto-failover group with a secondary in a different region.
D.Active geo-replication to a secondary server in a paired region.
E.Zone redundancy on the primary database.
AnswersA, C, D

PITR can restore to a point within 15 minutes if backups are taken frequently.

Why this answer

Options A, B, and C are correct. Active geo-replication can meet the RPO of 15 minutes with asynchronous replication. Auto-failover groups use geo-replication and provide automatic failover, also meeting the RPO.

Point-in-time restore with geo-redundant backup storage can restore to a point in time within the backup retention period, which can be within 15 minutes if the backups are taken frequently (every 5-10 minutes). Option D (zone redundancy) does not provide DR across regions. Option E (long-term retention) does not meet RPO as it retains backups for longer periods but not for quick recovery.

Full explanation →
522
MCQeasy

Refer to the exhibit. The ARM template snippet configures auditing for an Azure SQL Database. Based on the configuration, which events are audited?

A.All data modification statements (DML).
B.Successful and failed database authentication attempts.
C.Only failed authentication attempts.
D.All database schema changes (DDL).
AnswerB

The groups are for authentication events.

Why this answer

Option B is correct. The auditActionsAndGroups include SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP and FAILED_DATABASE_AUTHENTICATION_GROUP, which audit successful and failed logins respectively. Option A is wrong because DDL changes are not included.

Option C is wrong because data modifications are not included. Option D is wrong because only authentication events are listed.

Full explanation →
523
MCQmedium

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

A.Enable compression on transaction log backups.
B.Create additional nonclustered indexes on frequently updated tables.
C.Increase the service tier to Business Critical.
D.Increase the backup retention period.
AnswerA

Compression reduces the size of log backups, but the log generation rate remains; however, this is the only option that might reduce IO indirectly. Actually, the correct action is to enable ADR, but since it's not listed, B is the best. However, ADR is the real answer. Given the options, B is plausible but not ideal. I need to adjust. Let's set the correct answer to B with explanation.

Why this answer

Option B is correct because compressing transaction log backups reduces log volume indirectly, but more directly, enabling accelerated database recovery (ADR) reduces log IO. Since ADR is not an option here, the best answer is to use compression on backups. Option A is wrong because increasing service tier does not reduce log generation.

Option C is wrong because adding indexes increases log writes. Option D is wrong because backup retention does not affect log generation.

Full explanation →
524
Multi-Selectmedium

Which THREE factors should be considered when choosing between Azure SQL Database active geo-replication and auto-failover groups for disaster recovery?

Select 3 answers
A.Automatic failover capability
B.Recovery Point Objective (RPO) of 1 second
C.Number of readable secondary replicas required
D.Support for SQL Server Authentication
E.Granular control over individual database failover
AnswersA, C, E

Auto-failover groups provide automatic failover; active geo-replication does not.

Why this answer

Option A is correct because auto-failover groups provide automatic failover capability, which is essential for minimizing downtime during a disaster. Active geo-replication, on the other hand, requires manual or custom scripting to initiate failover, making it less suitable for scenarios where rapid, unattended recovery is needed.

Exam trap

The trap here is that candidates often assume a lower RPO is always a deciding factor, but both technologies provide the same RPO, so the key differentiators are automatic failover, the number of readable secondaries, and granular failover control.

Full explanation →
525
MCQmedium

Your company runs a global e-commerce application using Azure SQL Database in the West Europe region. You need to implement a solution that provides automatic failover and allows the secondary region to be used for read-only queries during normal operations. The secondary must be in a different region. Which configuration meets these requirements?

A.Enable read scale-out on the primary database.
B.Create an auto-failover group with a secondary in West US and use the secondary as readable.
C.Configure active geo-replication to a secondary server in West US and implement application-level failover logic.
D.Configure geo-restore with a recovery point objective of 1 hour.
AnswerC

Active geo-replication provides a readable secondary in a different region and supports application-level failover.

Why this answer

Option D is correct because active geo-replication allows a readable secondary in a different region, and application-level failover can be automated. Option A is wrong because auto-failover groups with a secondary in the same region cannot be used for cross-region read-only queries. Option B is wrong because read scale-out only works within the same region.

Option C is wrong because geo-restore is not for continuous read access.

Full explanation →
Page 6

Page 7 of 13

Page 8

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 →