Microsoft Microsoft Certifications DP-300 Questions & Answers

• Question 211:

  HOTSPOT

  You have an Azure subscription.

  You need to deploy a logical SQL server by using PowerShell. The solution must ensure that the logical SQL server can create Azure AD users and provide Transparent Data Encryption (TDE) with a customer-managed key.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: -AssignIdentity

  The New-AzSqlServer cmdlet creates an Azure SQL Database server.

  Parameter -AssignIdentity

  Generate and assign an Azure Active Directory Identity for this server for use with key management services like Azure KeyVault.

  Box 2: KeyId

  Parameter -KeyId

  The Azure Key Vault URI that is used for encryption.

  Example: Create a new Azure SQL Database server with TDE CMK

  New-AzSqlServer -ResourceGroupName "ResourceGroup01" -Location "East US" -ServerName "server01" -ServerVersion "12.0" -SqlAdministratorCredentials (Get-Credential) -AssignIdentity -IdentityType "UserAssigned" -

  PrimaryUserAssignedIdentityId "/subscriptions/xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/resourcegroup01/providers/Microsoft.ManagedIdentity/userAssignedIdentities/identity01" -UserAssignedIdentityId "/subscriptions/

  xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx/resourceGroups/resourcegroup01/providers/Microsoft.ManagedIdentity/userAssignedIdentities/identity01" -KeyId https://contoso.vault.azure.net/keys/contosokey/01234567890123456789012345678901

  Reference:

  https://learn.microsoft.com/en-us/powershell/module/az.sql/New-AzSqlServe

• Question 212:

  HOTSPOT

  You have an Azure subscription that contains an Azure SQL managed instance named SQL1.

  You use the Microsoft Power BI service.

  You need to configure connectivity from the Power BI service to SQL1. The solution must ensure that only the Power BI service can initiate connections to SQL1.

  Which type of endpoint should you use for SQL1, and what should you use to control the connections to SQL1? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Public Public endpoint for a managed instance enables data access to your managed instance from outside the virtual network. You're able to access your managed instance from multi-tenant Azure services like Power BI, Azure App Service, or an on-premises network. By using the public endpoint on a managed instance, you don't need to use a VPN, which can help avoid VPN throughput issues.

  Box 2: A network security group Allow public endpoint traffic on the network security group

  1.

  If you have the configuration page of the managed instance still open, navigate to the Overview tab. Otherwise, go back to your SQL managed instance resource. Select the Virtual network/subnet link, which will take you to the Virtual network configuration page.

  2.

  Select the Subnets tab on the left configuration pane of your Virtual network, and make note of the SECURITY GROUP for your managed instance.

  3.

  Go back to your resource group that contains your managed instance. You should see the Network security group name noted above. Select the name to go into the network security group configuration page.

  4.

  Select the Inbound security rules tab, and Add a rule that has higher priority than the deny_all_inbound rule with the following settings:

  * Details omitted *

  Reference:

  https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/public-endpoint-configure

• Question 213:

  HOTSPOT

  You need to use an Azure Resource Manager (ARM) template to deploy an Azure virtual machine that will host a Microsoft SQL Server instance. The solution must maximize disk I/O performance for the SQL Server database and log files.

  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:

  

  Box 1: None

  Set host caching to none for log file disks.

  Box 2: ReadOnly

  Set host caching to read-only for data file disks.

  Do not enable read/write caching on disks that contain SQL Server data or log files.

  Reference: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage

• Question 214:

  HOTSPOT

  You have an on-premises Microsoft SQL Server 2016 instance that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named MI1.

  You plan to perform an online migration of db1 to MI1 by using Azure Database Migration Service.

  You need to create the backups for the migration. The solution must minimize the number of backup files created.

  Which type of backups should you create, and how should you store the backups? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Full and transaction log

  Known issues/migration limitations with online migrations to Azure SQL Managed Instance

  Backup requirements

  Backup media

  Make sure to take every backup on a separate backup media (backup files). Azure Database Migration Service doesn't support backups that are appended to a single backup file. Take full, differential and log backups to separate backup files.

  Box 2: Write each backup to a separate file

  Reference:

  https://learn.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online

• Question 215:

  HOTSPOT

  You have an Azure SQL database named DB1 that contains a table named Table1.

  You run a query to load data into Table1.

  The performance metrics of Table1 during the load operation are 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: perform query tuning

  Implementation considerations

  Use memory-optimized tables for your core transaction tables, that is, the tables with the most performance-critical transactions. Use natively compiled stored procedures to optimize execution of the logic associated with the business

  transaction. The more of the logic you can push down into stored procedures in the database, the more benefit you see from In-Memory OLTP.

  To get started in an existing application:

  Use the transaction performance analysis report to identify the objects you want to migrate.

  Use the memory-optimization and native compilation advisors to help with migration.

  Box 2: an In-Memory OLTP non-durable

  Example: tempdb object replacement

  Use non-durable tables and memory-optimized table types to replace your traditional tempdb based structures, such as temporary tables, table variables, and table-valued parameters (TVPs).

  Memory-optimized table variables and non-durable tables typically reduce CPU and completely remove log IO, when compared with traditional table variables and #temp table.

  Note: In-memory technologies enable you to improve performance of your application, and potentially reduce cost of your database.

  In-Memory OLTP

  In-Memory OLTP technology provides extremely fast data access operations by keeping all data in memory. It also uses specialized indexes, native compilation of queries, and latch-free data-access to improve performance of the OLTP

  workload. There are two ways to organize your In-Memory OLTP data:

  *

  Memory-optimized rowstore format where every row is a separate memory object. This is a classic In-Memory OLTP format optimized for high-performance OLTP workloads. There are two types of memory-optimized tables that can be used in the memory-optimized rowstore format:

  Durable tables (SCHEMA_AND_DATA) where the rows placed in memory are preserved after server restart. This type of tables behaves like a traditional rowstore table with the additional benefits of in-memory optimizations.

  Non-durable tables (SCHEMA_ONLY) where the rows are not-preserved after restart. This type of table is designed for temporary data (for example, replacement of temp tables), or tables where you need to quickly load data before you move

  it to some persisted table (so called staging tables).

  *

  Memory-optimized columnstore format where data is organized in a columnar format

  Reference: https://learn.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/overview-and-usage-scenarios https://learn.microsoft.com/en-us/azure/azure-sql/in-memory-oltp-overview

