Online Access Free 70-765 Exam Questions

You need to optimize SRV1.
What configuration changes should you implement? To answer, select the appropriate option from each list in the answer area.

Correct Answer:

Explanation:
From the scenario: SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application hasapproximately 30,000 concurrent users and relies heavily on the use of temporary tables.
Box 1: Change the size of the tempdb log file.
The size and physical placement of the tempdb database can affect the performance of a system. For example, if the size that is defined for tempdb is too small, part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server. You can avoid this overhead by increasing the sizes of the tempdb data and log file.
Box 2: Add additional tempdb files.
Create as many files as needed to maximize disk bandwidth. Using multiple files reduces tempdb storage contention and yields significantly better scalability. However, do not create too many files because this can reduce performance and increase management overhead. As a general guideline, create one data file for each CPU on the server (accounting for any affinity mask settings) and then adjust the number of files up or down as necessary.
Topic 2, Contoso, Ltd Case Study
Background
You are the database administrator for Contoso, Ltd. The company has 200 offices around the world. The company has corporate executives that are located in offices in London, New York, Toronto, Sydney, and Tokyo.
Contoso, Ltd. has a Microsoft Azure SQL Database environment. You plan to deploy a new Azure SQL Database to support a variety of mobile applications and public websites.
The company is deploying a multi-tenant environment. The environment will host Azure SQL Database instances. The company plans to make the instances available to internal departments and partner companies. Contoso is in the final stages of setting up networking and communications for the environment.
Existing Contoso and Customer instances need to be migrated to Azure virtual machines (VM) according to the following requirements:
The company plans to deploy a new order entry application and a new business intelligence and analysis application. Each application will be supported by a new database. Contoso creates a new Azure SQL database named Reporting. The database will be used to support the company's financial reporting requirements. You associate the database with the Contoso Azure Active Directory domain.
Each location database for the data entry application may have an unpredictable amount of activity. Data must be replicated to secondary databases in Azure datacenters in different regions.
To support the application, you need to create a database named contosodb1 in the existing environment.
Objects

Database
The contosodb1 database must support the following requirements:
Application
For the business intelligence application, corporate executives must be able to view all data in near real-time with low network latency.
Contoso has the following security, networking, and communications requirements:

You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs owned by UserA To which role should you add UserA?

• A.SQLAgentUserRole
• B.Securityadmin
• C.ServerGroupAdministratorGroup
• D.DatabaseMailUserRole

You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory.
You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Premium database pool that has 125 DTUs.
Does the solution meet the goal?

• A.Yes
• B.No

You are a database administrator for a Microsoft SQL Server 2014 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 = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = YES,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
• B.AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,READ_ONLY_ROUTING_LIST = NONE)
• C.AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
• D.AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

You are planning a Microsoft SQL Server deployment. You do not have a Storage Area Network (SAN) or Network Attached Storage (NAS) infrastructure.
You have a physical host that has 128 gigabytes (GB) RAM, two 8-core processors, and two 1 GB network interface cards (NICs). The host supports RAID through a write-caching RAID controller. The RAID controller is not specifically designed for DBMS systems. The host can support up to eight physically attached disks. The system does not include a battery backup power supply.
You must implement SQL Server best practices when installing and configuring the server to host SQL Server.
You need to configure the system to support a SQL Server installation by using best practices.
What should you do?

• A.Disable write-caching on the RAID controller.
• B.Increase the RAM in the server.
• C.Configure NIC Teaming.
• D.Add two more processors.