DP-300 Practice Exam 62 — 20 Questions

Question 1

You are a database administrator for a healthcare company that uses Azure SQL Database for its patient management system. The database is currently 500 GB and uses the General Purpose tier in the West US region. The application experiences high read traffic during business hours, and you need to improve read performance without increasing costs significantly. You also need to ensure that read queries are isolated from write workloads. The application supports read-only routing using connection strings. You have the following options:

A. Create a secondary replica in the same region using active geo-replication and configure the application to use the secondary for read-only queries.
B. Upgrade the database to the Business Critical tier and use the built-in readable secondary.
C. Implement read scale-out by adding a readable secondary replica in the same region using the built-in read scale-out feature of the General Purpose tier.
D. Create an elastic pool with multiple databases and distribute read queries across them.

Which option should you choose?

Accepted Answer

Create a secondary replica using active geo-replication and configure read-only routing.. Option B is correct because active geo-replication allows you to create a readable secondary replica in the same region, which can be used to offload read-only queries from the primary. The application can use connection strings with ApplicationIntent=ReadOnly to automatically route read queries to the secondary, isolating them from write workloads. This improves read performance without upgrading to a higher-cost tier, as the General Purpose tier does not natively support readable secondaries within the same region.

Answer

Implement read scale-out in General Purpose tier.

Answer

Upgrade to Business Critical tier and use its readable secondary.

Answer

Create an elastic pool and distribute read queries.

Question 2

You are a database administrator for an e-commerce company that uses Azure SQL Database to store order data. The company is implementing a new policy that requires all database access to be audited, including both successful and failed attempts. Additionally, the audit logs must be retained for at least one year for compliance purposes. You need to configure auditing for the database. What should you do?

Accepted Answer

Enable auditing on the database, set the audit log destination to a storage account, and configure retention to 365 days. Option D is correct because Azure SQL Database auditing can be configured at the database level to send audit logs to a storage account, which allows you to set a retention policy of 365 days directly on the audit configuration. This meets the requirement to audit both successful and failed attempts and retain logs for at least one year for compliance.

Answer

Use the 'Audit Logs' feature in the Azure portal to enable auditing and set retention to 365 days

Answer

Enable auditing on the database and configure it to send logs to a Log Analytics workspace, then set the retention in Log Analytics to 365 days

Answer

Enable auditing on the server and configure it to send logs to an event hub

Question 3

You are a database administrator for a financial services company that uses Azure SQL Database for a critical trading application. The application connects using a service principal (Microsoft Entra application) and executes stored procedures. You need to implement the following security requirements:

1. All connections must use Microsoft Entra authentication with MFA enforced for the service principal.
2. The application should only be able to execute specific stored procedures (usp_Trade, usp_GetQuote) and no other operations.
3. All data at rest must be encrypted using customer-managed keys stored in Azure Key Vault.
4. Auditing must capture all failed login attempts and all changes to the database schema.
5. The database must be protected against SQL injection attacks from the application layer.

You have already configured Microsoft Entra authentication and enabled TDE with customer-managed key in Azure Key Vault. Which additional steps should you take to meet all remaining requirements?

Accepted Answer

Create a database role with EXECUTE permission on the required stored procedures and assign it to the service principal. Configure a Conditional Access policy that requires MFA for the service principal. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP. Ensure the application uses parameterized queries.. Option D is correct because it addresses all remaining requirements: (1) Conditional Access policy enforces MFA for the service principal; (2) EXECUTE permission on specific stored procedures limits operations; (3) Audit action groups for failed logins and schema changes; (4) Parameterized queries prevent SQL injection. Option A is incorrect because dynamic data masking does not prevent SQL injection. Option B is incorrect because row-level security does not prevent SQL injection. Option C is incorrect because Always Encrypted does not prevent SQL injection and does not enforce MFA.

Answer

Create a database role that only has EXECUTE permission on the required stored procedures. Configure dynamic data masking on sensitive columns. Enable audit for FAILED_DATABASE_AUTHENTICATION_GROUP and SCHEMA_OBJECT_CHANGE_GROUP.

