2023 Updated Verified DP-300 Q&As - Pass Guarantee or Full Refund
[Jun-2023] DP-300 Certification with Actual Questions from TestkingPass
The Microsoft DP-300 certification exam is ideal for database administrators, database developers, and IT professionals who work with Microsoft Azure. The certification exam is a great way to enhance your career prospects and demonstrate your expertise to potential employers. By passing the DP-300 exam, you will become a Microsoft Certified: Azure Database Administrator Associate, which is a highly respected certification in the industry.
NEW QUESTION # 140
You have an Azure SQL database.
You have a query and the associated execution plan 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.
Answer:
Explanation:
Explanation
Graphical user interface, text, application, email Description automatically generated
Box 1: Key Lookup
The Key Lookup cost is 99% so that is the performance bottleneck.
Box 2: nonclustered index
The key lookup on the clustered index is used because the nonclustered index does not include the required columns to resolve the query. If you add the required columns to the nonclustered index, the key lookup will not be required.
NEW QUESTION # 141
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.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
NEW QUESTION # 142
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.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online
NEW QUESTION # 143
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 are a.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
NEW QUESTION # 144
You have an Azure SQL database.
You are reviewing a slow performing query 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.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15
NEW QUESTION # 145
You have an Always On availability group deployed to Azure virtual machines. The availability group contains a database named DB1 and has two nodes named SQL1 and SQL2. SQL1 is the primary replica.
You need to initiate a full backup of DB1 on SQL2.
Which statement should you run?
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/
- A. mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ - B. mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ - C. mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);
- D. mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);
BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/
Answer: A
Explanation:
Section: [none]
Explanation:
BACKUP DATABASE supports only copy-only full backups of databases, files, or filegroups when it's executed on secondary replicas. Copy-only backups don't impact the log chain or clear the differential bitmap.
Incorrect Answers:
A: Differential backups are not supported on secondary replicas. The software displays this error because the secondary replicas support copy-only database backups.
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup- on-secondary-replicas-always-on-availability-groups
NEW QUESTION # 146
You have a resource group named App1Dev that contains an Azure SQL Database server named DevServer1. DevServer1 contains an Azure SQL database named DB1. The schema and permissions for DB1 are saved in a Microsoft SQL Server Data Tools (SSDT) database project.
You need to populate a new resource group named App1Test with the DB1 database and an Azure SQL Server named TestServer1. The resources in App1Test must have the same configurations as the resources in App1Dev.
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.
Answer:
Explanation:
1 - From the Azure portal, export the Azure Resource Manager templates
2 - Change the server name and related variables in the templates
3 - From the Azure portal, deploy the templates.
4 - From the database project, deploy the database schema and permissions
NEW QUESTION # 147
You have a new Azure SQL database named DB1 on an Azure SQL server named AzSQL1.
The only user who was created is the server administrator.
You need to create a contained database user in DB1 who will use Azure Active Directory (Azure AD) for authentication.
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:
Explanation:
Step 1: Set up the Active Directory Admin for AzSQL1.
Step 2: Connect to DB1 by using the server administrator.
Sign into your managed instance with an Azure AD login granted with the sysadmin role.
Step 3: Create a user by using the FROM EXTERNAL PROVIDER clause.
FROM EXTERNAL PROVIDER is available for creating server-level Azure AD logins in SQL Database managed instance. Azure AD logins allow database-level Azure AD principals to be mapped to server-level Azure AD logins. To create an Azure AD user from an Azure AD login use the following syntax:
CREATE USER [AAD_principal] FROM LOGIN [Azure AD login]
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
NEW QUESTION # 148
You have a new Azure SQL database named DB1 on an Azure SQL server named AzSQL1.
The only user who was created is the server administrator.
You need to create a contained database user in DB1 who will use Azure Active Directory (Azure AD) for authentication.
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 - Set up the Active Directory Admin for AzSQL1.
2 - Connect to DB1 by using the server administrator.
3 - Create a user by using the FROM EXTERNAL PROVIDER clause.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql
NEW QUESTION # 149
You have SQL Server on an Azure virtual machine that contains a database named DB1.
You view a plan summary that shows the duration in milliseconds of each execution of query 1178902 as
shown in the following exhibit:
What should you do to ensure that the query uses the execution plan which executes in the least amount of
time?
- A. Force the query execution plan for plan 1220917.
- B. Force the query execution plan for plan 1221065.
- C. Run the DBCC FREEPROCCACHEcommand.
- D. Disable parameter sniffing.
Answer: A
Explanation:
Section: [none]
Explanation/Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usage-scenarios
NEW QUESTION # 150
You have an Azure SQL Database instance named DatabaseA on a server named Server1.
You plan to add a new user named App1 to DatabaseA and grant App1 db_datacenter permissions. App1 will use SQL Server Authentication.
You need to create App1. The solution must ensure that App1 can be given access to other databases by using the same credentials.
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 - On the master database, run CREATE LOGIN [App1] WITH PASSWORD = 'p@aaW0rd!'
2 - On DatabaseA, run CREATE USER [App1] FROM LOGIN [App1]
3 - On DatabaseA run ALTER ROLE db_datareader ADD Member [App1]
Reference:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/
NEW QUESTION # 151
HOTSPOT
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.
Hot Area:
Answer:
Explanation:
Section: [none]
Explanation:
Azure Database Migration service
Box 1: Premium 4-VCore
Scenario: Migrate the SERVER1 databases to the Azure SQL Database platform.
* Minimize downtime during the migration of the SERVER1 databases.
Premimum 4-vCore is for large or business critical workloads. It supports online migrations, offline migrations,
and faster migration speeds.
Incorrect Answers:
The Standard pricing tier suits most small- to medium- business workloads, but it supports offline migration
only.
Box 2: A VPN gateway
You need to create a Microsoft Azure Virtual Network for the Azure Database Migration Service by using the
Azure Resource Manager deployment model, which provides site-to-site connectivity to your on-premises
source servers by using either ExpressRoute or VPN.
Reference:
https://azure.microsoft.com/pricing/details/database-migration/
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online
DP-300
Administering Relational Databases on Microsoft Azure
Version 2.0
Testlet 1
This is a case study. Case studies are not timed separately. You can use as much exam time as you
would like to complete each case. However, there may be additional case studies and sections on this exam.
You must manage your time to ensure that you are able to complete all questions included on this exam in the
time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the
case study. Case studies might contain exhibits and other resources that provide more information about the
scenario that is described in the case study. Each question is independent of the other questions in this case
study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to
make changes before you move to the next section of the exam. After you begin a new section, you cannot
return to this section.
To start the case study
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore
the content of the case study before you answer the questions. Clicking these buttons displays information such
as business requirements, existing environment, and problem statements. If the case study has an All
Information tab, note that the information displayed is identical to the information displayed on the subsequent
tabs. When you are ready to answer a question, click the Question button to return to the question.
Overview
Litware, Inc. is a renewable energy company that has a main office in Boston. The main office hosts a sales
department and the primary datacenter for the company.
Physical Locations
Existing Environment
Litware has a manufacturing office and a research office is separate locations near Boston. Each office has its
own datacenter and internet connection.
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
Network Environment
The primary datacenter has an ExpressRoute connection that uses both Microsoft peering and private peering.
The private peering connects to an Azure virtual network named HubVNet.
Identity Environment
Litware has a hybrid Azure Active Directory (Azure AD) deployment that uses a domain named litwareinc.com.
All Azure subscriptions are associated to the litwareinc.com Azure AD tenant.
Database Environment
The sales department has the following database workload:
* An on-premises named SERVER1 hosts an instance of Microsoft SQL Server 2012 and two 1-TB
databases.
* A logical server named SalesSrv01A contains a geo-replicated Azure SQL database named SalesSQLDb1.
SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool. SalesSQLDb1 uses database firewall rules
and contained database users.
* An application named SalesSQLDb1App1 uses SalesSQLDb1.
The manufacturing office contains two on-premises SQL Server 2016 servers named SERVER2 and
SERVER3. The servers are nodes in the same Always On availability group. The availability group contains a
database named ManufacturingSQLDb1
Database administrators have two Azure virtual machines in HubVnet named VM1 and VM2 that run Windows
Server 2019 and are used to manage all the Azure databases.
Licensing Agreement
Litware is a Microsoft Volume Licensing customer that has License Mobility through Software Assurance.
Current Problems
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking
queries.
Requirements
Planned Changes
Litware plans to implement the following changes:
* Implement 30 new databases in Azure, which will be used by time-sensitive manufacturing apps that have
varying usage patterns. Each database will be approximately 20 GB.
* Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01.
ResearchDB1 will contain Personally Identifiable Information (PII) data.
* Develop an app named ResearchApp1 that will be used by the research department to populate and access
ResearchDB1.
* Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
* Migrate the SERVER1 databases to the Azure SQL Database platform.
Technical Requirements
Litware identifies the following technical requirements:
* Maintenance tasks must be automated.
* The 30 new databases must scale automatically.
* The use of an on-premises infrastructure must be minimized.
* Azure Hybrid Use Benefits must be leveraged for Azure SQL Database deployments.
* 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.
Security and Compliance Requirements
Litware identifies the following security and compliance requirements:
* Store encryption keys in Azure Key Vault.
* Retain backups of the PII data for two months.
* Encrypt the PII data at rest, in transit, and in use.
* Use the principle of least privilege whenever possible.
* Authenticate database users by using Active Directory credentials.
* Protect Azure SQL Database instances by using database-level firewall rules.
* Ensure that all databases hosted in Azure are accessible from VM1 and VM2 without relying on public
endpoints.
Business Requirements
Litware identifies the following business requirements:
* Meet an SLA of 99.99% availability for all Azure deployments.
* Minimize downtime during the migration of the SERVER1 databases.
* Use the Azure Hybrid Use Benefits when migrating workloads to Azure.
* Once all requirements are met, minimize costs whenever possible.
NEW QUESTION # 152
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.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable
NEW QUESTION # 153
You have the following Azure Resource Manager template.
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:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models
https://docs.microsoft.com/en-us/azure/azure-sql/database/single-database-create-arm-template-quickstart
NEW QUESTION # 154
You are building an Azure Stream Analytics job to retrieve game data.
You need to ensure that the job returns the highest scoring record for each five-minute time interval of each game.
How should you complete the Stream Analytics query? 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/stream-analytics-query/topone-azure-stream-analytics
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md
NEW QUESTION # 155
You have a new Azure SQL database. The database contains a column that stores confidential information.
You need to track each time values from the column are returned in a query. The tracking information must be stored for 365 days from the date the query was executed.
Which three actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. Apply sensitivity labels named Highly Confidential to the column.
- B. Turn on auditing and write audit logs to an Azure Storage account.
- C. Add extended properties to the column.
- D. Turn on Azure Advanced Threat Protection (ATP).
- E. Turn on Advanced Data Security for the Azure SQL server.
Answer: A,B,E
Explanation:
C: Advanced Data Security (ADS) is a unified package for advanced SQL security capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive data D: You can apply sensitivity-classification labels persistently to columns by using new metadata attributes that have been added to the SQL Server database engine. This metadata can then be used for advanced, sensitivity-based auditing and protection scenarios.
A: An important aspect of the information-protection paradigm is the ability to monitor access to sensitive data.
Azure SQL Auditing has been enhanced to include a new field in the audit log called data_sensitivity_information. This field logs the sensitivity classifications (labels) of the data that was returned by a query. Here's an example:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/data-discovery-and-classification-overview
NEW QUESTION # 156
You have an Azure subscription that contains an Azure SQL database named SQLDb1. SQLDb1 contains a table named Table1.
You plan to deploy an Azure web app named webapp1 that will export rows in Table1 that have changed.
You need to ensure that webapp1 can identity the changes to Table'. The solution must meet the following requirements:
* Minimize compute times.
* Minimize storage.
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 - Connect to SQLDb1 and run the following Transact-SQL statment ALTER DATABASE SQLDb1 SET,,,,,
2 - Connect to SQLDb1 and run the following Transact-SQL statment ALTER TABLEdbo,,,,
3 - From webapp1,connect to,,,,,,
NEW QUESTION # 157
HOTSPOT
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.
Hot Area:
Answer:
Explanation:
Section: [none]
Explanation:
Box 1: Node majority with witness
As a general rule when you configure a quorum, the voting elements in the cluster should be an odd number.
Therefore, if the cluster contains an even number of voting nodes, you should configure a disk witness or a file share witness.
Note: Mode: Node majority with witness (disk or file share)
Nodes have votes. In addition, a quorum witness has a vote. The cluster quorum is the majority of voting nodes in the active cluster membership plus a witness vote. A quorum witness can be a designated disk witness or a designated file share witness.
Box 2: Azure Standard Load Balancer
Microsoft guarantees that a Load Balanced Endpoint using Azure Standard Load Balancer, serving two or more Healthy Virtual Machine Instances, will be available 99.99% of the time.
Scenario: Business Requirements
Litware identifies business requirements include: meet an SLA of 99.99% availability for all Azure deployments.
Incorrect Aswers:
Basic Balancer: No SLA is provided for Basic Load Balancer.
Note: There are two main options for setting up your listener: external (public) or internal. The external (public) listener uses an internet facing load balancer and is associated with a public Virtual IP (VIP) that is accessible over the internet. An internal listener uses an internal load balancer and only supports clients within the same Virtual Network.
Reference:
https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness
https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/
NEW QUESTION # 158
You have an Azure data factory that has two pipelines named PipelineA and PipelineB.
PipelineA has four activities as shown in the following exhibit.
PipelineB has two activities as shown in the following exhibit.
You create an alert for the data factory that uses Failed pipeline runs metrics for both pipelines and all failure types. The metric has the following settings:
Operator: Greater than
Aggregation type: Total
Threshold value: 2
Aggregation granularity (Period): 5 minutes
Frequency of evaluation: Every 5 minutes
Data Factory monitoring records the failures shown in the following table.
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:
Reference:
https://docs.microsoft.com/en-us/azure/azure-monitor/alerts/alerts-metric-overview
NEW QUESTION # 159
A company plans to use Apache Spark analytics to analyze intrusion detection data.
You need to recommend a solution to analyze network and system activity data for malicious activities and policy violations. The solution must minimize administrative efforts.
What should you recommend?
- A. Azure HDInsight
- B. Azure Databricks
- C. Azure Data Factory
- D. Azure Data Lake Storage
Answer: A
Explanation:
Explanation
Azure HDInsight offers pre-made, monitoring dashboards in the form of solutions that can be used to monitor the workloads running on your clusters. There are solutions for Apache Spark, Hadoop, Apache Kafka, live long and process (LLAP), Apache HBase, and Apache Storm available in the Azure Marketplace.
Note: With Azure HDInsight you can set up Azure Monitor alerts that will trigger when the value of a metric or the results of a query meet certain conditions. You can condition on a query returning a record with a value that is greater than or less than a certain threshold, or even on the number of results returned by a query. For example, you could create an alert to send an email if a Spark job fails or if a Kafka disk usage becomes over
90 percent full.
Reference:
https://azure.microsoft.com/en-us/blog/monitoring-on-azure-hdinsight-part-4-workload-metrics-and-logs/
NEW QUESTION # 160
You create a new Azure SQL managed instance named SQL1 and enable Database Mail extended stored procedures.
You need to ensure that SOL Server Agent jobs running on SQL 1 can notify administrators when a failure occurs.
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 a Database Mail account.
2 - Create a profile named AzureManagedInstance_dbmail_profile.
3 - Enable email notification upon failure.
NEW QUESTION # 161
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.
Answer:
Explanation:
Reference:
https://technet.microsoft.com/windows-server-docs/failover-clustering/deploy-cloud-witness
https://azure.microsoft.com/en-us/support/legal/sla/load-balancer/v1_0/
NEW QUESTION # 162
You have an Azure SQL database.
You run the following PowerShell script.
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:
Reference:
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackupshorttermretentionpolicy?view=azps-7.2.0
https://docs.microsoft.com/en-us/powershell/module/az.sql/set-azsqldatabasebackuplongtermretentionpolicy?view=azps-7.2.0
NEW QUESTION # 163
You have a database named db1.
The log for db1 contains the following entry.
You need to ensure That db1 can process transactions.
Answer:
Explanation:
1 - Add db1 back to the availability group.
2 - Shrink db1.
3 - Shrink the transaction log file.
4 - Remove db1 from the availability group.
5 - Back up the transaction log file.
NEW QUESTION # 164
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:
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 # 165
......
Skills measured
- Plan and implement a High Availability and Disaster Recovery (HADR) environment (15-20%)
- Plan and implement data platform resources (15-20%)
- Perform automation of tasks (10-15%)
- Perform administration by using T-SQL (10-15%)
- Implement a secure environment (15-20%)
- Optimize query performance (5-10%)
Microsoft DP-300 Exam Syllabus Topics:
| Topic | Details |
|---|---|
| Topic 1 |
|
| Topic 2 |
|
| Topic 3 |
|
| Topic 4 |
|
| Topic 5 |
|
| Topic 6 |
|
DP-300 Real Valid Brain Dumps With 256 Questions: https://www.testkingpass.com/DP-300-testking-dumps.html
Updated DP-300 Dumps PDF: https://drive.google.com/open?id=1scNJWkuaa_d9xn4W53clGfkkyN81kDJ1