[Improve] examcollection 70-458

Exam Code: 70-458 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-458 Exam.

2016 Apr 70-458 Study Guide Questions:

Q52. You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses 

the SQL Server change data capture (CDC) feature. 

You have added a CDC Source component to the data flow to read changed data from the source system. 

You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations. 

Which data flow transformation should you use? 

A. Merge Join 

B. DQS Cleansing 

C. Pivot 

D. CDC Splitter 

Answer: C 


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

The recovery model and backup schedule are configured as shown in the following table: 


One of the hard disk drives that stores the reporting database fails at 16:40 hours. 

You need to ensure that the reporting 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. 

Answer: E 


Q54. 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. Change Data Capture 

B. Database snapshot 

C. Availability Groups 

D. Log Shipping 

Answer: C 


Q55. You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined 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. Option A 

B. Option B 

C. Option C 

D. Option D 

Answer: D 


Q56. You are preparing to install SQL Server 2012 Master Data Services (MDS), 

You need to ensure that the database requirements are met. 

What should you install? 

A. Microsoft SharePoint Server 2010 Enterprise Edition SP1 

B. SQL Server 2012 Data Center (64-bit) x64 on the database server 

C. SQL Server 2012 Enterprise (64-bit) x64 on the database server 

D. SQL Server 2012 Standard (64-bit) x64 on the database server 

Answer: C 

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

What should you do? 

A. Use Kerberos authentication. 

B. Use certificates. 

C. Use SQL Authenticated Logins. 

D. Configure firewalls to allow Windows Logins. 

Answer: B 


70-458 practice

Renew exam 70-458 book:

Q57. HOTSPOT 

You are designing a package control flow. The package moves sales order data from a Windows Azure SQL Database transactional database to an on-premise reporting database. The package will run several times a day, while new sales orders are being added to the transactional database. 

The current design of the package control flow is shown in the answer area. (Click the Exhibit button.) 


The Insert New Orders Data Flow task must meet the following requirements: 

Usage of the tempdb database should not be impacted. 

Concurrency should be maximized, while only reading committed transactions. 

If the task fails, only that task needs to be rolled back. 

You need to configure the Insert New Orders Data Flow task to meet the requirements. 

How should you configure the transaction properties? To answer, select the appropriate setting or settings in the answer area. 



Answer: 



Q58. HOTSPOT 

You are developing a SQL Server Integration Services (SSIS) package. The data source for the data flow task is a table that has been configured as a change data capture (CDC) table. You are using a CDC Source component to obtain the CDC data. 

The data source will be polled once per hour. The data is updated with multiple important status changes per minute. 

For each captured data change, the before and after values must be included.You need to configure the CDC Source component. 

Which CDC processing mode should you select? (To answer, configure the appropriate option in the dialog box in the answer area.) 




Answer: 



Q59. HOTSPOT 

You are developing a SQL Server Integration Services (SSIS) package. 

The package control flow will contain many tasks. The tasks will execute consecutively and none will execute more than once. Certaingroups of tasks will share variable and transaction scope. 

You need to group tasks together while enabling them to be collapsed and expanded as a group. 

Which item should you use from the SSIS Toolbox? (To answer, select the appropriate item in the answer area.) 


Answer: 



Q60. 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. Database Mirroring 

B. Peer-to-Peer Replication 

C. Log Shipping 

D. Availability Groups 

Answer: B 


Q61. 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 SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named userA is a member of a role named Sales. 

UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema. 

You need to ensure that the following requirements are met: 

The Sales role does not have the Select permission on the Customers schema.UserA has the Select permission on the Regions table. 

Which Transact-SQL statement should you use? 

A. DENY SELECT ON Object::Regions FROM Sales 

B. DENY SELECT OH Schema:: Customers FROM Soles 

C. REVOKE SELECT ON Object::Regions FROM Soles 

D. REVOKE SELECT ON Schema:Customers FROM Soles 

E. DENY SELECT ON Object::Regions FROM UserA 

F. DENY SELECT ON Schema:Customers FROM UserA 

G. REVOKE SELECT ON Object::Regions FROM UserA 

H. REVOKE SELECT ON Schema::Customers FROM UserA 

I. EXEC sp_oddrolemember 'Sales', 'UserA' 

J. EXEC sp_droprolemember 'Sales', 'UserA' 

Answer: D 


70-458 actual test

Certified 458 lott vs 45-70 govt:

Q62. RAG DROP 

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: 



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

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

B. Transaction log 

C. FULL 

D. SIMPLE 

E. BULK_LOGGED 

F. SKIP 

G. RESTART 

H. STANDBY 

I. CHECKSUM 

J. DBO_ONLY 

K. COPY_ONLY 

L. NORECOVERY 

M. NO_CHECKSUM 

N. CONTINUE_AFTER_ERROR 

Answer: K 


Q64. HOTSPOT 

You are developing a SQL Server Integration Services (SSIS) package. You use a Data Profiling task to examine the data from a source system. You need to establish: 

. The minimum and maximum dates for the datetime columns in the source data . The minimum, maximum, and average values for numeric columns in the source data 

You need to use the appropriate profile type in the Data Profiling task. 

Which profile type should you use? (To answer, select the appropriate profile type in the answer area.) 



Answer: 



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

What should you do? 

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. 

Answer: D 


Q66. RAG DROP 

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: 



Q67. 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 deploying a new SQL Server Integration Services (SSIS) package to several servers. 

The package must meet the following requirements: 

. INET Common Language Runtime (CLR) integration in SQL Server must not be enabled. . The Connection Managers used in the package must be configurable without editing the package. . The deployment procedure must be automated as much as possible. 

You need to set up a deployment strategy that meets the requirements. 

What should you do? 

A. Use the gacutil command. 

B. use the dtutll /copy command. 

C. use the Project Deployment Wizard. 

D. create an OnError event handler. 

E. create a reusable custom logging component. 

F. Run the package by using the dtexec /rep /conn command. 

G. Run the package by using the dtexec /dumperror /conn command. 

H. Run the package by using the dtexecui.exe utility and the SQL Log provider. 

I. Add a data tap on the output of a component in the package data flow. 

J. Deploy the package by using an msi file. 

K. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration. 

Answer: B 


Q68. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode. 

The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes. 

You need to minimize the package execution time. 

What should you do? 

A. Reduce the value of the Maximum Insert Commit Size property. 

B. Partition the table by day and store. 

C. Create a clustered index on the Transaction table. 

D. Run the package in Performance mode. 

Answer: B 

Explanation: Partitioning the table would increase the large parallel imports. 



see more 70-458 - Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2

Microsoft 70-458 Certification Sample Questions and Answers: http://www.braindumpsall.net/70-458-dumps/

P.S. New 70-458 dumps PDF: http://www.4easydumps.com/70-458-dumps-download.html