Answer

Create a contained database user for the service principal with EXECUTE permission. Enable Always Encrypted for sensitive columns. Configure audit for FAILED_DATABASE_AUTHENTICATION_GROUP only.

Answer

Create a database role with EXECUTE on the stored procedures. Implement row-level security to restrict data access. Use Microsoft Defender for SQL to detect SQL injection attempts.

Question 4

Your company has an Azure SQL Database that is accessed by multiple applications. You need to implement a security solution that meets the following requirements:
- Each application must have its own database user with specific permissions.
- All authentication must use Microsoft Entra ID.
- You need to be able to rotate credentials for each application without impacting other applications.
- The solution must support automatic credential rotation for service principals.

What should you do?

Accepted Answer

Create a Microsoft Entra ID service principal for each application, store the client secret in Azure Key Vault, and create a contained database user mapped to each service principal.. Option D is correct because creating a Microsoft Entra ID service principal for each application, assigning a client secret (which can be rotated automatically via Azure Key Vault), and creating a contained database user mapped to the service principal meets all requirements. Option A is wrong because SQL authentication is not using Microsoft Entra ID. Option B is wrong because managed identity is limited to Azure-hosted applications and cannot be used for all applications. Option C is wrong because a single service principal would not allow per-application permissions.

Answer

Use managed identities for each Azure resource and assign permissions to the database.

Answer

Create a single Microsoft Entra ID service principal for all applications and assign different database roles.

Answer

Create SQL logins and users for each application with strong passwords, and configure password rotation policies.

Question 5

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements:
- All traffic to the database must be private and not traverse the internet.
- The database must be accessible from an Azure Virtual Machine in a specific VNet.
- The solution must minimize management overhead and cost.
- You need to ensure that the database can be failed over to a secondary region in case of an outage.

What should you do?

Accepted Answer

Create a private endpoint in the VNet, disable public network access, and configure a failover group with a private endpoint in the secondary region.. Option B is correct because it meets all requirements: private endpoint ensures private connectivity, failover group provides disaster recovery, and disabling public access enforces security. Option A is wrong because service endpoints still expose the public endpoint. Option C is wrong because a point-to-site VPN adds complexity and cost. Option D is wrong because a public endpoint with IP restriction does not meet the private traffic requirement.

Answer

Restrict firewall rules to only the VM's public IP and enable active geo-replication.

Answer

Configure a point-to-site VPN from the VM to the database and set up geo-replication.

Answer

Create a VNet service endpoint and a failover group. Keep public access enabled for failover.

Question 6

Your organization is migrating a legacy on-premises SQL Server database to Azure SQL Managed Instance. The database contains sensitive financial data. You need to implement column-level encryption so that even database administrators cannot view the plaintext data. The encryption keys must be stored in Azure Key Vault, and the application must be able to encrypt and decrypt data transparently. The application currently uses Entity Framework Core and runs on Azure App Service. You have the following requirements:
- Use a solution that provides the strongest security by ensuring the database never has access to the plaintext.
- Minimize changes to the application code.
- The application must be able to perform equality searches on encrypted columns.

What should you implement?

Accepted Answer

Use Always Encrypted with deterministic encryption and store column master key in Azure Key Vault.. Option A is correct because Always Encrypted with deterministic encryption allows equality searches and ensures the database never sees the plaintext. Client-side drivers handle encryption transparently. Option B is wrong because TDE does not provide column-level encryption. Option C is wrong because secure enclaves require additional configuration and are not necessary for equality searches. Option D is wrong because dynamic data masking does not encrypt data.

Answer

Use Always Encrypted with secure enclaves and randomized encryption.

Answer

Use dynamic data masking to hide the data from unauthorized users.

Answer

Use Transparent Data Encryption (TDE) with customer-managed keys in Azure Key Vault.

Question 7

You are setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements:
- The sensitive data should be automatically masked in query results for all developers except the database administrator.
- The masking should be applied without modifying the application code.
- The solution should be easy to manage and not require changes to the data model.

