Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 151:

  HOTSPOT

  Your company hosts Microsoft SQL Server instances for customers. One customer requires that the SQL Server instance send an email when an alert is generated in the Sales database.

  You need to create the alert.

  How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.

  Hot Area:

  

  

  ---

  The following example adds an e-mail notification for the specified alert (Test Alert).

  NOTE: This example assumes that Test Alert already exists and that François Ajenstat is a valid operator name.

  USE msdb ;

  GO

  EXEC dbo.sp_add_notification

  @alert_name = N'Test Alert',

  @operator_name = N'François Ajenstat',

  @notification_method = 1;

  GO

  References: https://docs.microsoft.com/en-us/sql/relational-databases/system-storedprocedures/sp-add-notification-transact-sql?view=sql-server-2017

• Question 152:

  HOTSPOT

  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 the scenario 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 troubleshoot and resolve the issue with InventoryQuery1.

  Use the drop-down menus to select the answer choice that answers each question based on the information presented in the graphic.

  Hot Area:

  

  

• Question 153:

  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 support an application that stores data in a Microsoft SQL Server database. You have a query that returns data for a report that users run frequently.

  The query optimizer sometimes generates a poorly-performing plan for the query when certain parameters are used. You observe that this is due to the distribution of data within a specific table that the query uses.

  You need to ensure that the query optimizer always uses the query plan that you prefer.

  Solution: You add the KEEPFIXED PLAN query hint to the query.

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 154:

  You deploy a database by using SQL Server 2014.

  The database contains a table named Table1.

  You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.

  What should you recommend?

  A. Change data capture
  B. Statistics
  C. A trigger
  D. Master Data Services
  A. Change data capture

• Question 155:

  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.

  You plan to create a stored procedure to access data in CorpDB.

  You need to ensure that the stored procedure supports dirty reads. What should you do?

  A. You should configure the stored procedure to use the READ UNCOMMITTED isolation level.
  B. You should configure the stored procedure to use the READ COMMITTED isolation level.
  C. You should configure the stored procedure to use the READ SERIALIZABLE isolation level.
  D. You should configure the stored procedure to use the REPEATABLE READ isolation level.
  E. You should configure the stored procedure to use the SNAPSHOT isolation level.
  A. You should configure the stored procedure to use the READ UNCOMMITTED isolation level.

• Question 156:

  Note: This question is a 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 designing a new high availability (HA) solution for a set of application databases. The solution will be contained within a single datacenter and provided instance-level HA protection.

  You need to deploy a solution that allows for minimal downtime if the production server goes offline.

  Which should you implement?

  A. A Microsoft Azure Stretch Database
  B. log shipping
  C. an Always On Availability Group with all replicas in synchronous-commit mode
  D. a file share witness
  E. a Microsoft SQL Server failover cluster instance (FCI)
  F. a Windows Cluster with a shared-nothing architecture
  G. an Always On Availability Group with secondary replicas in asynchronous-commit mode
  E. a Microsoft SQL Server failover cluster instance (FCI)

• Question 157:

  DRAG DROP

  You have a database named DB1.

  You need to encrypt two columns in DB1 by using column-level encryption.

  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:

  

  

  ---

  Step 1: You must have a database master key. If your database does not already have a database master key, create one.

  Step 2: Create a certificate.

  Step 3: Create a symmetric key.

  References: https://docs.microsoft.com/en-us/sql/relationaldatabases/security/encryption/encrypt-a-column-of-data

• Question 158:

  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 server named Server1 that has Microsoft SQL Server installed.

  Server1 has SQL Server Audit configured to send audit event records to a file.

  You need to ensure that a database user named User1 can review the audit data.

  Solution: You grant the VIEW SERVER STATE permission to User1.

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 159:

  You administer a SQL Server 2014 instance.

  Users report that the SQL Server has seemed slow today.

  A large database was being restored for much of the day, which could be causing issues.

  You want to write a query of the system views that will report the following:

  Number of users that have a connection to the server

  Whether a user's connection is active Whether any connections are blocked What queries are being executed Whether the database restore is still executing and, if it is, what percentage of the restore is complete.

  Which system objects should you use in your query to best achieve this task?

  A. sys.dm_exec_requests, sys.dm_exec_sessions, sys.objects
  B. sys.dm_exec_sessions, sys.dm_exec_query_stats, sys.dm_exec_query_text,sys.objects
  C. sys.sysprocesses, sys.dm_exec_query_text, sys.objects
  D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text
  D. sys.dm_exec_requests, sys.dm_exec_sessions, sys.dm_exec_query_text

• Question 160:

  You administer a Microsoft SQL Server 2016 instance that has several SQL Server Agent jobs configured.

  SQL Server Agent jobs fail, the error messages returned by the job steps are truncated.

  The following error message is an example of the truncated error message:

  "Executed as user CONTOSO\ServiceAccount. ...0.4035.00 for 64-bit Copyright (C) Microsoft Corp 1984-2011. All rights reserved. Started 63513 PM Error 2012-06-23 183536.87 Code 0XC001000E Source UserImport Description Code

  0x00000000 Source Log Import Activity Descript... The package execution fa... The step failed."

  You need to ensure that all the details of the job step failures are retained for SQL Server Agent jobs.

  What should you do?

  A. Expand agent logging to include information from all events.
  B. Disable the Limit size of job history log feature.
  C. Configure event forwarding.
  D. Configure output files.
  D. Configure output files.