[Nov-2025] Microsoft DP-300 Official Cert Guide PDF
Exam DP-300: Administering Relational Databases on Microsoft Azure - ActualVCE
The Microsoft DP-300 exam covers a wide range of topics, including configuring and managing databases, securing databases, implementing disaster recovery solutions, and monitoring and optimizing database performance. Candidates are expected to have a solid understanding of Azure SQL Database, SQL Server in Azure Virtual Machines, and Azure Cosmos DB.
NEW QUESTION # 96
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 # 97
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: A
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 Optimize Query Performance 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.
Existing Environment
Network Environment
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
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 # 98
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 VM1 cannot connect to any Azure SQL Server other than
SqlSrv1.
- Restrict network connectivity to SqlSrv1.
What should you create on VNet1?
- A. a service endpoint
- B. a VPN gateway
- C. a private link
- D. an ExpressRoute gateway
Answer: C
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 # 99
You have two on-premises servers that run Windows Server 2019 and host a Microsoft SQL Server 2017 Always On availability group named AG1. AG1 contains a single database named DB1.
You have an Azure subscription. The subscription contains a virtual machine named VM1 that runs Linux.
You need to migrate DB1 to a SQL Server 2019 instance on VM1. The solution must minimize the downtime of DB1 during the migration.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
see the answer below in explanation.
Explanation:
Answer as in image below.
NEW QUESTION # 100
You have four Azure subscriptions. Each subscription contains multiple Azure SQL databases.
You need to update the column and index statistics for the databases.
What should you use?
- A. an Azure Automation runbook
- B. automatic tuning in Azure SQL Database
- C. a SQL Agent job
- D. Azure SQL Analytics
Answer: A
Explanation:
You can create a runbook for index maintenance in an Azure SQL database.
Reference:
https://www.sqlshack.com/automate-azure-sql-database-indexes-and-statistics-maintenance/
NEW QUESTION # 101
SIMULATION
You need to configure your user account as the Azure AD admin for the server named sql12345678.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
Answer:
Explanation:
NEW QUESTION # 102
Hotspot Question
You configure a long-term retention policy for an Azure SQL database as shown in the exhibit.
The first weekly backup occurred on January 4, 2020. The dates for the first 10 weekly backups are:
January 4, 2020
January 11, 2020
January 18, 2020
January 25, 2020
February 1, 2020
February 8, 2020
February 15, 2020
February 22, 2020
February 29, 2020
March 7, 2020
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:
Box 1: 12 months
The January 4, 2020 backup is the first backup of the month, and it will keep for 12 months.
Box 2: 10 years.
The January 11, 2020 backup is the Week 2 backup. This backup will be the yearly backup, and it will be retained for 10 years.
Note: Long-term backup retention (LTR) leverages the full database backups that are automatically created to enable point in time restore (PITR). If an LTR policy is configured, these backups are copied to different blobs for long-term storage. The copy is a background job that has no performance impact on the database workload. The LTR policy for each database in SQL Database can also specify how frequently the LTR backups are created.
To enable LTR, you can define a policy using a combination of four parameters: weekly backup retention (W), monthly backup retention (M), yearly backup retention (Y), and week of year (WeekOfYear). If you specify W, one backup every week will be copied to the long-term storage.
If you specify M, the first backup of each month will be copied to the long-term storage. If you specify Y, one backup during the week specified by WeekOfYear will be copied to the long-term storage. If the specified WeekOfYear is in the past when the policy is configured, the first LTR backup will be created in the following year. Each backup will be kept in the long-term storage according to the policy parameters that are configured when the LTR backup is created.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/long-term-retention-overview?view=azuresql
NEW QUESTION # 103
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.
Answer:
Explanation:
Explanation:
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.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database- engine
NEW QUESTION # 104
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
https://docs.microsoft.com/en-us/dotnet/architecture/cloud-native/infrastructure-resiliency-azure
NEW QUESTION # 105
You have an Azure SQL database.
You need to implement a disaster recovery solution that meets the following requirements:
- Minimizes how long it takes to recover the database if a datacenter
fails
- Minimizes administrative effort
What should you include in the solution?
- A. Azure Site Recovery
- B. Azure Backup
- C. active geo-replication
- D. auto-failover groups
Answer: D
NEW QUESTION # 106
You have an Azure subscription.
You need to deploy a logical SQL server by using PowerShell. The solution must ensure that the logical SQL server can create Azure AD users and provide Transparent Data Encryption (TDE} with a customer-managed key.
How should you complete the command? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
"Assigned Identity" and "Key id" https://learn.microsoft.com/en-us/powershell/module/az.sql/new- azsqlserver?view=azps-10.2.0#code-try-3
NEW QUESTION # 107
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:
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 # 108
SIMULATION
You need to generate an email alert for db1 if the average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals. The alert must be sent to [email protected].
You may need to use SQL Server Management Studio and the Azure portal.
Answer:
Explanation:
To create an email alert for high CPU percentage utilization in an Azure database within the Azure portal, you'll first need to navigate to your database resource, then to the "Alerts" section.
From there, you'll create a new alert rule, define the CPU percentage threshold, configure an action group for email notifications, and finally, create the alert rule.
Create alerts for Azure SQL Database and Azure Synapse Analytics using the Azure portal Step 1: In the Azure portal, locate the resource you are interested in monitoring and select it.
[Select db1]
Step 2: In the resource menu under Monitoring, select Alerts. The text and icon might vary slightly for different resources.
Step 3: Select the + Create button, then Alert rule.
Step 4: On the Create an alert rule page, the Scope is automatically configured to the individual resource.
Capture activity peaks or sustained resource stress with alerts on the Metrics signal category.
Step 5: On the Condition tab, select See all signals and Select a signal from the list of Metrics.
Step 6: Select the desired metric, for example CPU percentage. Select Apply. [Select CPU percentage] Step 7: After you select a signal, the Alert logic options appear. A preview of recent activity in this resource for that signal is also displayed.
Step 8: Configure a Threshold type to determine when the alert will take action. Choose Aggregation type, Operator, and Threshold value as desired. A typical threshold is: Static, Maximum, Greater than, 80%.
Threshold: [Select 50%]
Operator: [Select Greater than]
Aggregation granularity: [Select 5 minutes]
Select the interval that's used to group the data points by using the aggregation type function.
Choose an Aggregation granularity (period) that's greater than the Frequency of evaluation to reduce the likelihood of missing the first evaluation period of an added time series.
Frequency of evaluation: [Select one minute]
Select how often the alert rule is to be run. Select a frequency that's smaller than the aggregation granularity to generate a sliding window for the evaluation.
Step 9: Under When to evaluate, determine the desired frequency of evaluation. Use the Check every and Lookback period dropdown lists.
Step 10: Optionally, you can add multiple conditions for this alert, choose the Add condition alert if desired.
Step 11: Select Next: Actions >.
Create the new alert rule
Step 12: Use the Use quick actions feature to create a new action group and provide an email or Azure mobile app notification target.
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/alerts-create?
NEW QUESTION # 109
You have an Azure subscription that contains an Azure SQL database.
The database fails to respond to queries in a timely manner.
You need to identify whether the issue relates to resource_semaphore waits.
How should you complete the Transact-SQL 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/azure/azure-sql/database/monitoring-with-dmvs
NEW QUESTION # 110
You have an Azure SQL database named DB1 that contains two tables named Table1 and Table2. Both tables contain a column named a Column1. Column1 is used for joins by an application named App1.
You need to protect the contents of Column1 at rest, in transit, and in use.
How should you protect the contents of Column1? To answer, select the appropriate options in the answer area.
NOTE:Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Box 1: Column encryption Key
Always Encrypted uses two types of keys: column encryption keys and column master keys. A column encryption key is used to encrypt data in an encrypted column. A column master key is a key-protecting key that encrypts one or more column encryption keys.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/always-encrypted-database-engine
NEW QUESTION # 111
......
The DP-300 certification exam is ideal for IT professionals who are looking to advance their careers in cloud computing and database administration. Administering Relational Databases on Microsoft Azure certification validates your skills and knowledge in administering and maintaining relational databases on Microsoft Azure, which is a highly sought-after skill in the IT industry.
Free DP-300 Exam Dumps to Improve Exam Score: https://dumps4download.actualvce.com/Microsoft/DP-300-valid-vce-dumps.html