Microsoft 70-458 Online Practice Questions and Exam Preparation

Microsoft 70-458 Online Questions & Answers

• Question 61:

  You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction.

  You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.

  You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process,

  A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE
  B. ALTER INDEX ALL ON AccountTransaction REBUILD
  C. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REBUILD
  D. CREATE INDEX IDX AccountTransactionAccountCode ON AccountTransaction.AccountCode WITH DROP EXISTING
  A. ALTER INDEX IDX_AccountTransaction_AccountCode ON AccountTransaction.AccountCode REORGANIZE

• Question 62:

  You are preparing to install SQL Server 2012 Master Data Services (MDS). You need to ensure that the database requirements are met.

  A. SQL Server 2012 Enterprise (64-bit) x64 on the database server
  B. SQL Server 2008 Enterprise (64-bit) x64 on the database server
  C. Microsoft SharePoint Server 2010 Enterprise Edition SP1
  D. Microsoft SharePoint Foundation 2010 SP1
  A. SQL Server 2012 Enterprise (64-bit) x64 on the database server

• Question 63:

  You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012.

  

  You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:

  Production transactions should be minimally affected.

  The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.

  Which Transact-SQL statement should you insert at line 06?

  

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

• Question 64:

  You administer two Microsoft SQL Server 2012 servers named ProdSrv1 and ProdSrv2. ProdSrv1 is configured as a Distributor. Both servers are configured to use the windows NT Service virtual accounts for all SQL Services.

  You are configuring snapshot replication from ProdSrv1 to ProdSrv2 by using ProdSrv2 as a pull subscriber.

  The distribution agent on ProdSrv2 regularly fails, displaying the following error message:

  "Cannot access the file. Operating system error code 5 (Access is denied.)."

  You need to configure the distribution agent by granting only the minimum required access to all accounts.

  A. Configure the SQL Server Agent service to run under the Local System account. Configure the Subscriber to use the SQL Server Agent service account.
  B. Configure the SQL Server Agent service to run under a Windows domain account. Configure the Subscriber to use the SQL Server Agent service account. Grant FULL CONTROL access for the domain account to the ReplData share on ProdSrv1.
  C. Configure the Subscriber to use the Local System account.
  D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.
  D. Configure the Subscriber to use a Windows domain account. Grant READ access for the domain account to the ReplData share on ProdSrv1.

• Question 65:

  You are developing a SQL Server Integration Services (SSIS) package to load data into a Windows Azure SQL Database database. Thepackage consists of several data flow tasks.

  The package has the following auditing requirements:

  

  If a data flow task fails, a Transact-SQL (T-SQL) script must be executed.

  

  You need to ensure that auditing is configured to meet these requirements.

  What should you do?

  A. Use an event handler for OnError for the package.
  B. Use an event handler for OnError for each data flow task.
  C. Use an event handler for OnTaskFailed for the package.
  D. View the job history for the SQL Server Agent job.
  E. View the All Messages subsection of the All Executions report for the package.
  F. Store the System::SourceID variable in the custom log table.
  G. Store the System::ServerExecutionID variable in the custom log table.
  H. Store the System::ExecutionInstanceGUID variable in the custom log table.
  I. Enable the SSIS log provider for SQL Server for OnError in the package control flow.
  J. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
  C. Use an event handler for OnTaskFailed for the package.

• Question 66:

  You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named

  ProductCode.

  Some of the data to be loaded will reference products that need special processing logic in the data flow.

  You need to enable separate processing streams for a subset of rows based on the source product code.

  A. Data Conversion
  B. Conditional Split
  C. Destination Assistant
  D. Script Task
  B. Conditional Split

• Question 67:

  You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain.

  You plan to configure database mirroring.

  You need to be able to create database mirroring endpoints on both servers.

  A. Use Kerberos authentication.
  B. Use certificates.
  C. Use SQL Authenticated Logins.
  D. Configure firewalls to allow Windows Logins.
  B. Use certificates.

• Question 68:

  You administer a Microsoft SQL Server 2012 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 S 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.

  You need to ensure that the backup size is as small as possible.

  A. Differential
  B. Transaction log
  C. FULL
  D. SIMPLE
  E. BULK_LOGGED
  F. SKIP
  G. RESTART
  H. STANDBY
  I. CHECKSUM
  J. DBO_ONLY
  B. Transaction log

• Question 69:

  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 2012 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.

  

  One of the hard disk drives chat stores the transactional database fails at 23:32 hours. Attempts to create a tail log backup are unsuccessful. You need to ensure that the transactional 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.
  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.

• Question 70:

  You administer a Microsoft SQL Server 2012 instance that contains a database of confidential data.

  You need to encrypt the database files at the page level. You also need to encrypt the transaction log files.

  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: