DP-300 Practice Test 39 — 15 Questions

Question 1

You administer an Azure SQL Database named HRDB. The security team requires that all data at rest be encrypted with a customer-managed key stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You create the Key Vault and grant the logical server's managed identity the necessary permissions. What should you do next to meet the requirement?

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

Accepted Answer

Configure TDE on the logical server to use a customer-managed key from Azure Key Vault and enable auto-rotation.. The requirement is for encryption at rest using a customer-managed key in Azure Key Vault with automatic rotation. TDE with a customer-managed key (BYOK) is the Azure SQL feature that provides this. Configuring TDE to use a Key Vault key and enabling auto-rotation ensures the key is rotated every 90 days without manual intervention, satisfying the security team's policy.

Answer

Enable Transparent Data Encryption (TDE) with a service-managed key on the logical server.

Answer

Enable Dynamic Data Masking on all sensitive columns.

Answer

Enable Always Encrypted on all columns containing sensitive data.

Question 2

You are responsible for an Azure SQL Managed Instance that hosts a critical database. You need to configure alerts to notify the operations team when the average CPU usage of the instance exceeds 80% for 10 minutes. You want to use the built-in monitoring capabilities of Azure. What should you create?

Accepted Answer

An Azure Monitor alert rule based on the CPU percentage metric.. Azure Monitor metric alerts are the built-in mechanism for alerting on platform metrics like CPU percentage. You can specify the aggregation (average), the threshold (80%), and the evaluation period (10 minutes). This meets the requirement efficiently. The other options involve custom scripting or log-based approaches that are not necessary for a simple metric threshold alert.

Answer

A SQL Server Agent job that queries sys.dm_os_performance_counters and sends an email.

Answer

A Log Analytics workspace query with a scheduled alert.

Answer

An Azure Automation runbook that runs a T-SQL query and sends a notification.

Question 3

You need to automate the deployment of an Azure SQL Database and its schema updates as part of a CI/CD pipeline. The pipeline must apply T-SQL scripts to the database after deployment. Which Azure DevOps task should you use to execute the T-SQL scripts against Azure SQL Database?

Accepted Answer

Azure SQL Database deployment task. The Azure SQL Database deployment task is built for executing T-SQL scripts against Azure SQL Database in a pipeline. It supports both inline scripts and script files, and it handles authentication via service connections. This makes it the most efficient and reliable choice for applying schema changes in CI/CD.

Answer

Azure PowerShell task

Answer

Command line task

Answer

Azure CLI task

Question 4

You are planning to migrate an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration supports these features with minimal changes. What should you do first?

Accepted Answer

Run Data Migration Assistant (DMA) to assess the database for compatibility issues with Azure SQL Managed Instance.. Data Migration Assistant (DMA) is the recommended tool to assess on-premises SQL Server databases for compatibility with Azure SQL Managed Instance. It identifies unsupported features such as certain cross-database queries and SQL Server Agent job steps, allowing you to plan remediation. Azure DMS is for migration, transactional replication is an alternative but not an assessment, and direct restore does not evaluate compatibility.

Answer

Deploy an Azure SQL Managed Instance and restore a backup of the on-premises database directly.

Answer

Use the Azure Database Migration Service (DMS) to perform an online migration without prior assessment.

Answer

Configure transactional replication from the on-premises SQL Server to Azure SQL Managed Instance.

Question 5

You manage a business-critical Azure SQL Database named OrdersDB in the Business Critical service tier. The database is in the East US region. The company requires a secondary readable copy in West US that provides a recovery point objective (RPO) of 5 seconds and a recovery time objective (RTO) of 30 seconds during a regional outage. You need to implement the solution with the least administrative effort. What should you do?

Accepted Answer

Create a failover group with the West US server as the secondary and configure automatic failover policy.. A failover group with automatic failover policy provides the needed readable secondary, automatic failover, and stable endpoints. For Business Critical, replication is synchronous within the region and asynchronous across regions with an RPO typically under 5 seconds. Zone redundancy and long-term retention do not address cross-region DR, and active geo-replication requires manual failover or custom automation.

Answer

Enable zone redundancy for the database and rely on Azure to fail over to a secondary zone.

Answer

Configure a long-term retention policy and restore the database to a West US server during an outage.

Answer

Configure active geo-replication to a West US server and create an Azure Automation runbook to fail over.

Question 6

