Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 241:

  HOTSPOT

  You plan to migrate on-premises Microsoft SQL Server databases to Azure.

  You need to identify which deployment and resiliency options meet the following requirements:

  1.

  Support user-initiated backups.

  2.

  Support multiple automatically replicated instances across Azure regions.

  3.

  Minimize administrative effort to implement and maintain business continuity.

  What should you identify? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: SQL Server on Azure VMs

  SQL Server on Azure Virtual Machines can take advantage of Automated Backup, which regularly creates backups of your database to blob storage. You can also manually use this technique.

  Box 2: Active geo-replication

  Geo-replication for services such as Azure SQL Database and Cosmos DB will create secondary replicas of your data across multiple regions. While both services will automatically replicate data within the same region, geo-replication

  protects you against a regional outage by enabling you to fail over to a secondary region.

  Reference:

  https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas-what-is-overview

  https://docs.microsoft.com/en-us/dotnet/architecture/cloud-native/infrastructure-resiliency-azure

• Question 242:

  HOTSPOT

  You have two Azure virtual machines named VM1 and VM2 that run Windows Server 2019. VM1 and VM2 each host a default Microsoft SQL Server 2019 instance. VM1 contains a database named DB1 that is backed up to a file named D:

  \DB1.bak.

  You plan to deploy an Always On availability group that will have the following configurations:

  1.

  VM1 will host the primary replica of DB1.

  2.

  VM2 will host a secondary replica of DB1.

  You need to prepare the secondary database on VM2 for the availability group.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15

• Question 243:

  HOTSPOT

  You have a 50-TB Microsoft SQL Server database named DB1.

  You need to reduce the time it takes to perform database consistency checks of DB1.

  Which Transact-SQL command should you run? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

• Question 244:

  HOTSPOT

  You have an Azure SQL database named db1 on a server named server1.

  You use Query Performance Insight to monitor db1.

  You need to modify the Query Store configuration to ensure that performance monitoring data is available as soon as possible.

  Which configuration setting should you modify and which value should you configure? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 245:

  HOTSPOT

  You have an Azure SQL database.

  You have a query and the associated execution plan as shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Key Lookup

  The Key Lookup cost is 99% so that is the performance bottleneck.

  Box 2: nonclustered index

  The key lookup on the clustered index is used because the nonclustered index does not include the required columns to resolve the query. If you add the required columns to the nonclustered index, the key lookup will not be required.

• Question 246:

  HOTSPOT

  You have an Azure subscription.

  You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.

  How should you complete the ARM template? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/create-template-quickstart?tabs=azure-powershell

• Question 247:

  HOTSPOT

  You have the following Azure Resource Manager template.

  

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-arm-template-quickstart

• Question 248:

  HOTSPOT

  You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.

  You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service.

  You need to create a backup of DB1 that is accessible to the Azure Database Migration Service.

  What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online

• Question 249:

  HOTSPOT

  You have an Azure SQL database.

  You are reviewing a slow performing query as shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15

• Question 250:

  HOTSPOT

  You are provisioning an Azure SQL database in the Azure portal as shown in the following exhibit.

  

  Use the drop-down menus to select the answer choice that completes each statement based on the information presented in the graphic.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview