DP-300 Practice Exam 54 — 20 Questions

Question 1

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

Accepted Answer

To store the shared access signature token for accessing the blob storage.. The database scoped credential in this context securely stores the Shared Access Signature (SAS) token required to authenticate and authorize access to Azure Blob Storage. When creating an external data source with the CREDENTIAL option, SQL Database uses this credential to pass the SAS token to the storage endpoint, enabling read operations for PolyBase or external table queries.

Answer

To define the file format of the external data.

Answer

To specify the schema of the external table.

Answer

To provide the location of the external data source.

Question 2

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

Accepted Answer

Zone redundancy enabled on Business Critical tier.. Zone redundancy on the Business Critical tier ensures that database replicas are placed in different availability zones within the same Azure region. During a zonal failure, the service automatically fails over to a synchronous replica in another zone, guaranteeing zero data loss because all transactions are synchronously committed across replicas. This directly meets the requirement of remaining available without data loss during a zonal outage.

Answer

General Purpose service tier.

Answer

Read scale-out.

Answer

Geo-redundant backup storage.

Question 3

You are designing a data archiving solution for an Azure SQL Database. The database contains a table with over 100 million rows of historical sales data that is rarely accessed. You need to reduce storage costs while keeping the data available for occasional queries. What should you do?

Accepted Answer

Use Stretch Database to migrate cold data to Azure Blob Storage. Stretch Database (now deprecated but still tested in DP-300) transparently migrates cold data from an Azure SQL Database table to Azure Blob Storage while keeping the data online and queryable via T-SQL. This reduces storage costs for rarely accessed historical data without requiring application changes, as the database engine automatically routes queries to the appropriate storage tier.

Answer

Partition the table by date and compress each partition

Answer

Use page compression on the table

Answer

Delete old data and store it in Azure Blob Storage as CSV files

Question 4

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Accepted Answer

Use Microsoft Entra ID authentication instead of SQL authentication.. Option A is correct because Microsoft Entra ID (formerly Azure AD) authentication provides centralized identity management, supports conditional access policies, and eliminates the need to manage SQL logins and passwords. It also enables features like multifactor authentication (MFA) and integration with Azure RBAC, which significantly reduces the risk of credential theft compared to SQL authentication.

Answer

Enable public network access to allow flexible connectivity.

Answer

Grant db_owner role to developers for ease of management.

Question 5

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

Accepted Answer

Azure Blob Storage with immutable storage policy. Azure Blob Storage with an immutable storage policy (WORM – Write Once, Read Many) is the correct choice because it ensures that audit logs cannot be modified or deleted for a specified retention period (7 years). This meets the immutability and retention requirements for compliance with regulations such as SOX or HIPAA. Azure SQL Database audit logs can be directly streamed to Azure Blob Storage, making it a seamless and secure storage solution.

Answer

Use Azure Files share with read-only permissions

Answer

Send logs to Azure Log Analytics workspace

Answer

Store logs in an audit table in Azure SQL Database

Question 6

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

Accepted Answer

Azure SQL Database Hyperscale with geo-replication. Azure SQL Database Hyperscale with geo-replication is the correct choice because it provides a single writable primary endpoint while supporting geo-distributed readable secondaries with low-latency reads for global users. Hyperscale's distributed architecture allows fast scaling and read scale-out, and geo-replication enables asynchronous replication to multiple regions, meeting the OLTP workload's low-latency read requirement without compromising the single write endpoint.

Answer

Azure SQL Database with active geo-replication

Answer

Azure SQL Managed Instance with failover groups

Answer

Azure SQL Database with failover groups

Question 7

A database administrator runs the PowerShell script shown in the exhibit against an Azure SQL Database. What does the script output?

Accepted Answer

Service tier and performance level of the database.. The PowerShell script uses the Get-AzSqlDatabase cmdlet, which retrieves an Azure SQL Database object. The output includes properties such as Edition (service tier) and ServiceObjective (performance level), making option D correct.

Answer

Details of table schemas.

Answer

List of backup retention policies.

Answer

List of users and their permissions.

Question 8

You are deploying Azure SQL Database for a new application. You need to ensure that connections from Azure services use a private IP address and do not traverse the public internet. What should you configure?

Accepted Answer

Use Azure Private Link for Azure SQL Database. Azure Private Link creates a private endpoint in a virtual network, mapping the Azure SQL Database logical server to a private IP address within that VNet. Traffic from Azure services (e.g., VMs, App Service) to the database uses Microsoft's backbone network via the private endpoint, never traversing the public internet. This meets the requirement for private, non-internet-routed connectivity.

Answer

