Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 301:

  DRAG DROP

  You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.

  You plan to provide a reporting solution by using a third node in the cluster.

  You need to add the third node. The solution must prevent any impact on the performance of database writes.

  You install another server that has SQL Server installed.

  Which three additional 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: Add a new availability replica in asynchronous mode.

  Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.

  Incorrect: Add a new availability replica in synchronous mode.

  Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.

  Step 2: Set Readable Secondary to Read-intent only.

  For the secondary role, select a new value from the Readable secondary drop list, as follows: 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.

  Step 3: Configure read-only routing.

  Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running

  under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify

  the application intent as "read-only." That is, they must be read-intent connection requests.

  References: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-for-an-availability-group-sql-server

  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server

• Question 302:

  You administer a Microsoft SQL Server 2012 server.

  When transaction logs grow, SQL Server must send an email message to the database administrators.

  You need to configure SQL Server to send the email messages.

  What should you configure?

  A. SQL Mail
  B. An Extended Events session
  C. Alerts and operators in SQL Server Agent
  D. Policies under Policy-Based Management
  C. Alerts and operators in SQL Server Agent

• Question 303:

  HOTSPOT

  You have a server named SQL1 that hosts a reporting database named REPORTING. REPORTING contains data that is also stored in a production database.

  You discover many blocking locks in REPORTING. The blocked queries are SELECT statements. The queries that hold the blocking locks are INSERT statements that wait on the WRITELOG wait type.

  You need to prevent the INSERT statements from creating the blocking locks. The solution must also minimize the amount of time the INSERT statements wait on the WRITELOG wait type.

  Which two database settings should you modify? To answer, select the appropriate settings in the answer area.

  Hot Area:

  

  

  ---

  Is Read Committed Snapshot On: True

  Snapshot isolation enhances concurrency for OLTP applications.

  Understanding Snapshot Isolation and Row Versioning Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.

  The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.

  References: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

• Question 304:

  DRAG DROP

  You are configuring a Microsoft SQL Server 2012 server and Setting up a new database.

  You need to ensure that the following requirements are met:

  Provide access to the database for a Windows group named CORP/Employees.

  The database can be moved to another server with no additional changes to the security configuration of the database or server.

  What should you do? (To answer, move the appropriate action or actions from the list of actions to the answer area and arrange them in the correct order.)

  Select and Place:

  

  

  ---

  Step 1: Use sp_configure to change the contained database authentication setting to 1.

  Use the contained database authentication option to enable contained databases on the instance of SQL Server Database Engine.

  The following example enables contained databases on the instance of the Database Engine.

  sp_configure 'contained database authentication', 1;

  Step 2: Create the database and set the Containment Setting to Partial

  When a database is created the "Containment type" should be set to "partial" to make the database a contained database, as shown below.

  Step 3: Create a contained database user named CORP/Employeed in the database

  Since contained database users are database level principals, you need to create contained database users in every database that you would use them.

  References:

  https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/contained-database-authentication-server-configuration-option

  https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

• Question 305:

  Your database contains a table named SalesOrders. The table includes a DATETIME column named OrderTime that stores the date and time each order is placed. There is a non-clustered index on the OrderTime column. The business team wants a report that displays the total number of orders placed on the current day.

  You need to write a query that will return the correct results in the most efficient manner.

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  D. OptionD

• Question 306:

  HOTSPOT

  You administer a Microsoft SQL Server 2012 database instance.

  The instance is running on a server with the following configuration:

  1TB RAM

  SAN storage for database and log files

  4 quad-core processors

  64-Bit Windows 2008R2 operating system

  This instance hosts a database with large partitioned tables. Users report that complex queries are taking a long time to complete.

  While troubleshooting, you discover that CPU utilization is low (less than 20 percent), disk activity is low (little or no waiting processes), and no significant blocking is occurring.

  You need to ensure that the instance can process queries as quickly and efficiently as possible.

  Which Setting should you configure?

  Hot Area:

  

  

• Question 307:

  Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.

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

  You need to write messages to the Application Log when users are added to or removed from a fixed server role in Server01.

  What should you create?

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

  ---

  Reference: http://technet.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

• Question 308:

  You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.

  The reference data to be used in the transformation does not change.

  You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.

  What should you do?

  A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
  B. Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
  C. Select the DropExistingMatchIndex option in the Fuzzy Lookup Transformation Editor.
  D. Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
  E. Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
  A. Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.

  ---

  References: http://msdn.microsoft.com/en-us/library/ms137786.aspx

• Question 309:

  You have installed the SQL Server Integration Services (SSIS) feature on a server running Windows Server 2008 R2 SP1. Which of the following features must you install if you want to ensure that you can run packages in 32-bit mode?

  A. Client Tools SDK
  B. Data Quality Client
  C. SQL Server Data Tools
  D. Client Tools Backwards Compatibility
  C. SQL Server Data Tools

• Question 310:

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

  You discover performance issues when complex calculations are performed on large amounts of data under heavy system load.

  You need to limit the number of cores that handle the processing.

  What should you configure?

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