Valid Microsoft DP-300 dumps questions shared by Pass4itsure for helping to pass the Microsoft DP-300 exam! Get the newest Pass4itsure Microsoft DP-300 exam dumps with VCE and PDF here: https://www.pass4itsure.com/dp-300.html (56 Q&As Dumps).
Suitable for DP-300 complete Microsoft learning pathway
The content is rich and diverse, and learning will not become boring. You can learn in multiple ways through the Microsoft DP-300 exam.
- Download
- Watch the video
- Answer practice questions, the actual test
DP-300 Administering Relational Databases on Microsoft Azure
Free Microsoft DP-300 dumps download
[PDF] Free Microsoft DP-300 dumps pdf download https://drive.google.com/file/d/1owLr8kD4zKssmYFequENAGSDXhFBie-I/view?usp=sharing
Pass4itsure offers the latest Microsoft DP-300 practice test free of charge 1-13
QUESTION 1
DRAG DROP
You have SQL Server on an Azure virtual machine named SQL1.
SQL1 has an agent job to back up all databases.
You add a user named dbadmin1 as a SQL Server Agent operator.
You need to ensure that dbadmin1 receives an email alert if a job fails.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions
to the answer area and arrange them in the correct order.
Select and Place:
Correct Answer:
Step 1: Enable the email settings for the SQL Server Agent.
To send a notification in response to an alert, you must first configure SQL Server Agent to send mail.
Step 2: Create a job alert
Step 3: Create a job notification
Example:
— adds an e-mail notification for the specified alert (Test Alert)
— This example assumes that Test Alert already exists
— and that François Ajenstat is a valid operator name.
USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N\\’Test Alert\\’,
@operator_name = N\\’François Ajenstat\\’,
@notification_method = 1 ;
GO
Reference:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status
https://docs.microsoft.com/en-us/sql/ssms/agent/assign-alerts-to-an-operator
QUESTION 2
You have an Azure SQL database named sqldb1.
You need to minimize the amount of space by the data and log files of sqldb1.
What should you run?
A. DBCC SHRINKDATABASE
B. sp_clean_db_free_space
C. sp_clean_db_file_free_space
D. DBCC SHRINKFILE
Correct Answer: A
DBCC SHRINKDATABASE shrinks the size of the data and log files in the specified database. Incorrect Answers:
D: To shrink one data or log file at a time for a specific database, execute the DBCC SHRINKFILE command.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql
QUESTION 3
HOTSPOT
You have SQL Server on an Azure virtual machine that contains a database named Db1.
You need to enable automatic tuning for Db1.
How should you complete the statements? To answer, select the appropriate answer in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
Box 1: SET AUTOMATIC_TUNING = AUTO
To enable automatic tuning on a single database via T-SQL, connect to the database and execute the following query:
ALTER DATABASE current SET AUTOMATIC_TUNING = AUTO
Setting automatic tuning to AUTO will apply Azure Defaults.
Box 2: SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
To configure individual automatic tuning options via T-SQL, connect to the database and execute the query such as this
one:
ALTER DATABASE current SET AUTOMATIC_TUNING (FORCE_LAST_GOOD_PLAN = ON)
Setting the individual tuning option to ON will override any setting that database inherited and enable the tuning option.
Setting it to OFF will also override any setting that database inherited and disable the tuning option.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/automatic-tuning-enable
QUESTION 4
You have two Azure SQL Database servers named Server1 and Server2. Each server contains an Azure SQL database
named Database1.
You need to restore Database1 from Server1 to Server2. The solution must replace the existing Database1 on Server2.
Solution: From the Azure portal, you delete Database1 from Server2, and then you create a new database on Server2
by using the backup of Database1 from Server1.
Does this meet the goal?
A. Yes
B. No
Correct Answer: B
Instead restore Database1 from Server1 to the Server2 by using the RESTORE Transact-SQL command and the
REPLACE option.
Note: REPLACE should be used rarely and only after careful consideration. Restore normally prevents accidentally
overwriting a database with a different database. If the database specified in a RESTORE statement already exists on
the current server and the specified database family GUID differs from the database family GUID recorded in the
backup set, the database is not restored. This is an important safeguard.
Reference: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
QUESTION 5
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 Last_Query_Plan_Stats in the master database
B. Enable Lightweight_Query_Profiling in DB1
C. Enable Last_Query_Plan_Stats in DB1
D. Enable Lightweight_Query_Profiling in the master database
E. Enable PARAMETER_SNIFFING in DB1
Correct Answer: AC
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
QUESTION 6
DRAG DROP
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.
Select and Place:
Correct Answer:
QUESTION 7
HOTSPOT
You have an Azure SQL Database managed instance named sqldbmi1 that contains a database name Sales.
You need to initiate a backup of Sales.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Box 1: TO URL = \\’https://storage1.blob.core.windows.net/blob1/Sales.bak\\’
Native database backup in Azure SQL Managed Instance.
You can backup any database using standard BACKUP T-SQL command:
BACKUP DATABASE tpcc2501
TO URL = \\’https://myacc.blob.core.windows.net/testcontainer/tpcc2501.bak\\’
WITH COPY_ONLY
Box 2: WITH COPY_ONLY
Reference:
https://techcommunity.microsoft.com/t5/azure-sql-database/native-database-backup-in-azure-sql-managed-instance/bap/386154
QUESTION 8
DRAG DROP
You need to configure user authentication for the SERVER1 databases. The solution must meet the security and
compliance requirements.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions
to the answer area and arrange them in the correct order.
Select and Place:
Scenario: Authenticate database users by using Active Directory credentials.
The configuration steps include the following procedures to configure and use Azure Active Directory authentication.
Create and populate Azure AD.
Optional: Associate or change the active directory that is currently associated with your Azure Subscription.
Create an Azure Active Directory administrator. (Step 1)
Configure your client computers.
Create contained database users in your database mapped to Azure AD identities. (Step 2) Connect to your database
by using Azure AD identities. (Step 3)
Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-overview
QUESTION 9
You receive numerous alerts from Azure Monitor for an Azure SQL database.
You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns
for an extended period.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Set Threshold Sensitivity to High
B. Set the Alert logic threshold to Dynamic
C. Set the Alert logic threshold to Static
D. Set Threshold Sensitivity to Low
E. Set Force Plan to On
Correct Answer: BD
B: Dynamic Thresholds continuously learns the data of the metric series and tries to model it using a set of algorithms
and methods. It detects patterns in the data such as seasonality (Hourly / Daily / Weekly), and is able to handle noisy
metrics (such as machine CPU or memory) as well as metrics with low dispersion (such as availability and error rate).
D: Alert threshold sensitivity is a high-level concept that controls the amount of deviation from metric behavior required
to trigger an alert.
QUESTION 10
You are building a database backup solution for a SQL Server database hosted on an Azure virtual machine.
In the event of an Azure regional outage, you need to be able to restore the database backups. The solution must
minimize costs.
Which type of storage accounts should you use for the backups?
A. locally-redundant storage (LRS)
B. read-access geo-redundant storage (RA-GRS)
C. zone-redundant storage (ZRS)
D. geo-redundant storage
Correct Answer: B
Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region to
protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates a
failover from the primary to secondary region. When you enable read access to the secondary region, your data is
available to be read if the primary region becomes unavailable. For read access to the secondary region, enable readaccess geo-redundant storage (RA-GRS) or read-access geo-zone-redundant storage (RA-GZRS).
Incorrect Answers:
A: Locally redundant storage (LRS) copies your data synchronously three times within a single physical location in the
primary region. LRS is the least expensive replication option, but is not recommended for applications requiring high
availability.
C: Zone-redundant storage (ZRS) copies your data synchronously across three Azure availability zones in the primary
region.
D: Geo-redundant storage (with GRS or GZRS) replicates your data to another physical location in the secondary region
to protect against regional outages. However, that data is available to be read only if the customer or Microsoft initiates
a failover from the primary to secondary region.
Reference: https://docs.microsoft.com/en-us/azure/storage/common/storage-redundancy
QUESTION 11
HOTSPOT
You have an Azure SQL database named db1.
You need to retrieve the resource usage of db1 from the last week.
How should you complete the statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:
Correct Answer:
Box 1: sys.resource_stats
sys.resource_stats returns CPU usage and storage data for an Azure SQL Database. It has database_name and
start_time columns.
Box 2: DateAdd
The following example returns all databases that are averaging at least 80% of compute utilization over the last one
week.
DECLARE @s datetime;
DECLARE @e datetime;
SET @s= DateAdd(d,-7,GetUTCDate());
SET @e= GETUTCDATE();
SELECT database_name, AVG(avg_cpu_percent) AS Average_Compute_Utilization FROM sys.resource_stats
WHERE start_time BETWEEN @s AND @e GROUP BY database_name HAVING AVG(avg_cpu_percent) >= 80
Incorrect Answers: sys.dm_exec_requests: sys.dm_exec_requests returns information about each request that is
executing in SQL Server. It does not have a column named database_name.
sys.dm_db_resource_stats:
sys.dm_db_resource_stats does not have any start_time column.
Note: sys.dm_db_resource_stats returns CPU, I/O, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. Historical data is maintained for
approximately one hour.
Sys.dm_user_db_resource_governance returns actual configuration and capacity settings used by resource governance
mechanisms in the current database or elastic pool. It does not have any start_time column.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-database
QUESTION 12
You have an Azure virtual machine based on a custom image named VM1.
VM1 hosts an instance of Microsoft SQL Server 2019 Standard.
You need to automate the maintenance of VM1 to meet the following requirements:
Automate the patching of SQL Server and Windows Server.
Automate full database backups and transaction log backups of the databases on VM1.
Minimize administrative effort.
What should you do first?
A. Enable a system-assigned managed identity for VM1
B. Register VM1 to the Microsoft.Sql resource provider
C. Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1
D. Register VM1 to the Microsoft.SqlVirtualMachine resource provider
Correct Answer: B
Automated Patching depends on the SQL Server infrastructure as a service (IaaS) Agent Extension. The SQL Server
IaaS Agent Extension (SqlIaasExtension) runs on Azure virtual machines to automate administration tasks. The SQL
Server IaaS extension is installed when you register your SQL Server VM with the SQL Server VM resource provider.
Reference: https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extensionautomate-management
QUESTION 13
You have an Azure SQL database named sqldb1.
You need to minimize the possibility of Query Store transitioning to a read-only state.
What should you do?
A. Double the value of Data Flush interval
B. Decrease by half the value of Data Flush Interval
C. Double the value of Statistics Collection Interval
D. Decrease by half the value of Statistics Collection interval
Correct Answer: B
The Max Size (MB) limit isn\\’t strictly enforced. Storage size is checked only when Query Store writes data to disk. This
interval is set by the Data Flush Interval (Minutes) option. If Query Store has breached the maximum size limit between
storage size checks, it transitions to read-only mode.
Incorrect Answers: C: Statistics Collection Interval: Defines the level of granularity for the collected runtime statistic,
expressed in minutes. The default is 60 minutes. Consider using a lower value if you require finer granularity or less
time to detect and mitigate issues. Keep in mind that the value directly affects the size of Query Store data.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
What customers say about Pass4itsure
Latest Microsoft dumps discount code “Microsoft” – Pass4itsure
Summarize:
[Q1-Q13] Free Microsoft DP-300 dumps pdf download https://drive.google.com/file/d/1owLr8kD4zKssmYFequENAGSDXhFBie-I/view?usp=sharing
Share all the resources: Latest Microsoft DP-300 practice questions, latest Microsoft DP-300 pdf dumps, Microsoft DP-300 exam video learning. Latest updated Microsoft DP-300 dumps https://www.pass4itsure.com/dp-300.html Study hard and practices a lot. This will help you prepare for the DP-300 exam. Good luck!
