Boost Your Performance in the Microsoft DP-300 Exam with Practice Tests

Question #1

DRAG DROP (Drag and Drop is not supported)You are building an Azure virtual machine.You allocate two 1-TiB, P30 premium storage disks to the virtual machine. Each disk provides 5,000 IOPS.You plan to migrate an on-premises instance of Microsoft SQL Server to the virtual machine. The instance has a database that contains a 1.2-TiB data file. The database requires 10,000 IOPS.You need to configure storage for the virtual machine to support the database.Which three objects should you create in sequence? To ans

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #2

You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server.You need to ensure that each customer can only connect to and access their respective database. Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. ee Explanation section for answer

View answer

Correct Answer: BC

Question #3

You have an Azure SQL database named DB1.You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.Which deployment option and pricing tier should you configure?

A. zure SQL Database Premium

B. zure SQL Database serverless

C. zure SQL Database managed instance Business Critical

D. zure SQL Database Standard

View answer

Correct Answer: A

Question #4

You have an Azure subscription.You need to deploy an instance of SQL Server on Azure Virtual Machines. The solution must meet the following requirements:? Custom performance configuration, such as IOPS, capacity, and throughout, must be supported.? Costs must be minimized.Which type of disk should you include in the solution?

A. Premium SSD v2

B. Premium SSD

C. Standard SSD

D. Ultra SSD

View answer

Correct Answer: A

Question #5

You have an Azure subscription that contains an Azure SQL database. The database contains a table named tablet.You execute the following Transact-SQL statements.You need to reduce the time it takes to perform analytic queries on the database.Which configuration should you enable?

A. OW_ MODE_MEMORY GRANT_FEEDBACK

B. ATCH MODE_MEMORY GRAIJT_FEEDBACK

C. ATCH_MODE_ADAPTIVE_TOIMS

D. ATC H_MODE_ON_ROWSTOR E

View answer

Correct Answer: D

Question #6

You are designing an enterprise data warehouse in Azure Synapse Analytics that will contain a table named Customers. Customers will contain credit card information.You need to recommend a solution to provide salespeople with the ability to view all the entries in Customers. The solution must prevent all the salespeople from viewing or inferring the credit card information.What should you include in the recommendation?

A. row-level security

B. data masking

C. Always Encrypted

D. column-level security

View answer

Correct Answer: B

Question #7

DRAG DROP (Drag and Drop is not supported)You need to implement statistics maintenance for SalesSQLDb1. The solution must meet the technical requirements.Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #8

You have an Azure SQL database named DB1.You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.Which deployment option and pricing tier should you configure?

A. zure SQL Database managed instance General Purpose

B. zure SQL Database Hyperscale

C. zure SQL Database Premium

D. zure SQL Database managed instance Business Critical

View answer

Correct Answer: C

Question #9

You are developing an application that uses Azure Data Lake Storage Gen 2.You need to recommend a solution to grant permissions to a specific application for a limited time period.What should you include in the recommendation?

A. role assignments

B. account keys

C. shared access signatures (SAS)

D. Azure Active Directory (Azure AD) identities

View answer

Correct Answer: C

Question #10

DRAG DROP (Drag and Drop is not supported)You create an Azure SQL managed instance and a job that performs backups.You need to configure the job to notify a distribution group by email when the job fails. The solution must minimize administrative effort.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.NOTE: More than one order of answer choices is correct. You will receive credit for

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #11

DRAG DROP (Drag and Drop is not supported)You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures.You need to ensure that SQL Server Agent jobs running on SQL1 can notify administrators when a failure occurs.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #12

You have an Azure SQL managed instance.You need to enable SQL Agent Job email notifications.What should you do?

A. se the Agent XPs option

B. nable the SQL Server Agent

C. un the sp_configure command

D. un the sp_set_agent_properties command

View answer

Correct Answer: C

Question #13

You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.You scale up the virtual machine to 8 vCPUs and 64 GB of memory.You need to reduce tempdb contention without negatively affecting server performance.What is the number of secondary data files that you should configure for tempdb?

A.

B.

C.

D. 4

View answer

Correct Answer: C

Question #14

HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database named db1.You need to retrieve the resource usage of db1 from the last week.How should you complete the statement? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #15

You have the following Azure Data Factory pipelines:-Ingest Data from System1-Ingest Data from System2-Populate Dimensions-Populate FactsIngest Data from System1 and Ingest Data from System2 have no dependencies. Populate Dimensions must execute after Ingest Data from System1 and Ingest Data from System2. Populate Facts must execute after the Populate Dimensions pipeline. All the pipelines must execute every eight hours.What should you do to schedule the pipelines for execution?

A. dd a schedule trigger to all four pipelines

B. dd an event trigger to all four pipelines

C. reate a parent pipeline that contains the four pipelines and use an event trigger

D. reate a parent pipeline that contains the four pipelines and use a schedule trigger

View answer

Correct Answer: D

Question #16

DRAG DROP (Drag and Drop is not supported)You have an Azure SQL managed instance named SQLMI1 that has Resource Governor enabled and is used by two apps named App1 and App2.You need to configure SQLMI1 to limit the CPU and memory resources that can be allocated to App1.Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #17

You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.You scale up the virtual machine to 8 vCPUSs and 64 GB of memory. You need to provide the lowest latency for tempdb.What is the total number of data files that tempdb should contain?

A.

B.

C.

D. 4

View answer

Correct Answer: C

Question #18

HOTSPOT (Drag and Drop is not supported)You have SQL Server on an Azure virtual machine that contains a database named Db1. You need to enable automatic tuning for Db1.How should you complete the statements? To answer, select the appropriate answer in the answer area.NOTE: Each correct selection is worth one point.Hot Area:

A. ARAMETERIZATION to FORCED

B. ARAMETERIZATION to SIMPLE

C. elayed Durability to Forced

D. EAD_COMMITTED_SNAPSHOT to ON

View answer

Correct Answer: A

Question #19

You have an Azure SQL database named db1 on a server named server1. You need to modify the MAXDOP settings for db1. What should you do?

A. onnect to db1 and run the sp_configure command

B. onnect to the master database of server1 and run the sp_configure command

C. onfigure the extended properties of db1

D. odify the database scoped configuration of db1

View answer

Correct Answer: D

Question #20

You have an Azure SQL database. You identify a long running query.You need to identify which operation in the query is causing the performance issue.What should you use to display the query execution plan in Microsoft SQL Server Management Studio (SSMS)?

A. uery Store

B. etrics

C. uery Performance Insight

D. lerts

View answer

Correct Answer: A

Question #21

You have an Azure SQL Database managed instance named SQLMI1. A Microsoft SQL Server Agent job runs on SQLMI1.You need to ensure that an automatic email notification is sent once the job completes. What should you include in the solution?

A. rom SQL Server Configuration Manager (SSMS), enable SQL Server Agent

B. rom SQL Server Management Studio (SSMS), run sp_set_sqlagent_properties

C. rom SQL Server Management Studio (SSMS), create a Database Mail profile

D. rom the Azure portal, create an Azure Monitor action group that has an Email/SMS/Push/Voice action

View answer

Correct Answer: C

Question #22

You plan to move two 100-GB databases to Azure.You need to dynamically scale resources consumption based on workloads. The solution must minimize downtime during scaling operations.What should you use?

A. n Azure SQL Database elastic pool

B. QL Server on Azure virtual machines

C. n Azure SQL Database managed instance

D. zure SQL databases

View answer

Correct Answer: A

Question #23

You have SQL Server on an Azure virtual machine that contains a database named DB1. You have an application that queries DB1 to generate a sales report.You need to see the parameter values from the last time the query was executed.Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. ee Explanation section for answer

View answer

Correct Answer: BC

Question #24

HOTSPOT (Drag and Drop is not supported)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.

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #25

You have an Azure SQL Database managed instance.The instance starts experiencing performance issues.You need to identify which query is causing the issue and retrieve the execution plan for the query. The solution must minimize administrative effort.What should you use?

A. QL Profiler

B. xtended Events

C. uery Store

D. ynamic management views

View answer

Correct Answer: D

Question #26

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 two Azure SQL Database servers named Server1 and Server2. Each server c

A. es

B. o

View answer

Correct Answer: B

Question #27

You have an instance of SQL Server on Azure Virtual Machines named SQL1.SQL1 contains an Extended Events session named session! that captures Microsoft SQL Server events.You need to correlate the session events with events captured by Event Tracing for Windows (ETW).What should you do for session1?

A. odify the Set Session Event Filters settings

B. dd a target

C. dd an action

D. odify the Specify Session Data Storage settings

View answer

Correct Answer: B

Question #28

You need to trigger an Azure Data Factory pipeline when a file arrives in an Azure Data Lake Storage Gen2 container.Which resource provider should you enable?

A. icrosoft

B. icrosoft

C. icrosoft

D. icrosoft

View answer

Correct Answer: B

Question #29

You have an Azure SQL database.You need to implement a disaster recovery solution that meets the following requirements:•Minimizes how long it takes to recover the database if a datacenter fails•Minimizes administrative effortWhat should you include in the solution?

A. zure Site Recovery

B. ctive geo-replication

C. uto-failover groups

D. zure Backup

View answer

Correct Answer: C

Question #30

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 recove

A. es

B. o

View answer

Correct Answer: B

Question #31

You have an Azure SQL database named DB1 in the General Purpose service tier.The performance metrics for DB1 are shown in the following exhibit.You need to reduce the Log IO percentage. The solution must minimize costs.What should you do?

A. hange Service tier to Business Critical

B. ncrease the number of vCores

C. erform a checkpoint operation

D. hange Recovery model to Simple

View answer

Correct Answer: A

Question #32

You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements.What should you include in the plan?

A. onfigure the Deleted databases settings for ResearchSrv01

B. eploy and configure an Azure Backup server

C. onfigure the Advanced Data Security settings for ResearchDB1

D. onfigure the Manage Backups settings for ResearchSrv01

View answer

Correct Answer: D

Question #33

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?

A. he table contains less than 10,000 rows

B. ll distributions contain data

C. he table uses round-robin distribution

D. he table is skewed

View answer

Correct Answer: D

Question #34

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 recove

A. es

B. o

View answer

Correct Answer: A

Question #35

What should you use to migrate the PostgreSQL database?

A. zure Data Box

B. zCopy

C. zure Database Migration Service

D. zure Site Recovery

View answer

Correct Answer: C

Question #36

You have 40 Azure SQL databases, each for a different customer. All the databases reside on the same Azure SQL Database server.You need to ensure that each customer can only connect to and access their respective database.Which two actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. Implement row-level security (RLS)

B. Create users in each database

C. Configure the database firewall

D. Configure the server firewall

E. Create logins in the master database

F. Implement Always Encrypted

View answer

Correct Answer: BE

Question #37

You have an Azure SQL managed instance named SQLMI1 that hosts 10 databases.You need to implement alerts by using Azure Monitor.The solution must meet the following requirements:-Minimize costs.-Aggregate Intelligent Insights telemetry from each database.What should you do?

A. rom the Diagnostic settings of each database, select Send to Log Analytics

B. rom the Diagnostic settings of each database, select Stream to an event hub

C. rom the Diagnostic settings of SQLMI1, select Send to Log Analytics

D. rom the Diagnostic settings of SQLMI1, select Stream to an event hub

View answer

Correct Answer: A

Question #38

HOTSPOT (Drag and Drop is not supported)You have an Azure SQL managed instance named SQLMI1 that hosts multiple databases.You need to monitor the performance of SQLMI1 and identify which database uses the most memory and the most disk I/O.Which objects should you query? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #39

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 recove

A. es

B. o

View answer

Correct Answer: B

Question #40

Based on the PaaS prototype, which Azure SQL Database compute tier should you use?

A. usiness Critical 4-vCore

B. yperscale

C. eneral Purpose v-vCore

D. erverless

View answer

Correct Answer: D

Question #41

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 SQL Server 2019 on an Azure virtual machine.You are troubleshooting per

A. es

B. o

View answer

Correct Answer: A

Question #42

HOTSPOT (Drag and Drop is not supported)You configure a long-term retention policy for an Azure SQL database as shown in the exhibit. (Click the Exhibit tab.)The first weekly backup occurred on January 4, 2020. The dates for the first 10 weekly backups are:-January 4, 2020-January 11, 2020-January 18, 2020-January 25, 2020-February 1, 2020-February 8, 2020-February 15, 2020-February 22, 2020-February 29, 2020-March 7, 2020Use the drop-down menus to select the answer choice that completes each statement base

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #43

You have 50 Azure SQL databases.You need to notify the database owner when the database settings, such as the database size and pricing tier, are modified in Azure.What should you do?

A. reate a diagnostic setting for the activity log that has the Security log enabled

B. or the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled

C. reate an alert rule that uses a Metric signal type

D. reate an alert rule that uses an Activity Log signal type

View answer

Correct Answer: D

Question #44

You deploy a database to an Azure SQL Database managed instance.You need to prevent read queries from blocking queries that are trying to write to the database.Which database option should set?

A. EGACY_CARDINALITY_ESTIMATION

B. UERY_OPTIMIZER_HOTFIXES

C. PTIMIZE_FOR_AD_HOC_WORKLOADS

D. CCELERATED_PLAN_FORCING

View answer

Correct Answer: D

Question #45

DRAG DROP (Drag and Drop is not supported)You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 is 30 TB and has a 1-GB daily rate of change.You back up the database by using a Microsoft SQL Server Agent job that runs Transact-SQL commands. You perform a weekly full backup on Sunday, daily differential backups at 01:00, and transaction log backups every five minutes.The database fails on Wednesday at 10:00.Which three backups should you restore in sequence? To answer, move

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #46

You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 contains a table named CustomerPII.You need to record whenever users query the CustomerPII table.Which two options should you enable? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. nable a system-assigned managed identity for VM1

B. egister the Azure subscription to the Microsoft

C. nstall an Azure virtual machine Desired State Configuration (DSC) extension on VM1

D. egister the Azure subscription to the Microsoft

View answer

Correct Answer: BC

Question #47

You have an Azure Synapse Analytics dedicated SQL pool named Pool1 and a database named DB1. DB1 contains a fact table named Table.You need to identify the extent of the data skew in Table1. What should you do in Synapse Studio?

A. onnect to Pool1 and query sys

B. onnect to the built-in pool and run DBCC CHECKALLOC

C. onnect to Pool1 and run DBCC CHECKALLO

D. onnect to the built-in pool and query sys

View answer

Correct Answer: D

Question #48

You need to recommend a disaster recovery solution for an on-premises Microsoft SQL Server database. The solution must meet the following requirements:•Support real-time data replication to a different geographic region.•Use Azure as a disaster recovery target.•Minimize costs and administrative effort.What should you include in the recommendation?

A. atabase mirroring on an instance of SQL Server on Azure Virtual Machines

B. vailability groups for SQL Server on Azure Virtual Machines

C. n Azure SQL Managed Instance link

D. ransactional replication to an Azure SQL Managed Instance

View answer

Correct Answer: D

Question #49

You are designing a dimension table in an Azure Synapse Analytics dedicated SQL pool.You need to create a surrogate key for the table. The solution must provide the fastest query performance. What should you use for the surrogate key?

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #50

You have an instance of SQL Server on Azure Virtual Machines named VM1.You need to implement a disaster recovery solution that meets the following requirements.* Returns the solution to an operational state within 15 minutes of a failure* Can perform disaster recovery testing in an isolated environment* Minimizes administrative effortWhat should you include in the solution?

A. zure Site Recovery

B. failover cluster instance (FCI)

C. uto-failover groups

D. ctive geo-replication

View answer

Correct Answer: A

Question #51

You have an Azure SQL managed instance named MI1.You need to implement automatic tuning for the databases of MI1.What should you do?

A. se The REST API to call the patch operation and modify the AutomaticTuningServerMode property

B. rom the Azure portal, configure automatic tuning

C. se Transact-SQL to enable the FORCE_LAST_GOOD_PLAN option

View answer

Correct Answer: C

Question #52

You deploy an instance of SQL Server on Azure Virtual Machines: named SQL1 that hosts multiple databases.You configure the full recovery model for all the databases.You perform a full backup of the master database on SQL1.You need to a perform an additional backup of the master database on SQL1. The solution must minimize how long it takes to perform the backup.Which type of backup should you perform?

A. og

B. ull

C. ifferential

D. ail-log

View answer

Correct Answer: C

Question #53

A data engineer creates a table to store employee information for a new application. All employee names are in the US English alphabet. All addresses are locations in the United States. The data engineer uses the following statement to create the table.You need to recommend changes to the data types to reduce storage and improve performance. Which two actions should you recommend? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. ive Query Statistics

B. n estimated execution plan

C. n actual execution plan

D. lient Statistics

View answer

Correct Answer: AE

Question #54

You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.? Clustered columnstore indexes? Automatic tuning? Change tracking? PolyBaseYou plan to migrate DB1 to an Azure SQL database.What feature should be removed or replaced before DB1 can be migrated?

A. istributed availability groups

B. atabase mirroring

C. lways On Availability Group

D. zure Database Migration Service

View answer

Correct Answer: B

Question #55

You have a new Azure SQL database. The database contains a column that stores confidential information.You need to track each time values from the column are returned in a query. The tracking information must be stored for 365 days from the date the query was executed.Which three actions should you perform? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. VPN gateway

B. service endpoint

C. private link

D. n ExpressRoute gateway

View answer

Correct Answer: ADE

Question #56

HOTSPOT (Drag and Drop is not supported)You need to implement the monitoring of SalesSQLDb1. The solution must meet the technical requirements. How should you collect and stream metrics? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.Hot Area:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #57

You have an instance of SQL Server on Azure Virtual Machines that has a database named DB1.You plan to implement Azure SQL Data Sync for DB1.Which isolation level should you configure?

A. ERIALIZABLE

B. NAPSHOT

C. EAD UNCOMMITTED

D. EAD COMMITTED

View answer

Correct Answer: B

Question #58

DRAG DROP (Drag and Drop is not supported)You have SQL Server on an Azure virtual machine.You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #59

You have SQL Server on an Azure virtual machine that contains a database named DB1.You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as shown in the following exhibit: What should you do to ensure that the query uses the execution plan which executes in the least amount of time?

A. orce the query execution plan for plan 1221065

B. un the DBCC FREEPROCCACHE command

C. orce the query execution plan for plan 1220917

D. isable parameter sniffing

View answer

Correct Answer: C

Question #60

HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database named db1 on a server named server1. You use Query Performance Insight to monitor db1.You need to modify the Query Store configuration to ensure that performance monitoring data is available as soon as possible.Which configuration setting should you modify and which value should you configure? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #61

You have an Azure subscription that uses a domain named contoso.com.You have two Azure VMs named DBServer1 and DBServer2. Each of them hosts a default SQL Server instance. DBServer1 is in the East US Azure region and contains a database named DatabaseA. DBServer2 is in the West US Azure region.DBServer1 has a high volume of data changes and low latency requirements for data writes.You need to configure a new availability group for DatabaseA. The secondary replica will reside on DBServer2. What should you do

A. onfigure the primary endpoint as TCP://DBServer1

B. onfigure the primary endpoint as TCP://DBServer1

C. onfigure the primary endpoint as TCP://DBServer1

D. onfigure the primary endpoint as TCP://DBServer1

View answer

Correct Answer: C

Question #62

DRAG DROP (Drag and Drop is not supported)You have an Azure subscription.You need to deploy an Azure SQL managed instance by using an Azure Resource Manager (ARM) template. The solution must meet the following requirements:•The SQL managed instance must be assigned a unique identity.•The SQL managed instance must be available in the event of an Azure datacenter outage.How should you complete the template? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than o

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #63

HOTSPOT (Drag and Drop is not supported)You have an Azure SQL database named DB1. The automatic tuning options for DB1 are configured as shown in the following exhibit.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:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #64

You have a data warehouse in Azure Synapse Analytics.You need to ensure that the data in the data warehouse is encrypted at rest.What should you enable?

A. Transparent Data Encryption (TDE)

B. Advanced Data Security for this database

C. Always Encrypted for all columns

D. Secure transfer required

View answer

Correct Answer: A

Question #65

HOTSPOT (Drag and Drop is not supported)You have an Azure subscription that contains an instance of SQL Server on Azure Virtual Machines named SQLVM1 and a user named User1. SQLVM1 hosts a database named DB1.You need to ensure that User1 can perform the following tasks on DB1:•Create jobs.•View all jobs.•Modify, delete, and disable the jobs the user created.The solution must use the principle of least privilege.Which built-in database role should you assign to User1, and where is the role defined? To answer

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #66

