Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 201:

  You administer a Microsoft SQL Server database named Orders.

  Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.

  You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.

  What should you do?

  A. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.
  B. Use System Monitor to catch the Lock Waits/sec event.
  C. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
  D. Start a Trace using SQL Server Profiler to catch the Lock: Deadlock event.
  D. Start a Trace using SQL Server Profiler to catch the Lock: Deadlock event.

  ---

  The Lock:Deadlock event class is produced when an attempt to acquire a lock is canceled because the attempt was part of a deadlock and was chosen as the deadlock victim. Use the Lock:Deadlock event class to monitor when deadlocks occur and which objects are involved. You can use this information to determine if deadlocks are significantly affecting the performance of your application. You can then examine the application code to determine if you can make changes to minimize deadlocks.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/lock-deadlock-event-class

• Question 202:

  You administer a Microsoft SQL Server database.

  You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.

  You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.

  Which Transact-SQL statement should you use?

  

  A. Option A
  B. Option B
  C. Option C
  D. Option D
  C. Option C

• Question 203:

  Which of the following SQL Server 2012 tools coordinates transactions across SQL Servers in a clustered environment?

  A. SQL Server Profiler
  B. SQL Server Query Optimizer
  C. SQL Server Management Studio
  D. Microsoft Distributed Transaction Coordinator
  D. Microsoft Distributed Transaction Coordinator

  ---

  References: http://blogs.msdn.com/b/alwaysonpro/archive/2014/01/15/msdtc-recommendations-on-sql-failover-cluster.aspx

• Question 204:

  You are a database administrator for a Microsoft SQL Server environment.

  You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.

  You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.

  Which feature should you use?

  A. snapshot replication
  B. peer-to-peer replication
  C. database audits
  D. failover clustering
  B. peer-to-peer replication

  ---

  Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.

  Although peer-to-peer replication enables scaling out of read operations, write performance for the topology is like that for a single node.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-replication

• Question 205:

  Which of the following is a valid upgrade path for SQL Server 2012?

  A. SQL Server 2000 -> SQL Server 2012
  B. SQL Server 2000 -> SQL Server 2005 -> SQL Server 2012
  C. SQL Server 7 -> SQL Server 2012
  D. Oracle 11g -> SQL Server 2012
  B. SQL Server 2000 -> SQL Server 2005 -> SQL Server 2012

  ---

  References: https://technet.microsoft.com/en-us/library/ms143393%28v=sql.110%29.aspx https://technet.microsoft.com/en-us/library/ms143393%28v=sql.90%29.aspx

• Question 206:

  DRAG DROP

  You administer a Microsoft SQL Server database.

  Every Sunday afternoon, a process inserts a large volume of data. Users generate reports and modify additional data throughout the week.

  You need to create a backup strategy that:

  1. minimizes the size of the transaction log,

  2. minimizes the size of the backups, and

  3. ensures a 24-hour recovery point objective (RPO).

  Which strategy or strategies should you use? (To answer, drag the appropriate strategy or strategies to their corresponding task or tasks in the answer area. Answer choices may be used once, more than once, or not at all. Answer targets

  may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

  ---

  Recovery Model: Full recovery model Only the Full recovery model provides a 24-hour recovery point objective. Sunday backup: Full backup Nightly backup: Transactional log backup References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server

• Question 207:

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

  You administer a Microsoft SQL Server database server that hosts a transactional database and a reporting database. The transactional database is updated through a web application and is operational throughout the day. The reporting

  database is only updated from the transactional database.

  The recovery model and backup schedule are configured as shown in the following table:

  

  One of the hard disk drives that stores the reporting database fails at 23:32 hours.

  You need to ensure that the reporting database is restored. You also need to ensure that data loss is minimal.

  What should you do?

  A. Perform a page restore.
  B. Perform a partial restore.
  C. Perform a point-in-time restore.
  D. Restore the latest full backup.
  E. Restore the latest full backup. Then, restore the latest differential backup.
  F. Restore the latest full backup, and restore the latest differential backup. Then, restore the latest log backup.
  G. Restore the latest full backup, and restore the latest differential backup. Then, restore each log backup taken before the time of failure from the most recent differential backup.
  H. Restore the latest full backup. Then, restore each differential backup taken before the time of failure from the most recent full backup.
  E. Restore the latest full backup. Then, restore the latest differential backup.

  ---

  To recover the database, you must restore the latest full backup and then restore the latest differential backup.

  Incorrect Answers:

  A: A page restore is used to repair isolated damaged pages. It is faster than a file restore id only a few individual pages are damaged.

  B: Partial restores allow a database that contains multiple filegroups to be restored and recovered in stages. It works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple model.

  C: Point in time restores are not supported for the simple backup model as log files are not part of the backup.

  D: The latest full backup was performed at 01:00 hours, 22 hours and 32 minutes before the hard disk failure. A differential backup was made at 13:00 hours, 10 hours and 32 minutes before the hard disk failure. A differential backup contains

  data that has been added or updated since the last full backup and should be restored to minimize data loss.

  F, G: There is not log backup in the simple backup model.

  H: Only the latest differential backup needs to be restored.

  References: https://msdn.microsoft.com/en-us/library/ms186216%28v=sql.110%29.aspx https://technet.microsoft.com/en-us/library/ms175168%28v=sql.110%29.aspx https://msdn.microsoft.com/en-us/library/ms177425%28v=sql.110%29.aspx https://msdn.microsoft.com/en-us/library/ms175526%28v=sql.110%29.aspx

• Question 208:

  You administer a Microsoft SQL Server instance that has multiple databases. You have a two-node SQL Server failover cluster. The cluster uses a storage area network (SAN).

  You discover I/O issues. The SAN is at capacity and additional disks cannot be added.

  You need to reduce the I/O workload on the SAN at a minimal cost.

  What should you do?

  A. Move user databases to a local disk.
  B. Expand the tempdb data and log files
  C. Modify application code to use table variables
  D. Move the tempdb files to a local disk
  D. Move the tempdb files to a local disk

• Question 209:

  On which of the following operating systems can you deploy the 64-bit version of SQL Server 2012 Enterprise edition? (Choose all that apply.)

  A. Windows 7 Ultimate (x64) edition
  B. Windows Server 2008 R2 SP1 (x64) Standard edition
  C. Windows Server 2008 SP2 (x64) Enterprise edition
  D. Windows Server 2003 R2 (x64) Enterprise edition
  B. Windows Server 2008 R2 SP1 (x64) Standard edition
  C. Windows Server 2008 SP2 (x64) Enterprise edition

• Question 210:

  Which of the following features can you install if you are installing SQL Server 2012 Enterprise edition on a computer running Windows Server 2008 R2 SP1 Enterprise edition in the Server Core configuration? (Choose all that apply.)

  A. Database Engine Services
  B. SQL Server Replication
  C. Analysis Services
  D. Reporting Services
  A. Database Engine Services
  B. SQL Server Replication
  C. Analysis Services