70-465 Exam
How Does Exambible Microsoft 70-465 exam answers Work?
Exam Code: 70-465 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Designing Database Solutions for Microsoft SQL Server 2012
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-465 Exam.
Q1. 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.
A. Max Degree of Parallelism
B. Resource Governor
C. Windows System Resource Manager (WSRM)
D. Processor affinity
Answer: D
Q2. You need to recommend a solution to minimize the amount of time it takes to execute sp1. With what should you recommend replacing Table1?
A. a table variable
B. a function
C. a view
D. a temporary table
Answer: A
Q3. You need to recommend a change to sp3 to ensure that the procedure completes only if all of the UPDATE statements complete.
Which change should you recommend?
A. Set the XACT_ABORT option to on.
B. Set the IMPLICIT_TRANSACTIONS option to off.
C. Set the IMPLICIT_TRANSACTIONS option to on.
D. Set the XACT_ABORT option to off
Answer: C
Q4. DRAG DROP
You plan to deploy SQL Server 2012.
Your company identifies the following monitoring requirements:
-Tempdb must be monitored for insufficient free space.
-Deadlocks must be analyzed by using Deadlock graphs.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Answer:
Q5. You need to recommend a solution for the planned changes to the customer classifications.
What should you recommend? (Each correct answer presents part of the solution. Choose all that apply.)
A. Implement change data capture.
B. Add a table to track any changes made to the classification of each customer.
C. Add columns for each classification to the Customers table.
D. Add a row to the Customers table each time a classification changes.
E. Add a column to the Classifications table to track the status of each classification.
Answer: AC
Topic 4, Litware, Inc Case B
Overview
You are a database administrator for a company named Litware, Inc. Litware is a book publishing house.
Litware has a main office and a branch office.
You are designing the database infrastructure to support a new web-based application that is being developed. The web application will be accessed at www.litwareinc.com. Both internal employees and external partners will use the application.
You have an existing desktop application that uses a SQL Server 2005 database named App1_DB. App1_Db will remain in production.
Requirements
Planned Changes
You plan to deploy a SQL Server 2012 instance that will contain two databases named Databasel and Database2.
Database1 will contain two tables named Orders and OrderDetails. Databasel will also contain a stored procedure named usp_UpdateOrderDetails. The stored procedure is used to update order information. The stored procedure queries the Orders table twice each time the procedure executes. Each query against the Orders table must return the same data. All statements in the stored procedure run in a single transaction.
Database1 will contain several queries that access data in the Database2 tables.
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 will contain a table named Inventory. Inventory will contain over 100 GB of data.
The Inventory table will have two indexes: a clustered index on the primary key and a nonclustered index. The column that is used as the primary key will use the identity property.
Database2 will contain a stored procedure named usp_UpdateInventory. Usp_UpdateInventory will manipulate several small data sets of up to 100 rows each. Each data set will be used multiple times.
Data from Database2 will be accessed periodically by an external application named Application!. The data from Database2 will be sent to a database named App1_Db1 as soon as changes occur to the data in Database2.
Litware plans to use offsite storage for all SQL Server 2012 backups.
Business Requirements
You have the following requirements:
-Costs for new equipment must be minimized.
-External access to the databases must be encrypted.
-Development effort must be minimized whenever possible.
-System administrators must be notified when disk space falls below a certain threshold.
-The databases must be available if the SQL Server service or the storage solution fails.
-Database administrators must receive a detailed report that contains allocation errors and data corruption.
-Application developers must be denied direct access to the database tables. Applications must be denied direct access to the tables. -You must encrypt the backup files to meet regulatory compliance requirements. The encryption strategy must minimize changes to the databases and to the applications.
Q6. You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script:
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.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
A. To the Sales.Orders table, add a column named Status that will store the order status. Update the Status column as the order status changes.
B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
C. Implement change data capture on the Sales.Orders table.
D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.
Answer: B
Q7. DRAG DROP
You plan to deploy SQL Server 2012.
Your company identifies the following monitoring requirements for the database:
-An e-mail message must be sent when a user logs in.
-An e-mail message must be sent if CPU utilization exceeds 90 percent.
You need to identify which feature meets each monitoring requirement.
Which features should you identify?
To answer, drag the appropriate feature to the correct monitoring requirement in the answer area.
Answer:
Q8. You need to recommend a disaster recovery solution for the Dev database.
What should you include in the recommendation?
A. The simple recovery model and full backups
B. The full recovery model, full backups, and transaction log backups
C. The full recovery model, full backups, and differential backups
D. The bulk-logged recovery model and full backups
Answer: A
Q9. 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 2012 installed.
You plan to deploy an additional server that has SQL Server 2012 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.
A. Database mirroring
B. Log shipping
C. Failover Clustering
D. AlwaysOn availability groups
Answer: D
Q10. You need to recommend a solution that addresses the concurrency requirement.
What should you recommend?
A. Call the stored procedures in a Distributed Transaction Coordinator (DTC) transaction.
B. Modify the stored procedures to update tables in the same order for all of the stored procedures.
C. Make calls to Sales.Proc1 and Sales.Proc2 synchronously.
D. Break each stored procedure into two separate procedures, one that changes Sales.Table1 and one that changes Sales.Table2.
Answer: B