Microsoft 70-765 Exam Practice Test Instant Access

Question 1

You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012.

You create an Availability Group defined by the following schem

a. (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?

AAVAILABILITY_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)

BAVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

CAVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

DAVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = YES,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')

Answer : C

As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.

Question 2

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 use drive D on the virtual machine to store the database files.

Does this meet the goal?

AYes

BNo

Answer : B

The D drive should only be used for temporary data.

Question 3

You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.

You need to ensure that the SQL Server does not limit the performance of App1.

What configuration option should you set?

Amin worker threads to 1

Bmax server memory to 24 GB

Cmax worker threads to 1

Dmin server memory to 16 GB

Answer : B

Before you set the amount of memory for SQL Server, determine the appropriate memory setting by

subtracting, from the total physical memory, the memory required for the OS, memory allocations not

controlled by the max_server_memory setting, and any other instances of SQL Server (and other system uses, if the computer is not wholly dedicated to SQL Server). This difference is the maximum amount of memory you can assign to the current SQL Server instance.

References:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configurationoptions?view=sql-server-2017

Question 4

You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.

You need to implement real-time encryption of the database and all backups.

Solution: You use the always Encrypted wizard to encrypt all possible for the tables in the primary instance.

Does the solution meet the goal?

AYes

BNo

Answer : B

Always Encrypted does not support geo replication.

Transparent Data Encryption (TDE) would provide a solution.

Note: Use the Always Encrypted Wizard to help protect sensitive data stored in a SQL Server database. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to SQL Server.

References:

https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/

http://blog.pragmaticworks.com/sql-server-2016-data-masking-and-always-encrypted

Question 5

You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.

You discover performance issues when large amounts of data are written to tables under heavy system load.

You need to limit the number of cores that handle I/O.

What should you configure?

AProcessor affinity

BLightweight pooling

CMax worker threads

DI/O affinity

Answer : D

The affinity Input-Output (I/O) mask Server Configuration Option.

To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.

References:

http://msdn.microsoft.com/en-us/library/ms189629.aspx

Question 6

You plan to install a Microsoft SQL Server 2014 instance.

The instance will support a database that has the following requirements:

Store Excel workbooks on the file system.

Access the workbooks through Transact-SQL.

Include the workbooks in database backups.

During installation, you need to ensure that the requirements will be met.

Which feature should you use?

AExcel Services

BFILESTREAM

CSQL Server Integration Services (SSIS)

DOpenXML

Answer : B

FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.

References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

Question 7

Note: This questions 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.

Your company has several Microsoft Azure SQL Database instances.

Data encryption should be allowed to be implemented by the client applications that access the dat

a. Encryption keys should not be made available to the database engine.

You need to configure the database.

What should you implement?

Atransport-level encryption

Bcell-level encryption

CTransparent Data Encryption

DAlways Encrypted

EEncrypting FileSystem

FBitLocker

Gdynamic data masking

Answer : A

Using encryption during transit with Azure File Shares

Azure File Storage supports HTTPS when using the REST API, but is more commonly used as an SMB file share attached to a VM.

HTTPS is a transport-level security protocol.

Microsoft 70-765 Provisioning SQL Databases Exam Practice Test