DP-300 Practice Test 34 — 15 Questions

Question 1

Your Azure SQL Database contains sensitive financial data. You need to audit all data modifications (INSERT, UPDATE, DELETE) and store the audit logs in a central Azure Storage account for compliance. What should you configure?

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

Accepted Answer

Enable auditing on the database and set the audit log destination to an Azure Storage account.. Azure SQL Database's built-in auditing feature can be configured to capture all data modifications (INSERT, UPDATE, DELETE) and write audit logs directly to an Azure Storage account. This meets the compliance requirement for centralized, durable storage of audit records without additional services or complex pipelines.

Answer

Configure diagnostic settings to stream query store data to an event hub.

Answer

Enable Microsoft Defender for SQL and configure security alerts to be sent to a storage account.

Answer

Enable SQL Vulnerability Assessment and export the results to a storage account.

Question 2

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

Accepted Answer

Add a new firewall rule at the server level that allows the developer's IP address.. Azure SQL Database firewall rules are configured at the server level (the logical server) to control inbound access. Adding a new server-level firewall rule for the developer's specific IP address allows temporary access without modifying or removing the existing range-based rule. This approach is the standard method for granting time-limited access to a single IP while preserving all other firewall configurations.

Answer

Configure a point-to-site VPN connection for the developer.

Answer

Update the existing firewall rule to include the developer's IP address.

Answer

Modify the database-level firewall rule to include the developer's IP.

Question 3

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it allows you to obfuscate sensitive data in query results without changing the underlying database. You can define a mask on the credit card column that shows only the last four digits by default, and grant the UNMASK permission to privileged users so they see the full value. This directly meets the requirement of role-based partial masking without altering the stored data.

Answer

Column-level security

Answer

Row-level security

Answer

Always Encrypted

Question 4

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

Accepted Answer

Configure a virtual network service endpoint and a virtual network firewall rule.. Using a virtual network service endpoint and a virtual network firewall rule allows Azure SQL Database to accept traffic only from the specific subnet hosting the application VMs, without exposing the database to the public internet. This minimizes latency by keeping traffic within the Azure backbone network and reduces the security risk by eliminating broad IP-based rules.

Answer

Add a firewall rule allowing all Azure IP addresses.

Answer

Add a firewall rule for each VM's public IP address.

Answer

Add a firewall rule allowing all Azure services to access the database.

Question 5

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

Accepted Answer

Configure SQL Server Managed Backup to Microsoft Azure.. SQL Server Managed Backup to Microsoft Azure (also known as Managed Backup) is the correct choice because it provides automated, policy-based backup management with minimal administrative effort. It natively supports point-in-time restore by automatically scheduling full, differential, and transaction log backups to Azure Blob Storage, and it handles backup retention and recovery point management without requiring custom scripts or additional infrastructure.

Answer

Install Azure Backup Server on-premises and configure backup of SQL Server databases.

Answer

Use SQL Server Agent jobs to perform full, differential, and log backups to an Azure Blob Storage URL.

Answer

Use Azure Data Factory to copy database backups to Blob Storage.

Question 6

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

Accepted Answer

Configure sensitivity labels using Microsoft Purview Information Protection.. Microsoft Purview Information Protection provides sensitivity labels that can be applied to columns in Azure SQL Database to classify and protect personal data, meeting GDPR requirements. These labels enforce encryption, access restrictions, and visual markings, integrating with Azure SQL's data classification capabilities.

Answer

Implement Always Encrypted for all columns containing personal data.

Answer

Install the Azure Information Protection client on all client machines.

Answer

Enable Microsoft Defender XDR for the database server.

Question 7

You are deploying a new Azure SQL Database for a small internal inventory application. The workload is steady, with predictable usage during business hours, and the application team requires a fixed monthly cost with no risk of unexpected overage charges. You need to choose a purchasing model and service tier that meets these requirements. What should you do?

Accepted Answer

Deploy the database by using the DTU purchasing model with a Standard service tier.. The DTU purchasing model with the Standard service tier provides a fixed monthly cost and is designed for steady, moderate workloads, which matches the small internal inventory application. The vCore model, whether Business Critical or General Purpose with auto-scaling, introduces variable costs that do not meet the fixed-cost requirement, and the Premium DTU tier is unnecessarily costly.

Answer

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

Answer

Deploy the database by using the DTU purchasing model with the Premium service tier.

Answer

Deploy the database by using the vCore purchasing model with the Business Critical service tier.

Question 8

You are a database administrator for a global e-commerce company. The company uses Azure SQL Database for its product catalog, which is a mission-critical OLTP workload. The database is currently deployed in the West US region using the Business Critical service tier with zone redundancy enabled. The database size is 200 GB and grows at 10 GB per month. The company has a disaster recovery requirement: in the event of a regional outage, the database must be failed over to a secondary region with an RPO of less than 5 seconds and an RTO of less than 1 minute. Additionally, the secondary database must be readable to support read-heavy reporting workloads. The solution must minimize additional compute costs. You need to recommend a configuration. Which option should you choose?

