Microsoft 70-458 real exam prep : Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2

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,
Which Transact-SQL batch should you use?

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

You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest-matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value. The current setting for the Fuzzy Lookup similarity threshold is 0.50.
Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?

• A. Change the similarity threshold to 0.40.
• B. Change the similarity threshold to 0.55.
• C. Change the Exhaustive property to True.
• D. Decrease the maximum number of matches per lookup.

DRAG DROP
You administer three Microsoft SQL Server 2012 servers named ServerA, Servers, and ServerC.
ServerA is the acting principal and ServerB is the mirror.
You need to add ServerC as a witness to the existing mirroring session between ServerA and ServerB. You need to achieve this goal without delaying synchronization.
Which three 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.)

Note: This question is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You are writing a SQL Server Integration Services (SSIS) package that transfers data from a legacy system.
Data integrity in the legacy system is very poor. Invalid rows are discarded by the package but must be logged to a CSV file for auditing purposes.
You need to establish the best technique to log these invalid rows while minimizing the amount of development effort.
What should you do?

• A. Run the package by using the dtexecui.exe utility and the SQL Log provider.
• B. Create a reusable custom logging component.
• C. Deploy the package to the Integration Services catalog by using dtutfl and use SQL Server to store the configuration.
• D. Deploy the package by using an msi file.
• E. Add a data tap on the output of a component in the package data flow.
• F. Use the Project Deployment Wizard.
• G. Run the package by using the dtexec /dumperror /conn command.
• H. Create an OnError event handler.
• I. Use the gacutil command.
• J. Run the package by using the dtexec /rep /conn command.
• K. Use the dtutil /copy command.

You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster.
Which user account should you use?

• A. a domain user
• B. the BUILTIN\SYSTEM account
• C. a local user with Run as Service permissions
• D. the SQLBrowser account