• Question 216:

  HOTSPOT

  You have an on-premises Microsoft SQL Server 2016 instance that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named Mil.

  You plan to perform an online migration of db1 to MM by using Azure Database Migration Service.

  You need to create the backups for the migration. The solution must minimize the number of backup files created.

  Which type of backups should you create, and how should you store the backups? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

• Question 217:

  HOTSPOT

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

  You need to identify and repair any physical or logical corruption in DB1. The solution must meet the following requirements:

  1.

  Minimize how long it takes to complete the procedure.

  2.

  Minimize data loss.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: REPAIR_REBUILD REPAIR_REBUILD Performs repairs that have no possibility of data loss. This option may include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index.

  Box 2: PHYSICAL_ONLY

  PHYSICAL_ONLY

  Limits the checking to the integrity of the physical structure of the page and record headers and the allocation consistency of the database. This check is designed to provide a small overhead check of the physical consistency of the database,

  but it can also detect torn pages, checksum failures, and common hardware failures that can compromise a user's data.

  Incorrect:

  TABLOCK

  Causes DBCC CHECKDB to obtain locks instead of using an internal database snapshot. This includes a short-term exclusive (X) lock on the database. TABLOCK will cause DBCC CHECKDB to run faster on a database under heavy load,

  but will decrease the concurrency available on the database while DBCC CHECKDB is running.

  EXTENDED_LOGICAL_CHECKS

  If the compatibility level is 100 ( SQL Server 2008) or higher, performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

  Reference:

  https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

• Question 218:

  HOTSPOT

  You have an Azure SQL database.

  You need to identify whether a delayed query execution is associated to a RESOURCE wait.

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

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  Correct Answer:

  

  Box 1: wait_type Determine if a RESOURCE_SEMAHPORE wait is a top wait Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait

  SELECT wait_type, SUM(wait_time) AS total_wait_time_ms FROM sys.dm_exec_requests AS req JOIN sys.dm_exec_sessions AS sess

  ON req.session_id = sess.session_id WHERE is_user_process = 1 GROUP BY wait_type ORDER BY SUM(wait_time) DESC;

  Box 2: sys.dm_exec_requests

  Use the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time.

  Azure SQL RESOURCE_SEMAPHORE wait "wait_time" is_user_process

• Question 219:

  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 SQL

  You need to use PowerShell to enable and configure automated patching for SQL 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.

  Hot Area:

  

  Correct Answer:

  

  Box 1: Set-AzVMSqlServerExtension

  The Set-AzVMSqlServerExtension cmdlet sets the AzureSQL Server extension on a virtual machine.

  Example: Set automatic patching settings on a virtual machine.

  $AutoPatchingConfig = New-AzVMSqlServerAutoPatchingConfig -Enable -DayOfWeek "Thursday" -MaintenanceWindowStartingHour 11 -MaintenanceWindowDuration 120 -PatchCategory "Important"

  Get-AzVM -ResourceGroupName "testrg" -Name "VirtualMachine11" | Set-AzVMSqlServerExtension -AutoPatchingSettings $AutoPatchingConfig | Update-AzVM

  The first command creates a configuration object by using the New-AzVMSqlServerAutoPatchingConfig cmdlet. The command stores the configuration in the $AutoPatchingConfig variable. The second command gets the virtual machine

  named VirtualMachine11 in the Resource Group testrg by using the Get-AzVM cmdlet. The command passes that object to the current cmdlet by using the pipeline operator. The current cmdlet sets the automatic patching settings in

  $AutoPatchingConfig for the virtual machine. The command passes the virtual machine to the Update-AzVM cmdlet.

  Box 2: -Full

  The solution must include both SQL Server and Windows security updates.

  Reference:

  https://learn.microsoft.com/en-us/powershell/module/az.compute/set-azvmsqlserverextension

• Question 220:

  HOTSPOT

  You have a Microsoft SQL Server 2017 server that hosts five databases.

  You Plan to migrate the databases to Azure.

  You need to recommend a solution that meets the following requirements:

  1.

  Automatically scales compute based on the workload demand

  2.

  Provides per-second billing

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

  Hot Area:

  

  Correct Answer:

  

  Box 1: A single Azure SQL database in the serverless computer tier Automatically scales compute based on the workload demand. Provides per-second billing.

  Serverless is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand and bills for the amount of compute used per second.

  Box 2: General Purpose service

  The serverless compute tier is available in the General Purpose service tier and currently in preview in the Hyperscale service tier.

  Reference:

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