NEW QUESTION NO: 10
You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.
You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)

You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (
ALLOW_CONNECTIONS = READ_WRITE,
READ_ONLY_ROUTING_LIST = NONE)
B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = READ_ONLY,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
FAILOVER_MODE = MANUAL
SECONDARY_ROLE (
ALLOW_CONNECTIONS = YES,
READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
Answer: C
Explanation/Reference:
Explanation:
As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.
Incorrect Answers:
A, B: Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
D: ALLOW_CONNECTIONS cannot be set to YES. The syntax is:
ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL }
References: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability- modes-always-on-availability-groups

NEW QUESTION NO: 11
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You create the desired SQL Server configuration in an Azure Resource Group, then export the Resource Group template and save it to the Templates Library.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Azure Resource Manager template consists of JSON, and expressions that you can use to construct values for your deployment.
A good JSON editor, not a Resource Group template, can simplify the task of creating templates.
Note: In its simplest structure, a Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References: https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring- templates

NEW QUESTION NO: 12
User report that a query takes a long time to execute. The query has the following wait statistics.

Which resource causes the issue?
A. processor
B. disk
C. blocking
D. network
Answer: B
Explanation/Reference:
Expalantion:
PAGEIOLATCH Wait time and WaitCount are both high.
One of the most common wait type seen on SQL Server and definitely one that causes a lot of troubles to less experienced database administrators is the PAGEIOLATCH_SH wait type. This is one of those wait types that clearly indicates one thing, but which background and potential causes are much subtler and may lead to erroneous conclusions and worse, incorrect solutions The Microsoft definition of this wait type is:
Occurs when a task is waiting on a latch for a buffer that is in an I/O request. The latch request is in Shared mode. Long waits may indicate problems with the disk subsystem.
References: https://www.sqlshack.com/handling-excessive-sql-server-pageiolatch_sh-wait-types/

NEW QUESTION NO: 13
Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in a development environment.
You need to provide storage to the environment that minimizes costs.
Which storage option should you use?
A. Standard geo-redundant disk storage
B. Premium P20 disk storage
C. Standard locally redundant blob storage
D. Premium P30 disk storage
E. Standard locally redundant disk storage
F. Premium P10 disk storage
G. Standard zone redundant blob storage
H. Standard geo-redundant blob storage
Answer: E

NEW QUESTION NO: 14
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
Your company plans to use Microsoft Azure Resource Manager templates for all future deployments of SQL Server on Azure virtual machines.
You need to create the templates.
Solution: You use Visual Studio to create a JSON template that defines the deployment and configuration settings for the SQL Server environment.
Does the solution meet the goal?
A. Yes
B. No
Answer: A
Explanation/Reference:
Azure Resource Manager template consists of JSON, not XAML, and expressions that you can use to construct values for your deployment.
A good JSON editor can simplify the task of creating templates.
Note: In its simplest structure, an Azure Resource Manager template contains the following elements:
{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"resources": [ ],
"outputs": { }
}
References: https://docs.microsoft.com/en-us/azure/azure-resource-manager/resource-group-authoring- templates

NEW QUESTION NO: 15
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:

Answer: 

Explanation/Reference:
Explanation:
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 transaction's 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

NEW QUESTION NO: 16
You administer a Microsoft SQL Server 2012 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup.
Which backup option should you use?
A. STATS
B. COMPRESSION
C. CHECKSUM
D. IN IT
Answer: A
Explanation/Reference:
Explanation:
STATS is a monitoring option of the BACKUP command.
STATS [ =percentage ]
Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION NO: 17
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You have a mission-critical application that stores data in a Microsoft SQL Server instance. The application runs several financial reports. The reports use a SQL Server-authenticated login named Reporting_User.
All queries that write data to the database use Windows authentication.
Users report that the queries used to provide data for the financial reports take a long time to complete.
The queries consume the majority of CPU and memory resources on the database server. As a result, read-write queries for the application also take a long time to complete.
You need to improve performance of the application while still allowing the report queries to finish.
Solution: You create a snapshot of the database. You configure all report queries to use the database snapshot.
Does the solution meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Use a Resource Governor instead.
References: https://msdn.microsoft.com/en-us/library/bb933866.aspx

NEW QUESTION NO: 18
You use Microsoft SQL Server 2012 to develop a database application. You need to create an object that meets the following requirements:
Takes an input variable

Returns a table of values

Cannot be referenced within a view

Which object should you use?
A. Scalar-valued function
B. Inline function
C. User-defined data type
D. Stored procedure
Answer: D
Explanation/Reference:
Explanation:
Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.
References: https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored- procedures-database-engine

NEW QUESTION NO: 19
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space. The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has 30 containers. You create a VHD in each container.
Does this meet the goal?
A. Yes
B. No
Answer: B
Explanation/Reference:
Expalantion:
Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References: https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/

NEW QUESTION NO: 20
HOTSPOT
You are building the database platform for a multi-tenant application. The application will have one database per tenant and will have at least 30 tenants. Each tenant will have a separate resource group for billing purposes.
The application will require at least 10 GB of clustered columnstore indexes for each database.
You need to implement the database platform for the application. The solution must minimize costs.
What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hot Area:

Answer: 

Explanation/Reference:
Explanation:
The Standard tier service allows for 1TB of data. Here 30 x 10 GB, 0.3 TB, is required.
Incorrect Answers:
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers