DP-300 Practice Exam 2 — 20 Questions

Question 1

Which THREE components are required to set up elastic jobs in Azure SQL Database?

Accepted Answer

Target groups. Target groups (C) are required because they define the set of databases (or elastic pools) against which an elastic job will execute. Without target groups, the job agent has no scope of execution. The elastic job agent (D) orchestrates job scheduling and execution, and job credentials (E) are needed to authenticate to the target databases for running T-SQL scripts.

Answer

Azure Automation account

Answer

SQL Server Agent

Question 2

Match each Azure SQL Database security feature to its purpose.

Accepted Answer

Transparent Data Encryption (TDE): Encrypts data at rest. TDE protects data at rest by encrypting the database files. Always Encrypted protects data in use by encrypting on the client side. Dynamic Data Masking hides sensitive data in query results from unauthorized users. Common confusions involve mixing up TDE and Always Encrypted.

Answer

Transparent Data Encryption (TDE): Encrypts data in use on client side

Answer

Always Encrypted: Encrypts data at rest

Question 3

A junior developer at your company connects to an Azure SQL Database using the SQL login 'appuser'. You need to grant 'appuser' the ability to read from a table named dbo.Orders in the Sales schema, but nothing else in the database. You also want to follow the principle of least privilege. What should you do?

Accepted Answer

Grant SELECT on the dbo.Orders table to 'appuser'.. The most granular permission that satisfies the requirement is a table-level GRANT SELECT on dbo.Orders. It gives the developer exactly the read access needed while leaving all other objects inaccessible. Role memberships such as db_datareader and db_owner, and schema-level CONTROL, all grant broader rights than the scenario allows.

Answer

Add 'appuser' to the db_datareader role.

Answer

Add 'appuser' to the db_owner role.

Answer

Grant CONTROL on the Sales schema to 'appuser'.

Question 4

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct choice because it encrypts sensitive data both at rest in the database and in transit between the application and the database. It ensures that encryption keys are never revealed to the database engine, so data remains encrypted throughout the entire data path, including during query execution. This meets the requirement for encryption at rest and in transit.

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 5

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

Accepted Answer

Create a metric alert in Azure Monitor. Azure Monitor metric alerts evaluate platform metrics such as DTU percentage over a defined time window and fire notifications via action groups. To alert when DTU consumption exceeds 80% for more than 10 minutes, you create a metric alert on the 'DTU percentage' metric with a threshold of 80 and an aggregation granularity/window of 10 minutes. This is the native, supported mechanism for Azure SQL Database resource alerts.

Answer

Configure a SQL Agent alert

Answer

Configure Azure SQL Auditing

Answer

Use Azure Advisor recommendations

Question 6

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

Accepted Answer

Set the 'minimal TLS version' to 1.2 in the server's properties in the Azure portal.. To enforce TLS 1.2 or higher for all connections to an Azure SQL Database, you must configure the 'minimal TLS version' setting at the server level in the Azure portal. This setting applies to all databases hosted on that logical server, ensuring that any client attempting to connect with a TLS version lower than 1.2 is rejected. The server-level property directly controls the TLS protocol version accepted during the SSL/TLS handshake, overriding any client-side or database-level settings.

Answer

Set the 'minimal TLS version' to 1.2 in the database's properties.

Answer

Add a firewall rule to deny connections using TLS 1.0 or 1.1.

Answer

Enable the 'Force encryption' option in the connection string and require TLS 1.2.

Question 7

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

Accepted Answer

Azure SQL Database long-term retention (LTR) backup policy. Only Azure SQL Database long-term retention (LTR) backup policy provides automated long-term backup retention for Azure SQL Database (PaaS), allowing retention beyond the default 7-35 days up to 10 years. Option C (Azure Backup for SQL Server in Azure VMs) is for SQL Server on Azure VMs (IaaS), not for Azure SQL Database, so it does not apply. The other options (A, B, D) do not provide automated backup with long-term retention for Azure SQL Database.

Answer

Azure Site Recovery

Answer

Azure Blob Storage snapshots

Answer

Azure Backup for SQL Server in Azure VMs

Answer

Azure Storage lifecycle management

Question 8

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

Accepted Answer

Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data, such as SSNs, is encrypted both at rest and in transit, and the encryption keys are stored client-side (e.g., in Azure Key Vault). This design ensures that only authorized client applications with access to the column master key can decrypt the data, preventing even database administrators or cloud operators from viewing the plaintext values.

