Microsoft DP-300 Exam Practice Test Instant Access

Question 1

You have an Azure Synapse Analytics dedicated SQL pool.

You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table.

Which statement accurately describes the dbo.FactInternetSales table?

AThe table contains less than 10,000 rows.

BAll distributions contain data.

CThe table uses round-robin distribution

DThe table is skewed.

Answer : D

The rows per distribution can vary up to 10% without a noticeable impact on performance. Here the distribution varies more than 10%. It is skewed.

Note: SHOWSPACEUSED displays the number of rows, disk space reserved, and disk space used for a specific table, or for all tables in a Azure Synapse Analytics or Parallel Data Warehouse database.

This is a very quick and simple way to see the number of table rows that are stored in each of the 60 distributions of your database. Remember that for the most balanced performance, the rows in your distributed table should be spread evenly across all the distributions.

ROUND_ROBIN distributed tables should not be skewed. Data is distributed evenly across the nodes by design.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute

https://github.com/rgl/azure-content/blob/master/articles/sql-data-warehouse/sql-data-warehouse-manage-distributed-data-skew.md

Question 2

You have an Azure SQL database named sqldb1.

You need to minimize the possibility of Query Store transitioning to a read-only state.

What should you do?

AHalve the value of Data Flush Interval.

BDouble the value of Statistics Collection Interval.

CHalve the value of Statistics Collection Interval.

DDouble the value of Data Flush Interval.

Answer : B

The Max Size (MB) limit isn't strictly enforced. Storage size is checked only when Query Store writes data to

disk. This interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum

size limit between storage size checks, it transitions to read-only mode.

Incorrect Answers:

C: Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic, expressed in

minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less time to

detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store

Question 3

You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1

to the internet is blocked.

You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.

You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:

Ensure that VM1 cannot connect to any Azure SQL Server other than SqlSrv1.

Restrict network connectivity to SqlSrv1.

What should you create on VNet1?

Aa VPN gateway

Ba service endpoint

Ca private endpoint

Dan ExpressRoute gateway

Answer : C

A private endpoint is a network interface that uses a private IP address from your virtual network. This network interface connects you privately and securely to a service powered by Azure Private Link. By enabling a private endpoint, you're bringing the service into your virtual network.

The service could be an Azure service such as:

Azure Storage

Azure Cosmos DB

Azure SQL Database

Your own service using a Private Link Service.

https://docs.microsoft.com/en-us/azure/private-link/private-endpoint-overview

Question 4

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have an Azure SQL database named Sales.

You need to implement disaster recovery for Sales to meet the following requirements:

During normal operations, provide at least two readable copies of Sales.

Ensure that Sales remains available if a datacenter fails.

Solution: You deploy an Azure SQL database that uses the General Purpose service tier and geo-replication.

Does this meet the goal?

AYes

BNo

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).

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

Question 5

After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

You have an Azure Synapse Analytics dedicated SQL pool that contains a table named Table1.

You have files that are ingested and loaded into an Azure Data Lake Storage Gen2 container named container1.

You plan to insert data from the files into Table1 and transform the data. Each row of data in the files will produce one row in the serving layer of Table1.

You need to ensure that when the source data files are loaded to container1, the DateTime is stored as an additional column in Table1.

Solution: In an Azure Synapse Analytics pipeline, you use a Get Metadata activity that retrieves the DateTime of the files.

Does this meet the goal?

AYes

BNo

Answer : B

Instead use a serverless SQL pool to create an external table with the extra column.

https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/create-use-external-tables

Question 6

You have an Azure subscription that contains a logical SQL server named Serverl. The master database of Serverl contains a user named Userl. You need to ensure that Userl can create databases on Serverl. Which database role should you assign to Userl?

Adb_owner

Bdbmanager

Cdbo

Ddb_ddladmin

Answer : B

Question 7

You have an Azure subscription.

You create a logical SQL server that hosts four databases Each database will be used by a separate customer.

You need to ensure that each customer can access only its own database. The solution must minimize administrative effort

Which two actions should you perform? Each correct answer presents part of the solution

NOTE: Each correct selection is worth one point.

ACreate a network security group (NSG)

BCreate a server-level firewall rule

CCreate a private endpoint

DCreate a database-level firewall rule.

EDeny public access.

Answer : C, D

Microsoft Administering Microsoft Azure SQL Solutions DP-300 Exam Practice Test