DP-300 Practice Test 10 — 10 Questions

Question 1

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

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

Accepted Answer

Query Store with custom retention settings. Query Store is the only feature that persists query execution plans and runtime statistics in the database, enabling historical performance analysis and before/after comparisons for index changes. It captures query text, plans, and runtime metrics over time, which is exactly what is needed to compare performance before and after index modifications. Custom retention settings allow you to control how long the data is kept, ensuring you have the necessary history.

Answer

SQL Server DMVs

Answer

Azure SQL Analytics solution in Log Analytics

Answer

Azure SQL Database Intelligent Insights

Question 2

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

Accepted Answer

Bicep templates. Bicep is a domain-specific language for deploying Azure resources declaratively. It is the recommended infrastructure as code tool for Azure. Option A is correct because Bicep templates allow you to define Azure SQL Database, firewall rules, and performance tier in a declarative manner. Option B (SSDT) is used for database schema management, not resource deployment. Option C (T-SQL scripts) are for database queries and management, not infrastructure. Option D (PowerShell scripts) can automate tasks but are procedural, not declarative IaC like Bicep.

Answer

SQL Server Data Tools (SSDT) database projects

Answer

T-SQL scripts

Answer

PowerShell scripts

Question 3

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Accepted Answer

Create Elastic Database Jobs that run index maintenance T-SQL scripts.. Option A is correct because Elastic Database Jobs are a native Azure SQL Database feature that lets you define and schedule T-SQL scripts (such as ALTER INDEX ... REBUILD/REORGANIZE) across one or many databases without needing an external orchestrator. Option C is correct because Azure SQL Database's built-in automatic tuning can automatically create and drop indexes based on workload analysis, which is a native, server-side index maintenance capability. Option B is not native to Azure SQL Database: Azure Automation is a separate Azure service, and its PowerShell runbooks must connect externally to run T-SQL. Option D is wrong because SQL Server Agent is not available in Azure SQL Database (it exists in Azure SQL Managed Instance and SQL Server on Azure VMs). Option E is wrong because Azure Data Factory is a data integration/ETL service, not an index maintenance mechanism.

Answer

Use Azure Automation PowerShell runbooks to invoke index rebuilds.

Answer

Schedule SQL Agent jobs with ALTER INDEX statements.

Answer

Use Azure Data Factory to copy data and rebuild indexes.

Question 4

Your company has an Azure SQL Database that uses a failover group with a secondary in a different region. You need to ensure that read-only queries are directed to the secondary database to offload the primary. What should you configure?

Accepted Answer

Add ApplicationIntent=ReadOnly to the connection string and use the failover group listener.. To direct read-only queries to the secondary database in a failover group, you must use the failover group listener and add 'ApplicationIntent=ReadOnly' to the connection string. The listener routes read-write connections to the primary and read-only connections to the secondary, based on the ApplicationIntent parameter. This is the built-in read-scale-out mechanism for failover groups.

Answer

Use a Traffic Manager profile to route read traffic.

Answer

Enable read-scale for the database.

Answer

Configure a separate connection string pointing to the secondary server.

Question 5

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?

Accepted Answer

Configure the maintenance window for the Managed Instance.. Azure SQL Managed Instance provides a built-in maintenance window feature that allows you to schedule patching of the underlying OS and SQL Server engine with minimal downtime. Configuring the maintenance window ensures updates are applied during a specified time, reducing impact on production workloads.

Answer

Use an Elastic Job agent to run a script that applies updates.

Answer

Schedule a manual patching using the Azure portal.

Answer

Use Azure Update Manager to schedule patching.

Question 6

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?

Accepted Answer

Log Analytics Workspace. Log Analytics Workspace (B) is correct because it is the service that stores and queries the diagnostic and metric telemetry collected from Azure SQL Database, enabling you to write Kusto Query Language (KQL) queries that define the performance conditions to monitor. Azure Monitor Alerts (C) is correct because it evaluates those log/metric queries against defined thresholds and triggers notifications (for example, email, SMS, or action groups) when the conditions are met. Together, Log Analytics Workspace provides the data and query layer while Azure Monitor Alerts provides the rule evaluation and notification layer, forming the standard automated monitoring and alerting pipeline for Azure SQL Database. Azure Sentinel (A) is a SIEM/SOAR tool focused on security threat detection, not general SQL performance monitoring. Application Insights (D) targets application-level telemetry (APM) for web apps and services, not Azure SQL Database performance metrics. Azure Advisor (E) only provides best-practice recommendations and does not perform real-time condition-based alerting.

Answer

Azure Sentinel

Answer

Application Insights

Answer

Azure Advisor

Question 7

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?

Accepted Answer

Dynamic Data Masking. 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.

Answer

Always Encrypted

Answer

Transparent Data Encryption (TDE)

Answer

Row-Level Security

Question 8

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Accepted Answer

Use Microsoft Entra ID authentication instead of SQL authentication.. Option A is correct because Microsoft Entra ID authentication centralizes identity management, supports MFA and conditional access, and eliminates the risk of weak or shared SQL logins and passwords. Option B is correct because Azure SQL Database firewall rules (server-level and database-level) limit inbound connections to specific, known IP address ranges, reducing the exposed attack surface from the public internet. Option C is correct because Transparent Data Encryption (TDE) encrypts data at rest, including backups and log files, protecting against unauthorized access to physical media or backup theft. Option D is not a best practice because enabling public network access broadens exposure; private endpoints or restricted firewall rules are preferred. Option E is not a best practice because granting db_owner to developers violates least privilege and gives excessive control over the database.

Answer

Enable public network access to allow flexible connectivity.

Answer

Grant db_owner role to developers for ease of management.

Question 9

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?

Accepted Answer

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

Answer

Create a contained database user mapped to the consultants' Microsoft Entra ID user principal names (UPNs)

Answer

Configure Azure SQL Database to trust the partner's Microsoft Entra ID tenant

Answer

Create a SQL Server authentication login and user for the consultants

Question 10

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

Accepted Answer

Enable Microsoft Defender for SQL. Microsoft Defender for SQL includes advanced threat detection capabilities that continuously monitor database activity for anomalous patterns, including SQL injection attempts. When enabled, it analyzes query execution patterns and can alert on suspicious queries that deviate from parameterized statement usage, directly addressing the security team's requirement to detect and alert on potential SQL injection attacks.

Answer

Enable auditing on the database

Answer

Enable data discovery and classification

Answer

Enable SQL vulnerability assessment