DP-300 Plan and implement data platform resources Practice Test 9 — 15 Questions

Question 1

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

Accepted Answer

Configure active geo-replication to East US. Active geo-replication for Azure SQL Database Business Critical tier provides a continuous, asynchronous replication stream to a secondary region, ensuring minimal data loss (typically a few seconds) during a regional outage. Unlike auto-failover groups, active geo-replication allows you to manually initiate a failover to the secondary region with a Recovery Point Objective (RPO) of up to 5 seconds, which meets the requirement for minimal data loss. The Business Critical tier also supports readable secondaries, but the key here is the replication mechanism that prioritizes low RPO over automatic failover.

Answer

Configure an auto-failover group with a readable secondary in East US

Answer

Enable zone redundancy for the database

Answer

Configure geo-restore from the West US database

Question 2

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

Accepted Answer

Use Azure Key Vault key rotation policy to automatically rotate the key every 90 days. Option D is correct because Azure Key Vault supports automatic key rotation policies that can be configured to rotate a customer-managed key (CMK) every 90 days without any manual intervention. When this key is used for TDE in Azure SQL Database, the database automatically uses the latest version of the key from Key Vault, so no additional steps are needed to update the database after rotation. This satisfies the security policy with minimal administrative overhead.

Answer

Manually rotate the key in Key Vault every 90 days and update the database

Answer

Use Azure SQL Database server-side TDE with a service-managed key

Answer

Store the key in the database and use a custom rotation script

Question 3

You are planning to migrate several on-premises SQL Server databases to Azure SQL Managed Instance. The databases use SQL Server Agent jobs, cross-database queries, and CLR assemblies. Which migration tool should you use to assess compatibility?

Accepted Answer

Data Migration Assistant (DMA). Option C is correct because the Data Migration Assistant (DMA) is specifically designed to assess compatibility of on-premises SQL Server databases with Azure SQL Managed Instance, including features like SQL Server Agent jobs, cross-database queries, and CLR assemblies. DMA performs a detailed feature parity check and identifies blocking issues, such as unsupported CLR permissions or cross-database dependencies, before migration. It provides a comprehensive report with remediation guidance, making it the ideal tool for this pre-migration assessment phase.

Answer

Azure Database Migration Service (DMS)

Answer

Azure Data Studio with SQL Server migration extension

Answer

SQL Server Management Studio (SSMS)

Question 4

You have an Azure SQL Database that experiences performance degradation due to high concurrency and blocking. You need to implement a solution that offloads reporting queries to a read-only copy of the database without impacting the primary workload. What should you configure?

Accepted Answer

Add the database to an auto-failover group and use the readable secondary for reporting. Option A is correct because adding the database to an auto-failover group and using the readable secondary for reporting offloads read-only workloads to a synchronized replica without impacting the primary. The readable secondary is a built-in feature of Azure SQL Database that provides a read-only endpoint, and it is automatically kept in sync via transactional replication, ensuring near-real-time data for reporting while the primary handles write-heavy transactions.

Answer

Create a database snapshot and use it for reporting

Answer

Configure a geo-replica and use it for reporting

Answer

Enable read scale-out on the database by adding a secondary replica

Question 5

Your organization is implementing Microsoft Purview for data governance. You need to scan an Azure SQL Database to automatically classify sensitive data. The security team requires that the scan use a managed identity and that the data classification be stored in Microsoft Purview. What should you configure on the Azure SQL Database?

Accepted Answer

Assign the Microsoft Purview managed identity the db_datareader role and configure the firewall to allow Purview IP ranges. Option C is correct because Microsoft Purview uses its own managed identity to authenticate to data sources when scanning. To scan an Azure SQL Database, you must grant Purview's managed identity the `db_datareader` role in the database to allow read access for classification, and you must configure the Azure SQL firewall to allow inbound connections from the Purview IP ranges (or enable 'Allow Azure services and resources to access this server'). This satisfies the security team's requirement to use a managed identity and store classification in Purview.

Answer

Enable system-assigned managed identity on the database and grant it permissions to access Purview

Answer

Use SQL authentication with a dedicated login and firewall rule

Answer

Create a user-assigned managed identity and assign it to the database

Question 6

You are deploying an Azure SQL Managed Instance for a financial application. The compliance requirements dictate that all data at rest must be encrypted using TDE with a customer-managed key stored in Azure Key Vault. Additionally, the key must be accessible only from the managed instance's virtual network. How should you configure the Key Vault firewall and managed instance?

Accepted Answer

Enable the Key Vault firewall, select 'Allow trusted Microsoft services', and configure the managed instance with a system-assigned managed identity. Option C is correct because enabling the Key Vault firewall with 'Allow trusted Microsoft services' and configuring the managed instance with a managed identity ensures access only from trusted services. Option A is wrong because disabling the firewall would allow public access. Option B is wrong because granting the managed instance's public IP is not recommended and may not work. Option D is wrong because disabling the firewall and using a service principal is less secure.

Answer

Disable the Key Vault firewall and assign the managed instance a service principal

Answer

Enable the Key Vault firewall and add the managed instance's public IP address to the firewall rules

Answer

Disable the Key Vault firewall and use a user-assigned managed identity for the managed instance

Question 7

Which TWO Azure SQL Database features can help you scale out read-intensive workloads without modifying application code?

Accepted Answer

Geo-replication with readable secondary. Geo-replication with readable secondary (Option B) is correct because it creates a continuously synchronized read-only replica of your Azure SQL Database in a different Azure region. Applications can connect to this secondary endpoint using the same connection string with 'ApplicationIntent=ReadOnly', enabling read-intensive workloads to be offloaded without any code changes. This provides both scalability for reads and disaster recovery.

Answer

Database sharding

Answer

In-memory OLTP

Answer

Elastic pool

Question 8

Which THREE actions should you take when planning to implement Azure SQL Database with Microsoft Defender for SQL and Microsoft Sentinel integration?

Accepted Answer

Configure Microsoft Sentinel data connectors for Azure SQL Database. Option B is correct because Microsoft Sentinel requires a data connector to ingest Azure SQL Database security events. The 'Azure SQL Database' data connector in Sentinel must be configured to pull diagnostic logs and alerts from the SQL resource into the Log Analytics workspace used by Sentinel, enabling advanced threat detection and incident response.

Answer

Enable Advanced Threat Protection for each database individually

Answer

Create a diagnostic setting to stream metrics to Event Hub

Question 9

Which TWO conditions must be met to use Azure SQL Database serverless compute tier?

Accepted Answer

The database must be a single database, not part of an elastic pool. Option C is correct because Azure SQL Database serverless compute tier is designed only for single databases, not for databases in elastic pools. The serverless tier auto-scales compute resources based on workload demand and pauses during idle periods, which is incompatible with the shared resource model of elastic pools where multiple databases share a fixed set of resources.

Answer

The database must use the DTU purchasing model

Answer

The database must be in the Hyperscale service tier

Answer

The database must be in the General Purpose service tier

Question 10

You are a database administrator for a global e-commerce company. The company runs a critical transactional workload on an Azure SQL Managed Instance (MI) in the West US region. The database is approximately 1 TB in size and uses the Business Critical tier with zone redundancy enabled. The application requires read latency for reporting queries to be under 10 ms. The company is expanding to Europe and needs to deploy a secondary copy of the database in the North Europe region to serve read-only reporting traffic for European users. The secondary must be kept in sync with the primary with a recovery point objective (RPO) of less than 5 seconds. Additionally, the solution must support automatic failover if the primary region becomes unavailable. You need to design the disaster recovery and read scaling solution. Which approach should you take?

Accepted Answer

Create an auto-failover group with the primary in West US and add a readable secondary in North Europe, using synchronous commit. Option C is correct because Azure SQL Managed Instance supports auto-failover groups with a readable secondary in a paired region. By configuring synchronous commit within the failover group, you achieve an RPO of less than 5 seconds (typically 0-2 seconds) while the secondary in North Europe can serve read-only traffic with low latency. The auto-failover group also provides automatic failover if the primary region becomes unavailable, meeting both the read scaling and disaster recovery requirements.

Answer

Create a geo-replica of the MI in North Europe and manually failover when needed

Answer

Deploy a separate managed instance in North Europe and use transactional replication to keep it in sync

Answer

Create an auto-failover group with the primary in West US and secondary in North Europe, but do not make the secondary readable

Question 11

You are designing a new Azure SQL Database deployment for a mission-critical application. The application requires 99.99% availability SLA, automated failover within 30 seconds, and read-scale capability for reporting. Which tier and configuration should you choose?

Accepted Answer

Business Critical with zone redundancy and a failover group. Business Critical with zone redundancy and a failover group meets all requirements: 99.99% SLA (supported by zone-redundant Business Critical), automated failover within 30 seconds (failover groups provide automatic, fast failover across regions), and read-scale capability (Business Critical includes a readable secondary replica).

Answer

General Purpose serverless with geo-replication

Answer

General Purpose with active geo-replication and read-scale

Answer

Hyperscale with zone redundancy enabled

Question 12

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

Accepted Answer

sys.dm_tran_locks and sys.dm_os_waiting_tasks. To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.

Answer

sys.dm_exec_requests

Answer

sys.dm_exec_query_stats

Answer

sys.dm_tran_active_snapshot_database_transactions

Question 13

Refer to the exhibit. The exhibit shows a partial ARM template for deploying an Azure SQL Database server. You run the deployment but get an error stating the server cannot be created because of a missing firewall rule. The template has public network access disabled and no firewall rules defined. What is the most likely cause?

Accepted Answer

The Entra ID admin SID is invalid (all zeros).. The error occurs because the Entra ID admin SID is set to all zeros ('00000000-0000-0000-0000-000000000000'), which is an invalid GUID. Azure SQL Database requires a valid, non-zero SID (object ID) for the Entra ID admin to establish authentication. When public network access is disabled, the server can still be created without firewall rules, but the invalid SID prevents the deployment from succeeding.

Answer

The firewallRules array must include at least one rule when publicNetworkAccess is Disabled.

Answer

The tenantId is missing in the Entra ID admin configuration.

Answer

The Entra ID admin type should be 'Group' not 'User'.

Question 14

You are migrating an on-premises SQL Server database with 5 TB of data to Azure SQL Database. The database has multiple large tables and indexes. You need to minimize downtime during migration. Which service should you use?

Accepted Answer

Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct choice because it minimizes downtime by continuously synchronizing ongoing changes from the on-premises SQL Server to Azure SQL Database while the initial bulk data copy is in progress. This allows you to cut over to the target with only a brief pause, which is critical for a 5 TB database where offline methods would cause extended unavailability.

Answer

Export to BACPAC and import using SqlPackage

Answer

Backup and restore using URL with log shipping

Answer

Transactional replication from on-premises to Azure SQL Database

Question 15

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