Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 71:

  DRAG DROP

  You manage a Microsoft SQL Server instance with data connection configured. You create a Management Data Warehouse instance in a different instance.

  Currently all the System Data collection sets are stopped.

  You need to configure the System Data collection sets to perform historical and trend analyses by using Data collection reports.

  Which System Data collection sets should you use? To answer, drag the appropriate System Data collection sets to the correct targets. Each System Data collection set may be used once, more than once, or not at all. You may need to drag

  the split bar between panes or scroll to view content.

  NOTE: Each correct selection is worth one point.

  Select and Place:

  

  

• Question 72:

  You have two SQL Server 2012 instances named SQLDev and SQLProd.

  You plan to create a new database on SQLProd that will use SQL Server Authentication.

  You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev if they do not have a login on the SQLDev instance.

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

  A. SQL Server Integration Services (SSIS) scripts
  B. Extended Events
  C. Triggers
  D. SQL Server Analysis Services (SSAS) scripts
  E. Contained database
  E. Contained database

• Question 73:

  You are creating a database that will store usernames and passwords for an application.

  You need to recommend a solution to store the passwords in the database.

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

  A. One-way encryption
  B. Transparent Data Encryption (TDE)
  C. Encrypting File System (EFS)
  D. Reversible encryption
  B. Transparent Data Encryption (TDE)

• Question 74:

  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 need to configure a Microsoft SQL Server instance to ensure that a user Mail1 can send mail by using Database Mail.

  Solution: You add the sysadmin server role to Mail1.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 75:

  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 data warehouse that stored 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 generate a new certificate on new instance.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 76:

  HOTSPOT

  You manage a Microsoft SQL Server instance. You have a user named User1.

  You need to grant the minimum permissions necessary to allow User1 to review audit logs.

  For each action, which option should you use? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  

  ---

  Box 1: securityadmin

  To access log files for instances of SQL Server that are online, this requires membership in the securityadmin fixed server role.

  Box 2: sys.server_audit_specifications

  sys.server_audit_specifications contains information about the server audit specifications in a SQL Server audit on a server instance.

• Question 77:

  HOTSPOT

  Your organization is developing a web application. The application will access data from a Microsoft SQL Server database.

  You must implement a security solution that meets the following requirements:

  All user logins must be associated with an Active Directory (AD) user account.

  Service accounts are not permitted.

  Constrained databases are not permitted.

  Users must not be able to log on to SQL Server as the web application and access the database

  The web application must be permitted to display records and add records to the database.

  You need to implement the required security and permissions structure for the web application while enforcing the principle of least privilege.

  Which settings should you use? To answer, select the appropriate options in the answer area.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Box 1: Use Windows authentication

  Windows authentication is usually the best choice in the following situations:

  There is a domain controller.

  The application and the database are on the same computer.

  You are using an instance of SQL Server Express or LocalDB.

  Box 2: Database application role

  An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application.

  Box 3: Select/Insert

  The web application must be permitted to display records and add records to the database.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/application-roles

• Question 78:

  You maintain a database named DB1 that has a nonpartitioned table. You create an index in the table. Automatic statistics updates are disabled.

  Users report that some Microsoft SQL Server Reporting Service (SSRS) queries take a long time to run. You determine that the issue is caused by stale statistics for Index1.

  You need to update the statistics for the index.

  What should you do?

  A. Run the system stored procedure sp_updatestats.
  B. Run the UPDATE STATISTICS command with the COLUMNS option.
  C. Run DBCC INDEXDEFRAG
  D. Set the value of the Database Read-Only property to True.
  B. Run the UPDATE STATISTICS command with the COLUMNS option.

• Question 79:

  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 attempt to restore a database on a new SQL Server instance and receive the following error message:

  

  You need to ensure that you can restore the database successfully.

  Solution: You generate a new certificate on the new instance.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 80:

  HOTSPOT

  A company has an on-premises Microsoft SQL Server environment and Microsoft Azure SQL Database instanced. The environments host several customer databases.

  You host a local database and a Stretch database that has a table named Members for one specific customer.

  You need to provide the customer with information about the space used in the databases.

  In the table below, identify the query that provides the required information for each database.

  NOTE: Make only one selection in each column.

  Hot Area: