Q1.A company’s Azure subscription contains two Azure virtual machines named VM1 and VM2, each running an instance of SQL Server.
Both instances run Microsoft SQL Server 2019 CU8.
You need to deploy a failover cluster instance (FCI) across these two machines.
The solution must make both of the following unnecessary:
◆A distributed network name (DNN)
◆A load balancer
Which approach should you use?
Show answer
When you configure an FCI for SQL Server on Azure VMs, a single subnet additionally requires a DNN or an Azure Load Balancer to maintain the connection target.
By contrast, placing the nodes in multiple subnets within the same virtual network lets you configure connections to the FCI in a manner close to on-premises, making these additional components unnecessary.
Therefore, C, which connects the nodes to different subnets, is correct.
Proximity placement groups are useful for optimizing latency, but they are not the solution to this requirement.
Prepare virtual machines for an FCI – SQL Server on Azure VMs
Q2.You have an on-premises app named App1 that stores its data in an on-premises Microsoft SQL Server 2016 database named DB1.
You plan to deploy additional instances of App1 and isolate them by Azure region.
Each region will host a separate instance of App1 and DB1, and each instance of DB1 will be synchronized by using Azure SQL Data Sync.
You need to recommend a database service for this deployment, and the solution must minimize administrative effort.
What should you include in the recommendation?
Show answer
In this scenario, you need to synchronize data across multiple regions by using Azure SQL Data Sync.
This feature is supported by Azure SQL Database (single database), and because it is a PaaS service, operational management tasks such as patching and backups are automated, minimizing the administrative burden.
Azure SQL Managed Instance, on the other hand, does not support Data Sync, and SQL Server on a virtual machine is IaaS, which increases operational overhead.
Therefore, B, which satisfies both reduced administration and Data Sync support, is correct.
Sync data across multiple databases with SQL Data Sync
Q3.You have an on-premises Microsoft SQL Server instance (SQL1) that hosts multiple databases.
Your Azure subscription contains an Azure SQL Managed Instance (SQLMI1).
You need to use Azure Data Studio to migrate the databases from SQL1 to SQLMI1 by using an offline migration.
What should you install on SQL1?
Show answer
An offline migration that uses Azure Data Studio works together with Azure Database Migration Service and requires a self-hosted integration runtime to communicate with the on-premises environment.
This is the component that securely relays data movement between on-premises and Azure.
For a migration to Azure SQL Managed Instance, it is especially important for establishing connectivity from a network-isolated environment.
DMA, by contrast, is used for assessment and schema conversion and is not used to run the actual migration process.
Therefore, D, which is essential for running the migration pipeline, is correct.
Migration overview: SQL Server to Azure SQL Managed Instance
Q4.You manage 25 Azure SQL databases.
You need to implement a centralized database management solution that uses Transact-SQL.
What should you include in the solution?
Show answer
To run Transact-SQL centrally against multiple Azure SQL databases, you need an automation platform that can run scripts on a schedule.
An Azure Automation runbook can schedule the execution of PowerShell or T-SQL and centrally manage administrative tasks across multiple databases.
Elastic jobs are also a candidate, but from an exam perspective Automation is preferred as the more general-purpose means of centralized management.
Therefore, an Azure Automation runbook is best for bulk management of multiple databases.
Overview of Azure Automation
Q5.You have an Azure subscription that contains an Azure SQL database, and the database contains a table named table1.
You run the following Transact-SQL statements.
CREATE CLUSTERED INDEX PK_index1 ON [dbo].table1
CREATE NONCLUSTERED INDEX [NCI_index2] ON [dbo].table1 ASC
You need to reduce the time it takes to run analytical queries on this database.
Which configuration should you enable?
Show answer
The goal is to speed up analytical queries, and the target is an ordinary rowstore table.
In this case, the effective option is BATCH_MODE_ON_ROWSTORE.
It allows batch-mode execution even without a columnstore index and improves the CPU efficiency of analytical queries that include aggregations and scans.
Because it directly reduces the response time of analytical workloads, it is optimal.
Memory grant feedback and adaptive joins are useful for improving specific execution plans, but the option that most directly satisfies the central requirement of speeding up analytical queries overall is D.
Intelligent query processing details – SQL Server
Q6.Your company uses Azure Stream Analytics to monitor devices.
You plan to double the current number of monitored devices.
You need to monitor the Stream Analytics job to ensure that there are enough processing resources to handle the additional load.
Which metric should you monitor?
Show answer
To evaluate the processing capacity of Stream Analytics, it is important to understand the delay in event processing.
Watermark delay indicates the difference between the current time on the processing node and the progress of processed events, allowing you to directly grasp bottlenecks when the load increases.
An increase in this value suggests insufficient processing resources or throughput.
Input errors and early event counts are not performance indicators, and late input events are of limited value.
Therefore, monitoring watermark delay, which is the most suitable metric for assessing scalability, is correct.
Time handling concepts in Azure Stream Analytics
Q7.You have an Azure subscription that contains an Azure SQL database.
The database is experiencing situations where it cannot respond to queries in a timely manner.
You need to determine whether the problem is caused by resource_semaphore waits.
How should you complete the following Transact-SQL query?
Show answer
resource_semaphore is a wait that indicates a query is waiting for the memory grant it needs to execute.
To check for this, you need to select wait_type as the aggregation target and review the total wait time by wait type.
In addition, to obtain wait information for currently executing sessions, you use sys.dm_exec_requests.
By joining this with sys.dm_exec_sessions and aggregating for user processes only, you can determine whether resource_semaphore waits are occurring.
Monitor performance by using DMVs – Azure SQL Database & SQL database in Microsoft Fabric | Microsoft Learn
Q8.You plan to build a structured streaming solution in Azure Databricks.
The solution counts new events every five minutes and reports only the events that arrived during that interval.
The output is sent to a Delta Lake table.
Which output mode should you use?
Show answer
In this scenario, you need to output the full window aggregation result each time, every five minutes.
In this case, you use Complete mode.
Because Complete mode re-outputs the entire result table on each trigger, it always keeps the window aggregation result in a complete state.
Append mode outputs only finalized new rows, and Update mode outputs only changed rows, so neither meets the requirement.
Therefore, the Complete output mode, which is suited to processing that fully refreshes the aggregation result each time, is correct.
Choose an output mode for structured streaming
Q9.SQL Server that hosts a database named Db1 is running on an Azure virtual machine.
You need to enable automatic tuning for Db1.
How should you complete the following statements?
Show answer
To enable automatic tuning, you first set AUTOMATIC_TUNING=AUTO for the entire database to enable Azure’s recommended automatic optimization behavior.
Then, as an individual feature, turning FORCE_LAST_GOOD_PLAN ON allows the system to automatically revert to a previously good execution plan when performance degrades.
These two-step settings achieve automatic optimization of query performance.
Use automatic tuning in the Azure portal to monitor queries and improve workload performance
Q10.SQL Server is running on an Azure virtual machine named SQL1.
SQL1 has an agent job configured that backs up all databases.
You register a user named dbadmin1 as a SQL Server Agent operator.
You need to ensure that dbadmin1 receives an email alert when a job fails.
Which three actions should you perform in sequence?
Drag (or tap) the required number of options from the “Options” on the left to the “Answer area” on the right. You can drag within the answer area to reorder them.
- Create a job alert
- Create a job notification
- Enable Database Mail
- Enable the email settings of SQL Server Agent
- Create a job target
- Drag here
Show answer
To send email notifications from SQL Server Agent, you must first enable the mail settings of SQL Server Agent.
This makes the notification feature available.
Next, you create an alert that fires when a job fails.
Finally, you create a job notification that notifies the operator for that alert.
If you do not configure them in this order, notifications will not work.
Database Mail is a prerequisite feature, but among the question’s options this order is the correct answer.
Notify an operator of job status – SQL Server Agent