You have an on-premises datacenter that contains a 14-TB Microsoft SQL Server database.You plan to create an Azure SQL managed instance and migrate the on-premises database to the new instance.Which three service tiers support the SQL managed instance? Each correct answer presents a complete solution.NOTE: Each correct selection is worth one point.

A. General Purpose Standard

B. Business Critical Memory Optimized Premium

C. General Purpose Premium

D. Business Critical Premium

E. Business Critical Standard

View answer

Correct Answer: ABC

Question #67

You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.You need to collect performance metrics for SQLMI1 and stream the metrics to Workspace.What should you do first?

A. reate a private endpoint connection on SQLMI1

B. onfigure Azure SQL Analytics to use Workspace1

C. odify the Compute + storage settings for SQLMI1

D. odify the diagnostic settings for SQLMI1

View answer

Correct Answer: D

Question #68

You have an Azure subscription that contains three instances of SQL Server on Azure Virtual Machines.You plan to implement a disaster recovery solution.You need to be able to perform disaster recovery drills regularly. The solution must meet the following requirements:•Minimize administrative effort for the recovery drills.•Isolate the recovery environment from the production environmentWhat should you use?

A. ative Microsoft SQL Server backup

B. zure Site Recovery

C. ecovery Services vaults

D. zure Backup

View answer

Correct Answer: B

Question #69

HOTSPOT (Drag and Drop is not supported)You need to recommend a configuration for ManufacturingSQLDb1 after the migration to Azure. The solution must meet the business requirements.What should you include in the recommendation? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #70

HOTSPOT (Drag and Drop is not supported)You have an Azure subscription that contains the resources shown in the following table.You need to create a read-only replica of DB1 and configure the App1 instances to use the replica.What should you do? To answer, select the appropriate options in the answer area.NOTE: Each correct selection is worth one point.Hot Area:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #71

You have an Azure SQL database named DB1.You need to display the estimated execution plan of a query by using the query editor in the Azure portal. What should you do first?

A. un the SET SHOWPLAN_ALL Transact-SQL statement

B. or DB1, set QUERY_CAPTURE_MODE of Query Store to All

C. un the SET FORCEPLAN Transact-SQL statement

D. nable Query Store for DB1

View answer

Correct Answer: A

Question #72

You have an Azure subscription that contains an Azure SQL database named SQL1.SQL1 is in an Azure region that does not support availability zones.You need to ensure that you have a secondary replica of SQL1 in the same region.What should you use?

A. og shipping

B. ctive geo-replication

C. icrosoft SQL Server failover clusters

D. uto-failover groups

View answer

Correct Answer: B

Question #73

You have an Azure SQL database named DB1.You need to query the fragmentation information of data and indexes for the tables in DB1.Which command should you run?

A. ys

B. BCC CHECKALLOC

C. BCC SHOWCONTIG

D. ys

View answer

Correct Answer: D

Question #74

Which audit log destination should you use to meet the monitoring requirements?

A. zure Storage

B. zure Event Hubs

C. zure Log Analytics

View answer

Correct Answer: C

Question #75

You have an on-premises datacenter that contains a 14-TB Microsoft SQL Server database.You plan to create an Azure SQL managed instance and migrate the on-premises database to the new instance.Which three service tiers support the SQL managed instance? Each correct answer presents a complete solution. NOTE. Each correct selection is worth one point.

A. eneral Purpose Standard

B. usiness Critical Premium

C. usiness Critical Memory Optimized Premium

D. eneral Purpose Premium

E. usiness Critical Standard

View answer

Correct Answer: BCD

Question #76

HOTSPOT (Drag and Drop is not supported)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.

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #77

You have 10 Azure virtual machines that have SQL Server installed.You need to implement a backup strategy to ensure that you can restore specific databases to other SQL Server instances. The solution must provide centralized management of the backups.What should you include in the backup strategy?

A. utomated Backup in the SQL virtual machine settings

B. zure Backup

C. zure Site Recovery

D. QL Server Agent jobs

View answer

Correct Answer: B

Question #78

What should you do after a failover of SalesSQLDb1 to ensure that the database remains accessible to SalesSQLDb1App1?

A. onfigure SalesSQLDb1 as writable

B. pdate the connection strings of SalesSQLDb1App1

C. pdate the firewall rules of SalesSQLDb1

D. pdate the users in SalesSQLDb1

View answer

Correct Answer: B

Question #79

You need to implement authentication for ResearchDB1. The solution must meet the security and compliance requirements.What should you run as part of the implementation?

A. ouble the value of Data Flush interval

B. ecrease by half the value of Data Flush Interval

C. ouble the value of Statistics Collection Interval

D. ecrease by half the value of Statistics Collection interval

View answer

Correct Answer: E

Question #80

You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance.Which type of compression provides the greatest space reduction for the database?

A. lustered columnstore indexes

B. olyBase

C. hange tracking

D. utomatic tuning

View answer

Correct Answer: D

Question #81

You have an Azure Data Factory pipeline that performs an incremental load of source data to an Azure Data Lake Storage Gen2 account.Data to be loaded is identified by a column named LastUpdatedDate in the source table. You plan to execute the pipeline every four hours.You need to ensure that the pipeline execution meets the following requirements:Automatically retries the execution when the pipeline run fails due to concurrency or throttling limits. Supports backfilling existing data in the table.Which type

A. umbling window

B. n-demand

C. vent

D. chedule

View answer

Correct Answer: A

Question #82

You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2. You need to limit the number of IOPs that App2 queries generate on SQL1.Which two actions should you perform on SQL1? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. mplement memory-optimized tables

B. un the DBCC FLUSHPROCINDB command

C. eplace the sequential index keys with nonsequential keys

D. un the DBCC DBREINDEX command

View answer

Correct Answer: BD

Question #83

HOTSPOT (Drag and Drop is not supported)You have SQL Server on an Azure virtual machine.You review the query plan shown in the following exhibit.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:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #84

You have an Azure subscription that contains an Azure SQL database. The database contains a table named table1.You execute the following Transact-SQL statements.You need to reduce the time it takes to perform analytic queries on the database.Which configuration should you enable?

A. OW_MODE_MEMORY_GRANT_FEEDBACK

B. ATCH_MODE_MEMORY_GRANT_FEEDBACK

C. ATCH_MODE_ADAPTIVE_JOINS

D. ATCH_MODE_ON_ROWSTORE

View answer

Correct Answer: D

Question #85

DRAG DROP (Drag and Drop is not supported)You need to apply 20 built-in Azure Policy definitions to all new and existing Azure SQL Database deployments in an Azure subscription. The solution must minimize administrative effort.Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.Select and Place:

A. ee Explanation section for answer

View answer

Correct Answer: A

Question #86

You have 20 Azure SQL databases provisioned by using the vCore purchasing model.You plan to create an Azure SQL Database elastic pool and add the 20 databases.Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution.NOTE: Each correct selection is worth one point.

A. otal size of all the databases

B. eo-replication support

C. umber of concurrently peaking databases * peak CPU utilization per database

D. aximum number of concurrent sessions for all the databases

E. otal number of databases * average CPU utilization per database

View answer

Correct Answer: ACE

Question #87

You have an Always On availability group deployed to Azure virtual machines. The availability group contains a database named DB1 and has two nodes named SQL1 and SQL2. SQL1 is the primary replica.You need to initiate a full backup of DB1 on SQL2. Which statement should you run?

A. ACKUP DATABASE DB1 TO URL='https://mystorageaccount

B. ACKUP DATABASE DB1 TO URL='https://mystorageaccount

C. ACKUP DATABASE DB1 TO URL='https://mystorageaccount

D. ACKUP DATABASE DB1 TO URL='https://mystorageaccount

View answer

Correct Answer: B

DON'T WANT TO MISS A THING?

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE!
Get Now

Boost Your Performance in the Microsoft DP-300 Exam with Practice Tests

View Answers after Submission

DON'T WANT TO MISS A THING?

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE! Get Now

Boost Your Performance in the Microsoft DP-300 Exam with Practice Tests

View Answers after Submission

100% Pass Cisco, PMP, CISA, CISM, AWS Practice test on SALE!
Get Now