Study with 070-458 Actual Test Questions

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.

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. Partition the table by day and store.
• B. Run the package in Performance mode.
• C. Create a clustered index on the Transaction table.
• D. Reduce the value of the Maximum Insert Commit Size property.

You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Create a package parameter named ServerName in each package.
• B. Set the Required property of the parameter to True.
• C. Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].
• D. Create a project parameter named ServerName.
• E. Edit each package Connection Manager. Set the ServerName property to @[$Project::ServerParam].
• F. Set the Sensitive property of the parameter to True.

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. Modify the stored procedure to include the EXECUTE AS OWNER statement. Grant VIEW SERVER STATE permissions to the owner of the stored procedure.
• B. Grant the VIEW SERVER STATE permission to User1.
• C. Create a SQL Server login that has VIEW SERVER STATE permissions. Modify the stored procedure to include the EXECUTE AS {newlogin} statement.
• D. Grant the db_datareader role on the database to User1.
• E. Move the stored procedure to the User1 schema.

DRAG DROP
You administer several Microsoft SQL Server 2012 servers. Your company has a number
of offices across the world connected by using a wide area network (WAN).
Connections between offices vary significantly in both bandwidth and reliability.
You need to identify the correct replication method for each scenario.
What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)