Winter Special Flat 65% Limited Time Discount offer - Ends in 0d 00h 00m 00s - Coupon code: suredis

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

Demo: 64 questions
Total 341 questions

Administering Relational Databases on Microsoft Azure Questions and Answers

Question 1

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

Options:

Question 2

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?

Options:

A.

Transparent Data Encryption (TDE)

B.

Advanced Data Security for this database

C.

Always Encrypted for all columns

D.

Secure transfer required

Question 3

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.

Options:

Question 4

You have an Azure subscription.

You need to deploy an Azure SQL managed instance that meets the following requirements:

• Optimize latency.

• Maximize the memory-to-vCore ratio.

Which service tier and hardware generation should you use? To answer, select the apocopate options in the answer area.

NOTE: Each correct selection is worth one point.

Options:

Question 5

You have an Azure subscription.

You plan to deploy an instance of SQL Sevier on Azure Virtual Machines that supports Write Accelerator.

Which virtual machine series should you use?

Options:

A.

H-series

B.

G -series

C.

M-series

D.

E-series

Question 6

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 contains an Azure SQL database named Database1.

You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.

Solution: You restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the REPLACE option.

Does this meet the goal?

Options:

A.

Yes

B.

No

Question 7

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 answer, move the appropriate objects from the list of objects to the answer area and arrange them in the correct order.

Options:

Question 8

You have an Azure subscription that contains 50 instances of SQL Server on Azure Virtual Machines. The instances host 500 Azure SQL databases. You need to ensure that all the databases have the same configuration. The solution must meet the following requirements:

• Auditing must be enabled.

• Azure Defender must be enabled.

• Public network access must be disabled.

• Administrative effort must be minimized.

Which two resources should you create in the subscription? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

Options:

A.

an Azure Policy assignment

B.

an Azure Automation account

C.

an Azure Policy initiative

D.

an Azure Automation runbook

E.

an Azure Policy definition

Question 9

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?

Options:

A.

Connect to Pool1 and query sys.dm_pdw_nodes_db_partition_stats.

B.

Connect to the built-in pool and run DBCC CHECKALLOC.

C.

Connect to Pool1 and run DBCC CHECKALLOC.

D.

Connect to the built-in pool and query sys.dm_pdw_nodes_db_partition_stats.

Question 10

You are provisioning an Azure SQL database in the Azure portal as 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.

Options:

Question 11

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 12

You have an Azure virtual machine named VM1 that runs Windows Server 2022 and hosts a Microsoft SQL Server 2019 instance named SQL1.

You need to configure SQL1 to use mixed mode authentication.

Which procedure should you run?

Options:

A.

sp_eddremotelogin

B.

xp_grant_login

C.

sp_change_users_login

D.

xp_instance_regwrite

Question 13

You have a Microsoft SQL Server 2019 database named DB1 and an Azure SQL managed instance named SQLMI1. You need to move a SQL Server Agent job from DB1 to SQLMI1. Which job attribute is unsupported in SQLMI1?

Options:

A.

log to table

B.

email notifications

C.

schedules

D.

output files

Question 14

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.

Options:

Question 15

You have an Azure SQL database named DB1 that contains a table named Orders. The Orders table contains a row for each sales order. Each sales order includes the name of the

user who placed the order.

You need to implement row-level security (RLS). The solution must ensure that the users can view only their respective sales orders.

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:

Question 16

You have an Azure subscription that contains the resources shown in the following table.

You need to back up db1 to mysqlbackups, and then restore the backup to a new database named db2 that is hosted on SQL1. The solution must ensure that db1 is backed up to a stripe set.

Which three Transact-SQL statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Options:

Question 17

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?

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 18

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:

Question 19

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 20

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 21

You need to recommend a solution to meet the security requirements and the business requirements for DB3. What should you recommend as the first step of the solution?

Options:

A.

Run the sys.5p_cdc_en«ble_db stored procedure.

B.

Run the alter table statement and specify the enable chahgc_tracking clause.

C.

Run the alter database statement and specify the set cmange_trackinc> - on clause.

D.

Run the sp_addarticle stored procedure.

Question 22

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 23

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 24

You plan to deploy Instance1 by using the following script.

You need to specify the licenseType and storagenedundancy parameters. The deployment must meet the availability requirements and the business requirements for DB1 and DB2.

To what should you set each parameter? To answer, select the appropriate options in the answer area.

Options:

Question 25

You need to recommend a backup solution to restore DB3. The solution must meet the availability requirements. Which type of backup should you use?

Options:

A.

transaction log

B.

point-in-time restore (PITR)

C.

differential

D.

long-term retention (LTR)

Question 26

You need to recommend which service and target endpoint to use when migrating the databases from SVR1 to Instance1. The solution must meet the availability requirements.

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

NOTE Each correct selection is worth one point.

Options:

Question 27

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 28

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.

Options:

Question 29

You need to identify the event_flle target for monitonng DB3 after the migration to Azure SQL Database. The solution must meet the management requirements,

What should you use as the event_file target?

Options:

A.

an Azure SQL database

B.

an Azure Blob Storage container

C.

a SQL Server filegroup

D.

an Azure Files share

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 a service tier and a method to offload analytical workloads for the databases migrated from SVR1. The solution must meet the availability and business requirements.

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

NOTE: Each correct selection is worth one point.

Options:

Question 32

You need to recommend a process to automate the management of DB3. The solution must meet the management requirements. What should be the first step of the process?

Options:

A.

Configure Microsoft Entra authentication for the logical server that hosts DB3.

B.

Create a database that has database-scoped credentials.

C.

Configure a private endpoint for connectivity to DB3.

D.

Create data base-scoped credentials in DB3.

Question 33

You need to recommend a solution that will enable remote developers to access DB1 and DB2. The solution must support the planned changes and meet the secunty requirements.

What should you include in the recommendation?

Options:

A.

a public endpoint via a database-level firewall rule

B.

a private endpoint

C.

a public endpoint via a server-level firewall rule

D.

a Point-to-Site (P2S) VPN

Question 34

You need to identify the event_file target for monitonng DB3 after the migration to Azure SQL Database. The solution must meet the management requirements.

What should you use as the event_file target?

Options:

A.

an Azure SQL database

B.

an Azure Blob Storage container

C.

a SQL Server filegroup

D.

an Azure Files share

Question 35

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 36

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 37

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 38

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 39

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 40

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 41

Task 3

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

Options:

Question 42

Task 2

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

Options:

Question 43

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 44

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 45

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 46

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 47

Task 10

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

Options:

Question 48

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 49

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 50

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 51

Task 4

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

Options:

Question 52

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 53

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.

Options:

Question 54

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.

Options:

Question 55

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.

Options:

Question 56

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 57

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.

Options:

Question 58

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 59

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 60

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 61

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 62

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.

Options:

Question 63

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 64

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.

Options:

Demo: 64 questions
Total 341 questions