What should you implement?

Accepted Answer

Configure dynamic data masking on the sensitive columns, and add the database administrator to the unmask permission.. Option C is correct because dynamic data masking can be configured in the database to mask columns for all users except those with explicit unmask permissions. It does not require code changes. Option A is wrong because Always Encrypted requires application changes. Option B is wrong because row-level security filters rows, not columns. Option D is wrong because views would require rewriting queries.

Answer

Create views that exclude sensitive columns and grant developers access to the views instead of the base tables.

Answer

Implement Always Encrypted with column encryption, and grant the developers access to the encryption keys.

Answer

Create a row-level security policy that denies access to sensitive rows for developers.

Question 8

You are a database administrator for a technology company that uses Azure SQL Database to support a multi-tenant SaaS application. Each tenant has its own database. The security team requires that users from one tenant should never be able to access data from another tenant, even if a user's credentials are compromised. You need to implement a solution that enforces tenant isolation at the database level. The solution must be transparent to the application and must not require application code changes. What should you do?

Accepted Answer

Create a contained database user for each tenant in their respective database and ensure that cross-database queries are not allowed by configuring server-level firewall rules to block access between databases. Option B is correct. Azure SQL Database provides built-in support for elastic queries and elastic jobs, but for tenant isolation, the best approach is to use a separate database per tenant, which is already the case. To prevent cross-tenant access, you should ensure that each tenant's database has its own contained database user mapped to the tenant's Microsoft Entra ID identity. Additionally, you can use row-level security (RLS) with a security policy that filters rows based on a TenantId column, but since each tenant has its own database, RLS is not strictly necessary. However, the question emphasizes preventing cross-tenant access even if credentials are compromised. The most robust solution is to ensure that each database has a dedicated firewall rule or private endpoint, and that users are granted access only to their specific database. Option B correctly states to use contained database users and ensure that no cross-database queries are allowed. Option A is wrong because elastic queries are designed to query across databases, which would increase the risk of cross-tenant access. Option C is wrong because creating a linked server would allow cross-database queries. Option D is wrong because always encrypted does not prevent cross-tenant access.

Answer

Use Always Encrypted to encrypt each tenant's data with a separate column encryption key

Answer

Implement elastic queries to query across all tenant databases and use a view to filter by tenant ID

Answer

Create a linked server between all tenant databases and use a security policy to restrict access

Question 9

You are the database administrator for a large e-commerce application hosted on Azure SQL Database in the Business Critical tier (8 vCores). The database has a table named Orders with 500 million rows. The table is partitioned by OrderDate using a monthly partition function. The application frequently runs queries that aggregate sales by month for the current year. Users report that these queries are slow. You examine the query execution plans and notice that the queries are scanning all partitions, not just the relevant ones. You need to improve query performance without changing the application code. Which action should you take?

Accepted Answer

Re-create the partition function with a different boundary type (e.g., RIGHT instead of LEFT) to align with query patterns.. Option A is correct because partition elimination requires the predicate to be on the partitioning column. In this case, the queries likely do not filter on OrderDate, or the predicate is not sargable. Adding a clustered index on OrderDate would help if the queries filter on OrderDate, but the stem says the queries aggregate by month for the current year, so they likely have a WHERE clause on OrderDate. If partition elimination is not happening, the most common reason is that the partition function is not aligned with the query predicate. Re-creating the partition function with a different boundary may help if the current boundaries are not matching. However, the best action is to ensure the queries use the partition column in the WHERE clause. Since we cannot change the code, we can modify the partition function to align with the typical query pattern. Option B is wrong because creating views does not force partition elimination. Option C is wrong because columnstore indexes do not directly cause partition elimination. Option D is wrong because updating statistics does not fix partition elimination.

Answer

Update statistics for the Orders table.

Answer

Create a columnstore index on the Orders table.

Answer

Create an indexed view that pre-aggregates sales by month.

Question 10

