Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 91:

  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 the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.

  You plan to delegate encryption operations to a user.

  You need to grant the user permission to implement cell-level encryption while following the principle of least privilege.

  Which permission should you grant?

  A. DDLAdmin
  B. db_datawriter
  C. dbcreator
  D. dbo
  E. View Database State
  F. View ServerState
  G. View Definition
  H. sysadmin
  G. View Definition

• Question 92:

  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:

  "Msg 33111, Level 16, State 3, Line 2

  Cannot find server certificate with thumbprint `0x7315277C70764B1F252DC7A5101F6F66EFB1069D."

  You need to ensure that you can restore the database successfully. Solution: You add the backup set password to the restore command. Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 93:

  You are a database administrator for Contoso, Ltd. You configure a Microsoft SQL Server failover cluster with four nodes by using Windows Server 2012 R2 Datacenter Edition and SQL Server 2016 Enterprise edition.

  A server in the datacenter needs to be replaced. The server is part of the SQL Server Failover Instance (FCI).

  You need to remove the FCI node for the server that will be replaced.

  What should you do?

  A. Evict the node from Failover Cluster Manager.
  B. Run the Remove-ClusterResource Windows PowerShell cmdlet.
  C. Run the Remove-Cluster Windows PowerShell cmdlet.
  D. Remove the shared storage from Failover Cluster Manager.
  A. Evict the node from Failover Cluster Manager.

• Question 94:

  You administer all the deployments of Microsoft SQL Server 2016 in your company.

  A database contains a large product catalog that is updated periodically.

  You need to be able to send the entire product catalog to all branch offices on a monthly basis.

  Which configuration should you use?

  A. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
  B. SQL Server that includes an application database configured to perform transactional replication
  C. Two servers configured in the same data center SQL Server Availability Group configured in AsynchronousCommit Availability Mode One server configured as an Active Secondary
  D. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  E. SQL Server that includes an application database configured to perform snapshot replication
  F. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  G. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  H. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  E. SQL Server that includes an application database configured to perform snapshot replication

• Question 95:

  DRAG DROP

  On January 6, 2018, you observe that the data files for DB1 are corrupt. The log file is not affected. You must perform a piecemeal restore to restore FGA as soon as possible. You must minimize the amount of time required to restore the logs.

  You need to restore the primary filegroup and FGA.

  You observe issues with indexes as well as other minor consistency problems.

  You need to identify and repair all physical database problems while minimizing data loss and database downtime.

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

  Select and Place:

  

  

  ---

  Reference: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-files-and-filegroups-over-existing-files-sql-server?view=sql-server-2017

• Question 96:

  DRAG DROP

  You plan to deploy SQL Server 2014. You are designing two stored procedures named SP1 and SP2 that have the following requirements:

  - Prevent data read by SP1 from being modified by other active processes.

  -Prevent SP2 from performing dirty reads.

  You need to recommend the isolation level for each stored procedure.

  The solution must maximize concurrency. Which isolation levels should you recommend?

  To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.

  Select and Place:

  

  

  ---

  SP1 – repeatable read; SP2 – read commited

  -REPEATABLE READ This isolation level includes the guarantees given by SNAPSHOT isolation level. In addition, REPEATABLE READ guarantees that for any row that is read by the transaction, at the time the transaction commits the row has not been changed by any other transaction. Every read operation in the transaction is repeatable up to the end of the transaction.

  - Committed Read is SQL Server's default isolation level. It ensures that an operation will never read data another application has changed but not yet committed.

• Question 97:

  You manage a Microsoft SQL Server environment. You plan to encrypt data when you create backups.

  You need to configure the encryption options for backups.

  What should you configure?

  A. a certificate
  B. an MD5 hash
  C. an SHA-256 hash
  D. an AES 256-bit key
  D. an AES 256-bit key

• Question 98:

  You administer a Microsoft SQL Server 2016 failover cluster that contains two nodes named Node A and Node B.

  A single instance of SQL Server is installed on the cluster.

  An additional node named Node C has been added to the existing cluster.

  You need to ensure that the SQL Server instance can use all nodes of the cluster.

  What should you do?

  A. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
  B. Use Node A to install SQL Server on Node C.
  C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.
  D. Use Cluster Administrator to add a new Resource Group to Node B.
  C. Run the Add Node to SQL Server Failover Cluster Wizard on Node C.

• Question 99:

  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 database that includes a table named Candidate.

  You need to update the statistics for a column named Skills in the table and turn off automatic statistics updates for the column.

  Solution: You run the following query:

  

  Does this meet the goal?

  A. Yes
  B. No
  B. No

• Question 100:

  You administer a Microsoft SQL Server 2012 instance.

  You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

  A. Disable FILESTREAM on the Database.
  B. Enable FILESTREAM on the Server Instance.
  C. Configure the Database for Partial Containment.
  D. Create a non-empty FILESTREAM file group.
  E. Enable Contained Databases on the Server Instance.
  F. Set the FILESTREAM directory name on the Database.
  B. Enable FILESTREAM on the Server Instance.
  D. Create a non-empty FILESTREAM file group.
  F. Set the FILESTREAM directory name on the Database.