Latest Jan 21, 2024 DP-300 Brain Dump A Study Guide with Tips & Tricks for passing Exam [Q136-Q155]

Latest Jan 21, 2024 DP-300 Brain Dump: A Study Guide with Tips & Tricks for passing Exam

DP-300 Question Bank: Free PDF Download Recently Updated Questions

Microsoft DP-300 exam is a professional-level certification that validates the skills and knowledge required to administer relational databases on the Microsoft Azure cloud platform. DP-300 exam is designed for database administrators who are responsible for managing and maintaining databases on Azure, and who have experience with SQL Server and Azure data services. DP-300 exam assesses the ability to implement security, backup and recovery, high availability, and performance tuning solutions for Azure databases.

Earning the DP-300 certification validates the candidate's expertise in administering Azure SQL databases and demonstrates their commitment to professional development. Administering Relational Databases on Microsoft Azure certification is recognized globally and can help advance the candidate's career by opening up new opportunities for roles such as Azure database administrator, database architect, and senior database engineer.

The Microsoft DP-300: Administering Relational Databases on Microsoft Azure exam is a qualifying test that the individuals must pass to obtain the Microsoft Certified: Azure Database Administrator Associate certification. The intended candidates for this exam are the specialists in data management and database administrators. They manage and implement the operational components of hybrid and Cloud-native data platform solution options that are created on SQL Server and Azure Data Services. These professionals use different tools and methods to carry out their daily business operations. These include the application of knowledge of the use of T-SQL for different purposes, including administrative management.

NEW QUESTION # 136
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.

Answer:

Explanation:

NEW QUESTION # 137
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?

A. Add an event trigger to all four pipelines.
B. Create a parent pipeline that contains the four pipelines and use an event trigger.
C. Add a schedule trigger to all four pipelines.
D. Create a parent pipeline that contains the four pipelines and use a schedule trigger.

Answer: D

Explanation:
Section: [none]
Explanation/Reference:
https://www.mssqltips.com/sqlservertip/6137/azure-data-factory-control-flow-activities-overview/

NEW QUESTION # 138
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.

Answer:

Explanation:

Explanation

NEW QUESTION # 139
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 the appropriate backups from the list of backups to the answer area and arrange them in the correct order.

Answer:

Explanation:

Explanation

NEW QUESTION # 140
You have SQL Server on an Azure virtual machine that contains a database named DB1.
The database reports a CHECKSUM error.
You need to recover the database.
How should you complete the statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Explanation:
Box 1: SINGLE_USER
The specified database must be in single-user mode to use one of the following repair options.
Box 2: REPAIR_ALLOW_DATA_LOSS
REPAIR_ALLOW_DATA_LOSS tries to repair all reported errors. These repairs can cause some data loss.
Note: The REPAIR_ALLOW_DATA_LOSS option is a supported feature but it may not always be the best option for bringing a database to a physically consistent state. If successful, the REPAIR_ALLOW_DATA_LOSS option may result in some data loss. In fact, it may result in more data lost than if a user were to restore the database from the last known good backup.
Incorrect Answers:
REPAIR_FAST
Maintains syntax for backward compatibility only. No repair actions are performed.
Box 3: MULTI_USER
MULTI_USER
All users that have the appropriate permissions to connect to the database are allowed.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql

NEW QUESTION # 141
You have an Azure SQL managed instance.
You need to gather the last execution of a query plan and its runtime statistics. The solution must minimize the impact on currently running queries.
What should you do?

A. Run sys.dm_exec_query_plan_stats.
B. Generate an estimated execution plan.
C. Generate an actual execution plan.
D. Generate Live Query Statistics.

Answer: A

Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-stats-transact-sql?view=sql-server-ver15

NEW QUESTION # 142
You have a Microsoft SQL Server database named DB1 that contains a table named Table1.
The database role membership for a user named User1 is 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.

Answer:

Explanation:

Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

NEW QUESTION # 143
You have an Azure SQL database named DB1. You run a query while connected to DB1.
You review the actual execution plan for the query, and you add an index to a table referenced by the query.
You need to compare the previous actual execution plan for the query to the Live Query Statistics.
What should you do first in Microsoft SQL Server Management Studio (SSMS)?

A. Enable Query Store for DB1.
B. Save the actual execution plan.
C. For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
D. Run the SET SHOWPLAN_ALLTransact-SQL statement.

Answer: B

Explanation:
Section: [none]
Explanation:
The Plan Comparison menu option allows side-by-side comparison of two different execution plans, for easier identification of similarities and changes that explain the different behaviors for all the reasons stated above.
This option can compare between:
Two previously saved execution plan files (.sqlplan extension).
One active execution plan and one previously saved query execution plan.
Two selected query plans in Query Store.

NEW QUESTION # 144
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?

A. 0
B. 1
C. 2
D. 3

Answer: B

Explanation:
Section: [none]
Explanation:
The number of files depends on the number of (logical) processors on the machine. As a general rule, if the
number of logical processors is less than or equal to eight, use the same number of data files as logical
processors. If the number of logical processors is greater than eight, use eight data files and then if contention
continues, increase the number of data files by multiples of 4 until the contention is reduced to acceptable
levels or make changes to the workload/code.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database

NEW QUESTION # 145
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. Create a diagnostic setting for the activity log that has the Security log enabled.
B. Create an alert rule that uses a Metric signal type.
C. Create an alert rule that uses an Activity Log signal type.
D. For the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled.

Answer: C

Explanation:
Explanation
Activity log events - An alert can trigger on every event, or, only when a certain number of events occur.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/alerts-insights-configure-portal

NEW QUESTION # 146
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.

Answer:

Explanation:

Explanation

Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-que

NEW QUESTION # 147
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.

Answer:

Explanation:

1 - Create an Azure AD administrator for the locial server
2 - Connect to the databases by using an Azure AD account
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview

NEW QUESTION # 148
You have an on-premises Microsoft SQL Server 2016 server named Server1 that contains a database named DB1.
You need to perform an online migration of DB1 to an Azure SQL Database managed instance by using Azure Database Migration Service.
How should you configure the backup of DB1? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Reference:
https://docs.microsoft.com/en-us/azure/dms/known-issues-azure-sql-db-managed-instance-online

NEW QUESTION # 149
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.

Answer:

Explanation:

1 - Create Credential,,,,
2 - BACKUP DATABASE db1,,,,
3 - RESTORE DATABASE db2 FROM URL =,,,,,,,
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver15

NEW QUESTION # 150
You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.
You plan to migrate DB1 to an Azure SQL Database managed instance.
What should you use to minimize downtime and data loss during the migration?

A. distributed availability groups
B. Database Migration Assistant
C. database mirroring
D. log shipping

Answer: B

Explanation:
Explanation
Ref: https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql

NEW QUESTION # 151
You have an Azure subscription that contains an Azure SQL managed instance named SQLMi1 and a SQL Agent job named Backupdb. Backupdb performs a daily backup of the databases hosted on SQLMi1.
You need to be notified by email if the job fails.
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 any of the correct orders you select.

Answer:

Explanation:

Explanation

Text Description automatically generated
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

NEW QUESTION # 152
You have an Azure Data Factory instance named ADF1 and two Azure Synapse Analytics workspaces named WS1 and WS2.
ADF1 contains the following pipelines:
P1:Uses a copy activity to copy data from a nonpartitioned table in a dedicated SQL pool of WS1 to an Azure Data Lake Storage Gen2 account P2:Uses a copy activity to copy data from text-delimited files in an Azure Data Lake Storage Gen2 account to a nonpartitioned table in a dedicated SQL pool of WS2 You need to configure P1 and P2 to maximize parallelism and performance.
Which dataset settings should you configure for the copy activity of each pipeline? To answer, select the appropriate options in the answer area.

Answer:

Explanation:

Explanation
Graphical user interface, text, chat or text message Description automatically generated

P1: Set the Partition option to Dynamic Range.
The SQL Server connector in copy activity provides built-in data partitioning to copy data in parallel.
P2: Set the Copy method to PolyBase
Polybase is the most efficient way to move data into Azure Synapse Analytics. Use the staging blob feature to achieve high load speeds from all types of data stores, including Azure Blob storage and Data Lake Store.
(Polybase supports Azure Blob storage and Azure Data Lake Store by default.) Reference:
https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-data-warehouse
https://docs.microsoft.com/en-us/azure/data-factory/load-azure-sql-data-warehouse

NEW QUESTION # 153
You plan to develop a dataset named Purchases by using Azure Databricks. Purchases will contain the following columns:
ProductID
ItemPrice
LineTotal
Quantity
StoreID
Minute
Month
Hour
Year
Day
You need to store the data to support hourly incremental load pipelines that will vary for each StoreID. The solution must minimize storage costs.
How should you complete the code? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

Reference:
https://intellipaat.com/community/11744/how-to-partition-and-write-dataframe-in-spark-without-deleting-partitions-with-no-new-data

NEW QUESTION # 154
You have an Azure SQL database named DB1 that contains a table named Table 1.
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.