[Mar 05, 2026] DP-300 Exam Brain Dumps - Study Notes and Theory
Pass Microsoft DP-300 Test Practice Test Questions Exam Dumps
Get information about the topics of Microsoft DP-300 Exam
Candidates must know the exam topics before they start of preparation. Because it will really help them in hitting the core. Our Microsoft DP-300 exam dumps will include the following topics:
- Optimize query performance (5-10%)
- Plan and implement a High Availability and Disaster Recovery (HADR) environment (15-20%)
- Perform administration by using T-SQL (10-15%)
- Implement a secure environment (15-20%)
- Monitor and optimize operational resources (15-20%)
NEW QUESTION # 112
You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.
* Clustered columnstore indexes
* Automatic tuning
* Change tracking
* PolyBase
You plan to migrate DB1 to an Azure SQL database.
What feature should be removed or replaced before DB1 can be migrated?
- A. PolyBase
- B. Change tracking
- C. Clustered columnstore indexes
- D. Automatic tuning
Answer: A
Explanation:
Section: [none]
Explanation:
This table lists the key features for PolyBase and the products in which they're available.
Incorrect Answers:
C: Change tracking is a lightweight solution that provides an efficient change tracking mechanism for applications. It applies to both Azure SQL Database and SQL Server.
D: Azure SQL Database and Azure SQL Managed Instance automatic tuning provides peak performance and stable workloads through continuous performance tuning based on AI and machine learning.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/polybase/polybase-versioned-feature-summary
NEW QUESTION # 113
You have an Azure SQL database that contains a table named Customer. Customer has the columns shown in the following table.
You plan to implement a dynamic data mask for the Customer_Phone column. The mask must meet the following requirements:
* The first six numerals of each customer's phone number must be masked.
* The last four digits of each customer's phone number must be visible.
* Hyphens must be preserved and displayed.
How should you configure the dynamic data mask? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
Explanation:
Box 1: 0
Custom String : Masking method that exposes the first and last letters and adds a custom padding string in the middle. prefix,[padding],suffix Box 2: xxx-xxx Box 3: 5 Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/dynamic-data-masking
NEW QUESTION # 114
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:
Explanation
Graphical user interface, application, Word Description automatically generated
Reference:
https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-managed-instance-online
NEW QUESTION # 115
You have an Azure subscription that contains an Azure SQL managed instance, a database named db1, and an Azure web app named Appl. Appl uses db1.
You need to enable Resource Governor for a App1. The solution must meet the following requirements:
App1 must be able to consume all available CPU resources.
App1 must have at least half of the available CPU resources always available.
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
NEW QUESTION # 116
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.
- A. Enable Lightweight_Query_Profilingin the master database
- B. Enable Lightweight_Query_Profilingin DB1
- C. Enable Last_Query_Plan_Statsin DB1
- D. Enable PARAMETER_SNIFFINGin DB1
- E. Enable Last_Query_Plan_Statsin the master database
Answer: C,E
Explanation:
Section: [none]
Explanation:
Last_Query_Plan_Stats allows you to enable or disable collection of the last query plan statistics (equivalent to
an actual execution plan) in sys.dm_exec_query_plan_stats.
Lightweight profiling can be disabled at the database level using the LIGHTWEIGHT_QUERY_PROFILING
database scoped configuration: ALTER DATABASE SCOPED CONFIGURATION SET
LIGHTWEIGHT_QUERY_PROFILING = OFF;.
Incorrect Answers:
E: Parameter sensitivity, also known as "parameter sniffing", refers to a process whereby SQL Server "sniffs"
the current parameter values during compilation or recompilation, and passes it along to the Query Optimizer
so that they can be used to generate potentially more efficient query execution plans.
Parameter values are sniffed during compilation or recompilation for the following types of batches:
* Stored procedures
* Queries submitted via sp_executesql
* Prepared queries
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-profiling-infrastructure
NEW QUESTION # 117
You have an Azure virtual machine named VM1 on a virtual network named VNet1. Outbound traffic from VM1 to the internet is blocked.
You have an Azure SQL database named SqlDb1 on a logical server named SqlSrv1.
You need to implement connectivity between VM1 and SqlDb1 to meet the following requirements:
* Ensure that all traffic to the public endpoint of SqlSrv1 is blocked.
* Minimize the possibility of VM1 exfiltrating data stored in SqlDb1.
What should you create on VNet1?
- A. an ExpressRoute gateway
- B. a service endpoint
- C. a VPN gateway
- D. a private link
Answer: D
Explanation:
Explanation
Azure Private Link enables you to access Azure PaaS Services (for example, Azure Storage and SQL Database) and Azure hosted customer-owned/partner services over a private endpoint in your virtual network.
Traffic between your virtual network and the service travels the Microsoft backbone network. Exposing your service to the public internet is no longer necessary.
Reference:
https://docs.microsoft.com/en-us/azure/private-link/private-link-overview
NEW QUESTION # 118
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 # 119
You are designing a security model for an Azure Synapse Analytics dedicated SQL pool that will support multiple companies.
You need to ensure that users from each company can view only the data of their respective company.
Which two objects should you include in the solution? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
- A. a column encryption key
- B. a security policy
- C. asymmetric keys
- D. a function
- E. a custom role-based access control (RBAC) role
Answer: B,E
Explanation:
Azure RBAC is used to manage who can create, update, or delete the Synapse workspace and its SQL pools, Apache Spark pools, and Integration runtimes.
Define and implement network security configurations for resources related to your dedicated SQL pool with Azure Policy.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/security/synapse-workspace-synapse-rbac
https://docs.microsoft.com/en-us/security/benchmark/azure/baselines/synapse-analytics-security-baseline
NEW QUESTION # 120
You have an Azure SQL database named that contains a table named Table1.
You run a query to bad data into Table1.
The performance Of Table1 during the load operation are shown in exhibit.
Answer:
Explanation:
Explanation
NEW QUESTION # 121
You have SQL Server 2019 on an Azure virtual machine that contains an SSISDB database.
A recent failure causes the master database to be lost.
You discover that all Microsoft SQL Server integration Services (SSIS) packages fail to run on the virtual machine.
Which four actions should you perform in sequence to resolve the issue? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct.
Answer:
Explanation:
Explanation:
Step 1: Attach the SSISDB database
Step 2: Turn on the TRUSTWORTHY property and the CLR property
If you are restoring the SSISDB database to an SQL Server instance where the SSISDB catalog was never created, enable common language runtime (clr) Step 3: Open the master key for the SSISDB database Restore the master key by this method if you have the original password that was used to create SSISDB.
open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB' Alter Master Key Add encryption by Service Master Key Step 4: Encrypt a copy of the mater key by using the service master key Reference:
https://docs.microsoft.com/en-us/sql/integration-services/backup-restore-and-move-the-ssis-catalog
NEW QUESTION # 122
You plan to migrate on-premises Microsoft SQL Server databases to Azure.
You need to identify which deployment and resiliency options meet the following requirements:
* Support user-initiated backups.
* Support multiple automatically replicated instances across Azure regions.
* Minimize administrative effort to implement and maintain business continuity.
What should you identify? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
Box 1: SQL Server on Azure VMs
SQL Server on Azure Virtual Machines can take advantage of Automated Backup, which regularly creates backups of your database to blob storage. You can also manually use this technique.
Box 2: Active geo-replication
Geo-replication for services such as Azure SQL Database and Cosmos DB will create secondary replicas of your data across multiple regions. While both services will automatically replicate data within the same region, geo-replication protects you against a regional outage by enabling you to fail over to a secondary region.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-on-azure-vm-iaas- what-is-overview
https://docs.microsoft.com/en-us/dotnet/architecture/cloud-native/infrastructure-resiliency-azure
NEW QUESTION # 123
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?
- A. Configure the Advanced Data Security settings for ResearchDBL
- B. Configure the Deleted databases settings for ResearchSrvOL
- C. Deploy and configure an Azure Backup server.
- D. Configure the Manage Backups settings for ResearchSrvOL
Answer: D
Explanation:
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/long-term-backup-retention-configure
NEW QUESTION # 124
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 # 125
You have an Azure subscription that contains the resources shown in the following table.
You plan louse SQLDB11 as an elastic job database to run jobs on SQlDb11 andSOtDB22. What is the minimum number of database scoped credentials required tor the elastic jobs?
- A. 0
- B. 1
- C. 2
- D. 3
Answer: D
NEW QUESTION # 126
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:
Answer:
Explanation:
Section: [none]
Explanation:
Scenario: Authenticate database users by using Active Directory credentials.
The configuration steps include the following procedures to configure and use Azure Active Directory authentication.
1. Create and populate Azure AD.
2. Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
3. Create an Azure Active Directory administrator. (Step 1)
4. Configure your client computers.
5. Create contained database users in your database mapped to Azure AD identities. (Step 2)
6. 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 Testlet 2 Case study 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
General Overview
Contoso, Ltd. is a financial data company that has 100 employees. The company delivers financial data to customers.
Physical Locations
Contoso has a datacenter in Los Angeles and an Azure subscription. All Azure resources are in the US West 2 Azure region. Contoso has a 10-Gb ExpressRoute connection to Azure.
The company has customers worldwide.
Existing Environment
Active Directory
Contoso has a hybrid Azure Active Directory (Azure AD) deployment that syncs to on-premises Active Directory.
Database Environment
Contoso has SQL Server 2017 on Azure virtual machines shown in the following table.
SQL1 and SQL2 are in an Always On availability group and are actively queried. SQL3 runs jobs, provides historical data, and handles the delivery of data to customers.
The on-premises datacenter contains a PostgreSQL server that has a 50-TB database.
Current Business Model
Contoso uses Microsoft SQL Server Integration Services (SSIS) to create flat files for customers. The customers receive the files by using FTP.
Requirements
Planned Changes
Contoso plans to move to a model in which they deliver data to customer databases that run as platform as a service (PaaS) offerings. When a customer establishes a service agreement with Contoso, a separate resource group that contains an Azure SQL database will be provisioned for the customer. The database will have a complete copy of the financial data. The data to which each customer will have access will depend on the service agreement tier. The customers can change tiers by changing their service agreement.
The estimated size of each PaaS database is 1 TB.
Contoso plans to implement the following changes:
* Move the PostgreSQL database to Azure Database for PostgreSQL during the next six months.
* Upgrade SQL1, SQL2, and SQL3 to SQL Server 2019 during the next few months.
* Start onboarding customers to the new PaaS solution within six months.
Business Goals
Contoso identifies the following business requirements:
* Use built-in Azure features whenever possible.
* Minimize development effort whenever possible.
* Minimize the compute costs of the PaaS solutions.
* Provide all the customers with their own copy of the database by using the PaaS solution.
* Provide the customers with different table and row access based on the customer's service agreement.
* In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The solution must provide automatic failover.
* Ensure that users of the PaaS solution can create their own database objects but be prevented from modifying any of the existing database objects supplied by Contoso.
Technical Requirements
Contoso identifies the following technical requirements:
* Users of the PaaS solution must be able to sign in by using their own corporate Azure AD credentials or have Azure AD credentials supplied to them by Contoso. The solution must avoid using the internal Azure AD of Contoso to minimize guest users.
* All customers must have their own resource group, Azure SQL server, and Azure SQL database. The deployment of resources for each customer must be done in a consistent fashion.
* Users must be able to review the queries issued against the PaaS databases and identify any new objects created.
* Downtime during the PostgreSQL database migration must be minimized.
Monitoring Requirements
Contoso identifies the following monitoring requirements:
* Notify administrators when a PaaS database has a higher than average CPU usage.
* Use a single dashboard to review security and audit data for all the PaaS databases.
* Use a single dashboard to monitor query performance and bottlenecks across all the PaaS databases.
* Monitor the PaaS databases to identify poorly performing queries and resolve query performance issues automatically whenever possible.
PaaS Prototype
During prototyping of the PaaS solution in Azure, you record the compute utilization of a customer's Azure SQL database as shown in the following exhibit.
Role Assignments
For each customer's Azure SQL Database server, you plan to assign the roles shown in the following exhibit.
NEW QUESTION # 127
You have a burstable Azure virtual machine named VMI that hosts an instance of Microsoft SQL Server.
You need to attach an Azure ultra disk to VMI. The solution must minimize downtime on VMI.
In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
1 - Stop and deallocate VM1.
2 - Attach the ultra disk.
3 - Set Enable Ultra disk compatibility to Yes.
4 - Resize VM1.
5 - Start VM1.
NEW QUESTION # 128
Your company wants to create an Azure SQL Database. The database will contain a table that would have Employee information. There are columns which contain sensitive information. You have to enabled Always Encryption for the data stored in the Employee table.
Which of the following could be used to store the encryption keys required for enabling Always Encrypted for the table?
- A. Azure Key vault
- B. Public Encryption keys
- C. Private Encryption keys
- D. Windows certificate store
Answer: A,D
Explanation:
The Encryption keys can only be stored in either Azure Key vault or the windows certificate store.
From the implementation shown below, you can see that these are the sources you can use for storing the encryption keys.
https://docs.microsoft.com/en-us/azure/azure-sql/database/always-encrypted-azure-key-vault-configure?tabs=azure-powershell
NEW QUESTION # 129
Hotspot Question
You have two Azure SQL databases named SQLDB1 and SQLDB2 in the Hyperscale service tier. SQLDB1 was set to the Hyperscale service tier when the database was created. SQLDB2 was changed from the Business Critical service tier to the Hyperscale service tier during the last
14 days.
You need to change SQLDB1 and SQLDB2 to the Business Critical service tier. The solution must minimize the administrative effort.
What should you do first? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
Box 1: Export the data
For databases that don't qualify for reverse migration [See Limitations for reverse migration below], the only way to migrate from Hyperscale to a non-Hyperscale service tier is to export/import using a bacpac file or other data movement technologies (Bulk Copy, Azure Data Factory, Azure Databricks, SSIS, etc.) Box 2: Set the Service tier to General Purpose.
Reverse migrate a database from Hyperscale
You can migrate an existing Hyperscale database in Azure SQL Database to the General Purpose service tier using the Azure portal, the Azure CLI, PowerShell, or Transact-SQL.
Reverse migration to the General Purpose service tier allows customers who have recently converted an existing database in Azure SQL Database to Hyperscale to move back in an emergency, should Hyperscale not meet their needs. While reverse migration is initiated by a service tier change, it's essentially a size-of-data move between different architectures.
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/reverse-migrate-from-hyperscale
NEW QUESTION # 130
You are performing exploratory analysis of bus fare data in an Azure Data Lake Storage Gen2 account by using an Azure Synapse Analytics serverless SQL pool.
You execute the Transact-SQL query 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.
Answer:
Explanation:
Explanation
Graphical user interface, table Description automatically generated
Box 1: CSV files that have file named beginning with "tripdata_2020"
Box 2: a header
FIRSTROW = 'first_row'
Specifies the number of the first row to load. The default is 1 and indicates the first row in the specified data file. The row numbers are determined by counting the row terminators. FIRSTROW is 1-based.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-openrowset
NEW QUESTION # 131
You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.
You need to deploy a failover cluster instance (FCI) to VM1 and VM2 that will use Azure shared disks. The solution must maximize resiliency.
Which quorum option should you use?
- A. node majority with a file share witness
- B. node majority with a cloud witness
- C. node majority with no witness
- D. node majority with a disk witness
Answer: D
Explanation:
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/hadr-cluster-best-practices?view=azuresql&tabs=windows2012#quorum
The disk witness is the most resilient quorum option, but to use a disk witness on a SQL Server on Azure VM, you must use an Azure Shared Disk which imposes some limitations to the high availability solution. As such, use a disk witness when you're configuring your failover cluster instance with Azure Shared Disks, otherwise use a cloud witness whenever possible.
NEW QUESTION # 132
......
Verified DP-300 dumps Q&As - DP-300 dumps with Correct Answers: https://www.topexamcollection.com/DP-300-vce-collection.html
The Best Microsoft Azure Study Guide for the DP-300 Exam: https://drive.google.com/open?id=1YrHqUhFIGKB-ZtfjqULH6su_CtA13NfZ
