Correct Answer: B

Scenario: In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The solution must provide automatic failover.

The auto-failover groups feature allows you to manage the replication and failover of a group of databases on a server or all databases in a managed instance to another region. It is a declarative abstraction on top of the existing active georeplication feature, designed to simplify deployment and management of geo-replicated databases at scale. You can initiate failover manually or you can delegate it to the Azure service based on a user-defined policy.

The latter option allows you to automatically recover multiple related databases in a secondary region after a catastrophic failure or other unplanned event that results in full or partial loss of the SQL Database or SQL Managed Instance

availability in the primary region.

Reference:

https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview

Correct Answer: D

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

Correct Answer: C

The exhibits displays Database [State] properties.

To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-properties-options-page

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.

Low - The thresholds will be loose with more distance from metric series pattern. An alert rule will only trigger on large deviations, resulting in fewer alerts.

Incorrect Answers:

A: High - The thresholds will be tight and close to the metric series pattern. An alert rule will be triggered on the smallest deviation, resulting in more alerts.

Reference:

https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresholds

Correct Answer: C

Azure HDInsight offers pre-made, monitoring dashboards in the form of solutions that can be used to monitor the workloads running on your clusters. There are solutions for Apache Spark, Hadoop, Apache Kafka, live long and process (LLAP), Apache HBase, and Apache Storm available in the Azure Marketplace.

Note: With Azure HDInsight you can set up Azure Monitor alerts that will trigger when the value of a metric or the results of a query meet certain conditions. You can condition on a query returning a record with a value that is greater than or less than a certain threshold, or even on the number of results returned by a query. For example, you could create an alert to send an email if a Spark job fails or if a Kafka disk usage becomes over 90 percent full.

Reference: https://azure.microsoft.com/en-us/blog/monitoring-on-azure-hdinsight-part-4-workload-metrics-and-logs/

Correct Answer: C

Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups?view=sql-server-ver15

Correct Answer: A

Reference: https://www.sqlshack.com/automate-azure-sql-database-indexes-and-statistics-maintenance/

Correct Answer: D

Sys.dm_db_index_useage_stats

Returns counts of different types of index operations and the time each type of operation was last performed.

Every individual seek, scan, lookup, or update on the specified index by one query execution is counted as a use of that index and increments the corresponding counter in this view. Information is reported both for operations caused by user-

submitted queries, and for operations caused by internally generated queries, such as scans for gathering statistics.

Incorrect:

* Sys.dm_db_index_operational_stats

Returns current lower-level I/O, locking, latching, and access method activity for each partition of a table or index in the database.

Reference:

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql

Correct Answer: A

You can choose to register your SQL IaaS extension in three management modes:

*

Full mode installs the SQL IaaS Agent to the VM to deliver full functionality. Use it for managing a SQL Server VM with a single instance. Full mode installs two Windows services that have a minimal impact to memory and CPU - these can be monitored through task manager. There is no cost associated with using the full manageability mode. System administrator permissions are required. As of September 2021, restarting the SQL Server service is no longer necessary when registering your SQL Server VM in full management mode.

Note: The SQL Server IaaS Agent extension allows for integration with the Azure portal, and depending on the management mode, unlocks a number of feature benefits for SQL Server on Azure VMs:

Feature benefits: The extension unlocks a number of automation feature benefits, such as portal management, license flexibility, automated backup, automated patching and more.

Incorrect:

*

NoAgent mode is dedicated to SQL Server 2008 and SQL Server 2008 R2 installed on Windows Server 2008. There is no impact to memory or CPU when using the NoAgent mode. There is no cost associated with using the NoAgent manageability mode, the SQL Server is not restarted, and an agent is not installed to the VM.

*

Lightweight mode copies extension binaries to the VM, but does not install the agent. Lightweight mode only supports changing the license type and edition of SQL Server and provides limited portal management. Use this option for SQL Server VMs with multiple instances, or those participating in a failover cluster instance (FCI). Lightweight mode is the default management mode when using the automatic registration feature, or when a management type is not specified during manual registration. There is no impact to memory or CPU when using the lightweight mode, and there is no associated cost.

Reference: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management

Correct Answer: C

Correct Answer: A

By default, the cluster of nodes for the premium availability model is created in the same datacenter. With the introduction of Azure Availability Zones, SQL Database can place different replicas of the Business Critical database to different

availability zones in the same region. To eliminate a single point of failure, the control ring is also duplicated across multiple zones as three gateway rings (GW). The routing to a specific gateway ring is controlled by Azure Traffic Manager

(ATM). Because the zone redundant configuration in the Premium or Business Critical service tiers does not create additional database redundancy, you can enable it at no extra cost. By selecting a zone redundant configuration, you can

make your Premium or Business Critical databases resilient to a much larger set of failures, including catastrophic datacenter outages, without any changes to the application logic. You can also convert any existing Premium or Business

Critical databases or pools to the zone redundant configuration.

Incorrect Answers:

Note:

There are several versions of this question with different incorrect answer options. Other incorrect answers you may see on the exam include:

1.

Azure SQL Database Hyperscale

2.

Azure SQL Database Basic

3.

Azure SQL Database managed instance General Purpose

Reference: https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla

Correct Answer: ACE

A: Change elastic pool storage size

*

vCore-based purchasing model

Storage can be provisioned up to the max size limit:

For storage in the Standard or General Purpose service tiers, increase or decrease size in 10-GB increments

For storage in the Premium or Business Critical service tiers, increase or decrease size in 250-GB increments

Storage for an elastic pool can be provisioned by increasing or decreasing its max size.

*

DTU-based purchasing model

The eDTU price for an elastic pool includes a certain amount of storage at no additional cost. Extra storage beyond the included amount can be provisioned for an additional cost up to the max size limit in increments of 250 GB up to 1 TB,

and then in increments of 256 GB beyond 1 TB.

C: You can add or remove databases to/from the pool.

E: Under some circumstances, you may need to shrink a database to reclaim unused space.

Reclaim unused allocated space

Shrink commands impact database performance while running, and if possible should be run during periods of low usage.

Reference:

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-scale

https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage

https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-manage

Correct Answer:

Reference: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store

Correct Answer:

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

https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function?view=sql-server-ver15

Correct Answer: