11.11 Special Limited Time Flat 70% Discount offer - Ends in 0d 00h 00m 00s - Coupon code: 70spcl

Microsoft DP-300 Administering Relational Databases on Microsoft Azure Exam Practice Test

Page: 1 / 34
Total 337 questions

Administering Relational Databases on Microsoft Azure Questions and Answers

Question 1

You have an Azure Data Lake Storage Gen2 container.

Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files.

You need to design a data archiving solution that meets the following requirements:

    New data is accessed frequently and must be available as quickly as possible.

    Data that is older than five years is accessed infrequently but must be available within one second when requested.

    Data that us older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible.

    Costs must be minimized while maintaining the required availability.

How should you manage the data? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Question # 1

Options:

Question 2

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 of trigger should you use?

Options:

A.

tumbling window

B.

on-demand

C.

event

D.

schedule

Question 3

You plan to perform batch processing in Azure Databricks once daily.

Which type of Databricks cluster should you use?

Options:

A.

automated

B.

interactive

C.

High Concurrency

Question 4

You have an on-premises Microsoft SQL Server 2019 instance that hosts a database named DB1.

You plan to perform an online migration of DB1 to an Azure SQL managed instance by using the Azure Database Migration Service.

You need to create a backup of DB1 that is accessible to the Azure Database Migration Service.

What should you run for the backup and where should you store the backup? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Question # 4

Options:

Question 5

You have an Azure SQL logical server.

You run the following script.

Question # 5

Question # 5

Options:

Question 6

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?

Options:

A.

Halve the value of Data Flush Interval.

B.

Double the value of Statistics Collection Interval.

C.

Halve the value of Statistics Collection Interval.

D.

Double the value of Data Flush Interval.

Question 7

You have an Azure subscription.

Vou need to deploy two instances of SQL Server on Azure virtual machines in a highly available configuration that will use an Always On availability group. The solution must meet the following requirements:

• Minimize how long it takes to fail over.

• Maintain existing connections to the primary replica during a failover.

What should you do?

Options:

A.

Connect each virtual machine to a single subnet on a single virtual network.

B.

Connect each virtual machine to a single subnet on a virtual network. Deploy a standard Azure load balancer.

C.

Connect each virtual machine to a different subnet on a single virtual network.

D.

Connect each virtual machine to a different subnet on a virtual network. Deploy a basic Azure load balancer.

Question 8

Your on-premises network contains a server that hosts a 60-TB database named DB 1. The network has a 10-Mbps internet connection.

You need to migrate DB 1 to Azure. The solution must minimize how long it takes to migrate the database.

What should you use?

Options:

A.

Azure Migrate

B.

Data Migration Assistant (DMA)

C.

Azure Data BOX

D.

Azure Database Migration Service

Question 9

From a website analytics system, you receive data extracts about user interactions such as downloads, link clicks, form submissions, and video plays.

The data contains the following columns:

Question # 9

You need to design a star schema to support analytical queries of the data. The star schema will contain four tables including a date dimension.

To which table should you add each column? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Question # 9

Options:

Question 10

You have an Azure SQL managed instance named Server1 and an Azure Blob Storage account named storage1 that contains Microsoft SQL Server database backup files.

You plan to use Log Replay Service to migrate the backup files from storage1 to Server1. The solution must use the highest level of security when connecting to storage1.

Which PowerShell cmdlet should you run, and which parameter should you specify to secure the connection? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Question # 10

Options:

Question 11

You have an Azure Synapse Analytics dedicated SQL pool.

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

Question # 11

Which statement accurately describes the dbo.FactInternetSales table?

Options:

A.

The table contains less than 10,000 rows.

B.

All distributions contain data.

C.

The table uses round-robin distribution

D.

The table is skewed.

Question 12

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.

Options:

A.

Enable Last_Query_Plan_Stats in the master database

B.

Enable Lightweight_Query_Profiling in DB1

C.

Enable Last_Query_Plan_Stats in DB1

D.

Enable Lightweight_Query_Profiling in the master database

E.

Enable PARAMETER_SNIFFING in DB1

Question 13

You have an Azure subscription that contains a resource group named RG1. RG1 contains an instance of SQL Server on Azure Virtual Machines named SQL

You need to use PowerShell to enable and configure automated patching for SQL The solution must include both SQL Server and Windows security updates.

How should you complete the command? To answer, select the appropriate options in the answer area.

NOTE: Each correct selection is worth one point.

Question # 13

Options:

