Pass4itSure Microsoft DP-300 exam dumps are designed with the help of Microsoft’s real exam content. You can get DP-300 VCE dumps and DP-300 PDF dumps from Pass4itSure! Check out the best and updated DP-300 exam questions by Pass4itSure DP-300 dumps https://www.pass4itsure.com/dp-300.html (Q&As: 142) (VCE and PDF), we are very confident that you will be successful on the Microsoft DP-300 exam.
Microsoft DP-300 exam questions in PDF file
Download those Pass4itSure DP-300 pdf from Google Drive: https://drive.google.com/file/d/1YDM-X1bV-r636nqLbD8gxt29jN4_9DfK/view?usp=sharing
Microsoft Other Certifications
AI-102 Exam: Designing and Implementing a Microsoft Azure AI Solution (beta)
Free AI-102 Exam Questions Answers
https://www.microsoft-technet.com/get-pass4itsure-microsoft-ai-102-exam-dumps-as-practice-test-and-pdf.html
AI-900 Exam: Microsoft Azure AI Fundamentals
Free AI-102 Exam Questions Answers https://www.microsoft-technet.com/get-pass4itsure-microsoft-ai-900-exam-dumps-as-practice-test-and-pdf.html
AZ-140 Exam: Configuring and Operating Windows Virtual Desktop on Microsoft Azure
Free AZ-140 Exam Questions Answers https://www.microsoft-technet.com/get-pass4itsure-microsoft-az-140-exam-dumps-as-practice-test-and-pdf.html
AZ-220 Exam: Microsoft Azure IoT Developer
Free AZ-220 Exam Questions Answers https://www.microsoft-technet.com/get-pass4itsure-microsoft-az-220-exam-dumps-as-practice-test-and-pdf.html
AZ-600 Exam: Configuring and Operating a Hybrid Cloud with Microsoft Azure Stack Hub
Free AZ-600 Exam Questions Answers https://www.microsoft-technet.com/get-pass4itsure-microsoft-az-600-exam-dumps-as-practice-test-and-pdf.html
DP-203 Exam: Data Engineering on Microsoft Azure
Free DP-203 Exam Questions Answers https://www.microsoft-technet.com/get-pass4itsure-microsoft-dp-203-exam-dumps-as-practice-test-and-pdf.html
Following are some Microsoft DP-300 exam questions for review (Microsoft practice test 1-13)
QUESTION 1
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. Live Query Statistics
B. an estimated execution plan
C. an actual execution plan
D. Client Statistics
Correct Answer: C
To include an execution plan for a query during execution
1.
On the SQL Server Management Studio toolbar, click Database Engine Query. You can also open an existing query
and display the estimated execution plan by clicking the Open File toolbar button and locating the existing query.
2.
Enter the query for which you would like to display the actual execution plan.
3.
On the Query menu, click Include Actual Execution Plan or click the Include Actual Execution Plan toolbar button.
Note: Actual execution plans are generated after the Transact-SQL queries or batches execute. Because of this, an
actual execution plan contains runtime information, such as actual resource usage metrics and runtime warnings (if
any). The execution plan that is generated displays the actual query execution plan that the SQL Server Database
Engine used to execute the queries.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan
QUESTION 2
DRAG DROP
You have an Azure SQL database that contains a table named Employees. Employees contains a column named
Salary.
You need to encrypt the Salary column. The solution must prevent database administrators from reading the data in the
Salary column and must provide the most secure encryption.
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.
Step 1: Create a column master key
Create a column master key metadata entry before you create a column encryption key metadata entry in the database
and before any column in the database can be encrypted using Always Encrypted.
Step 2: Create a column encryption key.
Step 3: Encrypt the Salary column by using the randomized encryption type.
Randomized encryption uses a method that encrypts data in a less predictable manner. Randomized encryption is more
secure, but prevents searching, grouping, indexing, and joining on encrypted columns.
Note: A column encryption key metadata object contains one or two encrypted values of a column encryption key that is
used to encrypt data in a column. Each value is encrypted using a column master key.
Incorrect Answers:
Deterministic encryption.
Deterministic encryption always generates the same encrypted value for any given plain text value. Using deterministic
encryption allows point lookups, equality joins, grouping and indexing on encrypted columns. However, it may also
allow
unauthorized users to guess information about encrypted values by examining patterns in the encrypted column,
especially if there\\’s a small set of possible encrypted values, such as True/False, or North/South/East/West region.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
QUESTION 3
HOTSPOT
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:
Correct Answer:
QUESTION 4
You have SQL Server on Azure virtual machines in an availability group.
You have a database named DB1 that is NOT in the availability group.
You create a full database backup of DB1.
You need to add DB1 to the availability group.
Which restore option should you use on the secondary replica?
A. Restore with Recovery
B. Restore with Norecovery
C. Restore with Standby
Correct Answer: B
Prepare a secondary database for an Always On availability group requires two steps:
1.
Restore a recent database backup of the primary database and subsequent log backups onto each server instance that
hosts the secondary replica, using RESTORE WITH NORECOVERY
2.
Join the restored database to the availability group.
Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-asecondary-database-for-an-availability-group-sql-server
QUESTION 5
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. PARAMETERIZATION to FORCED
B. PARAMETERIZATION to SIMPLE
C. Delayed Durability to Forced
D. READ_COMMITTED_SNAPSHOT to ON
Correct Answer: D
In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted
data modifications using either:
The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
The SNAPSHOT isolation level.
If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database), the Database Engine uses row
versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of
the
statement. Locks are not used to protect the data from updates by other transactions.
Incorrect Answers:
A: When the PARAMETERIZATION database option is set to SIMPLE, the SQL Server query optimizer may choose to
parameterize the queries. This means that any literal values that are contained in a query are substituted with
parameters. This process is referred to as simple parameterization. When SIMPLE parameterization is in effect, you
cannot control which queries are parameterized and which queries are not.
B: You can specify that all queries in a database be parameterized by setting the PARAMETERIZATION database
option to FORCED. This process is referred to as forced parameterization.
C: Delayed transaction durability is accomplished using asynchronous log writes to disk. Transaction log records are
kept in a buffer and written to disk when the buffer fills or a buffer flushing event takes place. Delayed transaction
durability reduces both latency and contention within the system.
Some of the cases in which you could benefit from using delayed transaction durability are:
You can tolerate some data loss.
You are experiencing a bottleneck on transaction log writes.
Your workloads have a high contention rate.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql
QUESTION 6
HOTSPOT
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:
Correct Answer:
Box 1: SET AUTOMATIC_TUNING = AUTO
To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO
Setting automatic tuning to AUTO will apply Azure Defaults.
Box 2: SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this
one:
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option.
Setting it to OFF will also override any setting that database inherited and disable the tuning option.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable
QUESTION 7
DRAG DROP
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.
Select and Place:
Correct Answer:
QUESTION 8
DRAG DROP
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:
Step 1: Create an Azure Policy Initiative
The first step in enforcing compliance with Azure Policy is to assign a policy definition. A policy definition defines under
what condition a policy is enforced and what effect to take.
With an initiative definition, you can group several policy definitions to achieve one overarching goal. An initiative
evaluates resources within scope of the assignment for compliance to the included policies.
Step 2: Create an Azure Policy Initiative assignment
Assign the initiative definition you created in the previous step.
Step 3: Run Azure Policy remediation tasks
To apply the Policy Initiative to the existing SQL databases.
Reference:
https://docs.microsoft.com/en-us/azure/governance/policy/tutorials/create-and-manage
QUESTION 9
DRAG DROP
You have SQL Server on an Azure virtual machine named SQL1.
SQL1 has an agent job to back up all databases.
You add a user named dbadmin1 as a SQL Server Agent operator.
You need to ensure that dbadmin1 receives an email alert if a 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.
Select and Place:
Correct Answer:
Step 1: Enable the email settings for the SQL Server Agent.
To send a notification in response to an alert, you must first configure SQL Server Agent to send mail.
Step 2: Create a job alert
Step 3: Create a job notification
Example:
— adds an e-mail notification for the specified alert (Test Alert)
— This example assumes that Test Alert already exists
— and that François Ajenstat is a valid operator name.
USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N\\’Test Alert\\’,
@operator_name = N\\’François Ajenstat\\’,
@notification_method = 1 ;
GO
Reference:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status
https://docs.microsoft.com/en-us/sql/ssms/agent/assign-alerts-to-an-operator
QUESTION 10
HOTSPOT
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.
Hot Area:
Box 1: delete a row from Table1
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.
Box 2: db_datareader
Members of the db_datareader fixed database role can read all data from all user tables.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles
QUESTION 11
HOTSPOT
You have an Azure SQL Database managed instance named sqldbmi1 that contains a database name Sales.
You need to initiate a backup of Sales.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
Box 1: TO URL = \\’https://storage1.blob.core.windows.net/blob1/Sales.bak\\’
Native database backup in Azure SQL Managed Instance.
You can backup any database using standard BACKUP T-SQL command:
BACKUP DATABASE tpcc2501
TO URL = \\’https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak\\’
WITH COPY_ONLY
Box 2: WITH COPY_ONLY
Reference:
https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-instance/bap/386154
QUESTION 12
DRAG DROP
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.
Select and Place:
Scenario: Authenticate database users by using Active Directory credentials.
The configuration steps include the following procedures to configure and use Azure Active Directory authentication.
Create and populate Azure AD.
Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
Create an Azure Active Directory administrator. (Step 1)
Configure your client computers.
Create contained database users in your database mapped to Azure AD identities. (Step 2) Connect to your database
by using Azure AD identities. (Step 3)
Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview
QUESTION 13
HOTSPOT
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:
Correct Answer:
Box 1: The server, the elastic pool, and the database Senario:
SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool.
Litware technical requirements include: all SQL Server and Azure SQL Database metrics related to CPU and storage
usage and limits must be analyzed by using Azure built-in functionality.
Box 2: Azure Event hubs
Scenario: Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
Event hubs are able to handle custom metrics.
Incorrect Answers:
Azure Log Analytics
Azure metric and log data are sent to Azure Monitor Logs, previously known as Azure Log Analytics, directly by Azure.
Azure SQL Analytics is a cloud only monitoring solution supporting streaming of diagnostics telemetry for all of your
Azure
SQL databases.
However, because Azure SQL Analytics does not use agents to connect to Azure Monitor, it does not support
monitoring of SQL Server hosted on-premises or in virtual machines.
PS.
Thanks for reading! Hope the newest DP-300 exam dumps can help you in your exam. Get full DP-300 exam questions to try Pass4itSure DP-300 dumps! DP-300 dumps in VCE and PDF are here: https://www.pass4itsure.com/dp-300.html (Updated: Jul 09, 2021).
Download Pass4itSure DP-300 dumps pdf from Google Drive: https://drive.google.com/file/d/1YDM-X1bV-r636nqLbD8gxt29jN4_9DfK/view?usp=sharing (DP-300 Exam Questions)