You are a database administrator for a financial services company that runs a critical application on Azure SQL Database in the Business Critical service tier. The database is named 'TransactionsDB' and has a size of 500 GB. The application experiences periodic performance degradation during end-of-month batch processing. Analysis shows that the degradation coincides with high log write activity and increased latency for write transactions. You have already verified that the log rate is within the service tier limits. The batch process performs a large number of INSERT, UPDATE, and DELETE operations on multiple tables. You need to optimize the transaction log performance without changing the application code or the service tier. The database uses the full recovery model and has a log backup every 5 minutes. What should you do?

Accepted Answer

Increase the initial size of the transaction log file to 200 GB and set the auto-growth increment to a fixed size of 1 GB.. Option A is correct because increasing the log file size reduces auto-growth events and improves log throughput; the current size may be too small causing frequent growth and fragmentation. Option B is wrong because transactional replication adds overhead. Option C is wrong because enabling accelerated database recovery does not directly improve log write performance. Option D is wrong because page compression reduces I/O but not log writes.

Answer

Implement transactional replication to offload write operations to a secondary database.

Answer

Enable page compression on all tables to reduce the amount of data written to the log.

Answer

Enable accelerated database recovery to reduce the log space required for long-running transactions.

Question 11

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the General Purpose tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

Accepted Answer

Create a read-only replica and direct reports to it. Option D is correct because creating a read-only replica allows you to offload reporting queries to the replica, which can be at a lower service tier, reducing load on the primary and improving performance. Option A is wrong because scaling up the primary to S3 during reporting hours increases cost. Option B is wrong because adding indexes helps but may not be sufficient. Option C is wrong because columnstore indexes are not ideal for OLTP workloads and may affect write performance.

Answer

Increase the service tier to S3 during the end-of-month period

Answer

Add nonclustered indexes to the tables used in reports

Answer

Convert the tables to clustered columnstore indexes

Question 12

You are a database consultant for a financial services company that uses an Azure SQL Managed Instance (MI) in the General Purpose tier (16 vCores, 1024 GB storage) for a critical application. The MI hosts a database that processes large batch transactions every night. Recently, the batch jobs have been failing due to timeout errors. You notice that the log write throughput is hitting the service tier limit (50 MB/s for General Purpose). The business requires the batch to complete within the same time window. You cannot change the application code or move to Business Critical tier due to budget constraints. You need to ensure the batch jobs complete successfully. What should you recommend?

Accepted Answer

Modify the batch jobs to use bulk insert with TABLOCK and batch inserts into smaller transactions.. Option C is correct because reducing the log write rate by batching transactions or using minimally logged operations (e.g., bulk insert with TABLOCK) can keep throughput under the limit. Option A is wrong because increasing storage doesn't affect log throughput. Option B is wrong because moving to Business Critical is not allowed. Option D is wrong because enabling accelerated database recovery reduces version store I/O, not log throughput.

Answer

Migrate the database to Business Critical tier.

Answer

Increase the managed instance storage to 2048 GB to improve log throughput.

Answer

Enable accelerated database recovery to reduce log I/O.

Question 13

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

Accepted Answer

Change the backup storage redundancy of the secondary replica to locally-redundant storage (LRS).. Option C is correct because changing the secondary replica's backup storage redundancy from locally-redundant storage (LRS) to geo-redundant storage (GRS) might cause increased latency, but here the issue is high log write latency. Actually, the correct answer is to change the secondary's backup storage redundancy to LRS (if it is GRS) to reduce latency. However, the options are as given. Option A is wrong because decreasing the secondary replica's service tier reduces latency but also reduces redundancy. Option B is wrong because asynchronous commit reduces protection. Option D is wrong because adding more replicas increases cost and complexity. The best answer is C, as it addresses the storage latency issue without compromising data protection.

Answer

Add an additional secondary replica to distribute the log write load.

Answer

Change the failover group to use asynchronous commit mode.

Answer

Decrease the service tier of the secondary replica to General Purpose.

Question 14

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Accepted Answer

