DP-300 Practice Test 38 — 15 Questions

Question 1

You administer an Azure SQL Managed Instance that hosts a mission-critical database. You need to configure an automated task that will execute a T-SQL script to perform a full backup of the database to a URL every night at midnight. The solution must use built-in Azure capabilities and minimize cost. What should you use?

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

Accepted Answer

SQL Server Agent job on the managed instance. Azure SQL Managed Instance includes SQL Server Agent, which allows you to create and schedule T-SQL jobs. For a nightly full backup to URL, a SQL Server Agent job with a T-SQL step is the most straightforward and cost-effective solution, leveraging existing functionality without extra services.

Answer

Azure Automation runbook with a schedule

Answer

Azure Logic Apps with a recurrence trigger

Answer

Elastic Database Jobs

Question 2

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that your organization manages and can revoke. The key must be stored in Azure Key Vault and rotated regularly. You also need to ensure that the database remains accessible during key rotation. What should you do?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault and configure the key version to be auto-rotated.. Transparent Data Encryption with a customer-managed key in Azure Key Vault allows the organization to own, manage, and revoke the encryption key. Configuring auto-rotation ensures that the key is rotated regularly without manual intervention, and the database remains accessible because Azure SQL Database automatically switches to the new key version. This satisfies all the stated requirements.

Answer

Enable Transparent Data Encryption (TDE) with a service-managed key and configure automatic key rotation.

Answer

Enable Always Encrypted with a column master key stored in Azure Key Vault and configure key rotation.

Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault and manually update the key version in the database configuration after each rotation.

Question 3

You are designing a disaster recovery strategy for a group of Azure SQL Databases hosted on a logical server in the East US region. The solution must provide automatic failover to a secondary region, support read-only access to the secondary during normal operations, and minimize application connection string changes during failover. You plan to use an auto-failover group. Which two actions should you perform? (Choose two.)

Accepted Answer

Create a secondary logical server in a different Azure region and add the databases to the failover group.. To implement an auto-failover group, you must create a secondary server in another region and add the databases to the failover group. You should also configure the read-write and read-only listener endpoints to provide stable connection strings and read-only access to the secondary. Zone redundancy is for intra-region HA, active geo-replication is redundant, and manual failover policy contradicts the automatic failover requirement.

Answer

Configure each database with active geo-replication to the secondary server.

Answer

Set the failover policy to manual to avoid unexpected failovers.

Answer

Enable zone redundancy on the primary databases.

Question 4

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires that a stored procedure, which archives old shipment records, runs every night at 2:00 AM UTC. You need to configure a solution that minimizes administrative overhead and uses built-in Azure SQL Database capabilities. What should you do?

Accepted Answer

Create an elastic job agent and define a recurring job with a T-SQL step that executes the stored procedure.. Elastic jobs are the native scheduling mechanism in Azure SQL Database for running T-SQL on a recurring basis. They eliminate the need for external services or additional infrastructure, directly satisfying the requirement to minimize administrative overhead. The other options either rely on features not available in Azure SQL Database or introduce unnecessary complexity.

Answer

Use Azure Automation with a runbook that connects to the database and executes the stored procedure.

Answer

Configure a SQL Server Agent job on the logical server that hosts the database.

Answer

Create a SQL Server Integration Services (SSIS) package and schedule it with Azure Data Factory.

Question 5

You are the Azure SQL Database administrator for a healthcare company. A new compliance requirement mandates that all data at rest in Azure SQL Database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that you can revoke access to the key at any time. The database is currently encrypted with the default service-managed key. What should you do first to meet this requirement?

Accepted Answer

Create an Azure Key Vault, generate a key, and configure the SQL server's Transparent Data Encryption (TDE) protector to use that key.. The requirement is for encryption at rest with a customer-managed key that can be revoked. Azure SQL Database TDE supports customer-managed keys stored in Azure Key Vault. The first step is to create the Key Vault and key, then configure the server's TDE protector to use it. This ensures the database encryption key is protected by the customer key, and revoking access in Key Vault effectively blocks decryption.

Answer

Enable dynamic data masking on all columns containing sensitive data.

Answer

Enable Always Encrypted on all sensitive columns using a column master key stored in Azure Key Vault.

Answer

Configure an Azure SQL Database auditing policy to send logs to a storage account encrypted with a customer-managed key.

Question 6

You are automating index maintenance for an Azure SQL Database using an Azure Automation runbook. The runbook connects to the database and executes T-SQL to rebuild fragmented indexes. You need to ensure the runbook can authenticate without storing credentials in the script. What should you configure?

Accepted Answer

A managed identity for the Automation account with a contained database user in the target database. The most secure and streamlined method for an Azure Automation runbook to authenticate to Azure SQL Database without storing credentials is to use a managed identity. The Automation account's managed identity is registered in Microsoft Entra ID, and you create a contained database user in the target database for that identity. The runbook then connects using Microsoft Entra ID authentication, eliminating secrets. This is the recommended practice for automation.

Answer

A service principal with a client secret stored in the Automation account certificates

Answer

A SQL login with a strong password stored in an Automation variable

Answer

Azure Key Vault to store the SQL admin credentials, retrieved by the runbook at runtime

Question 7

You are deploying an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server database. The application requires the ability to restore a database from a backup taken on an on-premises SQL Server 2019 instance. The backup file is stored in an Azure Storage account. You need to restore the database to the managed instance. What should you do first?

Accepted Answer

Use the RESTORE DATABASE statement with the FROM URL clause pointing to the backup file in Azure Storage.. Azure SQL Managed Instance supports the RESTORE DATABASE statement with the FROM URL clause, allowing you to restore a native SQL Server backup stored in Azure Blob Storage. This is the correct first step to restore the on-premises backup. Other tools like SqlPackage or DMA do not support native backup restoration.

Answer

Import the backup file into an Azure SQL Database using the SqlPackage utility.

Answer

Configure Azure Backup to back up the on-premises SQL Server and restore directly to the managed instance.

Answer

Use the Data Migration Assistant to migrate the database schema and data to the managed instance.

Question 8

You are the database administrator for an Azure SQL Database that contains several tables with columns that store personally identifiable information (PII). The security team requires that these columns be identified and labeled as 'Confidential' in the database. You need to implement a solution that automatically classifies these columns based on their names and data patterns. What should you use?

Accepted Answer

SQL Data Discovery and Classification.. SQL Data Discovery and Classification is the built-in feature in Azure SQL Database that scans for potentially sensitive columns, such as those containing PII, and allows you to apply classification labels like 'Confidential'. It uses pattern matching and column names to provide recommendations, which can be reviewed and applied. This directly meets the requirement to identify and label PII columns.

Answer

Always Encrypted.

Answer

Transparent Data Encryption (TDE).

Answer

Dynamic Data Masking (DDM).

Question 9

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to create an alert that fires when the database's CPU usage exceeds 90% for 10 minutes. Which metric should you use?

Accepted Answer

cpu_percent. Azure SQL Database exposes several Azure Monitor metrics, including cpu_percent, which directly measures CPU usage as a percentage. To alert when CPU exceeds 90% for 10 minutes, you create an alert rule on the cpu_percent metric with a threshold of 90 and an aggregation granularity of 10 minutes. Other metrics like dtu_consumption_percent or log_write_percent measure different resources and would not satisfy the specific CPU monitoring requirement.

Answer

dtu_consumption_percent

Answer

physical_data_read_percent

Answer

log_write_percent

Question 10

You administer an Azure SQL Database that must remain available if the primary region becomes unavailable. The business requires a secondary region with read-only access for reporting, and during a failover the application connection string must not change. You configure an auto-failover group. Which feature of the auto-failover group satisfies the requirement that the application connection string remains unchanged after failover?

Accepted Answer

The read-write listener endpoint of the failover group.. An auto-failover group creates listener endpoints with stable DNS names. The read-write listener always resolves to the current primary, so applications using that listener do not need to change their connection strings after a geo-failover. Firewall rules, backup retention, and TDE protectors are important for security and recoverability but do not provide a stable connection endpoint.

Answer

The geo-secondary database's server-level firewall rules.

