DP-300 Online Practice Questions and Answers

Correct Answer: B

There is no REPLACE option in Azure SQL Database, or even there is no restore option with T-SQL. https://learn.microsoft.com/en-us/azure/azure-sql/database/recovery-using-backups?view=azuresql-dbandtabs=azure-portal

Correct Answer: D

The Watermark delay metric is computed as the wall clock time of the processing node minus the largest watermark it has seen so far. The watermark delay metric can rise due to:

1.

Not enough processing resources in Stream Analytics to handle the volume of input events.

2.

Not enough throughput within the input event brokers, so they are throttled.

3.

Output sinks are not provisioned with enough capacity, so they are throttled.

Reference: https://docs.microsoft.com/en-us/azure/stream-analytics/stream-analytics-time-handling

Correct Answer: C

Serverless SQL pool can automatically synchronize metadata from Apache Spark. A serverless SQL pool database will be created for each database existing in serverless Apache Spark pools.

For each Spark external table based on Parquet and located in Azure Storage, an external table is created in a serverless SQL pool database. As such, you can shut down your Spark pools and still query Spark external tables from serverless SQL pool.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-spark-tables

Correct Answer: C

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-overview

Correct Answer: C

Active geo-replication is a feature that lets you to create a continuously synchronized readable secondary database for a primary database. The readable secondary database may be in the same Azure region as the primary, or, more commonly, in a different region. This kind of readable secondary databases are also known as geo-secondaries, or geo-replicas.

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview

Correct Answer: B

In-place change of SQL Server edition on Azure VM

The edition of SQL Server is determined by the product key, and is specified during the installation process using the installation media. The edition dictates what features are available in the SQL Server product. You can change the SQL

Server edition with the installation media and either downgrade to reduce cost or upgrade to enable more features.

Downgrade an edition

To downgrade the edition of SQL Server, you need to completely uninstall SQL Server, and reinstall it again with the desired edition setup media.

1.

Back up all databases, including the system databases.

2.

Move system databases (master, model, and msdb) to a new location.

3.

Completely uninstall SQL Server and all associated services.

4.

Restart the virtual machine.

5.

Install SQL Server by using the media with the desired edition of SQL Server.

6.

Install the latest service packs and cumulative updates.

7.

Replace the new system databases that were created during installation with the system databases that you previously moved to a different location.

8.

After the SQL Server edition is downgraded, modify the edition property of the SQL Server virtual machine in the Azure portal. This will update the metadata and billing associated with this VM.

Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/change-sql-server-edition

Correct Answer: C

az sql db create Create a database

Syntax: az sql db create --name --resource-group --server

Plus many additonal optional parameters.

Required Parameters

--name -n

Name of the Azure SQL Database.

--resource-group -g

Name of resource group. You can configure the default group using az configure --defaults group=.

--server -s

Name of the Azure SQL Server. You can configure the default using az configure --defaults sql-server=.

Example:

echo "Creating $database on $server..."

az sql db create --resource-group $resourceGroup --server $server --name $database --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2 --zone-redundant true # zone redundancy is only supported on

premium and business critical service tiers

Reference:

https://learn.microsoft.com/en-us/cli/azure/sql/db

Correct Answer: A

Explanation:

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). The routing to a specific gateway ring is controlled by Azure Traffic Manager

(ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can

make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business

Critical databases or pools to the zone redundant configuration.

Incorrect Answers:

Note:

There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

Azure SQL Database Hyperscale

Azure SQL Database Basic

Azure SQL Database managed instance General Purpose

Reference:

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

Correct Answer:

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-arm-template-quickstart

Correct Answer:

Box 1: event_file

SQL Server Extended Events tips

Example:

We can obtain information about the active event sessions, with the help of the sys.dm_xe_sessions dynamic management view. At the same time, this view returns information about the dropped event and buffer counts. To understand this

concept more clearly, we will create two different extended event sessions. The first one is retention mode is Multiple event loss and the second one is Single event loss. These events capture the locks and SQL statement executions.

CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER

ADD EVENT sqlserver.lock_acquired,

ADD EVENT sqlserver.lock_released,

ADD EVENT sqlserver.lock_request_priority_state,

ADD EVENT sqlserver.query_pre_execution_showplan,

ADD EVENT sqlserver.sql_statement_completed,

ADD EVENT sqlserver.sql_statement_starting

ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')

SQL Server reserves buffer memory for the event sessions to store the event data temporarily and then dispatch them to the target storage. In some cases, the extended event sessions generate data incredibly fast. However, the collected

event data can not be dispatched to the event storage as fast as the event data generation speed. To overcome these types of cases, the EVENT_RETENTION_MODE setting allows tolerance to be set for data loss when the extended event

is under pressure. So that, the event session impact on the database engine performance is minimized.

ALLOW_SINGLE_EVENT_LOSS: This option is the default option and allows single events to be dropped and lost from the session when the event Session memory buffer is full and can not be sent to the storage target.

ALLOW_MULTIPLE_EVENT_LOSS: This option affects the performance minimally and allows the entire memory buffer containing multiple events to be dropped and lost when the memory buffers are full.

NO_EVENT_LOSS: When we use this option, we can ensure that no event data can be lost but it causes to degrade database engine performance.

Example continued:

ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel')

WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,

TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

Box 2: ALLOW_MULTIPLE_EVENT_LOSS

SQL Server Extended Events EVENT_RETENTION_MODE option

We want to minimize the performance impact of capturing extended events.

Reference:

https://www.sqlshack.com/sql-server-extended-events-tips/