Free DP-300 Exam Braindumps
Administering Microsoft Azure SQL Solutions Questions 211–215 (page: 44)

Updated On: 10-Feb-2026

SIMULATION

You need to rebuild the index for PK_Address_AddressID in the SalesLT.Address table in db1. The solution must meet the following requirements:

Ensure that 30 percent of the index page is reserved for future updates and inserts.

Minimize page splits.

To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.

  1. See Explanation section for answer.

Answer(s): A

Explanation:

Note: The easiest way to fix page splits is to set a fill factor value (for instance, 90) when the offending index is rebuilt. If a fill factor is not set, SQL Server uses the server-level default, which is 100 (this default can be changed, however).

Reorganize or rebuild an index in SQL Server Management Studio

Step 1: In Object Explorer, Expand the database [Here db1] that contains the table on which you want to reorganize an index.

Step 2: Expand the Tables folder.

Step 3: Expand the table [Here: SalesLT.Address] on which you want to reorganize an index.

Step 4: Expand the Indexes folder.

Step 5: Right-click the index [Here for PK_Address_AddressID] with the fill factor that you want to specify and select Properties.

5a) Under Select a page, select Options.

5b) In the Fill factor row, enter the fill factor that you want. [Here: 70%]



HOTSPOT (Drag and Drop is not supported)

You have an Azure virtual machine named Server1 that has Microsoft SQL Server installed. Server1 contains a database named DB1.

You have a logical SQL server named ASVR1 that contains an Azure SQL database named ADB1.

You plan to use SQL Data Sync to migrate DB1 from Server1 to ASVR1.

You need to prepare the environment for the migration. The solution must ensure that the connection from Server1 to ADB1 does NOT use a public endpoint.

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

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: Azure Private link
Sync data by using

Private link for Data Sync
The new private link feature allows you to choose a service managed private endpoint to establish a secure connection between the sync service and your member/hub databases during the data synchronization process. A service managed private endpoint is a private IP address within a specific virtual network and subnet. Within Data Sync, the service managed private endpoint is created by Microsoft and is exclusively used by the Data Sync service for a given sync operation.

Box 2: Snapshot isolation
Set the database isolation level to

General requirements
* Snapshot isolation must be enabled for both Sync members and hub. F
* Etc.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database



HOTSPOT (Drag and Drop is not supported)

You have an Azure SQL managed instance named Server1 and an Azure Blob Storage account named storage1 that contains Microsoft SQL Server database backup files.

You plan to use Log Replay Service to migrate the backup files from storage1 to Server1. The solution must use the highest level of security when connecting to storage1.

Which PowerShell cmdlet should you run, and which parameter should you specify to secure the connection? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:





Box 1: start-azsqlinstancedatabaselogreplay
Cmdlet

Log Replay Service with Azure SQL Managed Instance

You can start the service with PowerShell (start-azsqlinstancedatabaselogreplay) or the Azure CLI (az_sql_midb_log_replay_start cmdlets). Choose between autocomplete or continuous migration mode.

The Start-AzSqlInstanceDatabaseLogReplay cmdlet initiate start of the log replay service.

Box 2: ManagedIdentity
Parameter

* ManagedIdentity
Example
Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01" -Name "ManagedDatabaseName" `
-StorageContainerIdentity ManagedIdentity -StorageContainerUri "https://test.blob.core.windows.net/testing"

Starts a Log Replay service with container identity specified as ManagedIdentity. In this case there is no need to specify StorageContainerSasToken parameter.

Incorrect:
* SharedAccessSignature

Limitations of LRS
Consider the following limitations of LRS:

* The shared access signature (SAS) token that LRS uses must be generated for the entire Azure Blob Storage container, and it must have Read and List permissions only. For example, if you grant Read, List, and Write permissions, LRS won't be able to start because of the extra Write permission.

* Etc.

Example
Start-AzSqlInstanceDatabaseLogReplay -ResourceGroupName "ResourceGroup01" -InstanceName "ManagedInstance01" -Name "ManagedDatabaseName" `
-StorageContainerIdentity SharedAccessSignature -StorageContainerUri "https://test.blob.core.windows.net/ testing" `
-StorageContainerSasToken "sv=2019-02-02&ss=b&srt=sco&sp=rl&se=2023-12-02T00:09:14Z&st=2019- 11-25T16:09:14Z&spr=https&sig=92kAe4QYmXaht%2Fgjocqwerqwer41s%3D"

Starts a Log Replay service with container identity specified as SharedAccessSignature.
StorageContainerSasToken parameter is required.


Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/log-replay-service-overview https://learn.microsoft.com/en-us/powershell/module/az.sql/start-azsqlinstancedatabaselogreplay



HOTSPOT (Drag and Drop is not supported)

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

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

You need to perform the following actions:

Sync DB1 with SQLDB1 by using SQL Data Sync.

Configure an index in DB1 by using CREATE INDEX.

Add data to DB1 at regular intervals by using BULK INSERT.

Which option should you specify when you use BULK INSERT, and which parameter should you specify when you use CREATE INDEX? To answer, select the appropriate options in the answer area.

Note: Each correct selection is worth one point.

Hot Area:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






Using ROWS_PER_BATCH with BULK INSERT helps optimize the data loading process by indicating how many rows constitute a batch. By specifying ROWS_PER_BATCH, you can improve the performance of bulk data loading operations, which is especially beneficial when adding large amounts of data at regular intervals. It can reduce the amount of logging and improve the speed of the operation by allowing SQL Server to handle data in manageable chunks.

Specifying UNIQUE with CREATE INDEX ensures that the index enforces uniqueness on the specified column (s), which is essential if you want to prevent duplicate entries in those columns. By ensuring uniqueness, this parameter helps maintain the quality and integrity of the data in DB1, which is important for reliable database operations and queries that depend on the indexed data.



HOTSPOT (Drag and Drop is not supported)

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

You execute the following T-SQL statements against DB1.



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:

  1. See Explanation section for answer.

Answer(s): A

Explanation:






The NCHAR(10) data type is a fixed-length Unicode character data type. Each character in NCHAR takes 2 bytes because it uses UTF-16 encoding. Therefore, NCHAR(10) will always occupy 20 bytes on disk (2 bytes per character for 10 characters), not 10 bytes. However, the statement is asking if each field occupies 10 bytes, which is misleading; it should be 20 bytes.

The statement "ALTER TABLE table1 REBUILD WITH (DATA_COMPRESSION = ROW);" specifies row-level compression, not dictionary compression. Row compression reduces the size of the data by storing fixed- length data types in variable-length storage format and by reducing metadata overhead. Dictionary compression is a feature of page compression, not row compression.

Column1 (TINYINT): Supports compression. Row compression can store this as a smaller size if possible. Column2 (VARCHAR(255)): Supports compression. Row compression can store only the used length of the string. Column4 (BINARY(50)): Supports compression. Row compression can store binary data more efficiently by removing unnecessary padding.



Viewing page 44 of 85
Viewing questions 216 - 220 out of 470 questions


Post your Comments and Discuss Microsoft DP-300 exam prep with other Community members:

AI Tutor AI Tutor 👋 I’m here to help!