Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 231:

  HOTSPOT

  You have a SQL Server on Azure Virtual Machines instance named VM1 that hosts a database named DB1.

  You run the following query.

  

  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:

  

  Box 1: No

  A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups.

  Box 2: No

  Box 3: Yes

  If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log. To avoid an error for an online database, you must use the ... WITH NORECOVERY option of the BACKUP

  Transact-SQL statement.

  Reference:

  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/tail-log-backups-sql-server

  https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server

• Question 232:

  HOTSPOT

  You have two on-premises servers that run Windows Server 2019 and host a Microsoft SQL Server 2017 Always On availability group named AG1. AG1 contains a single database named DB1.

  You have an Azure subscription. The subscription contains a virtual machine named VM1 that runs Linux.

  You need to migrate DB1 to a SQL Server 2019 instance on VM1. The solution must minimize the downtime of DB1 during the migration.

  What should you do? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Add a secondary replica to AG1.

  Migrate an availability group to SQL Server on Azure VM

  To ensure the migrated server is synchronized with the source server, stop the SQL Server service on every replica in the availability group, starting with secondary replicas (in SQL Server Configuration Manager > Services) while ensuring

  the disks hosting SQL data are online.

  Incorrect:

  *

  Upgrade to SQL Server 2019

  No need to upgrade to SQL Server 2019

  *

  Create a SQL Server 2019 Always ON available group on VM1.

  You do that afterwards.

  After the migration, enable Always On availability groups for the database.

  Box 2: Azure Migrate You can migrate your SQL Server Always On availability group to SQL Server on Azure VMs using the Azure Migrate: Server Migration tool. Using the migration tool, you will be able to migrate each replica in the availability group to an Azure VM hosting SQL Server, as well as the cluster metadata, availability group metadata and other necessary high availability components.

  Reference: https://learn.microsoft.com/en-us/azure/azure-sql/migration-guides/virtual-machines/sql-server-availability-group-to-sql-on-azure-vm

• Question 233:

  HOTSPOT

  You have a database on a SQL Server on Azure Virtual Machines instance.

  The current state of Query Store for the database is 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:

  

• Question 234:

  HOTSPOT

  You have an Azure subscription.

  You plan to deploy an Azure SQL database by using an Azure Resource Manager template.

  How should you complete the 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/database/single-database-create-arm-template-quickstart

• Question 235:

  HOTSPOT

  You have an Azure SQL database named db1 that contains an Azure Active Directory (Azure AD) user named user1.

  You need to test impersonation of user1 in db1 by running a SELECT statement and returning to the original execution context.

  How should you complete the Transact-SQL statement? 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/statements/execute-as-transact-sql?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/t-sql/functions/suser-sname-transact-sql?view=sql-server-ver15

• Question 236:

  HOTSPOT

  You have an Azure subscription that contains an Azure SQL database.

  The database fails to respond to queries in a timely manner.

  You need to identify whether the issue relates to resource_semaphore waits.

  How should you complete the Transact-SQL query? 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/database/monitoring-with-dmvs

• Question 237:

  HOTSPOT

  You have an Azure subscription that contains the resources shown in the following table.

  

  You need to create a read-only replica of DB1 and configure the App1 instances to use the replica.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Reference: https://sqlserverguides.com/read-only-replica-azure-sql/

• Question 238:

  HOTSPOT

  You have an Azure SQL managed instance.

  You need to restore a database named DB1 by using Transact-SQL.

  Which 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/statements/restore-statements-transact-sql?view=azuresqldb-mi-currentandpreserve-view=true

• Question 239:

  HOTSPOT

  You have an Azure SQL database.

  You run the following PowerShell script.

  

  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/powershell/module/az.sql/set-azsqldatabasebackupshorttermretentionpolicy?view=azps-7.2.0 https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy?view=azps-7.2.0

• Question 240:

  HOTSPOT

  You configure a long-term retention policy for an Azure SQL database as shown in the exhibit. (Click the Exhibit tab.)

  

  The first weekly backup occurred on January 4, 2020. The dates for the first 10 weekly backups are:

  1.

  January 4, 2020

  2.

  January 11, 2020

  3.

  January 18, 2020

  4.

  January 25, 2020

  5.

  February 1, 2020

  6.

  February 8, 2020

  7.

  February 15, 2020

  8.

  February 22, 2020

  9.

  February 29, 2020 10.March 7, 2020

  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: