Prep and try our 070-458 valid and latest training questions & answers

Microsoft 070-458 Exam Overview:

Microsoft 070-458 Exam Syllabus Topics:

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 2 Sample Questions:

1. 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) Availability Groups
B) Log Shipping
C) Database Mirroring
D) Peer-to-Peer Replication

2. 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. - The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?

A) Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
B) Deploy the project by using dtutil.exe with the /COPY SQL option.
C) Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
D) Store the System::SourceID variable in the custom log table.
E) Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.*tart_execution stored procedures.
F) View the All Messages subsection of the All Executions report for the package.
G) Deploy the .ispac file by using the Integration Services Deployment Wizard.
H) Store the System::ServerExecutionID variable in the custom log table.
I) Use an event handler for OnError for each data flow task.
J) Store the System::ExecutionInstanceGUID variable in the custom log table.
K) Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
L) Use an event handler for OnTaskFailed for the package.
M) Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
N) Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
O) Deploy the project by using dtutil.exe with the /COPY DTS option.
P) View the job history for the SQL Server Agent job.
Q) Enable the SSIS log provider for SQL Server for OnError in the package control flow.
R) Use an event handler for OnError for the package.

3. 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) CDC Splitter
B) Audit
C) Pivot
D) Merge

4. You administer a Microsoft SQL Server 2012 database that has Trustworthy set to on. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
What should you do? Choose all that apply.

A) Move the stored procedure to the User1 schema.
B) Grant the VIEW SERVER STATE permission to User1.
C) Grant the db_datareader role on the database to User1.
D) Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
E) Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.

5. DRAG DROP
You are developing a SQL Server Integration Services (SSIS) package that loads data into a data warehouse hosted on Windows Azure SQL Database.
You must combine two data sources together by using the ProductID column to provide complete details for each record. The data retrieved from each data source is sorted in ascending order by the ProductID column.
You need to develop a data flow that imports the data while meeting the requirements.
How should you develop the data flow? (To answer, drag the appropriate transformation from the list of transformations to the correct location in the answer area.)

Solutions: