70-465 Exam
Top Microsoft 70-465 answers Choices
Master the 70-465 Designing Database Solutions for Microsoft SQL Server 2012 content and be ready for exam day success quickly with this Examcollection 70-465 exam fees. We guarantee it!We make it a reality and give you real 70-465 questions in our Microsoft 70-465 braindumps.Latest 100% VALID Microsoft 70-465 Exam Questions Dumps at below page. You can use our Microsoft 70-465 braindumps and pass your exam.
Q1. You need to recommend a change to sp3 to ensure that the procedure continues to execute even if one of the UPDATE statements fails.
Which change should you recommend?
A. Set the XACT_ABORT option to off.
B. Set the XACT_ABORT option to on.
C. Set the IMPLICIT_TRANSACTIONS option to on.
D. Set the IMPLICIT_TRANSACTIONS option to off.
Answer: A
Q2. 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
Q3. 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.
A. One-way encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EFS)
D. Reversible encryption
Answer: A
Q4. 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
Q5. DRAG DROP
You need to recommend the actions that are required to partition a table.
In which order should the four actions be performed?
To answer, move the actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Q6. DRAG DROP
You are designing a database for a university.
The database will contain two tables named Classes and Enrollment that have the following specifications:
-Classes will store brochures in the XPS format. The brochures must be structured in folders and must be accessible by using UNC paths.
-Enrollment will store information about students and their classes. Performance must be enhanced for queries of the current enrollments.
You need to identify which SQL Server technology meets the specifications of each table.
Which technologies should you identify?
To answer, drag the appropriate technology to the correct table in the answer area.
Answer:
Q7. 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?
A. An implicit transaction that has XACT_ABORT enabled
B. An explicit transaction that has XACT_ABORT disabled
C. An implicit transaction that has error handling enabled
D. An explicit transaction that has error handling enabled
Answer: D
Q8. 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.)
A. A policy
B. A trigger
C. A maintenance plan
D. A job
E. An alert
Answer: CE
Q9. You need to recommend a solution that meets the data recovery requirement.
What should you include in the recommendation?
A. A database snapshot
B. A transaction log backup
C. Snapshot isolation
D. A differential backup
Answer: A
Topic 3, A. Datum Case A
General Overview
A. Datum Corporation has offices in Miami and Montreal.
The network contains a single Active Directory forest named adatum.com. The offices connect to each other by using a WAN link that has a 5-ms latency.
A. Datum standardizes its database platform by using SQL Server 2012 Standard edition.
Databases
Each office contains databases named Sales, Inventory, Customers, Products, Personnel, and Dev.
Servers and databases are managed by a team of database administrators. Currently, all of the database administrators have the same level of permissions on all of the servers and all of the databases.
The Customers database contains two tables named Customers and Classifications. The following graphic shows the relevant portions of the tables:
The following table shows the current data in the Classifications table:
The Inventory database is used mainly for reports. The database is recreated every day. A full backup of the database currently takes three hours to complete.
Stored Procedures
A stored procedure named sp1 generates millions of rows of data for multiple reports. Sp1 combines data from five different tables from the Sales and Customers databases in a table named Table1.
After Table1 is created, the reporting process reads data from a table in the Products database and searches for information in Table1 based on input from the Products table. After the process is complete, Table1 is deleted.
A stored procedure named sp2 is used to generate a product list. Sp2 takes several minutes to run due to locks on the tables the procedure accesses.
A stored procedure named sp3 is used to update prices. Sp3 is composed of several UPDATE statements called in sequence from within a transaction. Currently, if one of the UPDATE statements fails, the stored procedure continues to execute.
A stored procedure named sp4 calls stored procedures in the Sales, Customers, and Inventory databases. The nested stored procedures read tables from the Sales, Customers, and Inventory databases. Sp4 uses an EXECUTE AS clause.
A stored procedure named sp5 changes data in multiple databases. Security checks are performed each time sp5 accesses a database.
You suspect that the security checks are slowing down the performance of sp5.
All stored procedures accessed by user applications call nested stored procedures. The nested stored procedures are never called directly.
Design Requirements
Data Recovery
You must be able to recover data from the Inventory database if a storage failure occurs. You have a Recovery Point Objective (RPO) of one hour.
You must be able to recover data from the Dev database if data is lost accidentally. You have a Recovery Point Objective (RPO) of one day.
Classification Changes
You plan to change the way customers are classified. The new classifications will have four levels based on the number of orders. Classifications may be removed or added in the future.
Management requests that historical data be maintained for the previous classifications.
Security
A group of junior database administrators must be able to view the server state of the SQL Server instance that hosts the Sales database. The junior database administrators will not have any other administrative rights.
Q10. DRAG DROP
You plan to deploy SQL Server 2012.
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.
Answer: