DP-300 Practice Exam 52 — 20 Questions

Question 1

You are troubleshooting an Always On Availability Group named AG1. The exhibit shows the current state. The primary replica shows PENDING_FAILOVER. The secondary replica is DISCONNECTED with error 35202. What is the most likely cause of this issue?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Network connectivity between the replicas is blocked on port 5022. The PENDING_FAILOVER state on the primary replica indicates that a failover was initiated but could not complete because the secondary replica is DISCONNECTED. Error 35202 specifically indicates that a connection attempt to the secondary replica failed, which in an Always On Availability Group (AG) is typically due to a network connectivity issue on the dedicated endpoint port (default 5022). Since the secondary is unreachable, the failover cannot finalize, leaving the primary in a pending state.

Answer

The availability group listener is not configured correctly

Answer

The primary instance encountered a critical error and failed over automatically

Answer

The cluster has lost quorum

Question 2

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.)

Accepted Answer

Configure a failover group with a secondary instance in a paired region, both using Business Critical service tier.. (failover group with Business Critical secondary in paired region) meets the RPO of 15 seconds and RTO of 5 minutes because Business Critical tier uses synchronous commit within the region and asynchronous replication across regions, with failover group providing automated failover. Option E (active geo-replication) is not supported for Azure SQL Managed Instance; only failover groups are available. Option B does not protect against regional failure. Option C's General Purpose tier cannot guarantee the required low RPO/RTO. Option D is not a standalone solution.

Answer

Deploy a zone-redundant Business Critical instance in a single region.

Answer

Deploy a General Purpose instance with a failover group to a secondary in another region.

Answer

Ensure the secondary instance has at least one readable secondary replica.

Answer

Use active geo-replication between two Business Critical instances in different regions.

Question 3

You are a database administrator for a large e-commerce platform using Azure SQL Database. The application experiences intermittent performance degradation during peak hours. Query Store data shows that a specific stored procedure, `usp_getProducts`, has multiple query variants with different execution plans. Some plans are suboptimal. You need to ensure consistent and optimal performance for this stored procedure without rewriting the application code. What should you do?

Accepted Answer

Enable forced parameterization on the database to promote plan reuse.. Enabling forced parameterization causes the database to treat literal values in queries as parameters, which promotes plan reuse and reduces plan cache bloat. This is ideal for stored procedures like usp_getProducts that have multiple query variants with different plans. Option B is incorrect because plan guides target specific queries, not the entire stored procedure, and are complex to manage. Option C is incorrect because Query Store hints are used to force a specific plan for a query, but they require manual intervention and don't address the root cause of plan variability. Option D is incorrect because automatic tuning with 'FORCE LAST GOOD PLAN' is reactive and may not provide consistent performance for a procedure with many variants.

Answer

Create a plan guide to force a specific execution plan for the stored procedure.

Answer

Implement Query Store hints to force the last known good plan for the stored procedure.

Answer

Enable automatic tuning in Azure SQL Database and rely on the 'FORCE LAST GOOD PLAN' feature.

Question 4

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Answer

SQL Vulnerability Assessment

Answer

Advanced Threat Protection

Answer

Microsoft Defender for Cloud

Question 5

You are reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

Accepted Answer

Change state to Enabled and set retentionDays to 90.. The vulnerability assessment must be enabled (state: Enabled) and retentionDays must be set to at least 90 to meet the audit requirement. The exhibit shows state as Disabled and retentionDays as 30. Option A is wrong because adding email addresses only affects notifications, not the scan state or retention. Option B is wrong because retentionDays of 90 with state Disabled means scans are not running. Option D is wrong because disabledAlerts control which alerts are suppressed; removing them does not enable the scan or change retention.

Answer

Add additional email addresses to ensure notification.

Answer

Change retentionDays to 90 and keep the state as Disabled.

Answer

Remove the disabledAlerts entries to enable all alerts.

Question 6

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

Accepted Answer

Create a database role, grant EXECUTE on the 'Sales' schema to the role, and add 'AppUser' to the role.. Creating a database role with EXECUTE permission on the 'Sales' schema grants execute on all stored procedures in that schema, adhering to least privilege. Option A is wrong because granting SELECT on the schema would give read access to underlying tables, which is not allowed. Option B is wrong because adding AppUser to the db_datareader role provides read access to all tables in the database, exceeding the required permissions. Option D is not wrong in terms of functionality but is more administrative work compared to using a schema-level grant; however, for the requirement of least privilege, granting EXECUTE on each stored procedure individually would still work but is less efficient than the schema-level approach.

Answer

Grant the SELECT permission on the 'Sales' schema to 'AppUser'.

Answer

Add 'AppUser' to the db_datareader role.

Answer

Grant the EXECUTE permission on each stored procedure individually to 'AppUser'.

Question 7

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

Accepted Answer

A release pipeline with a 'Azure SQL Database deployment' task.. Options A, B, and C are correct. A release pipeline with the 'Azure SQL Database deployment' task automates the deployment. A SQL database project (.sqlproj) defines the schema to deploy. A service connection to Azure with appropriate permissions is needed for authentication and authorization. Option D is incorrect because while a schema compare tool can be used to generate scripts, it is not a strict requirement; the deployment task can handle the deployment directly from the project. Option E is incorrect because a self-hosted build agent is not required; Microsoft-hosted agents can be used.

Answer

A schema compare tool to generate deployment scripts.

Answer

A self-hosted build agent with SQL tools installed.

Question 8

You have an Azure SQL Database that uses a SQL Agent job to run a critical ETL process every night. The job recently started failing intermittently. You need to automate the monitoring and alerting of job failures, and automatically retry the job twice with a 10-minute interval between retries. What should you configure?

Accepted Answer

Configure the job step's 'Retry attempts' and 'Retry interval (minutes)' settings in the SQL Agent job step properties.. SQL Agent job steps have built-in 'Retry attempts' and 'Retry interval (minutes)' settings that allow you to configure automatic retries directly in the job step properties. This is the simplest and most native way to retry a failed job step. Option A is incorrect because a T-SQL loop checking job history is complex and not an automated built-in feature. Option B is incorrect because Power Automate polling is an external solution that adds unnecessary complexity. Option C is incorrect because Azure Monitor alerts can notify but do not automatically retry the job; they would require additional logic (e.g., webhook to PowerShell) which is not native or as reliable as the built-in retry settings.

Answer

Modify the job to use a T-SQL loop that checks job history and re-runs the job step.

Answer

Use Microsoft Power Automate to poll the job history and re-run the job if failed.

Answer

Create an Azure Monitor alert on the job failure event and use a webhook to trigger a PowerShell script that retries the job.

Question 9

You have an Azure SQL Database that uses the Business Critical service tier with zone redundancy enabled. The database is deployed in a single region. You want to ensure that if a zone fails, the database remains available with minimal downtime. How does zone redundancy achieve this?

Accepted Answer

It maintains multiple synchronous replicas in different availability zones within the region.. Zone redundancy for Azure SQL Database Business Critical tier maintains multiple synchronous replicas in different availability zones within the same region. If one zone fails, another replica automatically becomes the primary, ensuring minimal downtime. Option B is incorrect because read-only replicas are not promoted to primary; all replicas are read/write capable and failover is automatic. Option C is incorrect as it describes geo-replication to a secondary region, not zone redundancy. Option D is incorrect because replicas are kept synchronous, not asynchronous.

Answer

It creates read-only replicas in other zones that can be promoted to primary.

Answer

It replicates data to a secondary region for failover.

Answer

It uses asynchronous replication to a standby replica in another zone.

Question 10

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

Accepted Answer

Page Life Expectancy. Page Life Expectancy (PLE) is a key metric that indicates how long a data page remains in the buffer pool before being evicted. A consistently low PLE (e.g., below 300 seconds) signals that pages are being flushed too quickly due to memory pressure, often from insufficient buffer pool memory. Memory Grants Pending tracks the number of queries waiting for a memory grant to execute; a non-zero value indicates that the server cannot allocate enough memory to satisfy query workspace requirements, directly pointing to memory pressure.

Answer

Average CPU percent

Answer

Log IO

Answer

Data IO

Question 11

Which THREE of the following are features available in Azure SQL Managed Instance that are not available in Azure SQL Database (single database)?

Accepted Answer

Cross-database queries (three- and four-part names). Cross-database queries using three- and four-part names (e.g., `[DB1].[schema].[table]`) are supported in Azure SQL Managed Instance because it provides full SQL Server engine compatibility, including the ability to query across databases within the same instance. Azure SQL Database single databases are isolated and do not support cross-database queries, as each database is a separate logical boundary without instance-level context.

Answer

Transparent Data Encryption (TDE)

Answer

Elastic pools

Question 12

Refer to the exhibit. You are deploying an Azure SQL Database with Transparent Data Encryption (TDE) enabled via ARM template. The database will contain highly sensitive data, and your security policy requires that the encryption key be managed by your organization using Azure Key Vault. What additional configuration is needed?

Accepted Answer

Set the 'keyVaultUri', 'keyName', and 'keyVersion' properties to reference the key in Key Vault. When deploying Azure SQL Database with TDE and customer-managed keys (CMK) via ARM template, you must explicitly specify the 'keyVaultUri', 'keyName', and 'keyVersion' properties under the 'encryptionProtector' resource to link the database to the specific key in Azure Key Vault. Without these properties, the template would only enable TDE with a service-managed key, not the required customer-managed key.

Answer

No additional configuration is needed; the template already enables TDE with customer-managed keys

Answer

Deploy a separate key rotation policy in the ARM template

Answer

Enable 'autoRotationEnabled' property

Question 13

You need to provision an Azure SQL Database that supports a high-availability SLA of 99.995% and provides automatic failover to a secondary region in case of a regional outage. Which deployment option should you choose?

Accepted Answer

Azure SQL Database with active geo-replication and failover group. Active geo-replication with a failover group provides an SLA of 99.995% by replicating data asynchronously to a secondary region and enabling automatic failover during a regional outage. This is the only option that meets both the high-availability SLA and cross-region disaster recovery requirement.

