DP-300 Practice Test 20 — 15 Questions

Question 1

You are deploying Azure SQL Database for a multi-tenant application. Each tenant's data must be isolated. You need to ensure that tenants cannot access each other's data even if there is a SQL injection vulnerability. Which security feature should you implement?

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

Accepted Answer

Implement row-level security (RLS) with a security policy that filters rows by tenant ID.. Row-level security (RLS) is the correct choice because it enforces data isolation at the database engine level by filtering rows based on a tenant ID predicate. Even if a SQL injection vulnerability allows an attacker to execute arbitrary queries, RLS ensures that only rows belonging to the attacker's tenant are returned, preventing cross-tenant data access. This is a defense-in-depth measure that works regardless of application-layer flaws.

Answer

Use Always Encrypted to encrypt sensitive columns.

Answer

Configure Azure SQL Database auditing to monitor cross-tenant access.

Answer

Enable Transparent Data Encryption (TDE) on the database.

Question 2

You are the database administrator for a large e-commerce company. The company has a SQL Server 2019 Enterprise Edition instance running on-premises with multiple databases. One of the databases, 'OrdersDB', is 2 TB in size and has a high transaction rate. The company plans to migrate 'OrdersDB' to Azure SQL Managed Instance to reduce operational overhead. The migration must have minimal downtime, and the target service tier must be Business Critical with 16 vCores. You have set up a test environment and performed a successful test migration using the Azure Database Migration Service (DMS) with online mode. During the final migration, the network bandwidth between on-premises and Azure is limited to 100 Mbps. The migration is taking longer than expected, and you are concerned about the log backup growth. What should you do to minimize the risk of the migration failing due to log space exhaustion?

Accepted Answer

Increase the frequency of transaction log backups on the source database to keep the log small.. Increasing the frequency of transaction log backups on the source database reduces the size of the active log, preventing log space exhaustion during the long-running online migration. The Azure Database Migration Service (DMS) in online mode continuously captures and applies log changes, so a smaller log reduces the risk of the log file filling up before backups can be taken, especially under constrained network bandwidth.

Answer

Switch to offline migration mode to avoid log growth issues.

Answer

Pause the migration and request a higher network bandwidth from the ISP.

Answer

Reduce the Azure SQL Managed Instance storage performance tier to standard to reduce cost.

Question 3

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

Accepted Answer

Backup retention period (in days).. The backup retention period (in days) is a configurable setting for Azure SQL Database automated backups. You can set the retention period for point-in-time restore (PITR) backups between 1 and 35 days, and for long-term retention (LTR) backups up to 10 years. This directly controls how far back you can restore your database.

Answer

Backup compression.

Answer

Backup frequency (full, differential, log).

Answer

Point-in-time restore interval.

Question 4

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

Accepted Answer

Implement Always Encrypted with column master key stored in Azure Key Vault.. Always Encrypted is the correct choice because it ensures that sensitive data is encrypted at the column level and that the encryption keys are never revealed to the database engine. By storing the column master key in Azure Key Vault and using client-side encryption, the application manages the encryption and decryption process, so only authorized users with access to the key can read the plaintext data. This meets the requirement that encryption be managed by the application, not the database.

Answer

Use dynamic data masking to obfuscate the sensitive columns for unauthorized users.

Answer

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

Answer

Create a row-level security policy to restrict access to the sensitive rows.

Question 5

Your company is using Azure SQL Database with Microsoft Entra ID authentication. A developer needs to connect to the database using a service principal. What should you provide to the developer?

Accepted Answer

The service principal's client ID and client secret, and the connection string with 'Authentication=Active Directory Service Principal'.. To connect to Azure SQL Database using a service principal with Microsoft Entra ID authentication, the developer needs the service principal's client ID and client secret (or certificate) for authentication, and the connection string must include 'Authentication=Active Directory Service Principal' to specify the authentication method. This combination allows the application to obtain an access token from Microsoft Entra ID via the OAuth 2.0 client credentials grant flow, which is then used to authenticate to the database.

Answer

The connection string with 'Authentication=Active Directory Service Principal' and the service principal's object ID.

Answer

The service principal's username and password.

Answer

The service principal's managed identity endpoint.

Question 6

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

Accepted Answer

In the Azure portal, navigate to the SQL server's 'Microsoft Entra ID' blade and enable 'Azure AD-only authentication'. Enabling 'Azure AD-only authentication' in the SQL server's Microsoft Entra ID blade disables SQL authentication entirely, ensuring that only Microsoft Entra ID principals can authenticate. This directly meets the security requirement to eliminate SQL authentication logins, as it prevents any connection attempt using SQL login credentials, even if they exist in the system catalog.

Answer

Set the 'Deny public network access' property to 'Yes'

Answer

Configure a server-level firewall rule to block all IP addresses

Answer

Query sys.sql_logins to check for any SQL authenticated logins

Question 7

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

Accepted Answer

Internal Load Balancer. A is correct because an Internal Load Balancer is required to route traffic to the primary replica in a SQL Server Always On Availability Group (AG) deployed on Azure Virtual Machines. The listener uses the ILB's frontend IP and health probe to direct client connections to the current primary node, as the Windows Server Failover Cluster (WSFC) does not support the cluster IP address in Azure without a load balancer.

Answer

Azure Files share for witness

Answer

Azure SQL Database

Answer

VPN gateway between regions

Question 8

You have an Azure SQL Database with a table that stores historical data. To improve query performance, you create a nonclustered columnstore index on the table. However, after a large data load, you notice that query performance is worse than before. What is the most likely reason?

Accepted Answer

The data was loaded in small batches, causing rowgroups to be smaller than optimal.. When data is loaded in small batches into a columnstore index, each batch may form its own rowgroup. Rowgroups with fewer than 102,400 rows are considered 'delta rowgroups' and are not compressed into the columnstore format, leading to poor compression and suboptimal query performance. The large data load likely consisted of many small batches, resulting in numerous small rowgroups that degrade scan performance.

Answer

The columnstore index is fragmented and needs to be rebuilt.

Answer

The table has too many columns included in the index.

Answer

The index was created with compression disabled.

Question 9

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

Accepted Answer

Enable Transparent Data Encryption (TDE) with a customer-managed key from Azure Key Vault.. Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault encrypts the database at rest, using a key that you control and rotate independently. This meets the requirement for encryption at rest with a customer-managed key, as TDE performs real-time I/O encryption and decryption of the data and log files without requiring application changes.

Answer

Configure dynamic data masking (DDM).

Answer

Implement row-level security (RLS) to restrict access.

Answer

Enable Always Encrypted for the sensitive columns.

Question 10

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.. The issue is that partition elimination is not occurring, meaning the query scans all partitions instead of only the relevant ones for the current year. Since the table is partitioned by OrderDate using a monthly partition function, the alignment of partition boundaries with the query predicate is critical. If the current partition function uses LEFT boundaries, the boundary values represent the start of each partition, but queries for the current year might include dates that cross boundaries in a way that prevents elimination. Re-creating the partition function with RIGHT boundaries ensures that each boundary value is the first day of the next partition, which often aligns better with range queries like 'WHERE OrderDate >= '2025-01-01' AND OrderDate < '2025-12-31''. This can enable partition elimination without changing application code. Option A (update statistics) does not affect partition elimination. Option B (columnstore index) improves compression and query speed but does not force partition elimination. Option D (indexed view) requires code changes and does not directly cause partition elimination. Therefore, option C is correct.

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 11

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

Accepted Answer

Migrate the database to Azure SQL Managed Instance and recreate the job using SQL Server Agent.. Azure SQL Managed Instance supports SQL Server Agent, including jobs that run PowerShell scripts via CmdExec or PowerShell job steps. By migrating the database and recreating the job with the same script, you preserve the existing automation with minimal changes. This is the only option that keeps the job execution environment identical to the on-premises setup.

Answer

Migrate the database to Azure SQL Managed Instance and configure an Azure Automation runbook to run the script.

Answer

Migrate the database to Azure SQL Managed Instance and use Elastic Jobs to run the PowerShell script.

Answer

Migrate the database to Azure SQL Database and use Elastic Jobs to run the PowerShell script.

Question 12

Your company has an Azure SQL Database configured with active geo-replication to a secondary region. You need to perform a planned failover for disaster recovery testing with zero data loss. The primary database is under heavy write load. What should you do?

Accepted Answer

Set the primary database to read-only, perform a planned failover, then switch the new primary to read-write.. To achieve zero data loss during a planned failover under heavy write load, you should set the primary database to read-only to block any new writes. This allows the geo-replication to catch up and synchronize all committed transactions to the secondary. Then perform the planned failover (which switches the secondary to become the new primary). After failover, set the new primary to read-write mode to resume writes. Option B is incorrect: switching the secondary to read-write only enables read workloads on the secondary; it does not trigger a failover. The failover must be explicitly initiated, and the write-heavy primary still has pending transactions. Option C is incorrect: a full backup and restore would cause data loss because any transactions after the backup would be lost. Geo-replication provides near-real-time synchronization. Option D is incorrect: scaling up the primary does not affect the failover process or guarantee zero data loss. The only way to ensure zero data loss is to prevent writes and synchronize before failover.

Answer

Switch the secondary to read-write mode and then failover.

Answer

Take a full backup of the primary, restore it to the secondary, and then failover.

Answer

Scale up the primary database to the highest performance tier to handle writes during failover.

Question 13

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.. To resolve the timeout errors, you need to reduce the log write throughput to stay within the 50 MB/s limit of the General Purpose tier. Option B achieves this by modifying batch jobs to use bulk insert with TABLOCK, which enables minimal logging, and breaking transactions into smaller batches to reduce the log write rate per transaction. Option A is incorrect because moving to Business Critical is not possible due to budget constraints. Option C is incorrect because increasing storage does not affect the log write throughput limit; it only increases data and log file space. Option D is incorrect because accelerated database recovery reduces the amount of version store I/O during transaction rollback, not the log write throughput during normal operations.

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 14

You have an Azure SQL Managed Instance configured with a failover group to a secondary region. During a regional outage, the failover group automatically fails over to the secondary. After the primary region is restored, you need to bring the primary back online and re-establish the failover relationship. What should you do?

Accepted Answer

Initiate a planned failover from the current primary to the original primary.. After an automatic failover, the secondary region becomes the primary. To restore the original primary and re-establish the failover relationship, you must perform a planned failover (also called a graceful failover) from the current primary (the former secondary) to the original primary. This switches roles back without data loss. Option A is incorrect because deleting and recreating the failover group is unnecessary and disruptive. Option B is incorrect because you cannot add the original primary as a new secondary if it is already part of the failover group—it must be removed first. Option D is incorrect because removing and re-adding the original primary would break the geo-replication and is not the correct procedure.

Answer

Delete the failover group and recreate it with the original primary as the primary.

Answer

Add the original primary as a new secondary to the current primary.

Answer

Remove the original primary from the failover group and re-add it.

Question 15

Your company has an Azure SQL Database in the East US region. You need to configure a disaster recovery solution that provides a readable secondary in the West US region with an RPO of 5 seconds. The solution must support automatic failover from the application. What should you configure?

Accepted Answer

Create an auto-failover group with a secondary server in West US using synchronous replication.. Auto-failover groups with a secondary server in another region using synchronous replication can achieve an RPO of 5 seconds and support automatic failover from the application. Option A is wrong because zone-redundant replicas are in the same region and do not provide cross-region DR. Option B is wrong because geo-redundant backup storage and geo-restore are not for automatic failover and have a higher RPO. Option C is wrong because although active geo-replication provides a readable secondary, it does not support automatic failover; a group is needed.

Answer

Enable zone-redundant replicas in the same region.

Answer

Enable geo-redundant backup storage and perform geo-restore.

Answer

Configure active geo-replication to a secondary server in West US.