Microsoft 70-765 Exam Practice Test Instant Access

Question 1

A company hosts a default installation of Microsoft SQL Server 2017 on a Linux-based server.

You observe that the performance of the server has degraded. Processes for allocating tables show high latency.

You need to recommend a solution to improve performance when allocating tables.

What should you recommend?

AIncrease the Virtual Address Space limit.

BReinstall SQL Server on Linux.

CConfigure additional tempdb data files.

DSet a memory limit for the SQL server.

Answer : D

References:

https://www.itprotoday.com/microsoft-sql-server/top-10-sql-server-performance-tuning-tips

Question 2

You administer a Microsoft SQL Server 2014 instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN). You discover I/O issues. The SAN is at capacity and additional disks cannot be added.

You need to reduce the I/O workload on the SAN at a minimal cost.

What should you do?

AMove user databases to a local disk.

BExpand the tempdb data and log files

CModify application code to use table variables

DMove the tempdb files to a local disk

Answer : D

The use of local disks for TempDB allows us to have more flexibility when configuring for optimal performance. It is a common performance recommendation to create the TempDB database on the fastest storage available. With the capability to utilize local disk for TempDB placement we can easily utilize disks that are larger, have a higher rotational speed or use SSD disks.

References:https://www.mssqltips.com/sqlservertip/2817/sql-server-2012-cluster-with-tempdb-on-local-disk/

Question 3

You administer a SQL Server 2014 database instance.

You need to configure the SQL Server Database Engine service on a failover cluster.

Which user account should you use?

AA domain user

BThe BUILTIN\SYSTEM account

CA local user with Run as Service permissions

DThe SQLBrowser account

Answer : A

References:

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-setup

Question 4

You administer a Microsoft SQL Server 2016 instance.

You need to configure a new database to support FILETABLES.

What should you do? Choose all that apply.

ADisable FILESTREAM on the Database.

BEnable FILESTREAM on the Server Instance.

CConfigure the Database for Partial Containment.

DCreate a non-empty FILESTREAM file group.

EEnable Contained Databases on the Server Instance.

FSet the FILESTREAM directory name on the Database.

Answer : B, D, F

References:

https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable

Question 5

Answer : C, G

P30 stats: Disk size is 1024 GB (1 TB), IOPS per disk is 5000.

Recommendation: Use a minimum of 2 P30 disks (1 for log files and 1 for data files and TempDB; or stripe two or more disks and store all files in a single volume).

References:

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/premium-storage#scalability-and-performance-targets

Question 6

You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.

You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs.

You also need to maintain transactional consistency of the data across both servers.

You need to achieve these goals without manual intervention.

Which configuration should you use?

ATwo servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance

BSQL Server that includes an application database configured to perform transactional replication

CTwo servers configured in the same data centerA primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby

DTwo servers configured in different data centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary

ETwo servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary

FTwo servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode

GSQL Server that includes an application database configured to perform snapshot replication

HTwo servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode

Answer : H

Always On availability groups supports two availability modes---asynchronous-commit mode and synchronous-commit mode

Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.

References:https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-modes-always-on-availability-groups#SyncCommitAvMode

Question 7

You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.

You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.

What should you create?

AAn Alert

BA Resource Pool

CAn Extended Event session

DA Server Audit Specification

EA SQL Profiler Trace

FA Database Audit Specification

GA Policy

HA Data Collector Set

Answer : F

To audit users in a role use a Database Audit Specification.

References:https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-audit-specification-transact-sql

Microsoft Provisioning SQL Databases 70-765 Exam Practice Test