Exam 70-765 topic 3 question 11 discussion

HOTSPOT -

Background -
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2, Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All SSRS and SSAS servers use named instances.
You configure a firewall rule for SSAS.

Databases -
Database Name:

DB1 -
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted into DB1 or updated each second. Inserts are made by many different external applications that your company's developers do not control. You observe that transaction log write latency is a bottleneck in performance. Because of the transient nature of all the data in this database, the business can tolerate some data loss in the event of a server shutdown.
Database Name:

DB2 -
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or inserted per second. You observe that the
WRITELOG wait type is the highest aggregated wait type. Most writes must have no tolerance for data loss in the event of a server shutdown. The business has identified certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:

Reporting -
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You need to maximize performance of writes to each database without requiring changes to existing database tables.
In the table below, identify the database setting that you must configure for each database.
NOTE: Make only one selection in each column. Each correct selection is worth one point.
Hot Area:

Suggested Answer:
DB1: DELAYED_DURABILITY=FORCED -
From scenario: Thousands of records are inserted into DB1 or updated each second. Inserts are made by many different external applications that your company's developers do not control. You observe that transaction log write latency is a bottleneck in performance. Because of the transient nature of all the data in this database, the business can tolerate some data loss in the event of a server shutdown.
With the DELAYED_DURABILITY=FORCED setting, every transaction that commits on the database is delayed durable.
With the DELAYED_DURABILITY= ALLOWED setting, each transactions durability is determined at the transaction level.
Note: Delayed transaction durability reduces both latency and contention within the system because:
* The transaction commit processing does not wait for log IO to finish and return control to the client.
* Concurrent transactions are less likely to contend for log IO; instead, the log buffer can be flushed to disk in larger chunks, reducing contention, and increasing throughput.
DB2: ALLOW_SNAPSHOT_ISOLATION ON and READ_COMMITTED_SNAPSHOT ON
Snapshot isolation enhances concurrency for OLTP applications.
Snapshot isolation must be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option before it is used in transactions.
The following statements activate snapshot isolation and replace the default READ COMMITTED behavior with SNAPSHOT:

ALTER DATABASE MyDatabase -

SET ALLOW_SNAPSHOT_ISOLATION ON -

ALTER DATABASE MyDatabase -

SET READ_COMMITTED_SNAPSHOT ON -
Setting the READ_COMMITTED_SNAPSHOT ON option allows access to versioned rows under the default READ COMMITTED isolation level.
From scenario: The DB2 database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most writes must have no tolerance for data loss in the event of a server shutdown.
The business has identified certain write queries where data loss is tolerable in the event of a server shutdown.
References:
https://msdn.microsoft.com/en-us/library/dn449490.aspx
https://msdn.microsoft.com/en-us/library/tcbchxcb(v=vs.110).aspx