Answer

Row-level security (RLS) to restrict access based on user role.

Answer

Dynamic data masking (DDM) to mask SSNs for unauthorized users.

Answer

Transparent Data Encryption (TDE) with customer-managed keys.

Question 9

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Accepted Answer

Enable Microsoft Defender for SQL to detect and alert on SQL injection.. Option A is correct because Microsoft Defender for SQL provides advanced threat protection that specifically detects anomalous and potentially harmful activities such as SQL injection attempts against Azure SQL Database, generating alerts and recommendations. Option B is correct because parameterized queries and stored procedures ensure that user input is treated as data rather than executable SQL code, which is the fundamental application-level defense against SQL injection. Option D is correct because an Azure Web Application Firewall (WAF), for example on Application Gateway or Front Door, inspects HTTP/HTTPS traffic and blocks common injection patterns before they reach the application and database. Option C is not correct because dynamic data masking only obscures sensitive data in query results for unauthorized users; it does not prevent or detect SQL injection. Option E is not correct because Transparent Data Encryption protects data at rest by encrypting database files, but it does not stop SQL injection, which exploits the application's query logic rather than the storage layer.

Answer

Implement dynamic data masking to hide sensitive data from unauthorized users.

Answer

Enable Transparent Data Encryption (TDE) to encrypt the database at rest.

Question 10

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

Accepted Answer

Set the 'Force Encryption' option to Yes on the server properties.. Setting 'Force Encryption' to Yes on the Azure SQL Managed Instance server properties enforces the use of TLS (Transport Layer Security) for all client connections. This configuration ensures that any client attempting to connect without encryption will be rejected, thereby meeting the requirement that all connections use encrypted connections. The setting is applied at the instance level and overrides client-side encryption preferences.

Answer

Enable Transparent Data Encryption (TDE).

Answer

Enable Always Encrypted for sensitive columns.

Answer

Configure a firewall rule to allow only specific IP addresses.

Question 11

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Accepted Answer

Connect to the instance, create a job, define steps, schedule, then enable and start.. Connect to the instance, create a job, define steps, schedule, then enable and start.

Answer

Create a job, define steps, schedule, connect to the instance, then enable and start.

Answer

Connect to the instance, define steps, create a job, schedule, then enable and start.

Answer

Connect to the instance, create a job, schedule, define steps, then enable and start.

Question 12

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Accepted Answer

sys.dm_exec_query_stats. sys.dm_exec_query_stats (Option C) is the correct DMV because it returns aggregate performance statistics for cached query plans, including total CPU time (total_worker_time), execution count, and last execution time. By querying this view and ordering by total_worker_time descending, you can identify the queries that have consumed the most CPU over the last 24 hours, directly addressing the pattern of high CPU usage during business hours.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_sessions

Answer

sys.dm_os_performance_counters

Question 13

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: 
- Minimal application changes. 
- Support for SQL Server Agent jobs. 
- Ability to scale storage independently from compute. 
- Native support for cross-database queries. 
Which TWO options meet these requirements? (Choose two.)

Accepted Answer

SQL Server on Azure Virtual Machine. SQL Server on Azure Virtual Machine (D) and Azure SQL Managed Instance (E) provide full SQL Server engine compatibility, native SQL Server Agent, cross-database queries, independent storage scaling, and minimal application changes. Azure SQL Database elastic pool (B) does not support SQL Server Agent natively (Azure Elastic Jobs is not SQL Server Agent) and its cross-database query support via elastic query is not as seamless as native; thus it does not meet all requirements. Option A (single database) lacks SQL Server Agent and cross-database queries. Option C (Azure Synapse) is not designed for OLTP and lacks SQL Server Agent and cross-database query support. Therefore, the two correct options are D and E.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

Azure Synapse Analytics (dedicated SQL pool)

Question 14

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Accepted Answer

Create an Azure Automation runbook that uses the Export-AzureRmSqlDatabase cmdlet and schedule it to run nightly.. Azure Automation runbooks can execute PowerShell cmdlets like Export-AzureRmSqlDatabase (or the newer Export-AzSqlDatabase) to perform a consistent export of an Azure SQL Database to a storage account. By scheduling the runbook to run nightly, you automate the export with minimal administrative overhead, as the export operation uses database snapshots to ensure consistency without requiring complex orchestration.

Answer

Create an Elastic Database Job that runs a T-SQL script to export the database.

Answer

Deploy an Azure Data Factory pipeline with a Copy activity to export the database.

Answer

Use an Azure Logic App with the SQL Server connector to export the database.

Question 15

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

Accepted Answer

The queries are CPU-bound due to inefficient query plans.. High worker time (CPU time) with low logical reads indicates that the queries are CPU-bound rather than I/O-bound. Inefficient query plans, such as those with large hash joins, sorts, or non-sargable predicates, can cause excessive CPU consumption without generating many logical reads. Option A is incorrect because parameter sniffing typically leads to varying plan quality, but the consistent high worker time across multiple queries suggests a systematic plan efficiency issue, not necessarily parameter sniffing. Option B is incorrect because memory pressure would cause increased I/O activity (lazy writes) which is not observed with low logical reads. Option C is incorrect because transaction log writes are I/O operations and would not cause high worker time with low I/O.

Answer

The queries suffer from parameter sniffing leading to suboptimal plans.

Answer

The queries are experiencing memory pressure causing excessive lazy writes.

Answer

The queries are waiting on transaction log writes.

Question 16

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

Accepted Answer

Application-consistent snapshots are not enabled in the ASR replication policy.. Azure Site Recovery replicates VM disks, and by default it takes crash-consistent snapshots, which capture the disk state as if the power were cut — the database may have in-flight transactions and be inconsistent on recovery. Enabling application-consistent snapshots in the replication policy triggers VSS (Volume Shadow Copy Service) to quiesce the database and flush transactions before snapshotting, ensuring a consistent recovery point.

Answer

The secondary region is not in the same geo as the primary.

Answer

The backup retention period is too short.

Answer

The database is not part of an Always On availability group.

Question 17

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

Accepted Answer

Create an Azure Policy initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assign it to the root management group.. Azure Policy provides a scalable, automated, and continuous enforcement mechanism across all subscriptions. By creating an initiative that includes the 'Configure Microsoft Defender for SQL to be enabled' policy and assigning it to the root management group, you ensure that any new or existing subscription inherits the policy, and non-compliant resources are automatically remediated or flagged. This approach aligns with the requirement for a strict, company-wide policy without manual intervention.

Answer

Use Azure Blueprints to deploy a predefined ARM template that enables Defender for SQL.

Answer

Create a script that runs periodically to check and enable Defender for SQL on all databases.

Answer

Assign the 'SQL Security Manager' role to a central team to manually enable Defender for SQL.

Question 18

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

Accepted Answer

The database is in General Purpose tier and supports zone redundancy.. The JSON configuration shows zoneRedundant set to true and the service tier is General Purpose. As of current Azure SQL Database documentation, zone redundancy is supported for the General Purpose tier (vCore purchasing model) in many regions. Therefore, the configuration is valid. Option D correctly states that the database is in General Purpose tier and supports zone redundancy. Option A is incorrect because highAvailabilityReplicaCount is 0, not 1. Option B is incorrect because zone redundancy is available for General Purpose. Option C is incorrect because backupStorageRedundancy is Geo (geo-redundant), not locally-redundant.

Answer

The database has one high-availability replica.

Answer

The configuration is invalid because General Purpose tier cannot be zone-redundant.

Answer

The database uses locally-redundant backup storage.

Question 19

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?

Accepted Answer

Create an auto-failover group with a secondary replica in West US.. An auto-failover group in Azure SQL Managed Instance provides a secondary replica in a paired region with automatic failover, meeting the requirement for recovery within 1 hour. It uses asynchronous replication and allows a single connection string to fail over automatically, ensuring business continuity during a regional disaster.

Answer

Configure log shipping to a secondary instance in West US.

Answer

Enable geo-redundant backup storage and use geo-restore.

Answer

Deploy an Always On availability group with a synchronous secondary in West US.

Question 20

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?

Accepted Answer

Configure per-database min eDTU for the critical database. In an Azure SQL elastic pool, per-database min eDTU (or min vCore) guarantees a floor of resources that a specific database can always consume, even when other databases in the pool are competing for resources. Setting a min eDTU on the critical database ensures it is never starved during contention. This directly addresses the requirement that the critical database 'always gets a minimum amount of resources.'

Answer

Configure per-database max eDTU for the critical database

Answer

Increase the eDTU of the elastic pool

Answer

Move the critical database to a dedicated service tier