Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 11:

  You administer a Microsoft SQL Server 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 C.
  B. Build a new cluster, shut down the current instance, and then migrate to the new cluster.
  C. Run the New SQL Server stand-alone installation Wizard on Node C.
  D. Use Node B to install SQL Server on Node C.
  C. Run the New SQL Server stand-alone installation Wizard on Node C.

  ---

  To add a node to an existing SQL Server failover cluster, you must run SQL Server Setup on the node that is to be added to the SQL Server failover cluster instance. Do not run Setup on the active node.

  The Installation Wizard will launch the SQL Server Installation Center. To add a node to an existing failover cluster instance, click Installation in the left-hand pane. Then, select Add node to a SQL Server failover cluster.

  References:

  https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/add-or-remove-nodes-in-a-sql-server-failover-cluster-setup?view=sql-server-2017

• Question 12:

  You administer a Microsoft SQL Server 2012 database named ContosoDB. The database contains a table named Suppliers and a column named IsActive in the Purchases schemA.

  You create a new user named ContosoUser in ContosoDB. ContosoUser has no permissions to the Suppliers table.

  You need to ensure that ContosoUser can delete rows that are not active from Suppliers. You also need to grant ContosoUser only the minimum required permissions.

  Which Transact-SQL statement should you use?

  

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

  ---

  Incorrect:

  Not B: The dbo , or database owner, is a user account that has implied permissions to perform all activities in the database. This would go against the requirement to grant ContosoUser only the minimum required permissions.

  References:

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

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

• Question 13:

  DRAG DROP

  You administer a Microsoft SQL Server database.

  You want to import data from a text file to the database.

  You need to ensure that the following requirements are met:

  Data import is performed by using a stored procedure.

  Data is loaded as a unit and is minimally logged.

  Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or

  recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)

  Select and Place:

  

  

• Question 14:

  DRAG DROP

  You are a database administrator for a Microsoft SQL Server 2012 environment that contains two SQL Server instances named Server01 and Server02. The Contoso database is located on Server01. Through database mirroring, a copy of

  the Contoso database is on Server02.

  During the last manual failover of database mirroring, the recovery took longer than usual.

  You need to determine where the synchronization bottleneck is for the database mirroring topology.

  Which performance counter or counters should you use for each server? (To answer, drag the appropriate performance counter or counters to their corresponding server or servers 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:

  

  

• Question 15:

  Which of the following is NOT a potential Cluster resource when configuring clustering in SQL Server 2012?

  A. SQL Server Database Engine Service
  B. SQL Server Agent Service
  C. SQL Server Table Service
  D. All of the above
  D. All of the above

• Question 16:

  You administer a Microsoft SQL Server database. You want to make a full backup of the database to a file on disk.

  In doing so, you need to output the progress of the backup.

  Which backup option should you use?

  A. STATS
  B. COMPRESSION
  C. CHECKSUM
  D. IN IT
  A. STATS

• Question 17:

  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. Each question is independent of the other questions inthis series. Information and details provided in a question apply only to that question.

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

  You need to ensure that the following requirements are met:

  1.

  The database remains available if a data center outage occurs.

  2.

  Client computers do not require configuration changes if the redundant instance is activated.

  3.

  Failover to either server occurs by using the minimum amount of administrative effort.

  Which configuration should you use?

  A. SQL Server that includes an application database configured to perform snapshot replication.
  B. SQL Server that includes an application database configured to perform transactional replication.
  C. Two servers configured in different data centers. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode.
  D. Two servers configured on the same subnet. SQL Server Availability Group configured in Synchronous-Commit Availability Mode.
  E. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  F. 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
  G. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
  H. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  C. Two servers configured in different data centers. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode.

  ---

  We must use two different data centers, and the Synchronous-Commit Availability Mode should be used to avoid data loss.

  Note: Synchronous-commit replicas support two settings-automatic or manual. The "automatic" setting supports both automatic failover and manual failover. To prevent data loss, automatic failover and planned failover require that the failover

  target be a synchronous-commit secondary replica with a healthy synchronization state (this indicates that every secondary database on the failover target is synchronized with its corresponding primary database).

  Incorrect Answers:

  A, B, D, E, F, G: We must use two different data centers

  C: Asynchronous-Commit Availability Mode might result in data loss in case of failover.

  Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups

• Question 18:

  You administer a Microsoft SQL Server 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. Use Node A to install SQL Server on Node C.
  B. Use Cluster Administrator to add a new Resource Group to Node B.
  C. Use Node to install SQL Server on Node C.
  D. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node C.
  E. Use Node to install SQL Server on Node C.
  F. Build a new cluster, shut down the current instance, and then migrate to the new cluster.
  G. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node A.
  D. Create a ConfigurationFile.ini file from Node B, and then run the AddNode command-line tool on Node C.

  ---

  References: https://docs.microsoft.com/en-us/sql/database-engine/install-windows/install-sql-server-using-a-configuration-file

• Question 19:

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

  You need to ensure that data can be replicated from a production server to two reporting servers in real time.

  You also need to ensure that data on the reporting server is always accessible.

  Which solution should you use?

  A. Availability Groups
  B. Extended Events
  C. Snapshot Replication
  D. Policy Based Management
  A. Availability Groups

  ---

  The AlwaysOn Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. Introduced in SQL Server 2012, AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, that fail over together. An availability group supports a set of read-write primary databases and one to four sets of corresponding secondary databases. Optionally, secondary databases can be made available for read-only access and/or some backup operations.

  An availability group fails over at the level of an availability replica. Failovers are not caused by database issues such as a database becoming suspect due to a loss of a data file, deletion of a database, or corruption of a transaction log.

  References: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2012/hh510230(v=sql.110)

• Question 20:

  You are a database developer for an application hosted on a Microsoft SQL Server 2012 server. The database contains two tables that have the following definitions:

  

  Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders. Which Transact-SQL query do you use?

  

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