DP-300 Practice Test 32 — 15 Questions

Question 1

You are configuring an Azure Automation runbook to perform daily maintenance tasks on an Azure SQL Database. The runbook will run on a schedule and must securely connect to the database. Which two actions should you perform to enable the runbook to authenticate to the database? (Choose two.)

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

Accepted Answer

Enable a managed identity for the Automation account. To enable an Azure Automation runbook to authenticate to Azure SQL Database securely without storing credentials, you should enable a managed identity for the Automation account and create a contained database user for that identity in the target database. These two actions allow the runbook to obtain an Microsoft Entra ID token and connect. Other options either involve secret management or grant insufficient permissions.

Answer

Store the SQL admin credentials in Azure Key Vault and retrieve them in the runbook

Answer

Configure the runbook to use a service principal with a client secret stored in an Automation variable

Answer

Assign the Automation account the Contributor role on the Azure SQL Server

Question 2

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must support cross-database queries, SQL Server Agent jobs, and Service Broker. You need to ensure that the instance can handle the expected IOPS and throughput requirements. Which configuration should you implement?

Accepted Answer

Deploy the instance in the Business Critical service tier with local SSD storage.. The Business Critical service tier in Azure SQL Managed Instance uses local SSD storage, delivering the lowest latency and highest IOPS and throughput. It also supports cross-database queries, SQL Server Agent, and Service Broker, which are required. The General Purpose tier uses remote storage and may not meet high-performance demands. In-Memory OLTP alone does not overcome storage limitations, and geo-replication is for disaster recovery, not local performance. Therefore, Business Critical with local SSD is the correct choice.

Answer

Deploy the instance in the General Purpose service tier with standard storage.

Answer

Deploy the instance in the General Purpose service tier and enable the In-Memory OLTP feature.

Answer

Deploy the instance in the Business Critical service tier and configure geo-replication for read scale-out.

Question 3

You manage an Azure SQL Database named SalesDB in the East US region. The database is in the Business Critical service tier. You need to implement a disaster recovery strategy that provides a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds during a regional outage. You configure an auto-failover group with a secondary server in the West US region. Which action should you take to meet the RPO and RTO requirements?

Accepted Answer

Configure the failover group to use automatic failover policy.. To achieve an RPO of less than 5 seconds and an RTO of less than 30 seconds during a regional outage, the auto-failover group must be configured with an automatic failover policy. This enables Azure to automatically promote the secondary to primary, minimizing downtime. Geo-replication requires manual failover and may not meet the RTO, while read-only mode and backup retention do not affect failover performance.

Answer

Enable geo-replication for SalesDB to the secondary server.

Answer

Set the secondary database to read-only mode.

Answer

Increase the backup retention period for SalesDB.

Question 4

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data in transit between the application and the database be encrypted, and they want to enforce a minimum TLS version of 1.2 at the server level. The application connects using the server's fully qualified domain name. What should you configure to meet this requirement with the least administrative effort?

Accepted Answer

Set the 'Minimum TLS version' to 1.2 in the Azure SQL server's firewall and virtual networks settings.. The requirement is to enforce a minimum TLS version for all connections to the Azure SQL server. The logical server's 'Minimum TLS version' setting directly controls the minimum TLS version accepted. Configuring this setting to 1.2 ensures that any client attempting to connect with an older TLS version is rejected, and it requires no changes to the application or additional networking components.

Answer

Configure a private endpoint for the Azure SQL server and disable public network access.

Answer

Create a server-level firewall rule that allows only the application's IP address and set 'Allow Azure services' to OFF.

Answer

Enable Transparent Data Encryption (TDE) on the database and use a customer-managed key.

Question 5

You are configuring a new Azure SQL Database. The application that will use the database requires that all connections be encrypted and that the database be protected against SQL injection attacks. You also need to minimize administrative effort for monitoring and threat detection. What should you implement?

Accepted Answer

Enforce SSL connections and enable Azure Defender for SQL (Advanced Threat Protection) with vulnerability assessment.. Enforcing SSL connections ensures encrypted communication, and Azure Defender for SQL provides advanced threat detection, including SQL injection alerts, along with vulnerability assessment. This combination directly meets the requirements for encrypted connections, SQL injection protection, and reduced monitoring effort. Other options either address different security aspects or do not provide the necessary threat detection and connection encryption.

Answer

Enable Dynamic Data Masking and configure Azure Monitor alerts for failed logins.

Answer

Configure Always Encrypted with column encryption and enable auditing to a storage account.

