Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 281:

  You have a Microsoft SQL Server database named DB1. Nested and recursive triggers for DB1 are disabled. There is an existing DDL trigger named TableAudit in the database. The trigger captures the name of each table as the table is

  created.

  You define a policy for the database by using SQL Server policy-based management. The policy requires that all table names use the prefix tbl. You set the evaluation mode for the policy to On change: prevent.

  You observe that developers are able new tables that do not include the required prefix in the table name.

  You need to ensure the policy is enforced.

  What should you do?

  A. Enable recursive triggers
  B. Change the policy evaluation mode to On Schedule
  C. Disable the TableAudit trigger
  D. Enable nested triggers
  D. Enable nested triggers

• Question 282:

  You deploy a database by using SQL Server 2012.

  The database contains a table named Table1.

  You need to recommend a solution that meets the following requirements:

  Stores the most recent data from Table1 by using the fastest storage solution possible.

  Stores the historical data from Table1 by using a slower storage solution. What should you recommend?

  A. partitioned views
  B. a database snapshot
  C. change data capture
  D. table partitioning
  D. table partitioning

• Question 283:

  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 meets the data recovery requirement. What should you include in the recommendation?

  A. A differential backup
  B. A transaction log backup
  C. Snapshot isolation
  D. A database snapshot
  D. A database snapshot

• Question 284:

  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.

  Your company is developing a new business intelligence application that will access data in a Microsoft Azure SQL Database instance. All objects in the instance have the same owner.

  A new security principal named BI_User requires permission to run stored procedures in the database. The stored procedures read from and write to tables in the database. None of the stored procedures perform IDENTIFY_INSERT

  operations or dynamic SQL commands.

  The scope of permissions and authentication of BI_User should be limited to the database. When granting permissions, you should use the principle of least privilege.

  You need to create the required security principals and grant the appropriate permissions.

  Solution: You run the following Transact-SQL statement in the master database:

  CREATE LOGIN BI_User WITH PASSWORD = `Pa$$w 鴕d'

  You run the following Transact-SQL statement in the business intelligence database:

  

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 285:

  You have a SQL Server instance on a server named Server1. You need to recommend a solution to perform the following tasks every week:

  Rebuild the indexes by using a new fill factor.

  Run a custom T-SQL command.

  Back up the databases.

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

  A. A trigger
  B. An alert
  C. A maintenance plan
  D. Windows PowerShell
  E. A system policy
  C. A maintenance plan

• Question 286:

  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.

  You have a database named DB1 that is 640 GB and is updated frequently.

  You enable log shipping for DB1 and configure backup and restore to occur every 30 minutes.

  You discover that the hard disks on the database server are almost full.

  You need to reduce the amount of disk space used by the log shipping process.

  Solution: You reduce the frequency of the transaction log backups to once per hour.

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 287:

  You plan to implement a fault tolerance solution for a Microsoft SQL Server database.

  The solution must provide failover storage on the local network.

  You need to ensure the solution can route traffic to failover storage by using SMB 3.0.

  Which storage option should you use?

  A. Cluster Shared Volumes
  B. Microsoft Azure Blob storage
  C. Always On availability group
  D. Stretch Database
  A. Cluster Shared Volumes

• Question 288:

  DRAG DROP

  You are designing a database for a university.

  The database will contain two tables named Classes and Enrollment that have the following specifications:

  Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.

  Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.

  You need to identify which SQL Server technology meets the specifications of each table.

  Which technologies should you identify? To answer, drag the appropriate technology to the correct table in the answer area.

  Select and Place:

  

  

  ---

  The FileTable feature brings support for the Windows file namespace and compatibility with Windows applications to the file data stored in SQL Server. FileTable lets an application integrate its storage and data management components, and provides integrated SQL Server services including full-text search and semantic search-over unstructured data and metadata. In other words, you can store files and documents in special tables in SQL Server called FileTables, but access them from Windows applications as if they were stored in the file system, without making any changes to your client applications.

• Question 289:

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

  You have a Microsoft Azure SQL database that has Blob Auditing configured.

  You need to review the audit logs.

  Solution: From Microsoft SQL Server Management Studio, you connect to the database, and then you execute the following statement.

  

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 290:

  You maintain three datacenters in different geographical regions. You have a four-node failover cluster that hosts a Microsoft SQL Server Failover Cluster Instance (FCI) in Datacenter1.

  You must extend FCI to add four nodes in Datacenter2.

  You need to configure a node majority quorum mode for the cluster.

  What are the two possible ways to achieve the goal? Each correct answer presents a complete solution.

  NOTE: Each correct selection is worth one point.

  A. Use a cloud quorum witness that is hosted in Microsoft Azure
  B. Use no witness
  C. Use a disk witness that is hosted in Datacenter2
  D. Use a disk witness that is hosted in Datacenter1
  E. Use a file share witness that is hosted in Datacenter3
  A. Use a cloud quorum witness that is hosted in Microsoft Azure
  E. Use a file share witness that is hosted in Datacenter3