Answer

The transparent data encryption (TDE) protector stored in Azure Key Vault.

Answer

The automatic backup retention policy of the primary database.

Question 11

You manage an Azure SQL Database named OrderDB in the Business Critical service tier. A compliance requirement mandates that the database must remain available even if an entire Azure availability zone fails within the primary region. You need to configure the database to meet this requirement with the least administrative effort. What should you do?

Accepted Answer

Enable zone redundancy for the database.. Zone redundancy in the Business Critical tier replicates the database across multiple availability zones, ensuring automatic failover and continuous availability during a zone outage. It is the simplest configuration change that meets the requirement without application modifications or manual intervention.

Answer

Create a failover group with an automatic failover policy.

Answer

Increase the database compute size to the maximum vCore limit.

Answer

Configure active geo-replication to a secondary region.

Question 12

You are deploying SQL Server on an Azure Virtual Machine. You need to configure a high availability solution that provides automatic failover and does not require a shared storage solution. The solution must support multiple databases and allow for readable secondary replicas. What should you implement?

Accepted Answer

Implement an Always On availability group with multiple replicas.. Always On availability groups provide automatic failover without shared storage and support readable secondary replicas. They can protect multiple databases and are the standard high availability solution for SQL Server on Azure VMs. Failover cluster instances require shared storage, while log shipping and Azure Site Recovery do not provide automatic failover or readable secondaries.

Answer

Configure a failover cluster instance (FCI) with a premium file share.

Answer

Use Azure Site Recovery to replicate the VM to another region.

Answer

Configure log shipping to a secondary SQL Server instance.

Question 13

You are managing an Azure SQL Database that has automatic tuning enabled. You notice that a recent index creation recommended by automatic tuning has caused a performance regression for some queries. You need to revert the change and prevent automatic tuning from applying similar recommendations in the future. What should you do?

Accepted Answer

Use the automatic tuning option to revert the last change and then disable the CREATE INDEX tuning option.. Automatic tuning in Azure SQL Database allows you to revert individual tuning actions. To address the regression, you should revert the index creation and then disable the CREATE INDEX tuning option to prevent future automatic index creation. Disabling all automatic tuning or using manual workarounds like DDL triggers are less precise and can have unintended side effects.

Answer

Disable automatic tuning on the database.

Answer

Manually drop the index and create a database-level DDL trigger to block index creation.

Answer

Set the automatic tuning option to inherit from the server and disable it at the server level.

Question 14

You are administering an Azure SQL Managed Instance that hosts a busy OLTP database. Users report that during peak hours, queries that typically run in milliseconds now take seconds. You suspect that the issue is related to tempdb contention. Which action should you take to resolve the tempdb contention?

Accepted Answer

Increase the number of tempdb data files.. Tempdb contention in Azure SQL Managed Instance is commonly caused by allocation bottlenecks when many sessions create temporary objects. Adding more tempdb data files spreads the allocation metadata across multiple files, reducing contention. Other actions like moving to faster storage or changing isolation levels do not address the root cause of allocation contention.

Answer

Move tempdb to a faster storage tier.

Answer

Set the database compatibility level to the latest version.

Answer

Enable Read Committed Snapshot Isolation (RCSI).

Question 15

You manage an Azure SQL Database server that hosts multiple databases. The security policy requires that all connections to the server use a minimum TLS version of 1.2 and that the setting applies to all databases on the server. What should you configure?

Accepted Answer

Set the Minimum TLS version to 1.2 in the server's networking properties in the Azure portal.. The minimum TLS version is a server-level networking setting in Azure SQL Database. Configuring it to 1.2 in the server's networking properties ensures that all connections to all databases on that server must use TLS 1.2 or higher. Firewall rules, connection policies, and client-side settings do not enforce the minimum TLS version server-wide.

Answer

Set the Minimum TLS version to 1.2 in the server's Transact-SQL firewall settings.

Answer

Enable 'Enforce SSL connection' on the server and set the client driver to TLS 1.2.

Answer

Configure the database's connection policy to 'Proxy' and enable TLS 1.2 in the connection string.