Topic 5: Misc. Questions
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a
datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
A.
Azure SQL Database Premium
B.
Azure SQL Database serverless
C.
Azure SQL Database managed instance Business Critical
D.
Azure SQL Database Standard
Azure SQL Database Premium
Explanation:
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.
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/high-availability-sla
You have an Azure SQL database named DB1. You run a query while connected to DB1.
You review the actual execution plan for the query, and you add an index to a table
referenced by the query.
You need to compare the previous actual execution plan for the query to the Live Query
Statistics.
What should you do first in Microsoft SQL Server Management Studio (SSMS)?
A.
For DB1, set QUERY_CAPTURE_MODE of Query Store to All.
B.
Run the SET SHOWPLAN_ALL Transact-SQL statement.
C.
Save the actual execution plan.
D.
Enable Query Store for DB1.
Save the actual execution plan.
Explanation:
The Plan Comparison menu option allows side-by-side comparison of two different
execution plans, for easier identification of similarities and changes that explain the
different behaviors for all the reasons stated above. This option can compare between:
Two previously saved execution plan files (.sqlplan extension).
One active execution plan and one previously saved query execution plan.
Two selected query plans in Query Store
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 periodWhich 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
Set the Alert logic threshold to Dynamic
Set Threshold Sensitivity to Low
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.
Reference:
https://docs.microsoft.com/en-us/azure/azure-monitor/platform/alerts-dynamic-thresh
You have SQL Server on an Azure virtual machine that contains a database named DB1.
You view a plan summary that shows the duration in milliseconds of each execution of
query 1178902 as shown in the following exhibit:
What should you do to ensure that the query uses the execution plan which executes in the
least amount of time?
A.
Force the query execution plan for plan 1221065.
B.
Run the DBCC FREEPROCCACHE command.
C.
Force the query execution plan for plan 1220917.
D.
Disable parameter sniffing.
Force the query execution plan for plan 1220917.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/performance/query-store-usagescenarios
You have a new Azure SQL database named DB1 on an Azure SQL server named
AzSQL1.
The only user who was created is the server administrator.
You need to create a contained database user in DB1 who will use Azure Active Directory(Azure AD) for authentication.
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
You have an Azure SQL database named DB3.
You need to provide a user named DevUser with the ability to view the properties of DB3
from Microsoft SQL Server Management Studio (SSMS) as shown in the exhibit. (Click the
Exhibit tab.)
Which Transact-SQL command should you run?
A.
GRANT SHOWPLAN TO DevUser
B.
GRANT VIEW DEFINITION TO DevUser
C.
GRANT VIEW DATABASE STATE TO DevUser
D.
GRANT SELECT TO DevUser
GRANT VIEW DATABASE STATE TO DevUser
Explanation:
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-propertiesoptions-
page
You have a new Azure SQL database. The database contains a column that stores
confidential information.
You need to track each time values from the column are returned in a query. The tracking
information must be
stored for 365 days from the date the query was executed.
Which three actions should you perform? Each correct answer presents part of the
solution.
NOTE: Each correct selection is worth one point.
A.
Turn on auditing and write audit logs to an Azure Storage account.
B.
Add extended properties to the column.
C.
Turn on Advanced Data Security for the Azure SQL server.
D.
Apply sensitivity labels named Highly Confidential to the column.
E.
Turn on Azure Advanced Threat Protection (ATP).
Turn on auditing and write audit logs to an Azure Storage account.
Turn on Advanced Data Security for the Azure SQL server.
Apply sensitivity labels named Highly Confidential to the column.
Explanation:
C: Advanced Data Security (ADS) is a unified package for advanced SQL security
capabilities. ADS is available for Azure SQL Database, Azure SQL Managed Instance, and
Azure Synapse Analytics. It includes functionality for discovering and classifying sensitive
data
D: You can apply sensitivity-classification labels persistently to columns by using new
metadata attributes that have been added to the SQL Server database engine. This
metadata can then be used for advanced, sensitivity-based auditing and protection
scenarios.
A: An important aspect of the information-protection paradigm is the ability to monitor
access to sensitive data. Azure SQL Auditing has been enhanced to include a new field in
the audit log called data_sensitivity_information. This field logs the sensitivity classifications
(labels) of the data that was returned by a query. Here's an example:
You need to recommend an availability strategy for an Azure SQL database. The strategy
must meet the
following requirements:
Support failovers that do not require client applications to change their connection
strings.
Replicate the database to a secondary Azure region.
Support failover to the secondary region.
What should you include in the recommendation?
A.
failover groups
B.
transactional replication
C.
Availability Zones
D.
geo-replication
geo-replication
Explanation:
Active geo-replication is an Azure SQL Database feature that allows you to create readable
secondary databases of individual databases on a server in the same or different data
center (region).
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/active-geo-replication-overview
You need to apply 20 built-in Azure Policy definitions to all new and existing Azure SQL
Database deployments in an Azure subscription. The solution must minimize administrative
effort.
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.
You deploy a database to an Azure SQL Database managed instance.
D18912E1457D5D1DDCBD40AB3BF70D5D
You need to prevent read queries from blocking queries that are trying to write to the
database.
Which database option should set?
A.
PARAMETERIZATION to FORCED
B.
PARAMETERIZATION to SIMPLE
C.
Delayed Durability to Forced
D.
READ_COMMITTED_SNAPSHOT to ON
READ_COMMITTED_SNAPSHOT to ON
Explanation:
In SQL Server, you can also minimize locking contention while protecting transactions from
dirty reads of uncommitted data modifications using either:
The READ COMMITTED isolation level with theREAD_COMMITTED_SNAPSHOT database option set to ON.
The SNAPSHOT isolation level.
If READ_COMMITTED_SNAPSHOT is set to ON (the default on SQL Azure Database),
the Database Engine uses row versioning to present each statement with a transactionally
consistent snapshot of the data as it existed at the start of the statement. Locks are not
used to protect the data from updates by other transactions.
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-leveltransact-
sql
| Page 6 out of 22 Pages |
| Previous |
Copyright © - All Rights Reserved