DP-300 Practice Test 37 — 15 Questions

Question 1

You are configuring a new Azure SQL Database. The application that will use the database requires read-only access to the database from an Azure App Service. You need to ensure that the application connects securely without embedding credentials in code, and that access is limited to the minimum required permissions. What should you do?

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

Accepted Answer

Enable Microsoft Entra authentication for the Azure SQL Database, create a contained database user mapped to the App Service's managed identity, and grant the user the db_datareader role.. Enabling Microsoft Entra authentication and creating a contained database user for the App Service's managed identity eliminates credential management. Assigning the db_datareader role grants read-only access, adhering to the principle of least privilege. This approach is secure, requires no credentials in code, and limits permissions to what the application needs.

Answer

Enable Microsoft Entra authentication and create a contained database user mapped to the App Service's managed identity, then grant the user the db_owner role.

Answer

Create a contained database user with a strong password and store the credentials in Azure Key Vault. Configure the App Service to retrieve the credentials from Key Vault.

Answer

Enable SQL authentication and create a login with a strong password, then configure the App Service connection string to use this login.

Question 2

You manage an Azure SQL Database named SalesDB in the East US region. The database is in the General Purpose service tier and is business-critical. The application that uses SalesDB requires a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour in the event of a regional outage. You need to configure a disaster recovery solution that meets these requirements with minimal administrative effort. What should you implement?

Accepted Answer

Create an auto-failover group that includes SalesDB and a secondary server in West US.. An auto-failover group is the correct solution because it provides automatic failover for a group of databases, achieving an RPO of about 5 seconds and an RTO of about 1 hour. It also offers listener endpoints that remain constant, reducing application changes. Active geo-replication lacks automatic failover, zone redundancy only covers single-zone failures, and long-term retention restores are too slow and have a higher RPO.

Answer

Enable zone redundancy for SalesDB and rely on the built-in high availability.

Answer

Configure active geo-replication with a readable secondary in West US.

Answer

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

Question 3

You have an Azure SQL Database that is used by a development team. The team works only during business hours and the database can be unavailable outside those hours. You need to minimize compute cost while allowing the database to automatically pause when it is idle and resume when a connection is made. What should you configure?

Accepted Answer

Set the database to the General Purpose service tier with the Serverless compute tier and configure the auto-pause delay.. The Serverless compute tier is the only Azure SQL Database option that automatically pauses compute after an idle period and resumes on the next connection. Configuring it on the General Purpose service tier lets the development team pay only for the compute used during business hours. The auto-pause delay controls how long the database waits before suspending, directly reducing cost for an intermittently used database.

Answer

Set the database to the Hyperscale service tier with one read-scale replica.

Answer

Set the database to the General Purpose service tier with the Provisioned compute tier.

Answer

Set the database to the Business Critical service tier with zone redundancy enabled.

Question 4

A company uses Azure SQL Managed Instance in the East US region. They need to configure a disaster recovery strategy that provides a readable secondary in a paired region and supports manual failover. The solution must minimize administrative overhead and use built-in Azure capabilities. What should they implement?

Accepted Answer

Configure an auto-failover group with a managed instance in the paired region.. Auto-failover groups are the built-in DR feature for Azure SQL Managed Instance that provide cross-region replication, a readable secondary, and a listener endpoint. They support both manual and automatic failover and require minimal administration compared to custom replication or backup/restore. This makes them the correct choice for the stated requirements.

Answer

Use Azure SQL Managed Instance built-in backups and restore to the paired region during an outage.

Answer

Create a geo-secondary by using transactional replication to a managed instance in the paired region.

Answer

Deploy a SQL Server Always On availability group on Azure VMs in the paired region and replicate from the managed instance.

Question 5

You are the database administrator for a logistics company that uses Azure SQL Database. You need to automate the execution of a T-SQL script that rebuilds fragmented indexes every night at 2:00 AM. You want to minimize administrative overhead and avoid managing a separate virtual machine. What should you use?

Accepted Answer

Create an Elastic Job agent and define a job step that runs the T-SQL script on a schedule.. Elastic Jobs are the native automation feature for Azure SQL Database, allowing scheduled T-SQL execution without external compute. They support recurring schedules, target databases, and credential management, directly satisfying the need to run an index rebuild nightly at 2:00 AM with minimal administrative effort. Other options either require additional infrastructure or are not available for Azure SQL Database.

Answer

Configure a SQL Server Agent job on an Azure virtual machine that connects to the database.

Answer

Deploy an Azure Automation runbook that connects to the database and executes the script.

Answer

Use Azure Logic Apps with a recurrence trigger and a SQL Server connector to run the script.

Question 6

You are configuring alerts for an Azure SQL Database. You need to be notified when the database reaches 90% of its allocated storage. Which Azure Monitor alert signal should you use?

Accepted Answer

storage_percent. Azure SQL Database exposes the storage_percent metric, which directly reports the percentage of allocated storage used. Setting an alert on this metric at 90% will notify you when storage utilization reaches the threshold. Other metrics like storage (absolute size) or dtu_consumption_percent (compute) do not provide the required percentage of storage usage.

Answer

storage

Answer

physical_data_read_percent

Answer

dtu_consumption_percent

Question 7

You are deploying a new Azure SQL Database for a line-of-business application. The application's usage pattern is unpredictable, with long idle periods overnight and short bursts of heavy activity during business hours. Cost optimization is a priority, and the database can tolerate a brief reconnection delay when scaling. You need to select a purchasing model and service tier that minimizes cost while automatically adjusting compute resources. What should you do?

Accepted Answer

Deploy the database by using the vCore purchasing model with the General Purpose service tier and configure the serverless compute tier.. The serverless compute tier in the vCore purchasing model automatically scales compute resources based on workload activity and can pause the database during idle times, charging only for storage. This matches the need to minimize cost for unpredictable usage and tolerates the brief reconnection delay upon resuming. Other tiers either lack auto-scaling or are not cost-effective for this pattern.

Answer

Deploy the database by using the DTU purchasing model with the Standard service tier and configure elastic pool auto-scaling.

Answer

Deploy the database by using the vCore purchasing model with the Hyperscale service tier and enable read-scale replicas.

Answer

Deploy the database by using the vCore purchasing model with the Business Critical service tier and configure auto-scaling.

Question 8

You are the database administrator for a company that uses Azure SQL Database. The company wants to ensure that the database remains available even if the entire Azure region experiences an outage. The solution must provide a read-write endpoint that automatically redirects connections after a failover. What should you configure?

Accepted Answer

Auto-failover group.. An auto-failover group is the correct solution because it provides a read-write listener that automatically redirects connections to the secondary server in another region after a failover. It supports automatic failover policies, ensuring minimal downtime. Active geo-replication lacks automatic redirection, zone redundancy only covers a single region, and long-term retention backups are for restore, not failover.

Answer

Active geo-replication with a readable secondary.

Answer

Long-term retention backup and restore.

Answer

Zone-redundant configuration.

Question 9

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences high transaction log write waits during peak hours, and you observe that the log rate is frequently near its limit. You need to increase the maximum log rate for the database. What should you do?

Accepted Answer

Change the database to the Business Critical service tier.. The maximum log rate in Azure SQL Database is determined by the service tier and hardware generation. General Purpose has a lower log rate limit than Business Critical, which uses local SSD and offers higher throughput. To increase the log rate, you must move to Business Critical. Scaling compute within General Purpose or increasing storage does not raise the log rate limit. ADR improves recovery but not log throughput.

Answer

Enable accelerated database recovery (ADR) on the database.

Answer

Scale up the database to a higher compute size within the General Purpose tier.

Answer

Increase the maximum storage size of the database.

Question 10

A DBA manages an Azure SQL Database and needs to schedule a T-SQL script to run every day at 02:00 UTC to perform index maintenance. The solution must minimize administrative overhead and must not require an on-premises server. What should the DBA use?

Accepted Answer

Elastic Database Jobs in Azure SQL Database. Elastic Database Jobs provide a built-in scheduling mechanism for T-SQL scripts against Azure SQL Database. The DBA creates a job agent, defines a job with a T-SQL step, and attaches a schedule. This requires no on-premises server and minimal administrative effort, making it the correct choice for daily index maintenance.

Answer

SQL Server Agent job on the Azure SQL Database logical server

Answer

Azure Automation runbook with a schedule and the Az.Sql module

Answer

Azure Logic Apps with a recurrence trigger and SQL Server connector

Question 11

You manage an Azure SQL Database that contains a table with a column named CreditCardNumber. The security team requires that this column be encrypted so that even database administrators cannot view the plaintext values. The application that inserts and queries data must continue to work with minimal changes, and the encryption keys must be stored in Azure Key Vault. What should you implement?

Accepted Answer

Always Encrypted with column master key in Azure Key Vault and column encryption key for the CreditCardNumber column.. Always Encrypted ensures that sensitive data is never revealed to the database engine, protecting it from DBAs. The column master key stored in Azure Key Vault satisfies the key management requirement. Applications need only enable Always Encrypted in the client driver and use parameterized queries, which is a minimal change compared to other encryption methods.

Answer

Dynamic data masking on the CreditCardNumber column with a masking rule that shows only the last four digits.

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 12

You are the DBA for an Azure SQL Database that stores sensitive financial data. The security team requires that all user activity on the database be audited, and audit logs must be retained for 90 days. You need to configure auditing with minimal effort. What should you do?

Accepted Answer

Enable Azure SQL Database auditing and configure it to write logs to an Azure Storage account with a retention period of 90 days.. Enabling Azure SQL Database auditing and directing logs to Azure Storage with a 90-day retention period is the correct approach because it is a built-in feature that captures all user activity and meets the retention requirement with minimal configuration. The other options either are not supported in Azure SQL Database, require more manual effort, or do not provide the required audit scope.

Answer

Use SQL Server Extended Events to capture all statements and store the files in Azure Blob Storage.

Answer

Enable Azure Monitor diagnostic settings on the database and send logs to a Log Analytics workspace with 90-day retention.

Answer

Create a SQL Server Audit specification on the database and write to the Windows Application log.

Question 13

You manage an Azure SQL Database that runs a reporting workload. Users report that a complex stored procedure occasionally returns results in under 5 seconds but sometimes takes over 60 seconds. You have enabled Query Store with the default settings. You need to identify the plan that is causing the slow executions and force the faster plan. Which Query Store report should you use?

Accepted Answer

Queries With High Variation. The stored procedure alternates between fast and slow executions, which indicates multiple execution plans with varying performance. The Queries With High Variation report in Query Store is designed to surface exactly this pattern. It allows you to compare plans and force the efficient plan, stabilizing performance. The other reports focus on resource consumption, waits, or regressions, but not on plan-to-plan variability.

Answer

Top Resource Consuming Queries

Answer

Regressed Queries

Answer

Query Wait Statistics

Question 14

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences performance issues during peak hours, and you notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits. What should you do?

Accepted Answer

Switch to the Business Critical service tier.. PAGEIOLATCH_SH waits indicate that queries are waiting for data pages to be read from disk. In the General Purpose tier, storage is remote and can introduce latency. The Business Critical tier uses local SSD, which significantly reduces I/O latency and thus these waits. Other actions like enabling Query Store or increasing max size do not address the underlying I/O bottleneck.

Answer

Enable Query Store.

Answer

Increase the database's max size.

Answer

Scale up the database to a higher compute size.

Question 15

A DBA manages an Azure SQL Managed Instance and needs to automate a weekly full backup of a user database to an Azure Storage account. The solution must use native SQL Server functionality and minimize custom code. What should the DBA do?

Accepted Answer

Create a SQL Server Agent job with a T-SQL step that executes BACKUP DATABASE TO URL.. Azure SQL Managed Instance supports SQL Server Agent and native BACKUP DATABASE TO URL. Creating an Agent job with a T-SQL step that executes the backup to Azure Blob Storage provides a native, scheduled solution with minimal custom code. This is the most straightforward way to automate weekly full backups to a storage account.

Answer

Use Azure Data Factory with a copy activity to export the database to a storage account weekly.

Answer

Create an Azure Automation runbook that runs a PowerShell script using the Invoke-Sqlcmd cmdlet to perform a backup.

Answer

Configure automated backups in the Azure portal to write to a custom storage account.