DP-300 Free Practice Test

- (Exam Topic 5)
You have 20 Azure SQL databases provisioned by using the vCore purchasing model. You plan to create an Azure SQL Database elastic pool and add the 20 databases.
Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

1. A. total size of all the databases
2. B. geo-replication support
3. C. number of concurrently peaking databases * peak CPU utilization per database
4. D. maximum number of concurrent sessions for all the databases
5. E. total number of databases * average CPU utilization per database

Correct Answer: ACE
CE: Estimate the vCores needed for the pool as follows:
For vCore-based purchasing model: MAX(,
A: Estimate the storage space needed for the pool by adding the number of bytes needed for all the databases in the pool.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview

- (Exam Topic 5)
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.

Solution:


Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
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.

Solution:
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
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-resource-stats-azure-sql-data

Does this meet the goal?

1. A. Yes
2. B. No

Correct Answer: A

- (Exam Topic 5)
You have an Azure Data Factory pipeline that is triggered hourly. The pipeline has had 100% success for the past seven days.
The pipeline execution fails, and two retries that occur 15 minutes apart also fail. The third failure returns the following error.

What is a possible cause of the error?

1. A. From 06:00 to 07:00 on January 10, 2021, there was no data in wwi/BIKES/CARBON.
2. B. The parameter used to generate year=2021/month=01/day=10/hour=06 was incorrect.
3. C. From 06:00 to 07:00 on January 10, 2021, the file format of data in wwi/BIKES/CARBON was incorrect.
4. D. The pipeline was triggered too early.

Correct Answer: B

- (Exam Topic 5)
You have an Azure SQL managed instance named SQL1 and two Azure web apps named App1 and App2. You need to limit the number of IOPs that App2 queries generate on SQL1.
Which two actions should you perform on SQL1? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.

1. A. Enable query optimizer fixes.
2. B. Enable Resource Governor.
3. C. Enable parameter sniffing.
4. D. Create a workload group.
5. E. Configure In-memory OLTP.
6. F. Run the Database Engine Tuning Advisor.
7. G. Reduce the Max Degree of Parallelism value.

Correct Answer: BC
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor?view=sql-server