DP-300 practice questions 31–40

1. Question 31 · 354 in bank

   You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime. What should you do?

   • AConfigure Transaction Log Shipping.
   • BImplement Always On availability groups.
   • CConfigure transactional replication.
   • DImport a BACPAC.

   Show answer

   Answer: C

   Explanation

   Ans : C There are two primary methods for migrating a SQL Server 2005 or later database to Azure SQL Database. The first method is simpler but requires some, possibly substantial, downtime during the migration. The second method is more complex, but substantially eliminates downtime during the migration. Method 1: Migration with downtime during the migration Method 2: Use Transactional Replication Ref : https://docs.microsoft.com/en-us/azure/azure-sql/database/migrate-to-database-from-sql-server?view=azuresql#method-1-migration-with-downtime-during-the-migration

   Source: Adapted from community discussion — LanPlanche

   References

   • Microsoft Learn

   Next question

2. Question 32 · 354 in bank

   You have an Azure SQL database named DB1. You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled. During a database audit, you discover that none of the fields contain more than 150 characters. You need to ensure that you can apply page compression to Table1. What should you do?

   • AConfigure the columns as sparse.
   • BChange the column type to NVARCHAR(MAX).
   • CChange the column type to VARCHAR(MAX).
   • DChange the column type to VARCHAR(200).

   Show answer

   Answer: D

   Explanation

   D for sure - Changing the column type to VARCHAR(200) adjusts the column to a variable-length data type. Since the data never exceeds 150 characters, you can safely reduce the maximum length to 200.

   Source: Adapted from community discussion — bingomutant

   References

   • Microsoft Learn

   Next question

3. Question 33 · 354 in bank

   You have an on-premises Microsoft SQL Server named SQL1 that hosts five databases. You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss. What should you use?

   • AAlways On availability groups
   • BBackup and Restore
   • Clog shipping
   • DDatabase Migration Assistant

   Show answer

   Answer: B

   Explanation

   DMA does not support database migrations to Azure SQL Managed Instance. Recommendation is to use the Azure SQL migration extension for Azure Data Studio, which supports both online and offline database migrations to Azure SQL Managed Instance, but here we don´t have the option "Azure SQL migration extension for Azure Data Studio". https://learn.microsoft.com/en-us/sql/dma/dma-overview?view=sql-server-ver16#capabilities Regarding log shipping to Managed Instance is not possible; it only supports the restore of full backups. https://dba.stackexchange.com/questions/232332/is-it-possible-to-log-ship-from-on-premise-sql-server-to-azure-sql-managed-insta So the only option that we can use is Backup and Restore

   Source: Adapted from community discussion — Fer079

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

4. Question 34 · 354 in bank

   You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features. ✑ Clustered columnstore indexes ✑ Automatic tuning ✑ Change tracking ✑ PolyBase You plan to migrate DB1 to an Azure SQL database. What feature should be removed or replaced before DB1 can be migrated?

   • AClustered columnstore indexes
   • BPolyBase
   • CChange tracking
   • DAutomatic tuning

   Show answer

   Answer: B

   Explanation

   Polybase has many features and installs differently on different variants of SQL server. The table shows that SQL Database supports one of the features meaning it does partially supports Polybase. Probably, the issue here is that Azure SQL Database has its own Polybase which is different than on-premises Polybase and this could create some errors during migration

   Source: Adapted from community discussion — Backy

   References

   • Microsoft Learn

   Next question

5. Question 35 · 354 in bank

   You have an Azure subscription that contains an Azure SQL database. The database contains a table named tablet that uses partitioned columnstores. You need to configure table1 to meet the following requirements: • Each partition must be compressed. • The compression ratio must be maximized. • You must be able to index the compressed data. What should you use?

   • Apage compression
   • Bcolumnstore compression
   • CGZIP compression
   • Dcolumnstore archival compression

   Show answer

   Answer: D

   Explanation

   My answer: D Used below code to test if nonclustered index can be created on a table that has partitioned clustered columnstore index with archival compression: CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE RIGHT FOR VALUES (1, 2, 3) ; CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 ALL TO ('PRIMARY') ; GO create table Test_Columnstore_Archival ( ID int, tName varchar(5)) on myRangePS1 (ID) insert into Test_Columnstore_Archival (ID, tName) values (1,'aaa'),(2,'bbb'),(3,'ccc') create clustered columnstore index CCIX_ID on Test_Columnstore_Archival order (ID) with (data_compression=columnstore_archive) create nonclustered index IX_tName on Test_Columnstore_Archival (tName) with (data_compression=page) select * from Test_Columnstore_Archival exec sp_BLitzIndex @databasename = 'MyDB', @tablename = 'Test_Columnstore_Archival'

   Source: Adapted from community discussion — voodoo_sh

   References

   • Microsoft Learn

   Next question

