Microsoft 70-462 Online Practice Questions and Exam Preparation

Microsoft 70-462 Online Questions & Answers

• Question 41:

  You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

  

  You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

  

  

  Which Transact-SQL query should you use?

  

  A. OptionA
  B. OptionB
  C. OptionC
  D. OptionD
  E. OptionE
  F. OptionF
  G. OptionG
  H. OptionH
  E. OptionE

  ---

  For XML clause in Select Query allows you to return the query results in XML format. You can specify Auto, RAW, EXPLICIT and PATH in For XML clause.

  Example:

  

  Incorrect:

  Not F: The following line is incomplete (a 1 need to be appended to end of it):

  WHERE Customers.CustomerId =

  References: http://www.techbubbles.com/sql-server/for-xml-auto-mode-example-in-sql-server/

• Question 42:

  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 sp_who2 stored procedure into a table.
  B. Use System Monitor to catch the Lock Wait Time event.
  C. Start a trace using SQL Server Profiler to catch the Lock: Timeout event.
  D. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
  D. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.

  ---

  The Blocked Process Report event class indicates that a task has been blocked for more than a specified amount of time. This event class does not include system tasks or tasks that are waiting on non deadlock-detectable resources.

  To configure the threshold and frequency at which reports are generated, use the sp_configure command to configure the blocked process threshold option, which can be set in seconds. By default, no blocked process reports are produced.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/blocked-process-report-event-class?view=sql-server-2017

• Question 43:

  Which of the following recovery models must be in use for point-in-time recovery to be possible for an SQL Server 2012 database?

  A. Differential
  B. Full
  C. Simple
  D. ArchiveLog
  B. Full

  ---

  References: https://msdn.microsoft.com/en-us/library/ms189275%28v=sql.110%29.aspx

• Question 44:

  You want to remove SQL Server Integration Services from a server running the Windows Server 2008 R2 operating system that also has the Database Engine and SQL Server Analysis Services installed. Which of the following tools can you use to accomplish this goal?

  A. SQL Server Management Studio
  B. SQL Server Configuration Manager
  C. Add/Remove Programs in Control Panel
  D. SQL Server Installation Center
  C. Add/Remove Programs in Control Panel

  ---

  In the control panel go to Microsoft SQL Server and double click. You are prompted to Add, Repair or Remove. Select remove and step through to the features selection. Choose SSIS and complete the uninstall. References: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ffd6cc5b-0397-45ea-8aec-830fea6c78c8/how-to-remove-integration-services-feature-from-ms-sql-server-2012?forum=sqlintegrationservices

• Question 45:

  If you are restoring and recovering a database, which of the following keywords must you specify if you intend to roll forward a transaction log backup after restoring a database?

  A. NOFINISH
  B. NORECOVERY
  C. TRANSACTIONLOG RECOVER
  D. NEXTLOG
  B. NORECOVERY

  ---

  References: https://msdn.microsoft.com/en-us/library/ms186858%28v=sql.110%29.aspx

• Question 46:

  You develop a database for a travel application.

  You need to design tables and other database objects.

  You create a stored procedure.

  You need to supply the stored procedure with multiple event names and their dates as parameters.

  What should you do?

  A. Use the CAST function.
  B. Use the DATE data type.
  C. Use the FORMAT function.
  D. Use an appropriate collation.
  E. Use a user-defined table type.
  F. Use the VARBINARY data type.
  G. Use the DATETIME data type.
  H. Use the DATETIME2 data type.
  I. Use the DATETIMEOFFSET data type.
  J. Use the TODATETIMEOFFSET function.
  E. Use a user-defined table type.

• Question 47:

  DRAG DROP

  You administer a Windows Azure SQL Database database used for data warehouse operations.

  The database contains a table named OrdersHistory, defined as follows:

  

  A weekly ETL (extract-transform-load) runs a large INSERT statement to add data into the OrdersHistory table. The process is taking a long time to complete. You discover that the bulk of the process is performing non-clustered index

  updates on the OrdersHistory table.

  You need to improve the performance of the ETL process faster. You need to meet the following requirements:

  Avoid losing existing permissions on existing objects.

  Use minimal administrative effort.

  What should you do? (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: Run the following statement:

  ALTER INDEX ALL on OrdersHistory DISABLE.

  Step 2: Run the INSERT statement.

  Step 3: Run the following statement:

  ALTER INDEX ALL on OrdersHistory REBUILD

  The ALTER INDEX ALL REBUILD statement rebuilds and enables all disabled indexes on the table.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/indexes/disable-indexes-and-constraints

• Question 48:

  DRAG DROP

  You administer a Microsoft SQL Server database. Service accounts for SQL Agent are configured to use a local user.

  A Microsoft SQL Server Integration Services (SSIS) job step has been created within a SQL Server Agent job.

  The SSIS package accesses a network share when exporting data from a SQL Server database.

  When you execute the SQL Server Agent job, it fails due to a permissions failure on a share on a remote server. You need to ensure that the SQL Server Agent job can execute the SSIS package.

  Which four 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:

  

  

  ---

  SQL Server Agent allows creating a proxy account which defines the security context for the job step.

  We need to do is to create a credential to be used by the proxy account.

  The created proxy need to have access to the SSIS subsystem

  References: https://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/

• Question 49:

  You administer all the deployments of Microsoft SQL Server 2012 in your company. You have two servers in the same data center that hosts your production database.

  You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs.

  You also need to maintain transactional consistency of the data across both servers.

  You need to achieve these goals without manual intervention.

  Which configuration should you use?

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

  ---

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

• Question 50:

  You administer a Microsoft SQL Server instance that contains a financial database hosted on a storage area network (SAN).

  The financial database has the following characteristics:

  A data file of 2 terabytes is located on a dedicated LUN (drive D).

  A transaction log of 10 GB is located on a dedicated LUN (drive E).

  Drive D has 1 terabyte of free disk space.

  Drive E has 5 GB of free disk space.

  The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.

  The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.

  These data load operations must occur in the minimum amount of time.

  A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.

  On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.

  You need to perform a full database backup that will be restored on the development server.

  Which backup option should you use?

  A. NORECOVERY
  B. FULL
  C. NO_CHECKSUM
  D. CHECKSUM
  E. Differential
  F. 8ULK_LOGGED
  G. STANDBY
  H. RESTART
  I. SKIP
  J. Transaction log
  L

  ---

  A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored. However, occasionally, it is

  useful to take a backup for a special purpose without affecting the overall backup and restore procedures for the database. Copy-only backups serve this purpose.

  Incorrect:

  Not J:A transaction log update would not be a good option after a huge amount of data has been updated with the BULK INSERT command.

  References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/copy-only-backups-sql-server