Answer

Enable Transparent Data Encryption (TDE) and configure a firewall rule to allow only the application's IP address.

Question 6

You are the database administrator for an Azure SQL Database named HRDB. The security team mandates that the database must be protected against SQL injection attacks and that any suspicious activity must be automatically detected and reported. You need to enable a feature that provides this protection with minimal administrative effort. What should you enable?

Accepted Answer

Advanced Threat Protection. Advanced Threat Protection continuously monitors database activities and uses machine learning to identify potential vulnerabilities and anomalous access patterns, including SQL injection. It automatically raises alerts, satisfying the need for detection and reporting. The other features provide encryption, masking, or logging but lack the automated threat detection capability.

Answer

Transparent Data Encryption (TDE)

Answer

Auditing

Answer

Dynamic Data Masking

Question 7

You manage a SQL Server 2019 Always On availability group on Azure Virtual Machines. The availability group has two synchronous replicas in an availability set and one asynchronous replica in a different Azure region. During a planned maintenance window, you need to patch the operating system on the primary replica with minimal downtime and no data loss. What should you do first?

Accepted Answer

Perform a manual failover of the availability group to the synchronous secondary replica.. To patch the primary replica with no data loss and minimal downtime, you must first move the primary role to a synchronous secondary replica using a manual failover. Because the secondary is synchronized, the failover is seamless and lossless. After the failover, the former primary becomes a secondary and can be patched while the availability group remains online and accessible through the listener.

Answer

Take the primary replica offline and patch it, then bring it back online.

Answer

Fail over to the asynchronous replica in the remote region.

Answer

Set the availability group to asynchronous commit mode and then patch the primary.

Question 8

You are a database administrator for a healthcare company that uses Azure SQL Database. You need to automate the process of exporting a BACPAC file to Azure Blob Storage every day at 3:00 AM. You want to minimize development effort and use an Azure-native service. What should you use?

Accepted Answer

Azure Automation runbook with a PowerShell script that uses the New-AzSqlDatabaseExport cmdlet.. Azure Automation runbooks can run PowerShell on a schedule, and the New-AzSqlDatabaseExport cmdlet is designed to export an Azure SQL Database to a BACPAC in Blob Storage. This combination is Azure-native, requires minimal code, and directly automates the daily export. Other options either use unsupported commands, require more development, or do not produce BACPAC files.

Answer

SQL Server Agent job on an Azure virtual machine that runs a T-SQL script using BACKUP DATABASE TO URL.

Answer

Azure Logic Apps with a recurrence trigger and a SQL Server connector to execute an export command.

Answer

Azure Data Factory pipeline with a copy activity that exports the database to Blob Storage.

Question 9

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company wants to automate the deployment of database schema changes across multiple databases in an elastic pool. You need to implement a solution that tracks which scripts have been applied and ensures they are applied only once per database. Which two actions should you perform? (Choose two.)

Accepted Answer

Define a target group in the elastic job that includes all databases in the elastic pool.. Elastic jobs with a target group that includes all databases in the pool, combined with a T-SQL step that checks a version table, provide a robust solution for one-time schema deployment. The target group ensures all databases are covered, and the version check ensures idempotency. This leverages native Azure SQL Database automation and avoids external orchestration.

Answer

Use Azure Automation runbooks to deploy the scripts to each database individually.

Answer

Configure the elastic job to run on a schedule that executes every minute to ensure quick deployment.

Answer

Use SQL Server Agent on the logical server to execute the scripts across all databases.

Question 10

You administer an Azure SQL Managed Instance that hosts a database containing regulated data. The security team requires that all data be encrypted at rest with a customer-managed key stored in Azure Key Vault, and that the key be rotated annually. You configure a key in Key Vault and set the instance's Transparent Data Encryption protector to that key. Six months later, the key approaches its expiration date. What should you do to rotate the key while keeping the instance online and encrypted?

Accepted Answer

Create a new key version in the same Key Vault key and set the instance's TDE protector to the new version.. Customer-managed TDE keys are rotated by pointing the instance's TDE protector at a new key version in Key Vault. The database encryption key is re-wrapped with the new material while the instance remains online and encrypted. Retention features such as soft delete protect against loss but do not rotate keys, and disabling TDE would create an unprotected window.

Answer

Enable the Key Vault 'soft delete' and 'purge protection' features, and the key will rotate automatically each year.

Answer

Disable Transparent Data Encryption on the instance, create a new key, then re-enable TDE with the new key.

Answer