Enable Virtual Network service endpoints for Azure SQL Database

Answer

Configure the Azure SQL Database firewall to allow Azure services

Answer

Deploy Azure SQL Database inside a virtual network

Question 9

A company is designing a new Azure SQL Database for an e-commerce application. The database will experience variable workloads with frequent read-heavy operations. To optimize performance and cost, the company wants to use a serverless compute tier. However, they also need to ensure the database can handle sudden bursts of user activity without significant latency. Which configuration should the team implement?

Accepted Answer

Set the serverless auto-pause delay to a high value (e.g., 24 hours) and configure a minimum vCore count that matches the expected baseline load.. Option D is correct because setting a high auto-pause delay (e.g., 24 hours) prevents the serverless database from pausing during idle periods, avoiding cold-start latency. Configuring a minimum vCore count that matches the expected baseline load ensures the database always has sufficient resources for steady-state read-heavy operations, while the maximum vCore count can scale up to handle sudden bursts without significant latency.

Answer

Use a provisioned General Purpose tier with read scale-out enabled to offload read traffic to a secondary replica.

Answer

Set the serverless auto-pause delay to 0 (disabled) and configure a maximum vCore count equal to the expected burst peak.

Answer

Enable auto-pause with a 1-minute delay and configure the maximum vCore count to handle bursts.

Question 10

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

Accepted Answer

NVARCHAR. B is correct because Azure SQL Database uses the NVARCHAR data type to store JSON documents as text. JSON is stored as a string in NVARCHAR columns, which supports Unicode characters and allows the use of built-in JSON functions like JSON_VALUE, JSON_QUERY, OPENJSON, and ISJSON for validation and querying. NVARCHAR(MAX) is recommended for large or variable-length JSON documents.

Answer

TEXT

Answer

XML

Answer

VARCHAR

Question 11

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

Accepted Answer

Azure SQL Database elastic pools with per-database min/max DTU or vCore settings.. Azure SQL Database elastic pools with per-database min/max DTU or vCore settings are the correct choice because they provide resource isolation and predictable performance at the tenant level. Elastic pools allow you to allocate a shared pool of resources across multiple databases while setting per-database minimum and maximum limits, ensuring that no single tenant can consume excessive resources and that each tenant gets a guaranteed baseline. This directly addresses the multi-tenant SaaS requirement for isolated and predictable resource usage.

Answer

Azure SQL Database serverless compute tier.

Answer

Azure SQL Database single databases with DTU-based tier.

Answer

Azure SQL Managed Instance with multiple databases.

Question 12

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

Accepted Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and remove all other IP rules.. Setting the 'Allow Azure Services and resources to access this server' firewall rule to ON enables a special rule that permits traffic from all Azure datacenter IP ranges, while removing all other IP rules ensures no other external traffic can reach the server. This configuration meets the requirement to allow only Azure services and block all other traffic, as the Azure services rule is a blanket allow for Azure-originated connections without needing specific IP addresses.

Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to OFF and add a rule for 0.0.0.0.

Answer

Set firewall rules to deny all IP addresses.

Answer

Set the firewall rule 'Allow Azure Services and resources to access this server' to ON and add a rule for 0.0.0.0.

Question 13

You need to secure an Azure SQL Database. The requirements are: (1) encrypt sensitive columns such as credit card numbers, (2) prevent unauthorized users from seeing the full credit card number even if they have access to the database, and (3) allow authorized applications to decrypt the data. Which two features should you implement? (Choose TWO.)

Accepted Answer

Always Encrypted. Always Encrypted is correct because it encrypts sensitive columns like credit card numbers at the client-side, ensuring the data remains encrypted at rest, in transit, and during processing. This prevents unauthorized users, including database administrators, from viewing the plaintext data, while authorized applications with the column encryption key can decrypt it transparently.

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Answer

Row-level security

Question 14

You are planning a migration of on-premises SQL Server databases to Azure SQL Managed Instance. Which TWO tools or services can you use to assess compatibility and identify potential migration blockers? (Select TWO.)

Accepted Answer

Data Migration Assistant (DMA). Data Migration Assistant (DMA) is the correct tool because it performs a detailed assessment of on-premises SQL Server databases, identifying compatibility issues and migration blockers specifically for Azure SQL Managed Instance. It generates a report of feature parity, breaking changes, and behavior differences, enabling you to remediate issues before migration.

Answer

SQL Server Management Studio (SSMS) – Upgrade Data Tier Application wizard

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio – Migration Wizard

Question 15

You need to deploy an Azure SQL Database that complies with a regulatory requirement to encrypt data at rest using a customer-managed key stored in Azure Key Vault. The database will be used by a line-of-business application. Which feature should you enable?

Accepted Answer

Transparent Data Encryption (TDE) with customer-managed key. Transparent Data Encryption (TDE) with a customer-managed key (CMK) in Azure Key Vault encrypts the database at rest, meeting the regulatory requirement for customer-controlled encryption keys. TDE performs real-time I/O encryption and decryption of data files, logs, and backups, and when configured with a CMK (also known as Bring Your Own Key or BYOK), the encryption key is stored and managed in Azure Key Vault, giving the customer full control over key rotation and revocation.

Answer

Azure Defender for SQL

Answer

Dynamic Data Masking

Answer

Always Encrypted

Question 16

You are designing a disaster recovery strategy for an Azure SQL Database in the West US region. The database is used by a mission-critical application with an RPO of 5 seconds and an RTO of 30 seconds. Which deployment option should you choose?

Accepted Answer

Deploy the database with zone-redundant configuration in the primary region. Option C is correct because zone-redundant configuration for Azure SQL Database replicates the database across three Azure Availability Zones within the same region, providing automatic failover with an RPO of 0 seconds and an RTO of typically 10-30 seconds. This meets the stringent RPO of 5 seconds and RTO of 30 seconds without relying on cross-region replication, which would introduce higher latency and potential data loss.

Answer

Use active geo-replication with a readable secondary in another Azure region

Answer

Use a failover group with a secondary in a paired region

Answer

Deploy the database with an auto-failover group to a secondary region

Question 17

You are a database administrator for a global SaaS provider. You have multiple Azure SQL Databases in a single logical server. You need to implement a disaster recovery strategy that provides automatic failover across Azure regions with the lowest possible RPO and RTO for critical databases. The solution must not require any application code changes. What should you implement?

Accepted Answer

Configure an auto-failover group across two Azure regions with a grace period of 1 hour and include all critical databases in the group.. Option D is correct because auto-failover groups provide automatic failover across Azure regions with the lowest possible RPO (typically 5 seconds) and RTO (around 1 minute) for critical databases, without requiring any application code changes. By setting the grace period to 1 hour, you allow the system to tolerate transient outages before triggering failover, which balances availability and data loss tolerance. Including all critical databases in the group ensures consistent failover behavior and prevents split-brain scenarios.

Answer

Use Azure SQL Database backup and restore across regions with a recovery point objective (RPO) of 1 hour.

Answer

Configure an auto-failover group across two Azure regions with a grace period of 0 and include only the most critical database.

Answer

Use active geo-replication for each critical database and configure a custom health check to trigger failover.

Question 18

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

Accepted Answer

Azure SQL Auditing. Azure SQL Auditing is the feature specifically designed to track database events and write them to an audit log destination, including a Log Analytics workspace. This enables security analysis by capturing all queries executed against the database, which can then be queried and analyzed within the workspace. The other options focus on vulnerability scanning, threat detection, or security posture management, not on recording query execution logs.

Answer

SQL Vulnerability Assessment

Answer

Advanced Threat Protection

Answer

Microsoft Defender for Cloud

Question 19

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

Accepted Answer

Microsoft Entra authentication, firewall rules, and managed identity. Option B is correct because it satisfies all three requirements: Microsoft Entra authentication enforces identity-based access, firewall rules block IP addresses outside the corporate network, and a managed identity allows the application to connect without storing credentials in code by using a system-assigned or user-assigned identity to obtain an access token from Microsoft Entra ID.

Answer

Always Encrypted, VNet service endpoints, and SQL authentication

Answer

Transparent Data Encryption, IP firewall rules, and connection strings

Answer

Azure Defender for SQL, firewall rules, and service principal

Question 20

You are configuring an Azure SQL Database for a financial application that requires high availability and disaster recovery across regions. The database is in the Premium service tier. Which TWO configurations should you implement to meet these requirements? (Choose TWO.)

Accepted Answer

Create a failover group that includes the primary and secondary.. Option C is correct because a failover group in Azure SQL Database provides automated, coordinated failover of multiple databases across regions, ensuring high availability and disaster recovery with a defined replication policy and a readable secondary endpoint. Option D is correct because active geo-replication allows you to create up to four readable secondary databases in different Azure regions, enabling manual or automatic failover for cross-region disaster recovery, which is essential for a financial application requiring regional resilience.

Answer

Enable automatic tuning for the database.

Answer

Enable zone redundancy within the primary region.

Answer

Enable Transparent Data Encryption (TDE).

DP-300 Practice Exam 54 — 20 Questions

Exhibit

DP-300 Practice Exam 54 — 20 Questions

Exhibit