Question 14

You have an Azure SQL database.

You have a query and the associated execution plan as shown in the following exhibit.

Question # 14

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.

Question # 14

Options:

Question 15

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?

Options:

A.

database mirroring on an instance of SQL Server on Azure Virtual Machines

B.

availability groups for SQL Server on Azure Virtual Machines

C.

an Azure SQL Managed Instance link

D.

transactional replication to an Azure SQL Managed Instance

Question 16

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.

Options:

A.

Enable query optimizer fixes.

B.

Enable Resource Governor.

C.

Enable parameter sniffing.

D.

Create a workload group.

E.

Configure In-memory OLTP.

F.

Run the Database Engine Tuning Advisor.

G.

Reduce the Max Degree of Parallelism value.

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

Options:

A.

2

B.

4

C.

8

D.

64

Question 18

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 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 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 21

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 22

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 23

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 24

Task 3

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

Options:

Question 25

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 26

Task 2

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

Options:

Question 27

Task 4

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

Options:

Question 28

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 29

Task 10

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

Options:

Question 30

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 31

You need to recommend which configuration to perform twice to enable access to the primary and secondary replicas of DB3. The solution must meet the availability requirements.

What should you recommend?

Options:

A.

Configure virtual network service endpoints.

B.

Enable database firewall rules.

C.

Create database-scoped credentials.

D.

Configure connection strings that reference the read-write listener.

Question 32

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 33

You need to recommend an authentication solution for App1 access to DB1 and DB2 after their migration to Instance1. The solution must meet the availability requirements.

Which actions should you perform in sequence? To answer, drag the appropriate actions to the correct order. Each action may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.

NOTE: Each correct selection is worth one point.

Question # 33

Options:

Question 34

You need to recommend a solution to ensure that the performance of DB3 is optimized after the migration to Azure SQL Database. The solution must meet availability requirements.

What should you include in the recommendation?

Options:

A.

Resource Governor

B.

a custom resource pool

C.

vertical scaling

D.

horizontal scaling

Question 35

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 36

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.

Question # 36

Options:

Question 37

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 38

You are planning the migration of the SERVER1 databases. The solution must meet the business requirements.

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

NOTE: Each correct selection is worth one point.

Question # 38

Options:

Question 39

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.

Question # 39

Options:

Question 40

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

Options:

A.

Configure SalesSQLDb1 as writable.

B.

Update the connection strings of SalesSQLDb1App1.

C.

Update the firewall rules of SalesSQLDb1.

D.

Update the users in SalesSQLDb1.

Question 41

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?

Options:

A.

Configure the Deleted databases settings for ResearchSrvOL

B.

Deploy and configure an Azure Backup server.

C.

Configure the Advanced Data Security settings for ResearchDBL

D.

Configure the Manage Backups settings for ResearchSrvOL

Question 42

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.

Question # 42

Options:

Question 43

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.

Question # 43

Options:

Question 44

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?

Options:

A.

CREATE LOGIN and the FROM WINDOWS clause

B.

CREATE USER and the FROM CERTIFICATE clause

C.

CREATE USER and the FROM LOGIN clause

D.

CREATE USER and the ASYMMETRIC KEY clause

E.

CREATE USER and the FROM EXTERNAL PROVIDER clause

Question 45

You are evaluating the business goals.

Which feature should you use to provide customers with the required level of access based on their service agreement?

Options:

A.

dynamic data masking

B.

Conditional Access in Azure

C.

service principals

D.

row-level security (RLS)

Question 46

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.

Question # 46

Options:

Question 47

You need to recommend the appropriate purchasing model and deployment option for the 30 new databases. The solution must meet the technical requirements and the business requirements.

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

NOTE: Each correct selection is worth one point.

Question # 47

Options:

Question 48

You create all of the tables and views for ResearchDB1.

You need to implement security for ResearchDB1. 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.

Question # 48

Options:

Question 49

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 50

You need to identify the cause of the performance issues on SalesSQLDb1.

Which two dynamic management views should you use? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

Options:

A.

sys.dm_pdw_nodes_tran_locks

B.

sys.dm_exec_compute_node_errors

C.

sys.dm_exec_requests

D.

sys.dm_cdc_errors

E.

sys.dm_pdw_nodes_os_wait_stats

F.

sys.dm_tran_locks

Question 51

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

Options:

A.

Azure Storage

B.

Azure Event Hubs

C.

Azure Log Analytics

Page: 1 / 34
Total 337 questions