Delete the old key from Key Vault and create a new key with the same name; the instance will automatically pick it up.

Question 11

You manage an Azure SQL Database that contains a table with sensitive columns. You need to ensure that a specific application can access the data in those columns in plaintext, while other applications see ciphertext. You also need to minimize changes to the application code. What should you implement?

Accepted Answer

Always Encrypted with column encryption keys and a column master key.. Always Encrypted is designed to protect sensitive data by allowing clients to encrypt and decrypt data transparently. With Always Encrypted, the application that possesses the column master key can decrypt the data, while others see ciphertext. The application code changes are minimal, often just connection string modifications. This solution provides the required selective access without significant code changes.

Answer

Dynamic data masking on the sensitive columns.

Answer

Always Encrypted with secure enclaves.

Answer

Transparent Data Encryption (TDE) with a customer-managed key.

Question 12

You are designing a backup strategy for an Azure SQL Database. The database is in the General Purpose service tier and is used for a critical application. You need to ensure that you can restore the database to any point in time within the last 30 days, and you need to retain backups for 10 years for compliance. (Choose two.)

Accepted Answer

Set the point-in-time restore retention policy to 30 days.. To meet the requirements, you need to set the point-in-time restore retention to 30 days and configure long-term retention (LTR) for 10 years. PITR retention can be configured up to 35 days, so 30 days is achievable. LTR allows retention up to 10 years. Backup storage redundancy and geo-replication do not affect retention periods, and SQL Server Agent is not available in Azure SQL Database. Therefore, the correct actions are configuring PITR to 30 days and LTR to 10 years.

Answer

Enable geo-replication for the database.

Answer

Configure automatic backups to a storage account using SQL Server Agent.

Answer

Configure the backup storage redundancy to locally-redundant storage (LRS).

Question 13

A developer at your company needs to run ad hoc queries against an Azure SQL Database from a workstation on the corporate network. Security policy forbids storing credentials in the application and forbids any inbound public network access to the database. The workstation already has a Microsoft Entra ID-joined identity. What should you configure to meet these requirements?

Accepted Answer

Create a contained database user mapped to the Microsoft Entra identity and connect through a private endpoint using Microsoft Entra authentication. Meeting both constraints requires an authentication method that needs no stored secret and a network path that avoids the public endpoint. A contained database user mapped to a Microsoft Entra identity lets the developer sign in with directory credentials, and a private endpoint routes traffic over the private network. Password-based logins and firewall rules that keep the public endpoint reachable fail the policy.

Answer

Enable the Allow Azure services and resources to access this server firewall rule and connect over the public endpoint

Answer

Create a SQL login with a strong password and store the password in the connection string on the workstation

Answer

Issue the developer a shared SQL login and restrict it with an IP-based firewall rule for the corporate egress address

Question 14

You are configuring security for an Azure SQL Database. The security team requires that all administrative actions on the server and databases are logged to an Azure Storage account, and that the logs are retained for 90 days. You need to configure auditing to meet these requirements with minimal effort. What should you do?

Accepted Answer

Enable auditing at the server level and set the retention period to 90 days, targeting an Azure Storage account.. Server-level auditing with a 90-day retention targeting an Azure Storage account audits all databases on the server and meets the retention requirement. Database-level auditing would require per-database configuration, and using Log Analytics does not match the storage account requirement. Unlimited retention does not meet the 90-day specification.

Answer

Enable auditing at the server level and set the retention period to 0 (unlimited), targeting an Azure Storage account.

Answer

Enable auditing at the server level and target an Azure Log Analytics workspace with a 90-day retention.

Answer

Enable auditing at the database level for each database and set the retention period to 90 days, targeting an Azure Storage account.

Question 15

You are configuring security for an Azure SQL Database. The security policy requires that all connections to the database must be encrypted and that the encryption keys must be managed by your organization. You need to implement Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault. What should you do first?

Accepted Answer

Create an Azure Key Vault and grant the Azure SQL logical server's managed identity the get, wrapKey, and unwrapKey permissions on the key.. For TDE with customer-managed keys in Azure SQL Database, the first step is to set up Azure Key Vault and grant the logical server's managed identity the required permissions to access the key. This enables the server to use the key for encryption. Once this is done, you can configure TDE to use the customer-managed key. The other options either do not meet the key management requirement or are not applicable.

Answer

Configure a firewall rule to allow connections from your organization's IP addresses.

Answer

Create a database master key (DMK) in the master database of the Azure SQL logical server.

Answer

Enable TDE on the database using the default service-managed key.