You have an Azure SQL Managed Instance named MI1 in the East US region. The company requires a disaster recovery solution that provides a readable secondary in the West US region and automatic failover. You need to configure the solution with the least administrative effort. What should you do?

Accepted Answer

Create a failover group with MI1 as the primary and a secondary managed instance in West US.. Failover groups are the only option that provides automatic failover and a readable secondary for Azure SQL Managed Instance. Active geo-replication is not supported for Managed Instance, transactional replication requires manual failover, and Azure Site Recovery is for VMs. Failover groups also provide stable listener endpoints, reducing administrative effort.

Answer

Use Azure Site Recovery to replicate MI1 to West US.

Answer

Configure active geo-replication from MI1 to a managed instance in West US.

Answer

Set up transactional replication from MI1 to a managed instance in West US.

Question 7

You are deploying an Azure SQL Database for a new line-of-business application. The application's usage pattern is unpredictable, with long idle periods and occasional bursts of heavy read/write activity. You need to minimize compute cost while ensuring the database automatically scales compute resources based on workload demand. The database must remain online during scaling operations. What should you do?

Accepted Answer

Configure the database with the General Purpose service tier and the Serverless compute tier, setting an appropriate auto-pause delay.. The Serverless compute tier in Azure SQL Database is designed for intermittent, unpredictable workloads. It automatically scales compute based on demand and can pause during inactivity, reducing cost. Configuring an appropriate auto-pause delay ensures the database pauses when idle and resumes on the next connection, keeping the database available during scaling without manual intervention.

Answer

Configure the database with the Basic service tier and set a maximum database size of 2 GB.

Answer

Configure the database with the Hyperscale service tier and enable read-scale out.

Answer

Configure the database with the Business Critical service tier and enable zone redundancy.

Question 8

You manage an Azure SQL Database that contains a table with a column named 'CreditCardNumber' that stores sensitive data. You need to ensure that the data in this column is encrypted at rest and in use, and that only specific application users can decrypt it. You also need to minimize performance impact on queries that do not access this column. What should you implement?

Accepted Answer

Always Encrypted with secure enclaves and column master key stored in Azure Key Vault.. Always Encrypted with secure enclaves is designed to protect sensitive data both at rest and in use. It uses column encryption keys and a column master key stored in a key store such as Azure Key Vault. Only clients with access to the column master key can decrypt data. Secure enclaves allow computations on encrypted data without exposing it to the database engine. This approach meets the need for granular access control and minimal performance impact on unrelated queries.

Answer

Dynamic Data Masking on the 'CreditCardNumber' column.

Answer

Row-Level Security (RLS) with a security policy that filters rows based on user identity.

Answer

Transparent Data Encryption (TDE) with a customer-managed key in Azure Key Vault.

Question 9

You are a database administrator for a logistics company that uses Azure SQL Database. You need to automate the process of copying data from an on-premises SQL Server to Azure SQL Database every night. The data volume is large, and you want to minimize the impact on the source server. You also need to ensure that the copy operation is resilient to transient failures. What should you use?

Accepted Answer

Azure Data Factory with a self-hosted integration runtime and a copy activity.. Azure Data Factory with a self-hosted integration runtime is the best solution. It can connect to on-premises SQL Server, perform parallel data extraction to reduce source impact, and has built-in retry and resilience features. It is designed for scheduled, large-scale data movement. The other options either lack the necessary connectivity, are not optimized for performance, or do not provide the required resilience.

Answer

Azure Elastic Jobs with a T-SQL script that uses OPENROWSET to read from the on-premises server.

Answer

Azure Automation runbook that uses the Invoke-Sqlcmd cmdlet to copy data.

Answer

SQL Server Integration Services (SSIS) with an Azure-SSIS integration runtime.

Question 10

You manage an Azure SQL Database that is part of a business-critical application. You need to configure an alert that triggers when the database's CPU usage exceeds 80% for 10 minutes. The alert must notify an operations team via email. You want to minimize administrative effort. What should you do?

Accepted Answer

Create an alert rule in Azure Monitor with a metric signal for CPU percentage and an action group that sends email.. Azure Monitor is the native monitoring solution for Azure SQL Database. It allows you to create metric alerts on CPU percentage with a threshold and time window, and action groups can send emails. This requires no custom code and is the least administrative effort. SQL Agent is unavailable, automatic tuning is not for alerting, and Query Store lacks alerting features.

Answer

Enable automatic tuning and configure the FORCE_LAST_GOOD_PLAN option.

Answer

Create a SQL Agent job that queries sys.dm_db_resource_stats and sends an email if CPU exceeds 80%.

Answer

Use Query Store to create a custom alert when CPU usage exceeds 80%.

Question 11

You are a database administrator for a financial services company that uses Azure SQL Database. The company must ensure that all database backups are encrypted with a customer-managed key stored in Azure Key Vault. You need to configure the database to meet this requirement. What should you do first?

Accepted Answer

Create an Azure Key Vault and generate or import a key.. To use a customer-managed key for TDE, the key must exist in Azure Key Vault and the Azure SQL logical server must be granted permissions to access it. Creating the key vault and key is the necessary first step before enabling TDE with that key. Other options are security features but do not directly enable customer-managed key encryption.

Answer

Enable transparent data encryption (TDE) with a service-managed key.

Answer

Configure Azure SQL Database auditing to log all access to the database.

Answer

Enable Advanced Threat Protection on the Azure SQL server.

Question 12

You manage an Azure SQL Database. A security review finds that an application service principal is connecting with a SQL login that has db_owner membership, and that the login's password has not changed in two years. You must reduce the standing privilege and eliminate the long-lived password while keeping the application working. What should you do?

Accepted Answer

Create a contained database user mapped to the service principal's Microsoft Entra identity, grant it only the required permissions, and remove the SQL login. Replacing the password-based SQL login with a contained database user mapped to the service principal removes the long-lived secret and lets you grant only the permissions the application actually needs. Rotating a password or renaming the role leaves both problems intact, and merely enabling directory authentication without removing the login does not close the insecure path.

Answer

Rotate the SQL login password and move it into Azure Key Vault, leaving db_owner membership unchanged

Answer

Enable Microsoft Entra authentication on the server and keep the SQL login as a fallback for the application

Answer

Add the service principal to a custom database role with db_owner and enforce a password policy on the login

Question 13

You are tuning an Azure SQL Database that uses the General Purpose service tier. You notice that a specific query has a high average CPU time but a low average elapsed time. Query Store shows that the query plan uses a Hash Match (Aggregate) operator. You need to reduce the CPU consumption of this query. What should you do?

Accepted Answer

Rewrite the query to reduce the number of rows processed before aggregation.. A Hash Match (Aggregate) operator builds a hash table of group values and is CPU-intensive when processing many rows. The most effective way to reduce its CPU cost is to reduce the number of rows that reach the aggregate. This can be done by filtering earlier, pre-aggregating, or using indexed views. While indexes and statistics can influence plan choice, they do not directly reduce the CPU work of the hash aggregate if the row count remains high.

Answer

Update statistics on the involved tables.

Answer

Force a plan that uses a Stream Aggregate operator.

Answer

Create a covering index for the query's join and filter columns.

Question 14

You are deploying an Azure SQL Database and need to enforce that all connections to the database use encrypted channels and that the server presents a specific certificate that the client validates. You also need to ensure that the database cannot be accessed from the public internet except through a private endpoint. Which two actions should you perform? (Choose two.)

Accepted Answer

Set the Encrypt connection setting to mandatory in the connection string and use TrustServerCertificate=False with a trusted root certificate.. A private endpoint with public network access disabled ensures the logical server is reachable only through a private IP address inside a virtual network, removing exposure to the public internet. Requiring encryption in the connection string and setting TrustServerCertificate to false forces TLS and makes the client validate the server certificate against a trusted root. Together these actions satisfy both the private connectivity and certificate-validation requirements.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault.

Answer

Enable Always Encrypted with secure enclaves for the columns that contain sensitive data.

Answer

Configure an Azure SQL Database firewall rule that allows only the application's public IP address.

Question 15

You are responsible for an Azure SQL Database that hosts a reporting workload. The database runs a large number of ad-hoc queries that consume significant CPU. You need to identify the top CPU-consuming queries to optimize them. Which feature should you use?

Accepted Answer

Query Store. Query Store is designed to capture and persist query performance data, including CPU time. It provides built-in reports to identify top resource consumers, making it the best choice for finding high-CPU queries. Other features like auditing or automatic tuning do not offer the same level of detailed query-level CPU analysis.

Answer

Automatic tuning

Answer

Azure SQL Auditing

Answer

Dynamic management views (DMVs)