Microsoft Recertification for MCSE: Data Platform - 070-469 Exam Practice Test

DRAG DROP
You plan to deploy three highly available SQL Server environments that will use SQL Server 2014.
You identify the following specifications for each environment as shown following table.

You need to recommend which high-availability feature is required for each environment.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.

DRAG DROP
You need to recommend a backup process for data warehouse database.
The solution must meet the following requirements:
- Ensure that if a hardware failure occurs, you can bring the database online without
losing more than 24 hours of transactions. - Minimize the amount of administrative effort required to restore any lost data. - Minimize the space used by transaction logs.
What should you include in the recommendation?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

You need to recommend a solution to allow application users to perform tables. The solution must meet the business requirements.
What should you recommend?

You need to ensure that a new execution plan is used by usp_GetOrdersByProduct each time the stored procedure runs.
What should you do?

You need to identify the cause of the page split issues. Which SQL Server feature should you use?

You have a SQL Server 2012 database named Database1.
Database1 has a table named Customers. Customers contains more than 1 million rows.
The database has a stored procedure that was created by using the following script:

You need to ensure that up_customers returns rows when the following statement is executed:
EXECUTE up_customers'1,2,3,4,5';
What should you do?

You need to recommend a solution to minimize the amount of time it takes to execute USP_1.
With what should you recommend replacing Table1?

---
You have a SQL Server 2012 instance named SQL\Instance1. Instance1 contains a database named Database1.
You need to recommend an index defragmentation solution for an index named ContentIndex. ContentIndex must meet the following requirements:
Remain online during the defragmentation.
Update distribution statistics.
Perform defragmentation as quickly as possible.
Which type of index defragmentation solution should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

You plan to modify a stored procedure to use temporary data.
The stored procedure must meet the following requirements:
- Favor physical memory when physical memory is available.
- Be able to roll back changes to the temporary data.
You need to recommend which object to add to the stored procedure.
Which T-SQL command should you recommend?

You have a Microsoft SQL Azure database. You have the following stored procedure:

You discover that the stored procedure periodically fails to update Person.Contact.
You need to ensure that Person.Contact is always updated when UpdateContact executes. The solution must minimize the amount of time required for the stored procedure to execute and the number of locks held.
What should you do?