Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 411:

  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 load the data in a heap table.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 412:

  You work as a Database Administrator (DBA) for a company named ABC.com.

  The company uses a Microsoft SQL Server 2012 infrastructure.

  You have a database named CorpDB. CorpDB contains a table named SalesInfo.

  You discover that some table has been deleted from the SalesInfo table.

  You are unable to find out who deleted the information.

  You need to implement a solution to monitor the deletion of any further information from the SalesInfo table. You want to minimize the development effort required for the solution.

  What should you configure?

  A. You should configure table permissions.
  B. You should configure a user role.
  C. You should configure change data capture.
  D. You should configure a trigger.
  C. You should configure change data capture.

• Question 413:

  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 named Mail1 can send mail by using Database Mail.

  Solution: You add the DatabaseMailUserRole to Mail1 in the tempdb database.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 414:

  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 examining information about users, sessions, and processed in an on-premises Microsoft SQL Server Database Engine instance.

  You need to return information about processes that are not idle, that belong to a specific user, or that belong to a specific session.

  What should you use?

  A. Activity Monitor
  B. sp_who3 stored procedure
  C. SQL Server Management Studio (SSMS) Object Explorer
  D. SQL Server Data Collector
  E. SQL Server Data Tools (SSDT)
  F. SQL Server Configuration Manager
  B. sp_who3 stored procedure

• Question 415:

  You work as a Developer at ABC.com.

  All databases are hosted on Windows Server 2012 servers running SQL Server 2012.

  You are developing a custom CRM application named CorpCRM.

  The application will store usernames and passwords in a SQL Server 2012 database named CorpCRMDB1.

  You need to ensure the usernames and passwords are secure.

  Which of the following would be most suitable password storage solution?

  A. One-way encryption
  B. Reversible encryption
  C. Encrypting File System (EFS)
  D. Secure Sockets Layer (SSL)
  A. One-way encryption

• Question 416:

  You need to validate rows before they are added to a table every time a row is added using a user-defined function.

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

  A. DML Trigger
  B. Default constraint
  C. Foreign key
  D. CHECK constraint
  D. CHECK constraint

• Question 417:

  You have a SQL Server 2014 instance named SQL1. SQL1 creates error events in the Windows Application event log.

  You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log.

  Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)

  A. A policy
  B. A maintenance plan
  C. An alert
  D. A job
  E. A trigger
  D. A job
  E. A trigger

• Question 418:

  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 a server that runs Microsoft SQL Server 2016 Web edition. The server has a default instance that hosts a database named DB1.

  You need to ensure that you can perform auditing at the database level for DB1.

  Solution: You migrate DB1 to a named instance on a server than runs Microsoft SQL Server 2016 Standard edition.

  Does the solution meet the goal?

  A. Yes
  B. No
  B. No

• Question 419:

  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 text of the scenario is

  exactly the same in each question in this series.

  You maintain a Microsoft SQL Server instance that contains the following databases SalesDb1, SalesDb2, and SalesDb3. Each database has tabled named Products and Sales. The following table shows the configuration of each database.

  

  The backup strategies for each database are described in the following table.

  

  Each full or differential backup operation writes into a new file and uses a different sequence number. You observe the following database corruption issues.

  

  SalesDb3 reports a number of database corruption issues related to error 823 and 824 when reading data pages. You must display the following information about the corrupted pages:

  database name

  impacted file id

  impacted file physical name

  impacted page id

  event type that identifies the error type

  error count

  Users report performance issues when they run queries against SalesDb2. You plan to monitor query statistics and execution plans for SalesDb2 by using Query Store. The monitoring strategy must meet the following requirements:

  Perform automatic data cleanup when query store disk usage reaches 500 megabyte (MB).

  Capture queries based on resource consumption.

  Use a stale query threshold value of 60 days.

  The query optimizer generates suboptimal execution plans for a number of queries on the Sales table in SalesDb2. You will create a maintenance plan that updates statistics for the table. The plan should only update statistics that were

  automatically created and have not been updated for 30 days. The update should be based on all data in the table.

  Users reports that they encounter the following error when they query SalesDb1: “SQL

  Server detected a logical consistency-based I/O error: incorrect checksum (expected:

  0x5d672d9b; actual: 0xdd672d98). It occurred during a read of page (1.232) in database ID

  12 at offset 0x000000001d0000 in file F:\Databases\MSSQLServer

  Databases\MSSQL13.MSSQL2016\MSSQL\DATA\SalesDb1.mdt.”

  You must restore the impacted page from SalesDb1Full_1.bak. A single backup set named SalesDb1Log.bak was created since the latest full backup operation.

  You need to restore the impacted page.

  Which four Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.

  Select and Place:

  

  

  ---

  Step 1:

  Restore page

  Start a page restore with a full database, file, or filegroup backup that contains the page. In the RESTORE DATABASE statement, use the PAGE clause to list the page IDs of all of the pages to be restored.

  Step 2:

  Restore log file with norecovery. Use the first file (FILE = 1).

  Step 3:

  Backup the tail-end of the log.

  Create a new log backup of the database that includes the final LSN of the restored pages, that is, the point at which the last restored page is taken offline.

  Step 4:

  Restore database with recovery. Use second file (FILE = 2).

  Restore the new log backup. After this new log backup is applied, the page restore is completed and the pages are now usable.

  Example:

  The following example restores four damaged pages of file B with NORECOVERY. Next, two log backups are applied with NORECOVERY, followed with the tail-log backup, which is restored with RECOVERY. This example performs an

  online restore. In the example, the file ID of file B is 1, and the page IDs of the damaged pages are 57, 202, 916, and 1016.

  RESTORE DATABASE PAGE='1:57, 1:202, 1:916, 1:1016'

  FROM

  WITH NORECOVERY;

  RESTORE LOG FROM

  WITH NORECOVERY;

  RESTORE LOG FROM

  WITH NORECOVERY;

  BACKUP LOG TO ;

  RESTORE LOG FROM WITH RECOVERY;

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backuprestore/restore-pages-sql-server

• Question 420:

  You plan to install Microsoft SQL Server 2016 for a web hosting company.

  The company plans to host multiple web sites, each supported by a SQL Server database.

  You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.

  Which edition should you choose?

  A. Express Edition with Tools
  B. Standard Edition
  C. Web Edition
  D. Express Edition with Advanced Services
  B. Standard Edition