Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 141:

  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 142:

  You install a Microsoft SQL Server 2016 instance.

  The instance will store data extracted from two databases running on Windows Azure SQL Database.

  You hire a data steward to perform interactive data cleansing and ad hoc querying and updating of the database.

  You need to ensure that the data steward is given the correct client tools to perform these tasks.

  Which set of tools should you install?

  A. SQL Server Management Studio and Distributed Replay Client
  B. Master Data Services and Data Quality Client
  C. Data Quality Client and Distributed Replay Client
  D. Data Quality Client and SQL Server Management Studio
  B. Master Data Services and Data Quality Client

• Question 143:

  You have the following Microsoft SQL Server instances. The instances are members of a cluster.

  

  Server1 has 42 gigabytes (GB) of memory and Server 2 has 48 GB of memory. The operating system and other processes on each server require 4 GB of memory. Instance2 does not failover successfully to Server1.

  You need to resolve the issue.

  What should you do?

  NOTE: Each correct selection is worth one point.

  A. Set the value of the Maximum server memory option for Instance1 to 24576
  B. Set the value of the Minimum server memory option for Instance2 to 0
  C. Set the value of the Maximum server memory option for Instance2 to 32768
  D. Set the value of the Minimum server memory option for Instance1 to 16384
  C. Set the value of the Maximum server memory option for Instance2 to 32768

• Question 144:

  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 changes to the ERP application to resolve the search issue.

  The solution must minimize the impact on other queries generated from the ERP application.

  What should you recommend changing?

  A. The collation of the Products table
  B. The index on the ProductName column
  C. The collation of the ProductName column
  D. The data type of the ProductName column
  C. The collation of the ProductName column

• Question 145:

  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 data warehouse that stores sales data. One fact table has 100 million rows.

  You must reduce storage needs for the data warehouse.

  You need to implement a solution that uses column-based storage and provides real-time analytics for the operational workload.

  Solution: You remove any clustered indexes and load the table for processing.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 146:

  You administer a Microsoft SQL Server 2016 database that contains a table named OrderDetail. You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented.

  You need to reduce fragmentation.

  You need to achieve this goal without taking the index offline.

  Which Transact-SQL batch should you use?

  A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
  B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
  C. ALTER INDEX ALL ON OrderDetail REBUILD
  D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD
  B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE

• Question 147:

  HOTSPOT

  A server named AzureSqlSvr1 hosts several Microsoft Azure SQL Database instances.

  You need to configure audit logs for all databases to ensure that events are captured when a database schema is created, altered, or dropped.

  How should you complete the Azure PowerShell command? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: Set-AzureRmSqlServerAuditing

  The Set-AzureRmSqlServerAuditing cmdlet changes the auditing settings of an Azure SQL server. After the cmdlet runs successfully, auditing of the Azure SQL databases that are defined in the specified Azure SQL server is enabled.

  Box 2: AuditActionGroup

  Parameters include AuditActionGroup, but not AuditAction.

  Box 3: SCHEMA_OBJECT_CHANGE_GROUP

  SCHEMA_OBJECT_CHANGE_GROUP tracks whenever create, alter or drop is executed on a schema object such as a table, view or stored procedure.

  If you enable this group in a server audit specification, it will track changes to schema objects in all of the databases of this instance of SQL Server. If you enable the group in a database audit specification, it will track changes to schema objects within that database only.

  Incorrect Answers:

  The Set-AzureRmSqlDatabaseAuditing cmdlet changes the auditing settings of an Azure SQL database.

  References:

  https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlserverauditing

  https://www.ultimatewindowssecurity.com/sqlserver/auditpolicy/auditactiongroups/schema_object_change_group.aspx

• Question 148:

  DRAG DROP

  An organization deploys Microsoft SQL Server instances at a main office and the organization's remote office. SQL Server is not using Windows failover clustering.

  Remote employef s report connection issues when they attempt to run queries against a specific database that is hosted at the main office.

  You need to ensure that remote employees can retrieve data from a database instance that is hosted in the remote office.

  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:

  

  

  ---

  Database mirroring is a solution for increasing the availability of a SQL Server database.

  Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model.

• Question 149:

  You administer a Microsoft SQL Server 2016 failover cluster.

  You need to ensure that a failover occurs when the server diagnostics returns query_processing error.

  Which server configuration property should you set?

  A. SqlOumperDumpFlags
  B. FailureConditionLevel
  C. HealthCheckTimeout
  D. SqlDumperDumpPath
  B. FailureConditionLevel

• Question 150:

  You administer a Microsoft SQL Server database named Sales.

  The database is 3 terabytes in size.

  The Sales database is configured as shown in the following table.

  You discover that all files except Sales_2.ndf are corrupt.

  You need to recover the corrupted data in the minimum amount of time.

  What should you do?

  

  A. Perform a restore from a full backup.
  B. Perform a transaction log restore.
  C. Perform a file restore.
  D. Perform a filegroup restore.
  A. Perform a restore from a full backup.