DP-300 Practice Test 39 — 15 Questions

Question 1

Your Azure SQL Database is experiencing deadlocks. You have enabled deadlock graphs in the extended events session. After capturing a deadlock, you need to analyze it to determine which queries are involved. What should you use?

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

Accepted Answer

Open the deadlock graph file in SQL Server Management Studio (SSMS).. To analyze a deadlock graph captured via extended events, you should open the .xdl file in SQL Server Management Studio (SSMS), which provides a graphical representation of the deadlock, showing the processes involved, the resources, and the queries. Option B is correct. Option A is incorrect because sys.dm_exec_requests with a blocking filter shows current blocking, not historical deadlock details. Option C is incorrect because Azure Monitor for SQL provides metrics but not the detailed deadlock graph. Option D is incorrect because Query Store tracks query performance over time but does not capture deadlock events or graphs.

Answer

Query sys.dm_exec_requests with a filter on blocking.

Answer

Azure Monitor for SQL and view deadlock metrics.

Answer

Query Store and review the regressed queries.

Question 2

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

Accepted Answer

Create an Elastic Database Job with step scripts for each maintenance task, targeting all databases, and configure retry logic.. Elastic Database Jobs (formerly Elastic Database Jobs) allows you to run T-SQL scripts across many databases with built-in retry logic, error handling, and scheduling. It is designed for such maintenance tasks over elastic pools. Option B (SQL Agent) is not available in Azure SQL Database because it is a PaaS service with no SQL Agent. Option C (Azure Data Factory) is primarily for data movement and transformation, not for executing T-SQL maintenance scripts across databases. Option D (Azure Automation runbooks) can run PowerShell, but would require custom error handling and retry logic, and might be less efficient for large numbers of databases.

Answer

Create a SQL Agent job on each server to run a maintenance script.

Answer

Use Azure Data Factory pipelines with a ForEach activity to execute stored procedures.

Answer

Use Azure Automation runbooks with Invoke-SqlCmd to loop through each database.

Question 3

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

Accepted Answer

Set an Microsoft Entra ID administrator for the logical server. 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.

Answer

Create a login in the master database for each Entra ID user

Answer

Assign a system-assigned managed identity to the logical server

Answer

Enable 'contained database authentication' on the server

Question 4

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?

Accepted Answer

Use Azure Database Migration Service with online migration mode.. 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.

Answer

Set up transactional replication to replicate data to SQL Managed Instance.

Answer

Perform an offline migration using Azure Database Migration Service.

Answer

Export the database as a BACPAC file and import to SQL Managed Instance.

Question 5

You are monitoring an Azure SQL Managed Instance using dynamic management views. You want to identify which queries are consuming the most IOPS. Which DMV should you query?

Accepted Answer

sys.dm_exec_query_stats. Sys.dm_exec_query_stats provides per-query execution statistics, including I/O metrics such as total_logical_reads and total_logical_writes, which can be used to identify queries consuming the most IOPS. Option A is incorrect because sys.dm_db_io_stats is not a valid DMV in Azure SQL Managed Instance and does not provide per-query I/O information. Option B is incorrect because sys.dm_os_performance_counters provides OS-level performance counters, not per-query I/O. Option D is incorrect because sys.dm_db_index_usage_stats tracks index usage statistics, not query I/O.

Answer

sys.dm_db_io_stats

Answer

sys.dm_os_performance_counters

Answer

sys.dm_db_index_usage_stats

Question 6

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

Accepted Answer

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

Answer

Dynamic Data Masking (DDM)

Answer

Transparent Data Encryption (TDE)

Question 7

You are planning a disaster recovery solution for an Azure SQL Managed Instance. The solution must meet the following requirements: provide automatic failover to a secondary region, support read-only workloads on the secondary, and allow manual failback. Which TWO features should you include? (Choose two.)

Accepted Answer

Failover group. Options C and D are correct. Failover groups provide automatic failover to a secondary region, support readable secondary workloads, and allow manual failback by initiating a failover in the opposite direction. Option A (Active geo-replication) is not supported for Azure SQL Managed Instance. Option B (Geo-restore) does not provide automatic failover. Option E (Log shipping) is not built-in for managed instances and does not meet the requirements.

Answer

Active geo-replication

Answer

Geo-restore

Answer

Log shipping

Question 8

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?

Accepted Answer

Read scale-out.. 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.

Answer

Automatic tuning.

Answer

Connection pooling.

Answer

Active geo-replication.

Question 9

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

Accepted Answer

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

Answer

The database must use the DTU purchasing model

Answer

The database must be in the Hyperscale service tier

Answer

The database must be in the General Purpose service tier

Question 10

Your company plans to migrate multiple on-premises SQL Server databases to Azure SQL Managed Instance. You need to select the appropriate migration method that minimizes downtime and supports automated schema and data validation. The source databases are running SQL Server 2019 Enterprise Edition. Which tool should you recommend?

