Topic 6: Misc. Questions
You have an on-premises app named App1 that stores data in an on-premises Microsoft
SQL Server 2016 database named DB1.
You plan to deploy additional instances of App1 to separate Azure regions. Each region will
have a separate instance of App1 and DB1. The separate instances of DB1 will sync by
using Azure SQL Data Sync.
You need to recommend a database service for the deployment. The solution must
minimize administrative effort.
What should you include in the recommendation?
A. Azure SQL Managed instance
B. Azure SQL Database single database
C. Azure Database for PostgreSQL
D. SQL Server on Azure virtual machines
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
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.
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
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.
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
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).
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
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
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
You have 40 Azure SQL databases, each for a different customer. All the databases reside
on the same Azure SQL Database server.
You need to ensure that each customer can only connect to and access their respective
database.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Implement row-level security (RLS).
B. Create users in each database.
C. Configure the database firewall.
D. Configure the server firewall.
E. Create logins in the master database.
F. Implement Always Encrypted.
Explanation:
Manage database access by adding users to the database, or allowing user access with
secure connection strings.
Database-level firewall rules only apply to individual databases
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/database/secure-database-tutoria
| Page 5 out of 28 Pages |
| Previous |