DP-300 Online Practice Questions and Answers

Correct Answer: B

Instead deploy an Azure SQL database that uses the Business Critical service tier and Availability Zones.

Note: Premium and Business Critical service tiers leverage the Premium availability model, which integrates compute resources (sqlservr.exe process) and storage (locally attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes creating a three to four-node cluster.

By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW).

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

Correct Answer: D

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

Correct Answer: D

Transactional replication is a feature of Azure SQL Managed Instance and SQL Server that enables you to replicate data from a table in Azure SQL Managed Instance or a SQL Server instance to tables placed on remote databases. This feature allows you to synchronize multiple tables in different databases.D

You can use transactional replication to push changes made in an Azure SQL Managed Instance to:

1.

A SQL Server database - on-premises or on Azure VM

2.

A database in Azure SQL Database

3.

An instance database in Azure SQL Managed Instance

Incorrect:

Not A: Operates within the same location.

Database mirroring is a solution for increasing the availability of a SQL Server database.

Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations.

Not B: Always On availability group on SQL Server on Azure VMs - only within a single region

Always On availability groups on Azure Virtual Machines are similar to Always On availability groups on-premises, and rely on the underlying Windows Server Failover Cluster.

VM redundancy

To increase redundancy and high availability, SQL Server VMs should either be in the same availability set, or different availability zones.

Placing a set of VMs in the same availability set protects from outages within a data center caused by equipment failure (VMs within an Availability Set do not share resources) or from updates (VMs within an availability set are not updated at

the same time).

Availability Zones protect against the failure of an entire data center, with each Zone representing a set of data centers within a region.

Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/replication-transactional-overview

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/availability-group-overview

Correct Answer: BE

Correct Answer: A

Benefits of using user-assigned managed identities (UMI) There are several benefits of using UMI as a server identity.

*

User flexibility to create and maintain their own user-assigned managed identities for a given tenant. UMI can be used as server identities for Azure SQL. UMI is managed by the user, compared to an SMI, which identity is uniquely defined per server, and assigned by the system.

*

Users can choose a specific UMI to be the server or instance identity for all SQL Databases or Managed Instances in the tenant, or have multiple UMIs assigned to different servers or instances. For example, different UMIs can be used in different servers representing different features. For example, a UMI serving transparent data encryption in one server, and a UMI serving Azure AD authentication in another server.

*

Etc Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity?view=azuresql

Correct Answer:

Box 1: Key Lookup

The Key Lookup cost is 99% so that is the performance bottleneck.

Box 2: nonclustered index

The key lookup on the clustered index is used because the nonclustered index does not include the required columns to resolve the query. If you add the required columns to the nonclustered index, the key lookup will not be required.

Correct Answer:

Explanation:

Box 1: Start-AzAutomationRunBook

The Start-AzAutomationRunbook cmdlet starts an Azure Automation runbook job. Specify the ID or name of a runbook.

Syntax:

Start-AzAutomationRunbook [-Name] [-Parameters ] [-RunOn ] [-Wait] [-MaxWaitSeconds ] [-ResourceGroupName] [-AutomationAccountName] [-DefaultProfile ] []

Example: Start a runbook job

Start-AzAutomationRunbook -AutomationAccountName "Contoso17" -Name "Runbk01" -ResourceGroupName "ResourceGroup01"

This command starts a runbook job for the runbook named Runbk01 in the Azure Automation account named Contoso17.

Incorrect:

* ADAccount Box 2: -AutomationAccountName Reference:

https://learn.microsoft.com/en-us/powershell/module/az.automation/start-azautomationrunbook

Correct Answer:

Azure Database Migration service

Box 1: Premium 4-VCore

Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.

Minimize downtime during the migration of the SERVER1 databases.

Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations, and faster migration speeds.

Incorrect Answers:

The Standard pricing tier suits most small- to medium- business workloads, but it supports offline migration only.

Box 2: A VPN gateway

You need to create a Microsoft Azure Virtual Network for the Azure Database Migration Service by using the Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises source servers by using

either ExpressRoute or VPN.

Reference:

https://azure.microsoft.com/pricing/details/database-migration/

https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online

Correct Answer:

Box 1: Hash

Scenario:

Ensure that queries joining and filtering sales transaction records based on product ID complete as quickly as possible.

A hash distributed table can deliver the highest query performance for joins and aggregations on large tables.

Box 2: Round-robin

Scenario:

You plan to create a promotional table that will contain a promotion ID. The promotion ID will be associated to a specific product. The product will be identified by a product ID. The table will be approximately 5 GB.

A round-robin table is the most straightforward table to create and delivers fast performance when used as a staging table for loads. These are some scenarios where you should choose Round robin distribution:

When you cannot identify a single key to distribute your data.

If your data doesn't frequently join with data from other tables.

When there are no obvious keys to join.

Incorrect Answers:

Replicated: Replicated tables eliminate the need to transfer data across compute nodes by replicating a full copy of the data of the specified table to each compute node. The best candidates for replicated tables are tables with sizes less than

2 GB compressed and small dimension tables.

Reference: https://rajanieshkaushikk.com/2020/09/09/how-to-choose-right-data-distribution-strategy-for-azure-synapse/

Correct Answer:

Follow these same steps to create striped virtual disk:

1.

Create Log Storage Pool.

2.

Create Virtual Disk

3.

Create Volume Box 1: a storage pool Box 2: a virtual disk that uses stripe layout

Disk Striping: Use multiple disks and stripe them together to get a combined higher IOPS and Throughput limit. The combined limit per VM should be higher than the combined limits of attached premium disks.

Box 3: a volume

Reference:

https://hanu.com/hanu-how-to-striping-of-disks-for-azure-sql-server/