DP 300 Quiz 4 Topic 3

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

Answer explanation

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.

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?

• 2

• 4

• 8

• 64

You have 50 Azure SQL databases.

You need to notify the database owner when the database settings, such as the database size and pricing tier, are modified in Azure.

What should you do?

• A. Create a diagnostic setting for the activity log that has the Security log enabled.

• B. For the database, create a diagnostic setting that has the InstanceAndAppAdvanced metric enabled.

• C. Create an alert rule that uses a Metric signal type.

• D. Create an alert rule that uses an Activity Log signal type.

Answer explanation

Correct Answer: D

Activity log events - An alert can trigger on every event, or, only when a certain number of events occur.

The activity log of a database logs the change for the SKU (Stock-keeping-Unit) change.

Incorrect Answers:

C: Metric values - The alert triggers when the value of a specified metric crosses a threshold you assign in either direction. That is, it triggers

both when the condition is first met and then afterwards when that condition is no longer being met.

You have several Azure SQL databases on the same Azure SQL Database server in a resource group named ResourceGroup1.

You must be alerted when CPU usage exceeds 80 percent for any database. The solution must apply to any additional databases that are created

on the Azure

SQL server.

Which resource type should you use to create the alert?

• A. Resource Groups

• B. SQL Servers

• C. SQL Databases

• D. SQL Virtual Machines

Answer explanation

Correct Answer: C

There are resource types related to application code, compute infrastructure, networking, storage + databases.

You can deploy up to 800 instances of a resource type in each resource group.

Some resources can exist outside of a resource group. These resources are deployed to the subscription, management group, or tenant. Only

specific resource types are supported at these scopes.

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 8 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. 2

• B. 4

• C. 8

• D. 64

Answer explanation

Correct Answer: C

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.

You have SQL Server on an Azure virtual machine that contains a database named DB1. DB1 contains a table named CustomerPII.

You need to record whenever users query the CustomerPII table.

Which two options should you enable? Each correct answer presents part of the solution.

NOTE: Each correct selection is worth one point.

• A. server audit specification

• B. SQL Server audit

• C. database audit specification

• D. a server principal

Answer explanation

Correct Answer: BC

Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. The SQL Server

Audit object collects a single instance of server-level or database-level actions and groups of actions to monitor. The audit is at the SQL Server

instance level. You can have multiple audits per SQL Server instance. The Database-Level Audit Specification object belongs to an audit. You

can create one database audit specification per

SQL Server database per audit.

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 the Azure subscription to the Microsoft.Sql resource provider

• C. Install an Azure virtual machine Desired State Configuration (DSC) extension on VM1

• D. Register the Azure subscription to the Microsoft.SqlVirtualMachine resource provider

Answer explanation

Correct Answer: D

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.

To utilize the SQL IaaS Agent extension, you must first register your subscription with the Microsoft.SqlVirtualMachine provider, which gives the

SQL IaaS extension the ability to create resources within that specific subscription.

A company plans to use Apache Spark analytics to analyze intrusion detection data.

You need to recommend a solution to analyze network and system activity data for malicious activities and policy violations. The solution must

minimize administrative efforts.

What should you recommend?

• A. Azure Data Lake Storage

• B. Azure Databricks

• C. Azure HDInsight

• D. Azure Data Factory

Answer explanation

Correct Answer C

You have an Azure data solution that contains an enterprise data warehouse in Azure Synapse Analytics named DW1.

Several users execute adhoc queries to DW1 concurrently.

You regularly perform automated data loads to DW1.

You need to ensure that the automated data loads have enough memory available to complete quickly and successfully when the adhoc queries

run.

What should you do?

• A. Assign a smaller resource class to the automated data load queries.

• B. Create sampled statistics to every column in each table of DW1.

• C. Assign a larger resource class to the automated data load queries.

• D. Hash distribute the large fact tables in DW1 before performing the automated data loads.

Answer explanation

Correct Answer: C

The performance capacity of a query is determined by the user's resource class.

Smaller resource classes reduce the maximum memory per query, but increase concurrency.

You are monitoring an Azure Stream Analytics job.

You discover that the Backlogged input Events metric is increasing slowly and is consistently non-zero.

You need to ensure that the job can handle all the events.

What should you do?

• A. Remove any named consumer groups from the connection and use $default.

• B. Change the compatibility level of the Stream Analytics job.

• C. Create an additional output stream for the existing input stream.

• D. Increase the number of streaming units (SUs).

Answer explanation

Correct Answer: D

Backlogged Input Events: Number of input events that are backlogged. A non-zero value for this metric implies that your job isn't able to keep up

with the number of incoming events. If this value is slowly increasing or consistently non-zero, you should scale out your job, by increasing the

SUs.