Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 261:

  DRAG DROP

  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 next of thescenario is exactly the same in each question in this series.Start of repeated scenario

  Contoso, Ltd. has Microsoft SQL Server databases that support a custom application. The current SQL Server environment consists of two servers: ContosoSQL1 and ContosoSQL2. These two servers participate in an Always On Availability

  Group named ContosoAG1 that is configured to use synchronous-commit with automatic failover. The secondary replica is not configured for read-only access.

  The application performs both transactional processing and historical data retrieval in a database named ContosoDB. The application includes an inventory management module. The inventory management module and database have

  experienced performance issues.

  Users report that a query named InventoryQuery1 takes a long time to complete. The query is shown as follows:

  SELECT ProductNumber, Name, ProductLine

  FROM Production.Product

  WHERE ProductNumber = N''

  The query plan used by SQL Server for this query is shown in the exhibit. (Click the Exhibit tab.) Various performance issues, including frequent long-term blocking episodes, prevent business users from completing their daily tasks. You

  suspect the tempdb database resources could be responsible. You must create Blocking reports for the ContosoDB database to identify issues.

  Exhibit.

  

  You plan to use Extended Events to review all Transact-SQL statements that are run against the ContosoSQL1 instance. The output from the Extended Events session must contain both start and stop events and must be written to a file. You

  must configure the Extended Events session to minimize possible data loss and reduce the effect on server performance.

  You plan to deploy an additional secondary replica named ContosoSQL3 to ContosoAG1. Read-only traffic must be load-balanced between the two secondary replicas, regardless of which instance is the primary replica. Contoso plans to add

  an additional dedicated reporting system that will rely on real-time data from the transactional databases.

  End of repeated scenario

  You need to create the blocking reports.

  Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

  Select and Place:

  

  

• Question 262:

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

  You manage a Microsoft SQL Server environment. You implement Transparent Data Encryption (TDE).

  A user will assist in managing TDE.

  You need to ensure that the user can view the TDE metadata while following the principle of least privilege.

  Which permission should you grant?

  A. db_ddladmin
  B. db_datawriter
  C. dbcreator
  D. db_owner
  E. View Database State
  F. View Server State
  G. View Definition
  H. sysadmin
  G. View Definition

• Question 263:

  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.

  Start of repeated scenario.

  You are a database administrator for a company that has on-premises Microsoft SQL Server environment. There are two domains in separate forests. There are no trust relationships between the domains. The environment hosts several customer databases, and each customer uses a dedicated instance running SQL Server 2016 Standard edition. The customer environments are shown in the following table.

  

  End of repeated scenario.

  You need to monitor WingDB and gather information for troubleshooting issues.

  Which two tools should you use? Each correct answer presents a complete solution.

  A. sys.dm_tran_locks
  B. sp_lock
  C. sys.dm_tran_active_snapshot_database_transactions
  D. Activity Monitor
  E. sp_monitor
  B. sp_lock
  D. Activity Monitor

• Question 264:

  You administer a Microsoft SQL Server 2016 database instance.

  You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and to execute SQL Server Agent jobs.

  To which role should you add UserA?

  A. Securityadmin
  B. RSExecRole
  C. SQLAgentUserRole
  D. DatabaseMailUserRole
  C. SQLAgentUserRole

• Question 265:

  You have a SQL Server 2014 environment That includes four servers. The servers are configured as shown in the following table.

  

  You plan to configure Policy-Based Management to enforce the following rules:

  On Server1, enable SQL Server password policies and enable the default trace.

  On Server3, ensure that the names of user-defined stored procedures begin with the prefix "usp_" and ensure that all databases use a casesensitive collation.

  You need to recommend which server you must configure as a Central Management Server.

  Which server should you recommend? (Each correct answer presents a complete solution. Choose all that apply.)

  A. Server1
  B. Server2
  C. Server3
  D. Seiver4
  A. Server1
  C. Server3

• Question 266:

  You are designing a database named DB1.

  Changes will be deployed to DB1 every Wednesday night.

  You need to recommend a strategy to deploy the changes to DB1.

  The strategy must meet the following requirements:

  The strategy must not disrupt backup operations.

  DB1 must be unavailable to users while the changes are deployed.

  You must be able to undo quickly the entire operation.

  What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

  A. Perform a copy-only database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original. Objects from the restored database.
  B. Create a database snapshot. If the deployment fails, recover the objects from the database snapshot.
  C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.
  D. Perform a full database backup before the changes are deployed. If the deployment fails, restore the database to another server and recover the original objects from the restored database.
  C. Create a database snapshot. If the deployment fails, revert the database to the database snapshot.