Answer

Azure SQL Database in Hyperscale tier

Answer

Azure SQL Database with zone-redundant configuration

Answer

Azure SQL Managed Instance with failover group

Question 14

You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?

Accepted Answer

Use Query Store to compare the forced plan with the previous good plan and update statistics.. Reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression and is not the first step. Option C is wrong because adding query hints like OPTIMIZE FOR UNKNOWN may not resolve the plan regression and could lead to suboptimal plans for other parameter values. Option D is wrong because scaling up the database increases resources temporarily but does not address the underlying plan regression issue.

Answer

Disable automatic tuning and manually create a plan guide for the query.

Answer

Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.

Answer

Scale up the database to 16 vCores to handle the CPU load.

Question 15

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

Accepted Answer

Built-in high availability with a standby replica. The General Purpose service tier provides built-in high availability through automatic failover to a remote database copy on a different compute node, ensuring availability during planned patching events. Option A is incorrect because active geo-replication is a disaster recovery feature that requires manual configuration. Option B is incorrect because zone-redundant availability is an optional configuration for the General Purpose tier, not provided by default. Option D is incorrect because Business Critical is a separate service tier and not a high availability feature of General Purpose.

Answer

Active geo-replication

Answer

Zone-redundant availability

Answer

Business Critical service tier

Question 16

Which THREE security features are available in Azure SQL Database to protect data at rest?

Accepted Answer

Transparent Data Encryption (TDE) with customer-managed keys. Transparent Data Encryption (TDE) with customer-managed keys (Option A) protects data at rest by encrypting the database files and backups using a key stored in Azure Key Vault, giving the customer full control over key rotation and revocation. TDE with service-managed keys (Option C) also encrypts data at rest but uses a key managed by Azure, which is simpler but offers less control. Always Encrypted with secure enclaves (Option E) protects sensitive data at rest by encrypting columns with keys that are never revealed to the database engine, and the secure enclave allows computations on encrypted data, ensuring data remains encrypted even during processing.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Question 17

Your organization uses Azure SQL Database with Azure SQL Managed Instance for a multi-tenant SaaS application. You need to ensure that each tenant's data is isolated and that a compromised tenant cannot access other tenants' data. What is the most secure approach?

Accepted Answer

Deploy a separate database per tenant, configure server-level firewall rules per tenant IP, and use Microsoft Entra authentication with application roles.. Deploying a separate database per tenant provides the strongest isolation boundary at the storage and compute layer. Combining server-level firewall rules that restrict access to each tenant's IP range and using Microsoft Entra authentication with application roles ensures that even if one tenant's credentials are compromised, the attacker cannot access another tenant's database. This multi-layered approach (network + authentication + authorization) is the most secure for multi-tenant isolation in Azure SQL Managed Instance.

Answer

Use dynamic data masking to obfuscate tenant IDs in query results.

Answer

Implement row-level security (RLS) with a tenant ID filter on all tables.

Answer

Use column-level encryption with Azure Key Vault for sensitive columns.

Question 18

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

Accepted Answer

Develop an Azure SQL Database project in Visual Studio, generate a DACPAC, and use Elastic Database Jobs with a custom tracking table to apply the DACPAC to each tenant database in batches. Integrate with Azure DevOps to trigger the job after build.. Using a combination of Azure SQL Database project (DACPAC) for schema definition and Elastic Database Jobs for targeted deployment allows rolling updates per tenant. Azure DevOps can trigger the jobs. Option A is incorrect because Azure Data Factory is for data movement, not schema deployment. Option B is incorrect because Azure Automation is not designed for multi-tenant schema deployment. Option C is incorrect because SQL Agent is not available in Azure SQL Database.

Answer

Use Azure Data Factory with a ForEach activity to execute stored procedures in each database.

Answer

Create an Azure Automation runbook that connects to each database sequentially and runs ALTER TABLE statements.

Answer

Configure SQL Agent jobs on each database to run the schema changes.

Question 19

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?

Accepted Answer

Create a failover group with a secondary database in a different region using the General Purpose tier.. A failover group with a secondary in another region provides automatic failover with low RPO (<1 hour) and RTO (<2 hours), and using the General Purpose tier is cost-effective. Option B is wrong because zone redundancy provides high availability within a single region, not disaster recovery across regions. Option C is wrong because geo-restore from geo-redundant backups has an RTO of several hours (typically 7-12 hours), which exceeds the required 2 hours. Option D is wrong because upgrading to Business Critical tier is more expensive and not necessary for the RPO/RTO requirements; geo-replication is already available in General Purpose through failover groups.

Answer

Enable zone redundancy on the existing database.

Answer

Use geo-restore from geo-redundant backups.

Answer

Upgrade to Business Critical tier and configure geo-replication.

Question 20

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

Accepted Answer

Identify and create missing indexes.. High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Answer

Increase the maximum number of concurrent workers.

Answer

Reduce MAXDOP to 1.

Answer

Increase MAXDOP to 8.