March Sale Special Limited Time 70% Discount Offer - Ends in 0d 00h 00m 00s - Coupon code: 70percent

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

Demo: 35 questions
Total 311 questions

Administering Microsoft Azure SQL Solutions Questions and Answers

Question 1

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

Options:

A.

Azure Storage

B.

Azure Event Hubs

C.

Azure Log Analytics

Question 2

You are evaluating the role assignments.

For each of the following statements, select Yes if the statement is true. Otherwise, select No.

NOTE: Each correct selection is worth one point.

Options:

Question 3

What should you implement to meet the disaster recovery requirements for the PaaS solution?

Options:

A.

Availability Zones

B.

failover groups

C.

Always On availability groups

D.

geo-replication

Question 4

You need to implement a solution to notify the administrators. The solution must meet the monitoring requirements.

What should you do?

Options:

A.

Create an Azure Monitor alert rule that has a static threshold and assign the alert rule to an action group.

B.

Add a diagnostic setting that logs QueryStoreRuntimeStatistics and streams to an Azure event hub.

C.

Add a diagnostic setting that logs Timeouts and streams to an Azure event hub.

D.

Create an Azure Monitor alert rule that has a dynamic threshold and assign the alert rule to an action group.

Question 5

What should you use to migrate the PostgreSQL database?

Options:

A.

Azure Data Box

B.

AzCopy

C.

Azure Database Migration Service

D.

Azure Site Recovery

Question 6

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

Options:

A.

Business Critical 4-vCore

B.

Hyperscale

C.

General Purpose v-vCore

D.

Serverless

Question 7

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 failover groups.

Does this meet the goal?

Options:

A.

Yes

B.

No

Question 8

You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in fiveminute intervals and report only events that arrive during the interval.

The output will be sent to a Delta Lake table.

Which output mode should you use?

Options:

A.

complete

B.

append

C.

update

Question 9

You have an on-premises Microsoft SQL server that uses the FileTables and Filestream features.

You plan to migrate to Azure SQL.

Which service should you use?

Options:

A.

Azure SQL Database

B.

SQL Server on an Azure Virtual Machine

C.

Azure SQL Managed Instance

D.

Azure Database for MySQL

Question 10

You have a burstable Azure virtual machine named VMI that hosts an instance of Microsoft SQL Server.

You need to attach an Azure ultra disk to VMI. The solution must minimize downtime on VMI.

In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.

Options:

Question 11

Your company uses Azure Stream Analytics to monitor devices.

The company plans to double the number of devices that are monitored.

You need to monitor a Stream Analytics job to ensure that there are enough processing resources to handle the

additional load.

Which metric should you monitor?

Options:

A.

Input Deserialization Errors

B.

Late Input Events

C.

Early Input Events

D.

Watermark delay

Question 12

You have the following Azure Data Factory pipelines:

  • Ingest Data from System1
  • Ingest Data from System2
  • Populate Dimensions
  • Populate Facts

Ingest 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?

Options:

A.

Add a schedule trigger to all four pipelines.

B.

Add an event trigger to all four pipelines.

C.

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

D.

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

Question 13

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
  • PolyBase

You plan to migrate DB1 to an Azure SQL database.

What feature should be removed or replaced before DB1 can be migrated?

Options:

A.

Clustered columnstore indexes

B.

PolyBase

C.

Change tracking

D.

Automatic tuning

Question 14

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 effort

What should you include in the solution?

Options:

A.

Azure Backup

B.

active geo-replication

C.

Azure Site Recovery

D.

auto-failover groups

Question 15

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.

Options:

Question 16

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?

Options:

A.

Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Asynchronous.

B.

Configure the primary endpoint as TCP://DBServer1.contoso.com:445, configure the secondary endpoint as TCP://DBServer2.contoso.com:445, and set the availability mode to Synchronous.

C.

Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Asynchronous.

D.

Configure the primary endpoint as TCP://DBServer1.contoso.com:5022, configure the secondary endpoint as TCP://DBServer2.contoso.com:5022, and set the availability mode to Synchronous.

Question 17

Which windowing function should you use to perform the streaming aggregation of the sales data?

Options:

A.

Sliding

B.

Hopping

C.

Session

D.

Tumbling

Question 18

Which counter should you monitor for real-time processing to meet the technical requirements?

Options:

A.

SU% Utilization

B.

CPU% utilization

C.

Concurrent users

D.

Data Conversion Errors

Question 19

Task 8

You plan to perform performance testing of db1.

You need prevent db1 from reverting to the last known good query plan.

Options:

Question 20

Task 5

You need to configure a disaster recovery solution for db1. When a failover occurs, the connection strings to the database must remain the same. The secondary server must be in the West US 3 Azure region.

Options:

Question 21

Task 10

You need to protect all the databases on sql37006S95 from SQL injection attacks.

Options:

Question 22

Task 1

In an Azure SQL database named db1, you need to enable page compression on the PK_SalesOrderHeader_SalesOrderlD clustered index of the SalesLT.SalesOrderHeader table.

Options:

Question 23

Task 2

You need to configure your user account as the Azure AD admin for the server named sql3700689S.

Options:

Question 24

Task 9

You need to generate an email alert to admin@contoso.com when CPU percentage utilization for db1 is higher than average.

Options:

Question 25

Task 12

You need to configure high availability for dbl. The solution must tolerate the loss of an Azure datacenter without data loss or the need to modify application connection strings.

Options:

Question 26

Task 6

You need to ensure that any enhancements made to the Query Optimizer through patches are available to dbl and db2 on sql37006895.

Options:

Question 27

Task 4

You need to enable change data capture (CDC) for db1.

Options:

Question 28

Task 11

You have a legacy application written for Microsoft SQL Server 2012. The application will be the only application that accesses db1 You need to ensure that db1 is compatible with all the features and syntax of SQL Server 2012.

Options:

Question 29

Task 7

You plan to create an automation runbook that will create database users in db1 from Azure AD identities. You need to configure sq1370O6895 to support the creation of new database users.

Options:

Question 30

Task 3

You need to ensure that all queries executed against dbl are captured in the Query Store.

Options:

Question 31

You need to recommend a solution to ensure that the customers can create the database objects. The solution must meet the business goals.

What should you include in the recommendation?

Options:

A.

For each customer, grant the customer ddl_admin to the existing schema.

B.

For each customer, create an additional schema and grant the customer ddl_admin to the new schema.

C.

For each customer, create an additional schema and grant the customer db_writerto the new schema.

D.

For each customer, grant the customer db_writerto the existing schema.

Question 32

You need to configure user authentication for the SERVER1 databases. The solution must meet the security and compliance requirements.

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.

Options:

Question 33

You need to design a data retention solution for the Twitter feed data records. The solution must meet the customer sentiment analytics requirements.

Which Azure Storage functionality should you include in the solution?

Options:

A.

time-based retention

B.

change feed

C.

lifecycle management

D.

soft delete

Question 34

You need to implement the surrogate key for the retail store table. The solution must meet the sales transaction dataset requirements.

What should you create?

Options:

A.

a table that has a FOREIGN KEY constraint

B.

a table the has an IDENTITY property

C.

a user-defined SEQUENCE object

D.

a system-versioned temporal table

Question 35

You need to design an analytical storage solution for the transactional data. The solution must meet the sales transaction dataset requirements.

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

NOTE: Each correct selection is worth one point.

Options:

Demo: 35 questions
Total 311 questions