• Question 267:

  You administer a Microsoft SQL Server 2016 database that contains a table named AccountTransaction.

  You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.

  You need to defragment the index.

  You also need to ensure that user queries are able to use the index during the defragmenting process.

  Which Transact-SQL batch should you use?

  A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction. AccountCode REORGANIZE
  B. ALTER INDEX ALL ON AccountTransaction REBUILD
  C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction. AccountCode REBUILD
  D. CREATE INDEX IDXAccountTransactionAccountCode ON AccountTransaction. AccountCode WITH DROP EXISTING
  A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction. AccountCode REORGANIZE

• Question 268:

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

  You are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.

  One instance hosts a user database named HRDB. The database contains sensitive human resources data.

  You need to grant an auditor permission to view the SQL Server audit logs while following the principle of least privilege.

  Which permission should you grant?

  A. DDLAdmin
  B. db_datawriter
  C. dbcreator
  D. dbo
  E. View Database State
  F. View Server State
  G. View Definition
  H. sysadmin
  F. View Server State

• Question 269:

  Overview

  Application Overview

  Contoso, Ltd., is the developer of an enterprise resource planning (ERP) application.

  Contoso is designing a new version of the ERP application. The previous version of the ERP application used SQL Server 2008 R2.

  The new version will use SQL Server 2014.

  The ERP application relies on an import process to load supplier data. The import process updates thousands of rows simultaneously, requires exclusive access to the database, and runs daily. You receive several support calls reporting

  unexpected behavior in the ERP application. After analyzing the calls, you conclude that users made changes directly to the tables in the database.

  Tables

  The current database schema contains a table named OrderDetails.

  The OrderDetails table contains information about the items sold for each purchase order. OrderDetails stores the product ID, quantities, and discounts applied to each product in a purchase order. The product price is stored in a table named

  Products. The Products table was defined by using the SQL_Latin1_General_CP1_CI_AS collation.

  A column named ProductName was created by using the varchar data type. The database contains a table named Orders.

  Orders contains all of the purchase orders from the last 12 months. Purchase orders that are older than 12 months are stored in a table named OrdersOld.

  The previous version of the ERP application relied on table-level security.

  Stored Procedures

  The current version of the database contains stored procedures that change two tables. The following shows the relevant portions of the two stored procedures:

  

  Customer Problems

  Installation Issues

  The current version of the ERP application requires that several SQL Server logins be set up to function correctly. Most customers set up the ERP application in multiple locations and must create logins multiple times.

  Index Fragmentation Issues

  Customers discover that clustered indexes often are fragmented. To resolve this issue, the customers defragment the indexes more frequently. All of the tables affected by fragmentation have the following columns that are used as the

  clustered index key:

  

  Backup Issues

  Customers who have large amounts of historical purchase order data report that backup time is unacceptable.

  Search Issues

  Users report that when they search product names, the search results exclude product names that contain accents, unless the search string includes the accent.

  Missing Data Issues

  Customers report that when they make a price change in the Products table, they cannot retrieve the price that the item was sold for in previous orders.

  Query Performance Issues

  Customers report that query performance degrades very quickly. Additionally, the customers report that users cannot run queries when SQL Server runs maintenance tasks. Import Issues During the monthly import process, database

  administrators receive many supports call from users who report that they cannot access the supplier data. The database administrators want to reduce the amount of time required to import the data.

  Design Requirements

  File Storage Requirements

  The ERP database stores scanned documents that are larger than 2 MB. These files must only be accessed through the ERP application. File access must have the best possible read and write performance.

  Data Recovery Requirements

  If the import process fails, the database must be returned to its prior state immediately.

  Security Requirements

  You must provide users with the ability to execute functions within the ERP application, without having direct access to the underlying tables.

  Concurrency Requirements

  You must reduce the likelihood of deadlocks occurring when Sales.Prod and Sales.Proc2 execute.

  You need to recommend a solution that reduces the time it takes to import the supplier data. What should you include in the recommendation?

  A. Enable instant file initialization.
  B. Reorganize the indexes.
  C. Disable Resource Governor.
  D. Enable Auto Update Statistics.
  C. Disable Resource Governor.

• Question 270:

  You run the sp_who system stored procedure. A process is blocked by a long-running operation.

  You need to identify details of the processes involved in the deadlock including the resources that are being accessed. You must able to view the deadlock chain.

  What should you use?

  A. DBCC OPENTRAN()
  B. Extended events
  C. Query performance insights
  D. Data collector
  B. Extended events