DP-300 Practice Test 21 — 15 Questions

Question 1

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?

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

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 2

You are deploying an Azure SQL Database that will store sensitive customer data. Compliance requirements dictate that the data must be encrypted at rest using a customer-managed key that is rotated every 90 days. You configure TDE with Azure Key Vault. What additional step is critical to ensure data remains accessible after key rotation?

Accepted Answer

Ensure the previous key version remains enabled in Azure Key Vault.. When using TDE with Azure Key Vault and customer-managed keys, the database is encrypted using a Data Encryption Key (DEK) that is protected by the key in Key Vault. Key rotation creates a new key version, but the DEK remains encrypted with the previous key version. To decrypt the DEK and access the data after rotation, the previous key version must remain enabled in Key Vault. If it is disabled or deleted, the database becomes inaccessible.

Answer

Store the key in a different region for disaster recovery.

Answer

Use a premium tier of Azure Key Vault.

Answer

Re-encrypt the database with the new key immediately after rotation.

Question 3

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 4

A company runs a critical SQL Server database on Azure Virtual Machines. The database experiences intermittent performance degradation during peak hours. The database administrator notices high I/O latency on the data drive. The current disk configuration uses Standard HDDs. What is the most cost-effective action to reduce I/O latency without changing the virtual machine size?

Accepted Answer

Migrate the data files to Premium SSD managed disks.. Migrating the data files to Premium SSD managed disks is the most cost-effective action to reduce I/O latency without changing the virtual machine size. Premium SSDs provide consistent low-latency performance and higher IOPS/throughput compared to Standard HDDs, directly addressing the high I/O latency during peak hours. This change does not require resizing the VM, keeping compute costs unchanged while improving storage performance.

Answer

Enable instant file initialization in SQL Server.

Answer

Increase the virtual machine size to a series with higher I/O limits.

Answer

Migrate the data files to Standard SSD managed disks.

Question 5

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

Accepted Answer

FORCE_LAST_GOOD_PLAN. FORCE_LAST_GOOD_PLAN, is the correct automatic tuning option for Azure SQL Database to automatically correct query plan choice regressions. When the database engine detects that a newly compiled query plan performs worse than the previously known good plan, it can automatically force the last known good plan without manual intervention, which is ideal for a heavy OLTP workload where performance stability is critical.

Answer

DROP_INDEX

Answer

CREATE_INDEX

Answer

CORRECT_INDEX

Question 6

You are migrating a large on-premises SQL Server database (5 TB) to Azure SQL Managed Instance. The network bandwidth between your on-premises data center and Azure is limited to 100 Mbps. You need to minimize the migration time. Which approach should you use?

Accepted Answer

Use Azure Data Box to physically transfer the data.. Azure Data Box is the correct approach because it physically transfers the 5 TB database via a secure storage device, bypassing the 100 Mbps network bottleneck. At 100 Mbps, a 5 TB transfer would take approximately 5.5 days continuously, while Data Box can complete the transfer in a few days including shipping, making it the fastest option for such a large dataset over a limited bandwidth link.

Answer

Use transaction log shipping to continuously sync changes.

Answer

Export as BACPAC and upload to Azure Blob Storage, then import.

Answer

Use Azure Database Migration Service (DMS) online migration.

Question 7

You are designing an automation strategy for deploying schema changes to 50 Azure SQL Databases in a elastic pool. You need to ensure that changes are applied in a consistent order and rolled back if any database fails. Which approach should you use?

Accepted Answer

Create an Elastic Job that executes the schema change script within a transaction and uses target group error handling to roll back on failure.. Azure SQL Database Elastic Jobs can run T-SQL scripts across multiple databases with transactional control and error handling. Options A and C do not provide rollback capability. Option D is for infrastructure deployment, not schema changes.

Answer

Use Azure Data Studio with the Schema Compare extension and apply changes manually.

Answer

Deploy the schema changes using Azure Resource Manager templates with SQL extensions.

Answer

Use PowerShell to loop through each database and execute the script, catching exceptions.

Question 8

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

Accepted Answer

The SQL logins in the master database are not replicated to the secondary server.. When Active Geo-Replication is configured for Azure SQL Database, the secondary server is a separate logical server in a different region. The master database, which contains server-level logins, is not replicated as part of geo-replication; only the user databases are replicated. Therefore, after a failover, the new primary server does not have the server-level logins from the original primary, causing authentication failures for applications using those logins.

Answer

The DNS name of the secondary server changed after failover.

Answer

The firewall rules on the secondary server do not allow connections from the application IP.

Answer

The application uses contained database users, which are not replicated.

Question 9

Which TWO are valid ways to secure access to an Azure SQL Database?

Accepted Answer

