Free VCE and PDF Exam Dumps from PassLeader
Vendor: Microsoft Exam Code: 70-458 Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Exam Question 91 -- Question 120 Visit PassLeader and Download Full Version 70-458 Exam Dumps QUESTION 91 You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create? A. B. C. D.
SQL user without login SQL user with a custom SID SQL user with login Domain user
Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ff929071.aspx QUESTION 92 You are the lead database administrator (DBA) of a Microsoft SQL Server 2012 environment. All DBAs are members of the DOMAIN\JrDBAs Active Directory group. You grant DOMAIN\JrDBAs access to the SQL Server. You need to create a server role named SpecialDBARole that can perform the following functions: - View all databases. - View the server state. - Assign GRANT, DENY, and REVOKE permissions on logins. You need to add DOMAIN\JrDBAs to the server role. You also need to provide the least level of privileges necessary. Which SQL statement or statements should you use? Choose all that apply. A. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION setupadmin; B. ALTER SERVER ROLE [SpecialDBARole] ADD MEMBER [DOMAIN\JrDBAs]; C. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION securityadmin; 70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader D. GRANT VIEW DEFINITION TO [SpecialDBARole]; E. CREATE SERVER ROLE [SpecialDBARole] AUTHORIZATION serveradmin; F. GRANT VIEW SERVER STATE, VIEW ANY DATABASE TO [SpecialDBARole]; Answer: BCF Explanation: http://msdn.microsoft.com/en-us/library/ee677634.aspx http://msdn.microsoft.com/en-us/library/ms188659.aspx http://msdn.microsoft.com/en-us/library/ms191291.aspx QUESTION 93 You administer a Microsoft SQL Server 2012 server. You plan to deploy new features to an application. You need to evaluate existing and potential clustered and non-clustered indexes that will improve performance. What should you do? A. B. C. D.
Query the sys.dm_db_index_usage_stats DMV. Query the sys.dm_db_missing_index_details DMV. Use the Database Engine Tuning Advisor. Query the sys.dm_db_missing_index_columns DMV.
Answer: C Explanation: http://msdn.microsoft.com/en-us/library/ms174202.aspx QUESTION 94 Drag and Drop Question 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.)
Answer:
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader
Explanation: http://msdn.microsoft.com/en-us/library/bb510663.aspx http://msdn.microsoft.com/en-us/library/bb934049.aspx QUESTION 95 Drag and Drop Question You administer a Microsoft SQL Server 2012 database. The database uses SQL Server Agent jobs to perform regular FULL and LOG backups. The database uses the FULL recovery model. You plan to perform a bulk import of a very large text file. You need to ensure that the following requirements are met during the bulk operation: - The database transaction log is minimally affected. - The database is online and all user transactions are recoverable. - All transactions are fully recoverable prior to the import. 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.)
Answer:
Explanation: 70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader http://technet.microsoft.com/en-us/library/ms187048.aspx http://technet.microsoft.com/en-us/library/ms189272.aspx QUESTION 96 You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size. The Sales database is configured as shown in the following table.
You discover that Sales_2.ndf is corrupt. You need to recover the corrupted data in the minimum amount of time. What should you do? A. B. C. D.
Perform a file restore. Perform a transaction log restore. Perform a restore from a full backup. Perform a filegroup restore.
Answer: A Explanation: http://technet.microsoft.com/en-us/library/ms187048.aspx us/library/aa337540.aspx
http://msdn.microsoft.com/en-
QUESTION 97 You are developing a SQL Server Integration Services (SSIS) package to load data into a data warehouse.
You need to establish the correct order for loading each table to maximize parallel processing. Which order should you use? 70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader A. 1. DimCurrency, DimScenario, DimAccount in parallel 2. DimOrganization 3. FactFinance B. l. DimCurrency, DimOrganization in parallel 2. DimScenario, DimAccount in parallel 3. FactFinance C. 1. DimCurrency, FactFinance in parallel 2. DimOrganization, DimScenario, DimAccount in parallel D. 1. FactFinance 2. DimOrganization, DimScenario, DimAccount in parallel 3. DimCurrency E. 1. DimCurrency 2. DimOrganization 3. DimScenario, DimAccount in parallel 4. FactFinance Answer: A Explanation: http://msdn.microsoft.com/en-us/library/ms139892.aspx http://msdn.microsoft.com/en-us/library/ms141261.aspx QUESTION 98 You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night. The SQL Azure 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. B. C. D.
Change the Exhaustive property to True, Change the similarity threshold to 0.55. Change the similarity threshold to 0.40. Increase the maximum number of matches per lookup.
Answer: B Explanation: http://msdn.microsoft.com/en-us/library/ms137786.aspx QUESTION 99 Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed. You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts. What should you do? A. B. C. D. E. F.
Create a reusable custom logging component. Use the gacutil command. Use the Project Deployment Wizard. Run the package by using the dtexec /rep /conn command. Add a data tap on the output of a component in the package data flow. Create an OnError event handler.
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader G. H. I. J. K.
Use the dtutil /copy command. Deploy the package by using an msi file. Run the package by using the dtexec /dumperror /conn command. Run the package by using the dtexecui.exe utility and the SQL Log provider. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: C QUESTION 100 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. To facilitate the troubleshooting of SQL Server Integration Services (SSIS) packages, a logging methodology is put in place. The methodology has the following requirements: - The deployment process must be simplified. - All the logs must be centralized in SQL Server. - Log data must be available via reports or T-SQL. - Log archival must be automated. You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. What should you do? A. Open a command prompt and run the gacutil command. B. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. C. Add an OnError event handler to the SSIS project. D. Use an msi file to deploy the package on the server. E. Configure the output of a component in the package data flow to use a data tap. F. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. G. Open a command prompt and run the dtexec /rep /conn command. H. Open a command prompt and run the dtutil /copy command. I. Open a command prompt and run the dtexec /dumperror /conn command. J. Configure the SSIS solution to use the Project Deployment Model. K. Create a reusable custom logging component and use it in the SSIS project. Answer: B Explanation: http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx QUESTION 101 You are deploying a new SQL Server Integration Services (SSIS) project to the test environment. A package in the project uses a custom task component. You need to ensure that the custom object is deployed on the test environment correctly. What should you do? A. B. C. D. E.
Run the package by using the dtexec /rep /conn command. Create a reusable custom logging component. Create an OnError event handler. Use the gacutil command Use the dtutil /copy command.
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. G. Run the package by using the dtexec /dumperror /conn command. H. Use the Project Deployment Wizard. I. Deploy the package by using an msi file. J. Add a data tap on the output of a component in the package data flow. K. Run the package by using the dtexecui.exe utility and the SQL Log provider. Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ms403356.aspx QUESTION 102 You are deploying a project to the SQL Server Integration Services (SSIS) catalog. You need to ensure that the project is deployed to the production environment with the least amount of administrative effort. What should you do? A. B. C. D. E. F. G. H. I. J.
Create an On Error event handler. Add a data tap on the output of a component in the package data flow. Use the gacutil command. Create a reusable custom logging component. Run the package by using the dtexecui.exe utility and the SQL Log provider. Run the package by using the dtexec /rep /conn command. Use the Project Deployment Wizard. Run the package by using the dtexec /dumperror /conn command. Use the dtutil /copy command. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. K. Deploy the package by using an msi file. Answer: G Explanation: http://msdn.microsoft.com/en-us/library/hh479588.aspx http://msdn.microsoft.com/en-us/library/hh213290.aspx http://msdn.microsoft.com/en-us/library/hh213373.aspx QUESTION 103 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. B. C. D. E.
Add a data tap on the output of a component in the package data flow. Deploy the package by using an msi file. Run the package by using the dtexecui.exe utility and the SQL Log provider. Uses the dtutil /copy command. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader F. G. H. I. J. K.
Create an OnError event handler. Uses the Project Deployment Wizard. Use the gacutil command. Create a reusable custom logging component. Run the package by using the dtexec /rep /conn command. Run the package by using the dtexec /dumperror /conn command.
Answer: A Explanation: http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/hh230989.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx QUESTION 104 You are a database administrator for a Microsoft SQL Server 2012 instance. You need to ensure that data can be migrated from a production server to two reporting servers with minimal data latency. You also need to ensure that data on the reporting server is always accessible. Which solution should you use? A. B. C. D.
Change Data Capture Database snapshot Availability Groups Log Shipping
Answer: C QUESTION 105 You use a contained database named ContosoDb within a domain. You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations. Which type of user should you create? A. B. C. D.
SQL user with login SQL user without login Domain user User mapped to an asymmetric key
Answer: A QUESTION 106 You administer a Microsoft SQL Server 2012 database. You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01. You need to configure the availability group to minimize transaction latency on any available secondary databases. In the event of a database failure, the designated secondary database should come online automatically. Which Transact-SQL statement should you use?
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader
A. B. C. D.
Option A Option B Option C Option D
Answer: D Explanation: http://msdn.microsoft.com/en-us/library/ff877931.aspx QUESTION 107 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 implementing a SQL Server Integration Services (SSIS) package that loads data hosted in a SQL Azure database into a data warehouse. The source system contains redundant or inconsistent data. When the package finds invalid data, the row containing the invalid data must be omitted but it must also be written to a text file for further analysis. You need to establish the best technique to log these invalid rows while keeping the amount of development effort to a minimum. What should you do? A. B. C. D. E. F. G.
Add an OnError event handler to the SSIS project. Use an msi file to deploy the package on the server. Open a command prompt and run the gacutil command. Open a command prompt and run the dtutil /copy command. Open a command prompt and run the dtexec /rep /conn command. Open a command prompt and run the dtexec /dumperror /conn command. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. H. Create a reusable custom logging component and use it in the SSIS project. I. Configure the SSIS solution to use the Project Deployment Model. J. Configure the output of a component in the package data flow to use a data tap. K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. Answer: J Explanation: http://technet.microsoft.com/en-us/library/hh230989.aspx http://www.rafael-salas.com/2012/01/ssis-2012-quick-peek-to-data-taps.html http://msdn.microsoft.com/en-us/library/ms162820.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx 70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader http://technet.microsoft.com/en-us/library/ms140223.aspx http://msdn.microsoft.com/en-us/library/jj655339.aspx QUESTION 108 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. To ease the debugging of packages, you standardize the SQL Server Integration Services (SSIS) package logging methodology. The methodology has the following requirements: - Centralized logging in SQL Server - Simple deployment - Availability of log information through reports or T-SQL ?Automatic purge of older log entries - Configurable log details You need to configure a logging methodology that meets the requirements while minimizing the amount of deployment and development effort. What should you do? A. B. C. D. E. F. G. H. I. J. K.
Use the gacutil command. Use the dtutil /copy command. Use the Project Deployment Wizard. Create an OnError event handler. Create a reusable custom logging component. Run the package by using the dtexec /rep /conn command. Run the package by using the dtexec /dumperror /conn command. Run the package by using the dtexecui.exe utility and the SQL Log provider. Add a data tap on the output of a component in the package data flow. Deploy the package by using an msi file. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: H Explanation: http://msdn.microsoft.com/en-us/library/ms140246.aspx http://msdn.microsoft.com/en-us/library/hh231187.aspx QUESTION 109 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 developing a SQL Server Integration Services (SSIS) package. To process complex scientific data originating from a SQL Azure database, a custom task component is added to the project. You need to ensure that the custom component is deployed on a test environment correctly. What should you do? A. B. C. D. E. F.
Add an OnError event handler to the SSIS project. Use an msi file to deploy the package on the server. Open a command prompt and run the gacutil command. Open a command prompt and run the dtutil /copy command. Open a command prompt and run the dtexec /rep /conn command. Open a command prompt and run the dtexec /dumperror /conn command.
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader G. Open a command prompt and execute the package by using the SQL Log provider and running the dtexecui.exe utility. H. Create a reusable custom logging component and use it in the SSIS project. I. Configure the SSIS solution to use the Project Deployment Model. J. Configure the output of a component in the package data flow to use a data tap. K. Run the dtutil command to deploy the package to the SSIS catalog and store the configuration in SQL Server. Answer: C Explanation: http://msdn.microsoft.com/en-us/lib QUESTION 110 You are designing a data warehouse that uses SQL Server 2012. You are preparing to update the contents of a fact table that contains a non-clustered columnstore index. You need to run an update statement against the table. What should you do first? A. B. C. D.
Change the recovery model of the database to Bulk-logged. Pause the columnstore index. Change the non-clustered columnstore index to be a clustered columnstore index. Drop the columnstore index.
Answer: D QUESTION 111 You administer a Microsoft SQL Server 2012 database named Contoso that contains a single userdefined database role namedBillingUsers. All objects in Contoso are in the dbo schema. You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?
A. B. C. D.
Option A Option B Option C Option D
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader Answer: D QUESTION 112 You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory. Users need to be able to SELECT from all tables in the database and execute the stored procedure. You need to grant only the necessary permissions. What should you do? A. Add EXECUTE AS OWNER to the stored procedure. Add all users to the db_datareader role. B. Add all users to the db_datawriter role. Add all users to the db_datareader role. C. Create a new database role. Grant EXECUTE permission on p_AddInventory to the newrole. Add all users to the role. Add all users to the db_datareader role. D. Create a new database role. Grant EXECUTE permission on p_AddInventory to the new role. Grant VIEW DEFINITION to to the role. Add all users to the role. Answer: A Explanation: In SQL Server you can define the execution context of the following user- defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module. QUESTION 113 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. B. C. D.
a domain user the SQLBrowser account the BUILTIN\SYSTEM account a local user with Run as Service permissions
Answer: A QUESTION 1014 You administer a Microsoft SQL Server 2012 database instance. You plan to migrate the database to Windows Azure SQL Database. You verify that all objects contained in the database are compatible with Windows Azure SQL Database. You need to ensure that database users and required server logins are migrated to Windows Azure SQL Database. What should you do? A. B. C. D.
Back up the database from the local server and restore it to Windows Azure SQL Database. Use the Copy Database wizard. Use the Database Transfer wizard. Use SQL Server Management Studio to deploy the database to Windows Azure SQL Database.
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader Answer: D QUESTION 115 You are a database administrator for a Microsoft SQL Server 2012 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. B. C. D.
peer-to-peer replication snapshot replication failover clustering database audits
Answer: A QUESTION 116 You are a database administrator for a Microsoft SQL Server 2012 database named AdventureWorks2012. You create an Availability Group defined by the following schema. (Line numbers are included for reference only.)
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?
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader
A. B. C. D. E.
Option A Option B Option C Option D Option E
Answer: A QUESTION 117 You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2012 Standard Edition. The server hosts a database named Orders. Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmented. You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation. Which Transact-SQL command should you use?
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader A. B. C. D.
Option A Option B Option C Option D
Answer: C QUESTION 118 You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting. Which Transact-SQL command should you use?
A. B. C. D.
Option A Option B Option C Option D
Answer: A QUESTION 119 Drag and Drop Question 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 from a Windows batch file. - 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. 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.)
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader
Answer:
QUESTION 120 Drag and Drop Question You are building a fact table In a data warehouse. The table must have a columnstore index. The table cannot be partitioned. You need to design the fact table and load it with data. 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.)
Answer:
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps
Free VCE and PDF Exam Dumps from PassLeader
Visit PassLeader and Download Full Version 70-458 Exam Dumps
70-458 Exam Dumps
70-458 Exam Questions
70-458 PDF Dumps
http://www.passleader.com/70-458.html
70-458 VCE Dumps