Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 381:

  You administer a Microsoft SQL Server 2016 database named Contoso on a server named Server01.

  You need to track all SELECT statements issued in the Contoso database only by users in a role named Sales.

  What should you create?

  A. An Alert
  B. A Resource Pool
  C. An Extended Event session
  D. A Server Audit Specification
  E. A SQL Profiler Trace
  F. A Database Audit Specification
  G. A Policy
  H. A Data Collector Set
  F. A Database Audit Specification

• Question 382:

  You are migrating a database named Orders to a new server that runs Microsoft SQL Server 2016.

  You attempt to add the [Corpnet\User1] login to the database.

  However, you receive the following error message:

  "User already exists in current database."

  You need to configure the [Corpnet\User1] login to be able to access the Orders database and retain the original permissions.

  You need to achieve this goal by using the minimum required permissions. Which Transact-SQL statement should you use?

  A. DROP USER [User1]; CREATE USER [Corpnet\User1] FOR LOGIN [Corpnet\User1]; ALTER ROLE [db_owner] ADD MEM3ER [Corpnet\User1];
  B. ALTER SERVER RCLS Isysadmin] ADD MEMBER [Corpnet\User1];
  C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];
  D. ALTER ROLE [db owner] ADD MEMBBR [Corpnet\User1];
  C. ALTER USER [Corpnet\User1] WITH LOGIN [Corpnet\User1];

• Question 383:

  DRAG DROP

  You are designing two stored procedures named Procedure1 and Procedure2. You identify the following requirements:

  Procedure1 must take a parameter that ensures that multiple rows of data can pass into the stored procedure.

  Procedure2 must use business logic that resides in a Microsoft .NET Framework assembly.

  You need to identify the appropriate technology for each stored procedure.

  Which technologies should you identify? To answer, drag the appropriate technology to the correct stored procedure in the answer area. (Answer choices may be used once, more than once, or not at all.)

  Select and Place:

  

  

  ---

  Procedure 1 - A table-valued parameter (TVP); Procedure 2 - Common language runtime (CLR)

  References: http://msdn.microsoft.com/en-us/library/ms131102.aspx http://msdn.microsoft.com/en-us/library/bb522446.aspx http://msdn.microsoft.com/en-us/library/bb510489.aspx

• Question 384:

  HOTSPOT

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

  You configure an Always On Availability Group for a customer. You must create log reports for the customer that detail when the log is flushed to disk on the primary and secondary replica.

  You need to develop a report containing the requested information.

  In the table below, identify the log type that you should use for each replica.

  NOTE: Make only one selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Flush on primary: Log flush

  Log flush. Log data is generated and flushed to disk on the primary replica in preparation for replication to the secondary replica. It then enters the send queue.

  Flush on secondary: Log hardened

  The log is flushed on the secondary replica, and then a notification is sent to the primary replica to acknowledge completion of the transaction.

• Question 385:

  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 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.

  You need to recover SalesDb2 and SalesDb3 while minimizing the data loss.

  In the table below, identify the recovery strategy you should use for each database.

  NOTE: Make only one selection in each column. Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  SalesDB2:

  Row data are torn (error 824)

  How to Fix Torn Pages

  Run DBCC checkdb see for inconsistencies

  Check your error logs first and then restore your last backups and transaction logs.

  All backups are lost.

  SalesDB3:

  Checksum issues for data pages.

  FEPAIR_REBUILD

  Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backuprestore/manage-the-suspect-pages-table-sql-server

• Question 386:

  You configure the Microsoft SQL Server Agent service to start automatically. You create a SQL Server Agent operator. You receive email notifications from other SQL Server Agent alerts that have been defined.

  You have a SQL Server Agent that is set to notify you through email when error number 50010 occurs. You do not receive any email notifications related to this alert. You verify that the error occurred recently.

  You need to determine why this alert is not sending you email notifications.

  Why is the alert not sending email notifications?

  A. The fail-safe operator is disabled
  B. The alert is enabled
  C. The event is not written to the Windows Application log
  D. The operator is disabled
  D. The operator is disabled

• Question 387:

  You administer several Microsoft SQL Server 2016 database servers.

  Merge replication has been configured for an application that is distributed across offices throughout a wide area network (WAN). Many of the tables involved in replication use the XML and varchar (max) data types.

  Occasionally, merge replication fails due to timeout errors.

  You need to reduce the occurrence of these timeout errors.

  What should you do?

  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.
  B. Create a snapshot publication, and reconfigure the problem subscribers to use the snapshot publication.
  C. Change the Merge agent on the problem subscribers to run continuously.
  D. Set the Remote Connection Timeout on the Publisher to 0.
  A. Set the Merge agent on the problem subscribers to use the slow link agent profile.

• Question 388:

  You plan to integrate an on-premises Microsoft SQL Server environment with Microsoft Azure.

  You need to create the authentication object so that you can connect to Azure.

  Which Windows PowerShell command or commands should you run?

  A. Invoke-Sqlcmd "CREATE EXTERNAL DATA SOURCE MyAzureStorage WITH (LOCATION = `wasbs://[email protected]/", CREDENTIAL = Pa$$w0rd)"
  B. New-SqlAzureKeyVaultColumnMasterKeySettings-KeyUrl https://myvault.vault.contoso.net:443/keys/CMK/4c05fla41b12488f9cba2ea964b6a700
  C. Invoke-Sqlcmd "CREATE CREDENTIAL AzureCred WITH IDENTITY = `AzureKey', SECRET = `Pa$$w0rd"
  D. Invoke-Sqlcmd "CREATE LOGIN AzureCred WITH CREDENTIAL = `AzureKey', PASSWORD = `Pa$$w0rd"
  C. Invoke-Sqlcmd "CREATE CREDENTIAL AzureCred WITH IDENTITY = `AzureKey', SECRET = `Pa$$w0rd"

• Question 389:

  HOTSPOT

  You deploy a three node Windows Server Failover Clustering (WSFC) cluster. You configure a Microsoft SQL Server instance in failover cluster instance (FCI) mode.

  You need to identify the actions that will take place if the motherboard on one server fails and automatic failover occurs.

  For each of the following statements, select Yes if the statement is true. Otherwise, select No.

  NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  References: https://docs.microsoft.com/en-us/sql/sql-server/failoverclusters/windows/always-on-failover-cluster-instances-sql-server

• Question 390:

  You need to address the requirements for disc usage monitoring for the SQL Servers.

  What should you do?

  A. You should configure disc quotas.
  B. You should configure a Dynamic Management View.
  C. You should configure alerts sent by the SQL Server Agent.
  D. You should configure a SQL Server Maintenance Plan.
  B. You should configure a Dynamic Management View.