Enable compression on transaction log backups.. Option B is correct because compressing transaction log backups reduces log volume indirectly, but more directly, enabling accelerated database recovery (ADR) reduces log IO. Since ADR is not an option here, the best answer is to use compression on backups. Option A is wrong because increasing service tier does not reduce log generation. Option C is wrong because adding indexes increases log writes. Option D is wrong because backup retention does not affect log generation.

Answer

Create additional nonclustered indexes on frequently updated tables.

Answer

Increase the service tier to Business Critical.

Answer

Increase the backup retention period.

Question 15

You are a database administrator for a SaaS company. You manage an Azure SQL Database that uses the Hyperscale service tier. The database supports a multi-tenant application with unpredictable workloads. You notice that the database's page server is experiencing high IOPS utilization, causing query performance degradation. You need to improve performance by optimizing the page server usage. What should you do?

Accepted Answer

Enable accelerated database recovery (ADR) to reduce log IO.. Option A is correct because enabling accelerated database recovery reduces the log generation and IO on page servers. Option B is wrong because increasing the number of replicas does not reduce page server IO. Option C is wrong because changing to Business Critical might not address page server IO; Hyperscale has its own architecture. Option D is wrong because increasing max degree of parallelism might increase IO.

Answer

Increase the number of secondary replicas to distribute read workload.

Answer

Change the service tier to Business Critical to get faster IO.

Answer

Increase the MAXDOP setting for the database.

Question 16

You are a database administrator for an e-commerce company that uses Azure SQL Managed Instance. You have a database that experiences high blocking due to long-running transactions. You need to configure a solution that automatically terminates the blocking process after it has been waiting for more than 5 seconds. The solution should be set at the database level and should not require any application changes. What should you do?

Accepted Answer

Set the DEADLOCK_PRIORITY to LOW on the blocking session and set LOCK_TIMEOUT to 5000.. Option A is correct because setting the DEADLOCK_PRIORITY to LOW and using a lock timeout of 5 seconds will cause the blocking session to be killed if it exceeds the timeout. Option B is wrong because resource governor is not available in Managed Instance at the database level. Option C is wrong because read committed snapshot isolation does not kill blocking processes. Option D is wrong because changing transaction isolation level does not automatically terminate processes.

Answer

Enable read committed snapshot isolation (RCSI) on the database.

Answer

Create a resource governor workload group with a maximum grant time of 5 seconds.

Answer

Set the transaction isolation level to SNAPSHOT for all sessions.

Question 17

You are a database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 50 databases across 10 logical servers, each with a mix of General Purpose and Business Critical service tiers. The company has a strict requirement to automatically scale databases based on workload patterns to optimize cost without manual intervention. Specifically, during Black Friday sales, one of the Business Critical databases (DB-Sales) experiences a surge in transactions, and you need to temporarily upgrade it to a higher service objective (S9 instead of S6) for 48 hours. After the sale, it should automatically revert to S6. Additionally, you need to ensure that all databases have automated backups with a 35-day point-in-time restore retention and that backup storage costs are minimized by using geo-redundant storage only for critical databases. You have been asked to design an automation solution using Azure native services. Which approach should you recommend?

Accepted Answer

Use Azure Automation Runbooks scheduled to run before and after Black Friday to change the service objective, and set backup retention policies using Azure Policy.. Option B is correct because Azure Automation Runbooks can be scheduled to change the service objective of a database, and policies for backup retention are set at the server level but can be overridden per database using ARM templates or PowerShell. Option A is wrong because elastic jobs are for executing scripts across databases, not for scheduling scaling. Option C is wrong because autoscale is not available for Azure SQL Database. Option D is wrong because SQL Agent cannot change service objectives or set backup retention.

Answer

Use Elastic Job agents to run a script that alters the service objective and configure backup retention using the Azure portal.

Answer

Enable autoscale on the database to automatically adjust service objective based on CPU usage.

Answer

Create a SQL Server Agent job that runs ALTER DATABASE MODIFY (SERVICE_OBJECTIVE = 'S9') and configure backup retention using T-SQL.

Question 18

You are a database administrator for a retail company that uses Azure SQL Database with the Serverless compute tier. The database experiences unpredictable idle periods, and you want to minimize costs by automatically pausing the database when it is idle for more than 60 minutes and resuming it when a connection is attempted. However, you also need to ensure that a critical reporting job that runs every hour can connect even if the database is paused. What should you do?

Accepted Answer

Enable the serverless auto-pause feature with a delay of 60 minutes. No additional action is needed; the reporting job will automatically resume the database upon connection.. Option A is correct. The Serverless tier has built-in auto-pause and auto-resume features. You can configure the auto-pause delay to 60 minutes. The reporting job will automatically resume the database when it connects, as the serverless tier supports auto-resume on connection. Option B is incorrect because setting the auto-pause delay to 0 would cause the database to pause immediately, causing issues. Option C is incorrect because Azure Automation cannot resume a paused database automatically. Option D is incorrect because Elastic Jobs cannot resume the database.

Answer

Disable auto-pause for the database and use Azure Automation to scale down the database during idle periods.

Answer

Use Elastic Database Jobs to keep the database active by running a lightweight query every 59 minutes.

Answer

Set the auto-pause delay to 0 minutes to minimize costs, and create an Azure Automation runbook to keep the database active during the reporting job.

Question 19

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance. The instance is part of a failover group for disaster recovery. You need to automate the process of testing the failover group by performing a planned failover to the secondary region and then failing back. The test must be performed monthly during a maintenance window. The automation must ensure that the failover group is in a healthy state before and after the test and must log the results to a table. Additionally, the solution must be self-contained within the Managed Instance and not rely on external tools. What should you do?

Accepted Answer

Create a SQL Agent job with T-SQL that performs the planned failover using ALTER AVAILABILITY GROUP and logs the results to a table.. Option D is correct. SQL Agent jobs on Managed Instance can execute T-SQL commands to perform failover using the ALTER AVAILABILITY GROUP command (which is available for failover groups) and log results. Option A is incorrect because Elastic Database Jobs is not available for Managed Instance. Option B is incorrect because Azure Automation requires external setup. Option C is incorrect because Data Factory is not for failover orchestration.

Answer

Use Elastic Database Jobs to run T-SQL that initiates failover and logs results.

Answer

Create an Azure Automation runbook with PowerShell that uses the Az.Sql module to perform failover and log to a table.

Answer

Use Azure Data Factory to execute a stored procedure that performs failover.

Question 20

You are a database administrator for a large e-commerce company. The company uses Azure SQL Database in a Business Critical tier for its transactional systems. The database is part of an elastic pool with multiple databases. You need to automate the process of purging old data from a specific table (OrderHistory) that grows by 5 GB per day. The data must be retained for 90 days. After 90 days, the data should be moved to an Azure Blob Storage archive for long-term storage. The automation must run daily at midnight and must minimize impact on the transactional workload. Additionally, the solution should be cost-effective and require minimal manual intervention. What should you do?

Accepted Answer

Create a stored procedure that deletes data older than 90 days and exports it to Blob Storage using PolyBase. Schedule the procedure using Elastic Database Jobs.. Option B is the correct answer. Elastic Database Jobs can run a stored procedure that deletes data older than 90 days and exports it to Blob Storage using BULK INSERT or PolyBase. This approach is designed for minimal impact and uses Azure's PaaS capabilities. Option A (Azure Data Factory) is more suitable for complex ETL, but for simple purge and archive, Elastic Jobs is simpler and more cost-effective. Option C (Azure Automation runbook) would require additional components like Hybrid Runbook Worker and may introduce latency. Option D (SQL Agent) is not available in Azure SQL Database.

Answer

Create a SQL Agent job on the database to run a cleanup script at midnight.

Answer

Use Azure Automation with a PowerShell runbook that connects to the database, deletes old data, and uploads it to Blob Storage using AzCopy.

Answer

Use Azure Data Factory with a scheduled trigger to copy data older than 90 days to Blob Storage and then delete it from the table.