Accepted Answer

Use Azure Database Migration Service with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Managed Instance, and it provides built-in schema and data validation to ensure consistency before cutover. This aligns with the requirement for automated validation and minimal downtime.

Answer

Use the Log Replay Service to continuously replicate transaction logs.

Answer

Set up transactional replication from on-premises to Azure SQL Managed Instance.

Answer

Export the databases as BACPAC files and import them into Azure SQL Managed Instance.

Question 11

You are optimizing an Azure SQL Database that has a heavy workload of both reads and writes. The database has a clustered columnstore index on a large fact table. You notice that the index has high fragmentation and the performance of queries against this table is degrading. What should you do to improve performance?

Accepted Answer

Rebuild the clustered columnstore index. Rebuilding the clustered columnstore index reduces fragmentation and improves query performance. Rebuilding is the recommended method to defragment a columnstore index. Option B is incorrect because increasing the service tier addresses performance issues related to IOPS but does not resolve fragmentation. Option C is incorrect because dropping and recreating the index is more disruptive and unnecessary; a rebuild achieves the same result with less overhead. Option D is incorrect because reorganize is not supported for columnstore indexes; only rebuild is.

Answer

Increase the service tier to get more IOPS

Answer

Drop and recreate the clustered columnstore index

Answer

Reorganize the clustered columnstore index

Question 12

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

Accepted Answer

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

Answer

Deny public network access

Answer

Enable contained database authentication

Answer

Allow Azure Services and resources to access this server

Question 13

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

Accepted Answer

The script uses Out-GridView, which is not supported in Azure Automation.. In Azure Automation runbooks, there is no interactive desktop environment, so cmdlets that require a graphical user interface, such as Out-GridView, are not supported. When the script runs in a runbook, Out-GridView fails silently or does not produce any output, causing the subsequent alert logic (which likely depends on the output) to never trigger. Option A is incorrect because the -OutputAs parameter is used with Invoke-SqlCmd to specify the output format, not to return results for alerting. Option C is incorrect because Invoke-SqlCmd is fully compatible with Azure SQL Database when proper authentication is used. Option D is incorrect because the script runs successfully, indicating the query syntax is valid.

Answer

The script must use the -OutputAs parameter to return results.

Answer

The Invoke-SqlCmd cmdlet is not compatible with Azure SQL Database.

Answer

The query syntax is incorrect for Azure SQL Database.

Question 14

You are a database administrator for a financial services company that uses Azure SQL Database for a critical trading application. The application connects using a service principal (Microsoft Entra application) and executes stored procedures. You need to implement the following security requirements:

1. All connections must use Microsoft Entra authentication with MFA enforced for the service principal.
2. The application should only be able to execute specific stored procedures (usp_Trade, usp_GetQuote) and no other operations.
3. All data at rest must be encrypted using customer-managed keys stored in Azure Key Vault.
4. Auditing must capture all failed login attempts and all changes to the database schema.
5. The database must be protected against SQL injection attacks from the application layer.

You have already configured Microsoft Entra authentication and enabled TDE with customer-managed key in Azure Key Vault. Which additional steps should you take to meet all remaining requirements?

Accepted Answer

Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.. It addresses all remaining requirements: (1) A database role with EXECUTE permission on the required stored procedures restricts the service principal to only executing those procedures. (2) A Conditional Access policy enforcing MFA for the service principal meets the MFA requirement. (3) Audit enabled for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP captures failed logins and schema changes. (4) Using parameterized queries prevents SQL injection. Option B is incorrect because dynamic data masking does not prevent SQL injection and does not enforce MFA. Option C is incorrect because a contained database user with EXECUTE permission does not enforce MFA, and Always Encrypted does not prevent SQL injection. Option D is incorrect because row-level security restricts data access but does not prevent SQL injection, and Microsoft Defender for SQL only detects but does not prevent SQL injection; also, no MFA enforcement is mentioned.

Answer

Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.

Answer

Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.

Question 15

You are designing a database migration from on-premises SQL Server 2019 to Azure SQL Managed Instance. The source database uses Transparent Data Encryption (TDE) with a certificate stored in the local certificate store. The target is a General Purpose SQL Managed Instance. Which migration method should you use to minimize downtime and preserve TDE?

Accepted Answer

Use Azure Database Migration Service (DMS) with online mode.. Azure Database Migration Service (DMS) with online mode is the correct choice because it supports minimal-downtime migrations by continuously replicating changes from the source SQL Server to the target Azure SQL Managed Instance using the transactional replication technology. DMS also preserves Transparent Data Encryption (TDE) by migrating the TDE certificate and key to the managed instance, ensuring the database remains encrypted throughout the process.

Answer

Use the SQL Server Import/Export Wizard to copy data.

Answer

Export the database as a BACPAC file and import it into the managed instance.

Answer

Use transaction log shipping from the source to the target.