Section A - Multiple Choice Qs

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 deploy a database by using SQL Server 2012. The database contains a table named Table1. You need to recommend a solution that meets the following requirements: – Stores the most recent data from Table1 by using the fastest storage solution possible. – Stores the historical data from Table1 by using a slower storage solution. What should you recommend?

You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You have a SQL Server 2014 environment That contains 20 servers. The corporate security policy states that all SQL Server 2014 instances must meet specific security standards. You need to recommend a management strategy for the SQL Server 2014 servers. What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

You deploy a database by using SQL Server 2014. The database contains a table named Table1. You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required. What should you recommend?

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 are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly available. After the event, the database will be deleted. You need to recommend a solution to implement the database while minimizing costs. The solution must not affect any existing applications. What 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 unavailable to users while the changes are deployed. – You must be able to undo quickly the entire operation. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script: CREATE TABLE Sales.Orders ( OrderID int IDENTITY (1,1) NOT NULL PRIMARY KEY, OrderDate date NOT NULL, CustomerID int NOT NULL ); Each order is tracked by using one of the following statuses: – Fulfilled – Shipped – Ordered – Received You need to design the database to ensure that you can retrieve the status of an order on a given date. The solution must ensure that new statuses can be added in the future. You need to design the database to ensure that that you can retrieve the following information: ✑ The current status of an order ✑ The previous status of an order. ✑ The date when the status changed. The solution must minimize storage. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

You have two SQL Server 2014 instances named SQLDev and SQLProd. You plan to create a new database on SQLProd that will use SQL Server Authentication. You need to ensure that when the new database is copied from SQLProd to SQLDev, users can connect to the database on SQLDev if they do not have a login on the SQLDev instance. What should you use? More than one answer choice may achieve the goal. Select the BEST answer.

Your company has offices in Seattle and Montreal. The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. The servers are located in separate building within your campus. The latency of the WAN link between the buildings is less than 10 ms. You plan to implement an AlwaysOn availability group on both servers. You need to recommend a failover type for the availability group. What should you recommend?

You have a database named DB1. You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next. Occasionally, the identifying value must be reset to its initial value. You need to design a mechanism to hold the identifying values for the stored procedure to use. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

You are the new database administrator for a SQL Server 2014 instance. You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring. You want to set up monitoring to see if query performance is being affected. You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed. What should you do?

You are building a stored procedure for a SQL Azure database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following requirements: - If any of the new rows violates a table constraint, then no further additions must be attempted and all changes made by the stored procedure must be discarded. - If any errors occur, a row must be added to an audit table, and the original error must be returned to the caller of the stored procedure. What should you include in the design?

You have a SQL Server 2012 database named DB1. You plan to import a large number of records from a SQL Azure database to DB1. You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation. What should you include in the recommendation?

You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media. You plan to synchronize SQLDev and SQLProd. You need to recommend a solution that meets the following requirements: - The database schemas must be synchronized from SQLDev to SQLProd. - The database on SQLDev must be deployed to SQLProd by using a package. - The package must support being deployed to SQL Azure. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

Your company has offices in Seattle and Montreal. The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. Server1 is located in the Seattle office. Server2 is located in the Montreal office. The latency of the WAN link between the Montreal office and the Seattle office is more than 200 ms. You plan to implement an AlwaysOn availability group on both servers. You need to recommend a failover type for the availability group. What should you recommend?

You have a server that has SQL Server 2012 installed. The server contains 100 user databases. You need to recommend a backup solution for the user databases. The solution must meet the following requirements: - Perform a transaction log backup every hour. - Perform a full backup of each database every week. - Perform a differential backup of each database every day. - Ensure that new user databases are added automatically to the backup solution. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

You have a SQL Server 2012 instance named SQL1. SQL1 creates error events in the Windows Application event log. You need to recommend a solution that will run an application when SQL1 logs a specific error in the Application log. Which SQL elements should you include in the recommendation? (Each correct answer presents part of the solution. Choose all that apply.)