Real Microsoft DP-300 Exam Questions [Updated 2024]
DP-300 Exam Dumps Pass with Updated 2024 Administering Relational Databases on Microsoft Azure
Microsoft DP-300 certification exam is suitable for database administrators, developers, and IT professionals who want to enhance their skills in managing and deploying databases on the Azure platform. It is also a valuable credential for those who want to advance their careers in the field of cloud computing and database administration. By passing DP-300 exam, candidates can demonstrate their expertise in administering relational databases on Microsoft Azure and distinguish themselves from their peers in the industry.
NEW QUESTION # 110
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 # 111
You have a database on a SQL Server on Azure Virtual Machines instance.
The current state of Query Store for the database is shown in the following exhibit.
Answer:
Explanation:
Explanation
Graphical user interface, text Description automatically generated
NEW QUESTION # 112
You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 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
Table Description automatically generated
Reference:
https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/
NEW QUESTION # 113
You have an Azure SQL database named DB1.
You need to encrypt DB1. The solution must meet the following requirements;
* Encrypt data in motion.
* Support comparison operators.
* Provide randomized encryption.
What should you include in the solution?
- A. Always Encrypted with secure enclaves
- B. Transparent Data Encryption (TDE)
- C. column-level encryption
- D. Always Encrypted
Answer: D
NEW QUESTION # 114
You have an Azure subscription that is linked to a hybrid Azure Active Directory (Azure AD) tenant. The subscription contains an Azure Synapse Analytics SQL pool named Pool1.
You need to recommend an authentication solution for Pool1. The solution must support multi-factor authentication (MFA) and database-level authentication.
Which authentication solution or solutions 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:
Explanation
Graphical user interface, text, application, chat or text message Description automatically generated
Box 1: Azure AD authentication
Azure Active Directory authentication supports Multi-Factor authentication through Active Directory Universal Authentication.
Box 2: Contained database users
Azure Active Directory Uses contained database users to authenticate identities at the database level.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-authentication
NEW QUESTION # 115
Hotspot Question
You are designing an enterprise data warehouse in Azure Synapse Analytics that will store website traffic analytics in a star schema.
You plan to have a fact table for website visits. The table will be approximately 5 GB.
You need to recommend which distribution type and index type to use for the table. The solution must provide the fastest query performance.
What should you recommend? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation:
Box 1: Hash
Consider using a hash-distributed table when:
The table size on disk is more than 2 GB.
The table has frequent insert, update, and delete operations.
Box 2: Clustered columnstore
Clustered columnstore tables offer both the highest level of data compression and the best overall query performance.
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-distribute
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-index
NEW QUESTION # 116
You have an Azure SQL managed instance named SQLMI1 that has Resource Governor enabled and is used by two apps named App1 and App2.
You need to configure SQLMI1 to limit the CPU and memory resources that can be allocated to App1.
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:
Explanation
Text, table Description automatically generated
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server-
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defi
NEW QUESTION # 117
You have two Azure virtual machines named VM1 and VM2 that run Windows Server 2019. VM1 and VM2 each host a default Microsoft SQL Server 2019 instance. VM1 contains a database named DB1 that is backed up to a file named D:\DB1.bak.
You plan to deploy an Always On availability group that will have the following configurations:
VM1 will host the primary replica of DB1.
VM2 will host a secondary replica of DB1.
You need to prepare the secondary database on VM2 for the availability group.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
Answer:
Explanation:
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15
NEW QUESTION # 118
Case Study 4 - A.Datum
Overview
ADatum Corporation is a retailer that sells products through two sales channels: retail stores and a website.
Existing Environment
ADatum has one database server that has Microsoft SQL Server 2016 installed. The server hosts three mission-critical databases named SALESDB, DOCDB, and REPORTINGDB.
SALESDB collects data from the stores and the website.
DOCDB stores documents that connect to the sales data in SALESDB. The documents are stored in two different JSON formats based on the sales channel.
REPORTINGDB stores reporting data and contains several columnstore indexes. A daily process creates reporting data in REPORTINGDB from the data in SALESDB. The process is implemented as a SQL Server Integration Services (SSIS) package that runs a stored procedure from SALESDB.
Requirements
Planned Changes
ADatum plans to move the current data infrastructure to Azure. The new infrastructure has the following requirements:
Migrate SALESDB and REPORTINGDB to an Azure SQL database.
Migrate DOCDB to Azure Cosmos DB.
The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.
As they arrive, all the sales documents in JSON format must be transformed into one consistent format.
Azure Data Factory will replace the SSIS process of copying the data from SALESDB to REPORTINGDB.
Technical Requirements
The new Azure data infrastructure must meet the following technical requirements:
Data in SALESDB must encrypted by using Transparent Data Encryption (TDE). The encryption must use your own key.
SALESDB must be restorable to any given minute within the past three weeks.
Real-time processing must be monitored to ensure that workloads are sized properly based on actual usage patterns.
Missing indexes must be created automatically for REPORTINGDB.
Disk IO, CPU, and memory usage must be monitored for SALESDB.
Which windowing function should you use to perform the streaming aggregation of the sales data?
- A. Sliding
- B. Tumbling
- C. Session
- D. Hopping
Answer: B
Explanation:
Scenario: The sales data, including the documents in JSON format, must be gathered as it arrives and analyzed online by using Azure Stream Analytics. The analytics process will perform aggregations that must be done continuously, without gaps, and without overlapping.
Tumbling window functions are used to segment a data stream into distinct time segments and perform a function against them, such as the example below. The key differentiators of a Tumbling window are that they repeat, do not overlap, and an event cannot belong to more than one tumbling window.
Reference:
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/stream-analytics/stream-analytics-window-functions.md
NEW QUESTION # 119
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?
- A. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (File_Snapshot, STATS=5, COMPRESSION);
- B. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (NoInit, STATS=5, COMPRESSION);
- C. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (COPY_ONLY, STATS=5, COMPRESSION);
- D. BACKUP DATABASE DB1 TO URL='https://mystorageaccount.blob.core.windows.net/ mycontainer/DB1.bak' with (Differential, STATS=5, COMPRESSION);
Answer: C
Explanation:
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.
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-backup-on
NEW QUESTION # 120
You have an Azure subscription that contains the resources shown in the following table.
App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.
You need to modify db1 to resolve the issues experienced by Appl as soon as possible, without considering immediate costs What you do?
- A. Disable auto-pause delay for db1.
- B. Increase the number of vCores allocated to db1.
- C. Decrease the auto-pause delay for db1.
- D. Enable automatic tuning for db1.
Answer: D
NEW QUESTION # 121
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 # 122
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 # 123
Hotspot Question
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
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 # 124
You have SQL Server on an Azure virtual machine.
You need to use Policy-Based Management in Microsoft SQL Server to identify stored procedures that do not comply with your naming conventions.
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 customer condition based on a built-in facet.
2 - Create a custom policy based on a condition.
3 - Run a policy evaluation.
Reference:
https://www.mssqltips.com/sqlservertip/2298/enforce-sql-server-database-naming-conventions-using-policy-based-management/
NEW QUESTION # 125
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 # 126
You have an on-premises Microsoft SQL Server 2019 instance named SQL1 that hosts a database named db1. You have an Azure subscription that contains an Azure SQL managed instance named MI1 and an Azure Storage account named storage1.
You need to ensure that you can back up db1 to storage1. The solution must meet the following requirements:
* Use block blob storage.
* Maximize security.
- A. Create an access policy.
- B. Rotate the storage keys
- C. Enable infrastructure encryption.
- D. Generate a shared access signature (SAS)
Answer: C
NEW QUESTION # 127
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:
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store
NEW QUESTION # 128
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails.
The solution must minimize costs.
Which deployment option and pricing tier should you configure?
- A. Azure SQL Database Premium
- B. Azure SQL Database Basic
- C. Azure SQL Database serverless
- D. Azure SQL Database Hyperscale
Answer: A
Explanation:
To ensure automatic failover without data loss if a datacenter fails, you should choose the Business Critical deployment option and the Premium pricing tier for the Azure SQL database named DB1. The Business Critical deployment option provides a 99.995% availability SLA, and the Premium pricing tier includes features such as advanced security, high availability, and business continuity. This option also offers the ability to use a failover group with multiple readable secondary replicas.
NEW QUESTION # 129
You have an Azure Synapse Analytics Apache Spark pool named Pool1.
You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file.
You need to load the files into the tables. The solution must maintain the source data types.
What should you do?
- A. Load the data by using PySpark.
- B. Use a Conditional Split transformation in an Azure Synapse data flow.
- C. Use a Get Metadata activity in Azure Data Factory.
- D. Load the data by using the OPENROWSET Transact-SQL command in an Azure Synapse Analytics serverless SQL pool.
Answer: A
Explanation:
Synapse notebooks support four Apache Spark languages:
PySpark (Python)
Spark (Scala)
Spark SQL
.NET Spark (C#)
Note: Bring data to a notebook.
You can load data from Azure Blob Storage, Azure Data Lake Store Gen 2, and SQL pool as shown in the code samples below.
Read a CSV from Azure Data Lake Store Gen2 as a Spark DataFrame. from pyspark.sql import SparkSession from pyspark.sql.types import * account_name = "Your account name" container_name = "Your container name" relative_path = "Your path" adls_path =
'abfss://%s@%s.dfs.core.windows.net/%s' % (container_name, account_name, relative_path) df1
= spark.read.option('header', 'true') \
.option('delimiter', ',') \
.csv(adls_path + '/Testfile.csv')
Reference:
https://docs.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-development-using-notebooks
NEW QUESTION # 130
Task 5
You need to configure a disaster recovery solution for db1. When a failover occurs, the connection strings to the database must remain the same. The secondary server must be in the West US 3 Azure region.
Answer:
Explanation:
See the explanation part for the complete Solution.
Explanation:
To configure a disaster recovery solution for db1, you can use the failover groups feature of Azure SQL Database. Failover groups allow you to manage the replication and failover of a group of databases across different regions with the same connection strings1. You can also use active geo-replication as an alternative, but you will need to update the connection strings manually after a failover2.
Here are the steps to create a failover group for db1 with the secondary server in the West US 3 region:
* Using the Azure portal:
* Go to the Azure portal and select your Azure SQL Database server that hosts db1.
* Select Failover groups in the left menu and click on Add group.
* Enter a name for the failover group and select West US 3 as the secondary region.
* Click on Create a new server and enter the details for the secondary server, such as server name, admin login, password, and subscription.
* Click on Select existing database(s) and choose db1 from the list of databases on the primary server.
* Click on Configure failover policy and select the failover mode, grace period, and read-write failover endpoint mode according to your preferences.
* Click on Create to create the failover group and start the replication of db1 to the secondary server.
* Using PowerShell commands:
* Install the Azure PowerShell module and log in with your Azure account.
* Run the following command to create a new server in the West US 3 region: New-AzSqlServer - ResourceGroupName <your-resource-group-name> -ServerName <your-secondary-server-name>
-Location "West US 3" -SqlAdministratorCredentials $(New-Object -TypeName System.
Management.Automation.PSCredential -ArgumentList "<your-admin-login>", $(ConvertTo- SecureString -String "<your-password>" -AsPlainText -Force))
* Run the following command to create a new failover group with db1: New- AzSqlDatabaseFailoverGroup -ResourceGroupName <your-resource-group-name> -ServerName
<your-primary-server-name> -PartnerResourceGroupName <your-resource-group-name> - PartnerServerName <your-secondary-server-name> -FailoverGroupName <your-failover-group- name> -Database db1 -FailoverPolicy Manual -GracePeriodWithDataLossHours 1 - ReadWriteFailoverEndpoint "Enabled"
* You can modify the parameters of the command according to your preferences, such as the failover policy, grace period, and read-write failover endpoint mode.
These are the steps to create a failover group for db1 with the secondary server in the West US 3 region.
NEW QUESTION # 131
You have an Azure Data Lake Storage Gen2 container.
Data is ingested into the container, and then transformed by a data integration application. The data is NOT modified after that. Users can read files in the container but cannot modify the files.
You need to design a data archiving solution that meets the following requirements:
* New data is accessed frequently and must be available as quickly as possible.
* Data that is older than five years is accessed infrequently but must be available within one second when requested.
* Data that us older than seven years is NOT accessed. After seven years, the data must be persisted at the lowest cost possible.
* Costs must be minimized while maintaining the required availability.
How should you manage the data? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Text, table Description automatically generated
Box 1: Move to cool storage
The cool access tier has lower storage costs and higher access costs compared to hot storage. This tier is intended for data that will remain in the cool tier for at least 30 days. Example usage scenarios for the cool access tier include:
Short-term backup and disaster recovery
Older data not used frequently but expected to be available immediately when accessed Large data sets that need to be stored cost effectively, while more data is being gathered for future processing Note: Hot - Optimized for storing data that is accessed frequently.
Cool - Optimized for storing data that is infrequently accessed and stored for at least 30 days.
Archive - Optimized for storing data that is rarely accessed and stored for at least 180 days with flexible latency requirements, on the order of hours.
Box 2: Move to archive storage
Example usage scenarios for the archive access tier include:
Long-term backup, secondary backup, and archival datasets
Original (raw) data that must be preserved, even after it has been processed into final usable form Compliance and archival data that needs to be stored for a long time and is hardly ever accessed Reference:
https://docs.microsoft.com/en-us/azure/storage/blobs/storage-blob-storage-tiers
NEW QUESTION # 132
You have an Azure subscription that contains the resources shown in the following table.
You need to create a read-only replica of DB1 and configure the App1 instances to use the replica.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Answer:
Explanation:
Explanation
Text Description automatically generated
Reference:
https://sqlserverguides.com/read-only-replica-azure-sql/
NEW QUESTION # 133
......
Microsoft DP-300 evaluates one’s capacity to perform the technical tasks identified in the exam objectives. It is important to review these topics before sitting for the test and develop competence in each domain. The details of the subject area that you need to study for this certification exam are enumerated below:
Plan and Apply Data Platform Resources: 15-20%
- Assess the Strategy for Transferring to Azure: This topic covers competence in assessing online/offline migration strategies, prerequisites for migrations, online/offline upgrade strategies, and prerequisites for the upgrade.
- Implement the Upgrade/Migration Strategy for Transferring to Azure: The applicants should develop competence in implementing the online/offline migration and online/offline upgrade strategies.
- Use Manual Techniques to Deploy Resources: The candidates must demonstrate competence in deploying different database offerings on specific platforms, configuring tailored deployment models, and using updates and patches for IaaS and hybrid deployment.
- Configure Identified Resources for Performance & Scale: This domain will measure the individuals’ skills in the configuration of Azure SQL database, managed instances, and Azure VMs for performance and scale. It also evaluates their capacity to measure the resource prerequisites, set-up SQL Data Sync, and assess database partitioning methods.
- Suggest a Suitable Database Offering according to Specified Prerequisites: This section requires that the examinees have the skills in measuring the prerequisites for deploying as well as measuring the functional impacts/benefits of likely database offerings. They should also have the expertise in evaluating various security areas of probable database offerings as well as HA/DR of likely database offerings.
DP-300 Exam Dumps, DP-300 Practice Test Questions: https://www.dumpstillvalid.com/DP-300-prep4sure-review.html
Free DP-300 Exam Dumps to Pass Exam Easily: https://drive.google.com/open?id=1W2p-g0gPqQ7CUQ5lrswewsO7zFK9v5H0
