Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 131:

  You are designing a monitoring application for a new SQL Server 2014 instance.

  You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance.

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

  A. The SQL Server error log
  B. The sys.dm_os_wait_stats dynamic management view
  C. The DBCC SQLPERF(WAITSTATS) command
  D. SQL Server Profiler
  B. The sys.dm_os_wait_stats dynamic management view

• Question 132:

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

  Does the solution meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 133:

  You administer a Microsoft SQL Server 2016 server.

  The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.

  You plan to configure Instant File Initialization.

  You need to ensure that Data File Autogrow operations use Instant File Initialization.

  What should you do? Choose all that apply.

  A. Restart the SQL Server Agent Service.
  B. Disable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
  E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
  F. Enable snapshot isolation.
  C. Restart the SQL Server Service.
  D. Add the CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.

• Question 134:

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

  You need to prevent users from disabling server audits in Server01.

  What should you create?

  A. an Alert
  B. a Resource Pool
  C. an Extended Event session
  D. a Policy
  E. a Database Audit Specification
  F. a SQL Profiler Trace
  G. a Server Audit Specification
  D. a Policy

• Question 135:

  You administer a Microsoft SQL Server 2016 Enterprise Edition server that uses 64 cores.

  You discover performance issues when large amounts of data are written to tables under heavy system load.

  You need to limit the number of cores that handle I/O.

  What should you configure?

  A. Processor affinity
  B. Lightweight pooling
  C. Max worker threads
  D. I/O affinity
  D. I/O affinity

• Question 136:

  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 have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.

  

  You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.

  

  Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.

  Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O.

  Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads data into an empty table that has a rowstore clustered index and two nonclustered rowstore indexes.

  You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction.

  Changes made must not interrupt the log backup chain.

  You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.

  All databases use the full recovery model. All backups are written to the network location \\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.

  

  Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.

  You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.

  Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data.

  The queries must be load balanced over variable read-only replicas.

  Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles.

  The user has EXECUTE permissions on the database. Queries from the operations system will perform both DDL and DML operations.

  The wait statistics monitoring requirements for the instances are described in the following table.

  

  You need to create the connection strings for the operations and reporting systems. In the table below, identify the option that must be specified in each connection string. NOTE: Make only one selection in each column.

  Hot Area:

  

  

  ---

  Reporting system: Connect to any current read-only replica instance

  We configure Read-OnlyAccess on an Availability Replica. We select Read-intent only.

  Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.

  From Scenario: Reporting system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader role. The user has EXECUTE permissions on the database. Queries make no changes to the data. The queries must be load balanced over variable read-only replicas.

  Operating system: Connect to the current primary replica SQL instance

  By default, both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group.

  From scenario: Operations system: This solution accesses data inDB1with a login that is mapped to a database user that is a member of the db_datareader and db_datawriter roles. The user has EXECUTE permissions on the database.

  Queries from the operations system will perform both DDL and DML operations.

  References: https://docs.microsoft.com/en-us/sql/database-engine/availabilitygroups/windows/configure-read-only-access-on-an-availability-replica-sql-server

• Question 137:

  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 Microsoft Azure SQL database that has Blob Auditing configured.

  You need to review the audit logs.

  Solution: You download the log files by using Microsoft Azure Storage Explorer, and then you open the files by using Microsoft SQL Server Management Studio (SSMS).

  Does this meet the goal?

  A. Yes
  B. No
  A. Yes

• Question 138:

  HOTSPOT

  You view the Restore Transaction Log settings for a database named DB1 as shown in the following exhibit.

  

  Use the drop down menus to select the answer choice that completes each statement based on the information presented in the graphic. NOTE: Each correct selection is worth one point.

  Hot Area:

  

  

  ---

  Log shipping secondary database can be kept in two restore modes:

  Restoring - This mode is also known as NORECOVERY mode.

  Standby - This mode is also known as Read-Only mode.

  When you use the NORECOVERY mode, the database will be in a restoring state and inaccessible to users, so uncommitted database transactions are not an issue.

  When you use the STANDBY mode, database will be in Read-Only state and users can access this database for read operations. Transactions in process on primary server or any uncommitted transaction cannot be read in secondary database.

  References: https://www.mssqltips.com/sqlservertip/3600/change-the-restore-mode-of-asecondary-sql-server-database-in-log-shipping-with-ssms/

• Question 139:

  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 disaster recovery (DR) solution for a heavily-utilized application database. The DR site is in a remote datacenter. You should minimize network traffic to the DR site during business hours.

  You need to deploy a solution that allows near-real time queries to be run on the secondary instance and minimizes the effect on performance for the main production database.

  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
  C. an Always On Availability Group with all replicas in synchronous-commit mode

• Question 140:

  HOTSPOT

  You manage a sales database that uses the full recovery model. You use the following backup schedule:

  

  The date is April 15, 2018. A developer creates a transaction named modificationtest and runs the transaction at 9:20 AM.

  You determine that the change implemented by the transaction must be undone. You immediately back up the database log to L:\Logbackup\Sales\taillog.bak using the NORECOVERY and INIT options. Then, you restore the full database backup from the previous night using the NORECOVERY option.

  You need to recover as much data as possible without recovering the change implemented by the transaction.

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

  NOTE: Each correct selection is worth one point.

  Hot Area: