Microsoft Recertification for MCSE Data Platform Set 3

You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 installed. You plan to deploy an additional server that has SQL Server 2014 installed. You need to design a high-availability solution for the databases that meets the following requirements: If SQL1 fails, the databases must be available. Users must be able to run reports against a secondary copy of the databases. What should you include in the design? More than one answer choice may achieve the goal. Select the BEST answer.

You administer a SQL Server 2014 instance. Users report that the SQL Server has seemed slow today. A large database was being restored for much of the day, which could be causing issues. You want to write a query of the system views that will report the following: Number of users that have a connection to the server Whether a user's connection is active Whether any connections are blocked What queries are being executed Whether the database restore is still executing and, if it is, what percentage of the restore is complete. Which system objects should you use in your query to best achieve this task?

You are designing a monitoring application for a new SQL Server 2014 instance. You need to recommend a solution to generate a report that displays the 10 most frequent wait types that occur for the instance. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows. You need to recommend a repeatable method to migrate the database to SQL Azure. Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You are designing a database named DB1. Changes will be deployed to DB1 every Wednesday night. You need to recommend a strategy to deploy the changes to DB1. The strategy must meet the following requirements: The strategy must not disrupt backup operations. DB1 must be online while the changes are deployed. You must be able to undo quickly any changes made to objects. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You use SQL Azure to store data used by an e-commerce application. You develop a stored procedure named sp1. Sp1 is used to read and change the price of all the products sold on the e-commerce site. You need to ensure that other transactions are blocked from updating product data while sp1 is executing. Which transaction isolation level should you use in sp1?

You review a query that runs slowly. The query accesses data in a table named Schema1.Table1. The following is the relevant portion of the execution plan for the query: You need to create the missing index. Which code segment should you execute?

You use SQL Server 2012 to maintain the data used by the applications at your company. You plan to create a table named Table1 by using the following statement. (Line numbers are included for reference only.) You need to ensure that Table1 contains a column named UserName. The UserName column will: Store string values in any language. Accept a maximum of 200 characters. Be case-insensitive and accent-insensitive. Which code segment should you add at line 03?

Your company has a SQL Azure subscription. You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1. Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1. You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1. What should you do?

You execute the following code: You create a nonclustered index named IX_CustomerName on the CustomerName column. You execute the following query: You need to reduce the amount of time it takes to execute the query. What should you do?