Accepted Answer

Configure active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary.. Active geo-replication to a secondary database in a paired region using Business Critical tier with a readable secondary meets all requirements: it provides cross-region DR with an RPO of less than 5 seconds (synchronous replication within the primary region, asynchronous to secondary), RTO of less than 1 minute (failover is fast), and the secondary is readable for reporting. Zone redundancy within the primary region does not provide cross-region DR, so options B and C are incorrect. Hyperscale tier (option D) is more expensive and does not inherently provide cross-region DR with the required RPO; named replicas add cost.

Answer

Create a failover group within the same region using Business Critical tier with a readable secondary.

Answer

Add a second zone-redundant replica in the same region and configure a failover group.

Answer

Upgrade to Hyperscale tier with zone redundancy and configure a named replica in a secondary region.

Question 9

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

Accepted Answer

Identify and optimize the queries running during the spike periods, possibly rescheduling a heavy ETL job.. The issue is not a consistent resource shortage but periodic spikes caused by specific queries, likely from a heavy ETL job. By identifying and optimizing those queries or rescheduling the job, you can eliminate the spikes without permanently scaling up resources, which would waste cost and capacity. This aligns with the DP-300 focus on performance tuning and resource optimization rather than blind scaling.

Answer

Migrate the database to the Hyperscale service tier.

Answer

Scale the database to a higher service tier to absorb the spikes.

Answer

Enable Query Store and use the Regressed Queries feature to find slow queries.

Question 10

You are configuring Azure SQL Database firewall rules. You need to allow a team of developers to connect from their office IP range (192.168.1.0/24) to a specific database. The developers should not be able to access other databases on the same logical server. What should you do?

Accepted Answer

Add a database-level firewall rule for the IP range 192.168.1.0/24.. Database-level firewall rules in Azure SQL Database allow you to restrict access to a specific database on a logical server, rather than the entire server. By adding a rule for the IP range 192.168.1.0/24 at the database level, the developers can connect only to that database, and they will be blocked from accessing other databases on the same server. This is the correct approach because server-level rules would grant access to all databases, which violates the requirement.

Answer

Create a private endpoint for the database.

Answer

Add a server-level firewall rule for the IP range 192.168.1.0/24.

Answer

Configure a virtual network service endpoint for the server.

Question 11

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

Accepted Answer

The OrdersDB database's service objective is updated to S2.. The Azure CLI command 'az sql db update' with '--service-objective S2' modifies the service tier of an existing database. The immediate effect is that the OrdersDB database's service objective is changed to S2, which alters its performance level and cost. It does not create a new database or change the server tier.

Answer

The server's performance tier is changed to S2.

Answer

The database is scaled down from a higher tier to S2.

Answer

A new database named OrdersDB is created with S2 tier.

Question 12

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 13

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

Accepted Answer

Transparent data encryption (TDE) and TLS 1.2. Transparent Data Encryption (TDE) encrypts data at rest by performing real-time I/O encryption and decryption of the database, data files, and transaction logs, while TLS 1.2 encrypts data in transit between the client and Azure SQL Database. Together, they satisfy the requirement to encrypt all patient data both at rest and in transit.

Answer

Dynamic data masking and row-level security

Answer

Azure Active Directory authentication and firewall rules

Answer

Always Encrypted and transparent data encryption (TDE)

Question 14

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

Accepted Answer

Configure the server to enforce TLS 1.2 by setting the 'Minimal TLS Version' property. Option C is correct because setting the server's 'Minimal TLS Version' property to 1.2 forces all client connections to Azure SQL Database to negotiate TLS 1.2, which encrypts data in transit between the application and the database. Option E is correct because Always Encrypted encrypts sensitive column data on the client side using a column master key stored in Azure Key Vault, so the data is never decrypted on the SQL server and DBAs cannot view the plaintext. Option A is incorrect because row-level security only filters which rows a user can access; it does not encrypt data in transit or hide column values from DBAs. Option B is incorrect because TDE encrypts data at rest (database, log, and backup files) and does not protect data in transit, nor does it prevent DBAs from viewing data since they can still query decrypted values. Option D is incorrect because dynamic data masking only obscures data in query results for non-privileged users and does not encrypt data in transit or prevent DBAs, who can be granted UNMASK permission, from viewing the actual data.

Answer

Implement row-level security (RLS) to filter rows

Answer

Enable transparent data encryption (TDE) on the database

Answer

Implement dynamic data masking on sensitive columns

Question 15

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

Accepted Answer

Identify and create missing indexes.. High CPU usage in an S3 Azure SQL Database often stems from inefficient query plans caused by missing indexes. Creating appropriate indexes reduces the number of rows scanned and the CPU cycles needed for operations like key lookups and sorting, directly lowering CPU consumption without changing the service tier.

Answer

Increase the maximum number of concurrent workers.

Answer

Reduce MAXDOP to 1.

Answer

Increase MAXDOP to 8.