Microsoft Recertification for MCSE: Data Platform - 070-469 Exam Practice Test
You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company's data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
Correct Answer: D
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
DRAG DROP
You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
- Prevent data read by USP_1 from being modified by other active processes. - Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
- Prevent data read by USP_1 from being modified by other active processes. - Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
Correct Answer:
You have a SQL Server 2012 database named Database1. Database1 contains a table named OrderDetails.
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
For a given sales order, you need to retrieve the OrderID, Quantity, and LineTotal columns for all of the items in the OrderDetails table. The solution must ensure that the results can be joined to other tables.
Which code segment should you execute?
Correct Answer: B
Vote an answer
You need to recommend an isolation level for usp_UpdateOrderDetails.
Which isolation level should recommend?
Which isolation level should recommend?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
DRAG DROP
You plan to deploy SQL Server 2014.
You identify the following security requirements for the deployment:
- Users must be prevented from intercepting and reading the T-SQL statements sent from the clients to the database engine. - All database files and log files must be encrypted if the files are moved to another disk on another server.
You need to identify which feature meets each security requirement. The solution must minimize processor overhead.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
You plan to deploy SQL Server 2014.
You identify the following security requirements for the deployment:
- Users must be prevented from intercepting and reading the T-SQL statements sent from the clients to the database engine. - All database files and log files must be encrypted if the files are moved to another disk on another server.
You need to identify which feature meets each security requirement. The solution must minimize processor overhead.
Which features should you identify?
To answer, drag the appropriate feature to the correct requirement in the answer area.
Correct Answer:
DRAG DROP
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.
Which upgrade steps should you recommend?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database contents are updated once a month.
You need to upgrade the database to SQL Server 2014. The solution must minimize downtime.
Which upgrade steps should you recommend?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
You need to modify usp_SelectEmployeesByName to support server-side paging. The solution must minimize the amount of development effort required.
What should you add to usp_SelectEmployeesByName?
What should you add to usp_SelectEmployeesByName?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You need to implement a solution that solves the performance issues of
usp_GetOrdersAndItems.
Which statements should you execute?
usp_GetOrdersAndItems.
Which statements should you execute?
Correct Answer: A
Vote an answer
You have an application that uses a view to access data from multiple tables.
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
You need to ensure that you can insert rows into the underlying tables by using the view.
What should you do?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Developers report that usp_UpdateSessionRoom periodically returns error 3960.
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?
You need to prevent the error from occurring. The solution must ensure that the stored procedure returns the original values to all of the updated rows.
What should you configure in Procedures.sql?
Correct Answer: D
Vote an answer
You need to identify which long running transactions use an index.
Which dynamic management view should you use?
Which dynamic management view should you use?
Correct Answer: B
Vote an answer
You have a Microsoft SQL Azure database that contains a table named Customers.
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
You have a table-valued function named TopCustomers that returns a list of all the customers that have purchased items during the last 12 months. The ID of the customer is passed as an argument to the TopCustomers function.
You need to create a query that returns a list of all the Customer names and the purchase dates.
The solution must return only customers that have purchased an item during the last 12 months.
What should you add to the query?
Correct Answer: B
Vote an answer
You plan to create a database that has multiple tables. The tables will contain product information. Each product has a stock-keeping unit (SKU).
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
You need to recommend a solution to ensure that each SKU starts with the letters "ADV" and is followed by 10 digits.
The solution must minimize the amount of development effort required.
What should you include in the recommendation?
Correct Answer: A
Vote an answer
DRAG DROP
You need to create the usp.AssignUser stored procedure.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
You need to create the usp.AssignUser stored procedure.
Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Correct Answer:
You need to recommend a solution for Application1 that meets the security requirements. What should you include in the recommendation?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).