Microsoft 70-765 Provisioning SQL Databases Exam Practice Test

Page: 1 / 14
Total 267 questions
Question 1

A company has an infrastructure that runs Microsoft SQL Server 2017 in Azure.

You need to ensure validation is performed on a memory-optimized Linux system.

Which Transact-SQL statement should you run?



Answer : B

References:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15


Question 2

You need to ensure that the columns are protected.

What should you use?



Answer : A


Question 3

You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

You plan to configure Instant File Initialization.

You need to ensure that Data File Autogrow operations use Instant File Initialization.

What should you do? Choose all that apply.



Answer : C, D

How To Enable Instant File Initialization

References:

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


Question 4

You plan to migrate on-premises Microsoft SQL Server databases to SQL Server on a Microsoft Azure virtual machine.

A full backup of the databases is 3 TB. The corporate network has a 1.5 MB/sec Internet connection.

You need to move the backups to Azure to ensure that you can restore the databases to the Azure virtual machine as quickly as possible.

What should you do?



Answer : A

Azure Import/Export service is used to securely import large amounts of data to Azure Blob storage and Azure Files by shipping disk drives to an Azure datacenter. This service can also be used to transfer data from Azure Blob storage to disk drives and ship to your on-premises sites. Data from one or more disk drives can be imported either to Azure Blob storage or Azure Files.

References:

https://docs.microsoft.com/en-us/azure/storage/common/storage-import-export-service


Question 5

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?



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


Question 6

You are tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance.

The virtual machine originally had four CPU cores and now has 32 CPU cores.

The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.

Several reports use the PIVOT statement and access more than 100 million rows in table1. You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database.

You need to prevent the PAGELATCH_IO waits from occurring.

Solution: You rewrite the queries to use aggregates instead of PIVOT statements.

Does this meet the goal?



Answer : B

Instead you can add more files to the database.

References:

https://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and-latches/


Question 7

You are a database administrator for a Microsoft SQL Server 2014 environment.

You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.

You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.

Which feature should you use?



Answer : B

Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

References:https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication


Page:    1 / 14   
Total 267 questions