Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 61:

  You administer two Microsoft SQL Server 2012 servers.

  Each server resides in a different, untrusted domain.

  You plan to configure database mirroring.

  You need to be able to create database mirroring endpoints on both servers.

  What should you do?

  A. Configure the SQL Server service account to use Network Service.
  B. Use a server certificate.
  C. Use a database certificate.
  D. Configure the SQL Server service account to use Local System.
  B. Use a server certificate.

• Question 62:

  You administer a Microsoft SQL Server 2016 environment.

  One of the SQL Server 2016 instances contains a database named Sales.

  You plan to migrate Sales to Windows Azure SQL Database.

  To do so, you need to implement a contained database.

  What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

  A. Set database containment to AZURE.
  B. Enable server property contained database authentication.
  C. Disable server property cross db ownership chaining.
  D. Set database containment to PARTIAL.
  E. Disable server property contained database authentication.
  F. Set database containment to FULL.
  B. Enable server property contained database authentication.
  D. Set database containment to PARTIAL.

• Question 63:

  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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.

  A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases.

  One customer reports that their database is not responding as quickly as the service level agreements dictate. You observe that the database is fragmented.

  You need to optimize query performance.

  Solution: You rebuild all indexes.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 64:

  HOTSPOT

  Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is

  exactly the same in each question in this series.

  You are a database administrator for a company that has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instances. The environment hosts several customer databases, and each customer uses a

  dedicated instance. The environments that you manage are shown in the following table.

  

  You need to configure the Contoso instance.

  How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.

  Hot Area:

  

  

  ---

  Box 1: show advanced options Advanced configuration options are displayed by first setting show advanced option to 1.

  Box 2: max worker threads SQL Server uses the native thread services of the operating systems so that one or more threads support each network that SQL Server supports simultaneously, another thread handles database checkpoints, and a pool of threads handles all users. The default value for max worker threads is 0. This enables SQL Server to automatically configure the number of worker threads at startup. The default setting is best for most systems.

  References: https://docs.microsoft.com/en-us/sql/database-engine/configurewindows/configure-the-max-worker-threads-server-configuration-option

• Question 65:

  You administer a Microsoft SQL Server 2016 server.

  One of the databases on the server supports a highly active OLTP application.

  Users report abnormally long wait times when they submit data into the application.

  You need to identify which queries are taking longer than 1 second to run over an extended period of time.

  What should you do?

  A. use SQL Profiler to trace all queries that are processing on the server. Filter queries that have a Duration value of more than 1,000.
  B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.
  C. Use the Job Activity monitor to review all processes that are actively running. Review the Job History to find out the duration of each step.
  D. Run the sp_who command from a query window.
  E. Run the DBCC TRACEON 1222 command from a query window and review the SQL Server event log.
  B. Use sp_configure to set a value for blocked process threshold. Create an extended event session.

• Question 66:

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

  You need to be notified immediately when fatal errors occur on Server01.

  What should you create?

  A. an Alert
  B. a Server Audit Specification
  C. an Extended Event session
  D. a Resource Pool
  E. a Policy
  F. a SQL Profiler Trace
  G. a Database Audit Specification
  A. an Alert

• Question 67:

  DRAG DROP

  You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1.

  Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the Date field. The schema of Table1 is configured as shown in the following table.

  

  Table1 contains the indexes shown in the following table.

  

  You need to recommend an index strategy to maximize performance for the queries that consume the indexes available to Table1.

  Which type of index storage should you recommend? To answer, drag the appropriate index storage type to the correct index in the answer area.

  Select and Place:

  

  

  ---

  Index Storage Type

  Designing a partitioned index independently (unaligned) of the base table can be useful in the following cases:

  -The base table has not been partitioned.

  -

  The index key is unique and it does not contain the partitioning column of the table.

  -

  You want the base table to participate in collocated joins with more tables using different join columns.

• Question 68:

  You administer a Microsoft SQL Server 2016 database that includes a table named dbo.Log. This table contains millions of records about user activity in an application.

  Records in dbo.Log that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention withinserts.

  You need to be able to modify dbo.Log without requiring any changes to the applications that utilize dbo.Log.

  Which type of solution should you use?

  A. Extended events
  B. Columnstore index
  C. Partitioned tables
  D. Read committed snapshot
  C. Partitioned tables

• Question 69:

  DRAG DROP

  You have a database named DB1.

  You must ensure that DB1 is highly available. You implement a Windows cluster. You need to create a Cluster Shared Volume (CSV) for the database and log files.

  Which three Windows Power Shell commands should you execute in sequence? To answer, move the appropriate Windows PowerShell commands from the list of Windows PowerShell commands to the answer area and arrange them in the

  correct order.

  Select and Place:

  

  

  ---

  Box 1: $cluster = Get-Cluster

  The Get-Cluster cmdlet gets information about one or more failover clusters in a given domain.

  This cmdlet can obtain a variety of configuration and state information about a failover cluster, including the following items, such as the EnabledSharedVolumes property.

  Box 2: $cluster.EnabledSharedVolumes ="Enabled"

  Box 3: Add-ClusterSharedVolume "Disk1"

  The Add-ClusterSharedVolume cmdlet makes a volume available in the Cluster Shared Volumes in a failover cluster. The Cluster Shared Volume must be chosen from the disks in Available Storage (that is, disks that have been added to the

  cluster but not assigned to a specific use in the cluster).

  References:

  https://docs.microsoft.com/en-us/powershell/module/failoverclusters/get-cluster

  https://docs.microsoft.com/en-us/powershell/module/failoverclusters/addclustersharedvolume?view=winserver2012-ps

• Question 70:

  HOTSPOT

  You have a database named WideWorldImporters that contains the following tables: TableA and TableB. Both tables are legacy tables. TableA contains a column that uses the ntext data type. TableB contains a column that uses the nvarchar (max) data type.

  The indexes in each table have become highly fragmented. You must reduce the fragmentation in both tables. You must permit concurrent user queries during defragmentation, even if the queries take longer to complete than usual.

  When possible, the REBUILD option is preferred. Table statistics must be updated at the end of this maintenance.

  In the table below, identify the strategy that must be used for each table.

  NOTE: Make only one selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  TableA: REBUILD -WITH (ONLINE =OFF)

  The data types image, ntext, and text can be rebuild offline only.

  TableB: REBUILD -WITH (ONLINE =ON)

  You CAN do online index rebuilds of varchar(max), nvarchar(max), and varbinary(max) data types.

  Note: When an index is created or rebuilt in SQL Server, statistics are created or updated by scanning all the rows in the table.

  However, starting with SQL Server 2012 (11.x), statistics are not created or updated by scanning all the rows in the table when a partitioned index is created or rebuilt. Instead, the query optimizer uses the default sampling algorithm to generate these statistics. To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/indexes/reorganize-and-rebuild-indexes