6. Question 36 · 354 in bank

   You have an Azure subscription linked to an Azure Active Directory (Azure AD) tenant. The subscription contains 10 virtual machines that run Windows Server 2019 and host Microsoft SQL Server 2019 instances. You need to ensure that you can manage the SQL Server instances by using a single user account. What should you do first?

   • AEnable a user-assigned managed identity on each virtual machine.
   • BDeploy an Azure Active Directory Domain Services (Azure AD DS) domain and join the virtual machines to the domain.
   • CEnable a system-assigned managed identity on each virtual machine.
   • DJoin the virtual machines to the Azure AD tenant.

   Show answer

   Answer: B

   Explanation

   According to the article below we can use managed identity on azure vm only on SQL Server 2022. In the question is 2019, so the answer is B. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/configure-azure-ad-authentication-for-sql-vm?view=azuresql&tabs=azure-portal

   Source: Adapted from community discussion — Adamus2077

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

7. Question 37 · 354 in bank

   DRAG DROP - You have an Azure subscription. You plan to deploy a new Azure virtual machine that will host a Microsoft SQL Server instance. You need to configure the disks on the virtual machine. The solution must meet the following requirements: • Minimize latency for transaction logs. • Minimize the impact on IO throughput of the virtual machine. Which type of disk should you use for each workload? To answer, drag the appropriate disk types to the correct workloads. Each disk type may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content. 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

   According this article: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql "For the log drive ... If submillisecond storage latency is required, use Azure ultra disk" - so the answer is correct "Place tempdb on the local ephemeral SSD (default D:\) drive for most SQL Server workloads" - thus the answer should be perhaps "local" ?

   Source: Adapted from community discussion — licna

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   

   Next question

8. Question 38 · 354 in bank

   Select 3

   You have an Azure SQL Database elastic pool that contains 10 databases. You receive the following alert. Msg 1132, Level 16, state 1, Line 1 The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (76800) MBs. You need to resolve the alert. The solution must minimize administrative effort. Which three actions can you perform? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

   • AIncrease the maximum storage of the elastic pool.
   • BDelete data from a database.
   • CRemove a database from the pool.
   • DEnable data compression.
   • EShrink individual databases.

   Show answer

   Answer: ADE

   Explanation

   To resolve the storage limit alert for your Azure SQL Database elastic pool, you can: A. Increase the maximum storage limit of the elastic pool through Azure's management tools, which is a quick fix requiring minimal administrative effort. D. Enable data compression to reduce the storage size of tables and indexes, which can provide space savings without data loss. E. Shrink individual databases to release unused space back to the pool, although this should be done carefully to avoid performance issues due to fragmentation. Options B and C are less desirable as they involve deleting data or databases, which requires more effort and could impact your applications. Always consider the long-term implications and monitor storage after making changes to prevent future issues.

   Source: Adapted from community discussion — [Removed]

   References

   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn
   • Microsoft Learn

   Next question

9. Question 39 · 354 in bank

   You have an Azure subscription. You need to deploy a new Azure SQL database by using Azure Command-Line Interface (CLI). Which three parameters are required?

   • A--name, --edition, and --capacity
   • B--name, --tier, and --min-capacity
   • C--name, --resource-group, and --server
   • D--name, --licence-type, and --capacity

   Show answer

   Answer: C

   Explanation

   Answer is correct. When deploying the resource, obviously it needs a name. You must also define where to deploy it (resource group). And you also need to define the "type" of SQL resource (--server)

   Source: Adapted from community discussion — igorclapa

   References

   • Microsoft Learn

   Next question

10. Question 40 · 354 in bank

    HOTSPOT - You have an Azure subscription. You plan to migrate 10 on-premises Microsoft SQL Server instances to Azure. You need to ensure that the migrated environment can be managed by using multiserver administration and supports master/target (MSX/TSX) jobs. The solution must minimize administrative effort. Which SQL deployment options should you select as the master server (MSX) and the target server (TSX)? 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

    On Azure SQL Managed Instance, The Multi Server Administration feature for master/target (MSX/TSX) jobs are not supported. as per info from below Microsoft URL. SO, I think answer is Azure Virtual machines for MSX and TSX setup. https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-multiserver-environment?view=sql-server-ver16 https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server?view=azuresql

    Source: Adapted from community discussion — bsk1983

    References

    • Microsoft Learn
    • Microsoft Learn

    

    Next question

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

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