Configure a Virtual Network service endpoint. A Virtual Network service endpoint extends your virtual network private address space and the identity of your VNet to Azure SQL Database over a direct connection. This allows you to secure access by limiting connectivity to only traffic originating from a specific subnet in your VNet, effectively blocking public internet access while using the Azure backbone network.

Answer

Use IPsec VPN from on-premises

Answer

Join the database to an on-premises Active Directory domain

Answer

Disable all firewall rules and rely only on authentication

Question 10

You are the database administrator for a global e-commerce company. The company uses Azure SQL Database in the Business Critical service tier for its transactional database, db1, hosted in the East US region. The database is 500 GB and experiences high write throughput. The current disaster recovery solution uses active geo-replication to a secondary in West US, but during a recent failover drill, the failover took 45 seconds, exceeding the corporate RTO of 30 seconds. The RPO requirement is zero data loss. You need to improve the failover time while maintaining zero data loss. The secondary must be in a different Azure region for compliance. What should you do?

Accepted Answer

Configure an auto-failover group between East US and West US using zone-redundant deployment within each region and ensure the secondary is zone-redundant.. None of the provided options fully satisfy both requirements: zero data loss (RPO=0) and failover time under 30 seconds across regions. Auto-failover groups (Option C) use asynchronous replication across regions, so RPO is not zero. General Purpose tier (Option B) also uses async replication. Increasing replicas (Option A) does not affect failover time. Hyperscale with named replicas (Option D) does not support cross-region failover. To achieve zero data loss across regions, an alternative solution such as SQL Server on Azure VMs with an availability group or Azure SQL Managed Instance with a distributed availability group would be required.

Answer

Increase the number of replicas in the Business Critical tier to 5.

Answer

Change the secondary to a General Purpose tier and use async replication.

Answer

Move both primary and secondary to Hyperscale tier and use named replicas.

Question 11

You are monitoring an Azure SQL Database and notice that the 'tempdb' database is experiencing contention. Which THREE actions can reduce tempdb contention? (Choose three.)

Accepted Answer

Add multiple tempdb data files. Adding multiple tempdb data files reduces allocation contention on system pages like PFS (Page Free Space), GAM (Global Allocation Map), and SGAM (Shared Global Allocation Map). When multiple sessions concurrently allocate pages in tempdb, a single data file becomes a bottleneck; additional files spread the allocation load across separate file system objects, reducing latch contention.

Answer

Increase the size of tempdb

Answer

Change the collation of tempdb

Question 12

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

Accepted Answer

Enable 'Azure AD-only authentication' on the logical server.. Enabling 'Azure AD-only authentication' on the logical server explicitly blocks all SQL authentication connections, including those from contained database users. This setting enforces that only Microsoft Entra ID (formerly Azure AD) principals can authenticate, directly aligning with the policy to prevent accidental use of SQL authentication.

Answer

Configure server-level firewall rules to block all IP addresses except Azure services.

Answer

Disable SQL authentication for all contained database users.

Answer

Create a database-level trigger to reject connections using SQL authentication.

Question 13

Refer to the exhibit. You are reviewing an ARM template for deploying an Azure SQL Database. The template specifies a point-in-time restore from a source database. The source database is configured with geo-redundant backup storage. You need to ensure that the restored database can be used for disaster recovery in a different region. What is missing from the template to achieve this?

Accepted Answer

Set 'createMode' to 'GeoRestore' and specify a target server in the desired region.. To perform a point-in-time restore in a different region for disaster recovery, you must set 'createMode' to 'GeoRestore' and specify the target server in the desired region. The template currently uses 'PointInTimeRestore', which only restores to the same region. Option A is incorrect because adding a 'location' property alone does not enable cross-region restore—the restore mode must be changed. Option B is incorrect because 'zoneRedundant' refers to high availability within a single region, not disaster recovery across regions. Option C is incorrect because 'GeoZone' backup redundancy still does not support cross-region point-in-time restore; geo-restore uses geo-redundant backups automatically.

Answer

Add a 'location' property to the database resource.

Answer

Set 'zoneRedundant' to true.

Answer

Change 'requestedBackupStorageRedundancy' to 'GeoZone'.

Question 14

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.. 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 15

Your company uses Azure SQL Database Hyperscale tier for a large OLTP workload. You need to implement a disaster recovery solution that provides a readable secondary in a different Azure region with an RPO of less than 5 seconds and automatic failover. What should you configure?

Accepted Answer

Create a named replica in the target region and configure application failover logic.. Hyperscale supports named replicas that can be created in a different region and can be used for failover. Option A is wrong because Hyperscale does not support auto-failover groups. Option B is wrong because Hyperscale does not support active geo-replication. Option C is wrong because geo-restore does not provide a readable secondary.

Answer

Create an auto-failover group with a secondary in the paired region.

Answer

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

Answer

Configure active geo-replication to a secondary server in another region.