DP-300 practice questions 341–350

1. Question 341 · 354 in bank

   HOTSPOT - You have SQL Server on an Azure virtual machine that contains a database named DB1. The database reports a CHECKSUM error. You need to recover the database. How should you complete the statements? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: SINGLE_USER - The specified database must be in single-user mode to use one of the following repair options. Box 2: REPAIR_ALLOW_DATA_LOSS - REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can cause some data loss. Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup. Incorrect Answers: REPAIR_FAST - Maintains syntax for backward compatibility only. No repair actions are performed. Box 3: MULTI_USER - MULTI_USER - All users that have the appropriate permissions to connect to the database are allowed. Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

   Source: Adapted from community discussion — JohnCrawford

   References

   • Microsoft Learn
   • azure.microsoft.com documentation

   

   Next question

2. Question 342 · 354 in bank

   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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   The right answer is: none of the Max Size (MB) The first answer is "none of the" because the Operation Mode (actual) is "Read only" so it is not retaining any query for evaluation due to the Query Store Available is 0.0 MB. When the actual state is read-only, use the readonly_reason column to determine the root cause. Typically, you find that Query Store transitioned to read-only mode because the size quota was exceeded. In that case, the readonly_reason is set to 65536 Consider the following steps to switch Query Store to read-write mode and activate data collection: * Increase the maximum storage size by using the MAX_STORAGE_SIZE_MB option of ALTER DATABASE or * Clean up Query Store data https://learn.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store?view=sql-server-ver16#Verify

   Source: Adapted from community discussion — Fer079

   References

   • Microsoft Learn

   

   Next question

3. Question 343 · 354 in bank

   HOTSPOT - You have a SQL Server on Azure Virtual Machines instance that hosts a database named DB1. You need to configure the autogrow and autoshrink settings for DB1. Which statements should you use? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   answer is correct. To configure the autogrow setting: java Copy code ALTER DATABASE DB1 MODIFY FILE (NAME = DB1_Data, FILEGROWTH = 10%) To configure the autoshrink setting: sql Copy code ALTER DATABASE DB1 SET AUTO_SHRINK ON

   Source: Adapted from community discussion — louisaok

   

   Next question

4. Question 344 · 354 in bank

   HOTSPOT - You have an Azure subscription that contains a resource group named RG1. RG1 contains an instance of SQL Server on Azure Virtual Machines named SQ1. You need to use PowerShell to enable and configure automated patching for SQ1. The solution must include both SQL Server and Windows security updates. How should you complete the command? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Correct: Box 1: Set-AzVMSqlServerExtension, SQL in lowercase. Box 2: Full. SQL IAAS Extension only allows patching automatically in Full mode. Lightway gives a few metrics and NoAgent is for older versions. https://learn.microsoft.com/en-us/powershell/module/az.compute/set-azvmsqlserverextension?view=azps-9.7.0

   Source: Adapted from community discussion — OBIJUAN88

   References

   • Microsoft Learn

   

   Next question

5. Question 345 · 354 in bank

   You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1. You need to configure SQL1 to use mixed mode authentication. Which procedure should you run?

   • Asp_addremotelogin
   • Bxp_instance_regwrite
   • Csp_change_users_login
   • Dxp_grant_login

   Show answer

   Answer: B

   Explanation

   What is mixed mode authentication in SQL? There are two possible modes: Windows Authentication mode and mixed mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled.Feb 28, 2023 Choose an Authentication Mode - SQL Server | Microsoft Learn Microsoft https://learn.microsoft.com › Learn › SQL › SQL Server

   Source: Adapted from community discussion — vcloudpmp

   References

   • Microsoft Learn
   • Microsoft Learn

   Next question

6. Question 346 · 354 in bank

   HOTSPOT - You have an Azure SQL Database managed instance named sqldbmi1 that contains a database name Sales. You need to initiate a backup of Sales. 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:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: TO URL = 'https://storage1.blob.core.windows.net/blob1/Sales.bak' Native database backup in Azure SQL Managed Instance. You can backup any database using standard BACKUP T-SQL command: BACKUP DATABASE tpcc2501 - TO URL = 'https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak' WITH COPY_ONLY - Box 2: COPY_ONLY - Reference: https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-instance/ba-p/386154

   Source: Adapted from community discussion — KNG

   References

   • Microsoft Learn

   

   Next question

7. Question 347 · 354 in bank

   You have the following Transact-SQL query.

   

   Which column returned by the query represents the free space in each file?

   • AColumnA
   • BColumnB
   • CColumnC
   • DColumnD

   Show answer

   Answer: C

   Next question

8. Question 348 · 354 in bank

   HOTSPOT - You have an Azure Data Lake Storage Gen2 account named account1 that stores logs as shown in the following table.

   

   You do not expect that the logs will be accessed during the retention periods. You need to recommend a solution for account1 that meets the following requirements: ✑ Automatically deletes the logs at the end of each retention period ✑ Minimizes storage costs What should you include in the recommendation? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Store the infrastructure logs in the Cool access tier the application logs in the Archive access tier Hot - Optimized for storing data that is accessed frequently. Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days. Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours. Box 2: Azure Blob storage lifecycle management rules Blob storage lifecycle management offers a rich, rule-based policy that you can use to transition your data to the best access tier and to expire data at the end of its lifecycle. Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

   Source: Adapted from community discussion — quermi

   References

   • Microsoft Learn

   

   Next question

9. Question 349 · 354 in bank

   HOTSPOT - You have an Azure Data Lake Storage Gen2 container. Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files. You need to design a data archiving solution that meets the following requirements: ✑ New data is accessed frequently and must be available as quickly as possible. ✑ Data that is older than five years is accessed infrequently but must be available within one second when requested. ✑ Data that us older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible. ✑ Costs must be minimized while maintaining the required availability. How should you manage the data? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point. Hot Area:

   

   This is an interactive task on the real exam — a drag-and-drop, hotspot or ordering question. There is nothing to select here, so the answer is shown below for reference.

   Show the answer figure

   Answer: See figure below

   Explanation

   Box 1: Move to cool storage - The cool access tier has lower storage costs and higher access costs compared to hot storage. This tier is intended for data that will remain in the cool tier for at least 30 days. Example usage scenarios for the cool access tier include: Short-term backup and disaster recovery Older data not used frequently but expected to be available immediately when accessed Large data sets that need to be stored cost effectively, while more data is being gathered for future processing Note: Hot - Optimized for storing data that is accessed frequently. Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days. Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours. Box 2: Move to archive storage - Example usage scenarios for the archive access tier include: ✑ Long-term backup, secondary backup, and archival datasets ✑ Original (raw) data that must be preserved, even after it has been processed into final usable form ✑ Compliance and archival data that needs to be stored for a long time and is hardly ever accessed Reference: https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers

   

   Next question

10. Question 350 · 354 in bank

    You plan to perform batch processing in Azure Databricks once daily. Which type of Databricks cluster should you use?

    • Aautomated
    • Binteractive
    • CHigh Concurrency

    Show answer

    Answer: A

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DP-300 practice, question 341. 354 questions available.